<dashboardcomponent<definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboardsreports.xsd">
	<name>Rewards Participants Signups</name>
	<description>Rewards Participants Signups Bar Chart</description>
	<ejb>CRMUIRewardDashboard</ejb>
	<method>loadParticipantsSignups</method>
	<type>bar</type>
	<orientation>horizontal</orientation>
	<primaryfield>REWARDPARTICIPANTS.REWPARTID</primaryfield>
	<primaryfieldlabel>Number of Rewards Participants</primaryfieldlabel>
	<groupbyfield>MONTH_SIGN_UP_DATE_FILTER</groupbyfield>
	<summarypage>rewards/participants</summarypage>
</dashboardcomponent>
    <name>Subscriptions Life Cycle State</name>
    <description>Subscriptions Life Cycle State</description>
    <entityTable>SUBSCRIPTIONS</entityTable>
    <entityField>SUBID</entityField>
    <dataset></dataset>
    <joins>
        INNER JOIN ACCOUNTSRECEIVABLE ON ACCOUNTSRECEIVABLE.ACCRECID = SUBSCRIPTIONS.ACCRECID
        INNER JOIN CONTACTINFORMATION ON CONTACTINFORMATION.CIID = ACCOUNTSRECEIVABLE.CIID
        INNER JOIN CONTACTINFODEFINITIONS ON CONTACTINFODEFINITIONS.CIDEFACTIVE = 1 AND CONTACTINFODEFINITIONS.CIDEFDELETED = 0
        LEFT JOIN SUBSCRIPTIONTYPES ON SUBSCRIPTIONTYPES.SUBTYPEID = SUBSCRIPTIONS.SUBTYPEID
        LEFT JOIN (
            SELECT LIFECYCLESTATE, SUBID
            FROM SUBLIFECYCLESTATEHISTORY
            WHERE SUBLIFECYCLESTATEHISTORY.SUBLIFECYCLESTATEDELETED = 0 AND SUBLIFECYCLESTATEFROMDATE &lt;= CURRENT_TIMESTAMP
            AND (
            SUBLIFECYCLESTATETODATE &gt; CURRENT_TIMESTAMP
            OR SUBLIFECYCLESTATETODATE IS NULL
            )
        ) CURRENTSTATE ON CURRENTSTATE.SUBID = SUBSCRIPTIONS.SUBID
        LEFT JOIN (
            SELECT BTAGREEMENTDATE, BTBINDINGSTARTDATE, BTBINDINGENDDATE, BTSCHEMECODE, BTBINDINGPERIOD, BTBILLFREQUENCY, SUBID, PRICEPLANID
            FROM BILLINGTERMS
            WHERE BTEFFECTIVEDATE &lt;= CURRENT_TIMESTAMP
            AND (
            BTEXPIRATIONDATE &gt; CURRENT_TIMESTAMP
            OR BTEXPIRATIONDATE IS NULL
            )
        ) CURRENTBILLINGTERMS ON CURRENTBILLINGTERMS.SUBID = SUBSCRIPTIONS.SUBID
        LEFT JOIN OUGROUPS ON OUGROUPS.OUGID=SUBSCRIPTIONS.OWNEROUGID
        LEFT JOIN CONTACTINFOADDRESSES ON CONTACTINFOADDRESSES.CIADDRESSID = SUBSCRIPTIONS.CIADDRESSID
        LEFT JOIN PRICEPLANS ON PRICEPLANS.PRICEPLANID = CURRENTBILLINGTERMS.PRICEPLANID
    </joins>
    <conditions>
        SUBSCRIPTIONS.SUBDELETED = 0 AND CURRENTSTATE.LIFECYCLESTATE NOT IN ( 'DRAFT', 'REGRETTED', 'CANCELLED')
    </conditions>
    <template>A00_A4_Landscape_With_SubReport</template>
    <subReport>  
        <name>subscriptionServicesSubReport</name>
        <folder>Subscriptions</folder>
    </subReport>
    <criteria>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Subscription Type</label>
            <extralabel>Subscription Type</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>SUBSCRIPTIONS</entitytablename>
            <entityfieldname>SUBTYPEID</entityfieldname>
            <displaytable>SUBSCRIPTIONTYPES</displaytable>
            <displaylabel>Name</displaylabel>
            <displayfield>SUBTYPENAME</displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>SUBTYPEID</sourcealias>
            <displayalias>SUBTYPENAME</displayalias>
            <datasetname>subscriptions.loadtypes</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>1</multiselect>
            <isMandatory>1</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Life Cycle State</label>
            <extralabel>Life Cycle State</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>CURRENTSTATE</entitytablename>
            <entityfieldname>LIFECYCLESTATE</entityfieldname>
            <displaytable></displaytable>
            <displaylabel>Life Cycle State</displaylabel>
            <displayfield></displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>LIFECYCLESTATE</sourcealias>
            <displayalias></displayalias>
            <datasetname></datasetname>
            <lookupname>ejb/CRMUISubscription.getReportLifeCycleStateOptions:All</lookupname>
            <isMandatory>0</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Effective Date</label>
            <extralabel>Effective Date</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>SUBSCRIPTIONS</entitytablename>
            <entityfieldname>SUBEFFECTIVEDATE</entityfieldname>
            <displaytable></displaytable>
            <displaylabel></displaylabel>
            <displayfield></displayfield>
            <fieldtype>ftXDateTime</fieldtype>
            <fieldsize>-1</fieldsize>
            <sourcealias></sourcealias>
            <displayalias></displayalias>
            <datasetname></datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>0</multiselect>
            <isMandatory>1</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Billing Term Scheme</label>
            <extralabel>Billing Term Scheme</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>CURRENTBILLINGTERMS</entitytablename>
            <entityfieldname>BTSCHEMECODE</entityfieldname>
            <displaytable>CURRENTBILLINGTERMS</displaytable>
            <displaylabel>Code</displaylabel>
            <displayfield>BTSCHEMENAME</displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>BTSCHEMECODE</sourcealias>
            <displayalias>BTSCHEMENAME</displayalias>
            <datasetname>billing.loadschemes</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>1</multiselect>
            <isMandatory>0</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Billing Frequency</label>
            <extralabel>Billing Frequency</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>CURRENTBILLINGTERMS</entitytablename>
            <entityfieldname>BTBILLFREQUENCY</entityfieldname>
            <displaytable>CURRENTBILLINGTERMS</displaytable>
            <displaylabel>Code</displaylabel>
            <displayfield>BTDEFLOOKUPLABEL</displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>BTDEFLOOKUPNAME</sourcealias>
            <displayalias>BTDEFLOOKUPLABEL</displayalias>
            <datasetname>billing.loadbillingfrequencies</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>0</multiselect>
            <isMandatory>0</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Binding Period</label>
            <extralabel>Binding Period</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>CURRENTBILLINGTERMS</entitytablename>
            <entityfieldname>BTBINDINGPERIOD</entityfieldname>
            <displaytable>CURRENTBILLINGTERMS</displaytable>
            <displaylabel>Binding Period</displaylabel>
            <displayfield>BTDEFLOOKUPLABEL</displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>BTDEFLOOKUPNAME</sourcealias>
            <displayalias>BTDEFLOOKUPLABEL</displayalias>
            <datasetname>billing.loadbindingperiods</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>0</multiselect>
            <isMandatory>0</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Price Plan</label>
            <extralabel>Price Plan</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>CURRENTBILLINGTERMS</entitytablename>
            <entityfieldname>PRICEPLANID</entityfieldname>
            <displaytable>CURRENTBILLINGTERMS</displaytable>
            <displaylabel>Name</displaylabel>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>PRICEPLANID</sourcealias>
            <displayalias>PRICEPLANNAME</displayalias>
            <datasetname>priceplan.load</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect></multiselect>
            <isMandatory>0</isMandatory>
        </criterion>
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Area</label>
            <extralabel>Area</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>CONTACTINFOADDRESSES</entitytablename>
            <entityfieldname>CIADDAREA</entityfieldname>
            <displaytable>CONTACTINFOADDRESSES</displaytable>
            <displaylabel>Area</displaylabel>
            <displayfield>CIADDAREA</displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>CIADDAREA</sourcealias>
            <displayalias>CIADDAREA</displayalias>
            <datasetname>contactinfo.loadactiveaddresses</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>1</multiselect>
        </criterion>
 
        <criterion>
            <criteriatype>0</criteriatype>
            <label>Owned by Group</label>
            <extralabel>Owned by Group</extralabel>
            <tooltip></tooltip>
            <hibernatefield></hibernatefield>
            <entitytablename>OUGROUPS</entitytablename>
            <entityfieldname>OUGID</entityfieldname>
            <displaytable>OUGROUPS</displaytable>
            <displaylabel>Name</displaylabel>
            <displayfield>OUGNAME</displayfield>
            <fieldtype>ftXString</fieldtype>
            <fieldsize>32</fieldsize>
            <sourcealias>OUGID</sourcealias>
            <displayalias>OUGNAME</displayalias>
            <datasetname>networkmanagement.loadgroups</datasetname>
            <lookupname></lookupname>
            <treename></treename>
            <multiselect>1</multiselect>
        </criterion>
    </criteria>
    <columns>
        <column>
            <fieldname>SUBSCRIPTIONS.SUBID</fieldname>
            <alias>PRIMARY_KEY</alias>
            <hidden>1</hidden>
        </column>
        <column>
            <fieldname>SUBNUM</fieldname>
            <width>8</width>
            <widthType>PERCENTAGE</widthType>
            <header>Number</header>
        </column>
        <column>
            <fieldname>ACCOUNTSRECEIVABLE.ACCRECNUM || ' - ' ||
            CASE
            WHEN CONTACTINFORMATION.CITYPE = 'COMPANY'
            THEN CONTACTINFORMATION.CICOMPANYNAME
            WHEN CONTACTINFODEFINITIONS.CIDEFNAMEGENRULE = 1
            THEN CONTACTINFORMATION.CIFIRSTNAME || ' ' || CONTACTINFORMATION.CILASTNAME
            WHEN CONTACTINFODEFINITIONS.CIDEFNAMEGENRULE = 2
            THEN CONTACTINFORMATION.CILASTNAME|| ' ' || CONTACTINFORMATION.CIFIRSTNAME
            WHEN CONTACTINFODEFINITIONS.CIDEFNAMEGENRULE = 3
            THEN CONTACTINFORMATION.CIFIRSTNAME || ' ' || COALESCE (CONTACTINFORMATION.CIMIDDLENAME || ' ','') || '' || CONTACTINFORMATION.CILASTNAME
            WHEN CONTACTINFODEFINITIONS.CIDEFNAMEGENRULE = 4
            THEN CONTACTINFORMATION.CILASTNAME|| ' ' || COALESCE (CONTACTINFORMATION.CIMIDDLENAME || ' ','') || '' || CONTACTINFORMATION.CIFIRSTNAME
            END
            </fieldname>
            <alias>ACCOUNTSRECEIVABLE</alias>
            <width>9</width>
            <widthType>PERCENTAGE</widthType>
            <header>Accounts Receivable</header>
        </column>
        <column>
            <fieldname>SUBTYPENAME</fieldname>
            <header>Type</header>
            <width>8</width>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
            <fieldname>BTSCHEMECODE</fieldname>
            <header>Billing Term Scheme</header>
            <width>8</width>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
            <fieldname>PRICEPLANNAME</fieldname>
            <width>8</width>
            <header>Price Plan</header>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
            <fieldname>BTBILLFREQUENCY</fieldname>
            <header>Billing Frequency</header>
            <width>8</width>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
            <fieldname>BTBINDINGPERIOD</fieldname>
            <header>Binding Period</header>
            <width>8</width>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
            <fieldname>CIADDAREA</fieldname>
            <width>8</width>
            <header>Area</header>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
            <fieldname>OUGNAME</fieldname>
            <width>8</width>
            <header>Owned by Group</header>
            <widthType>PERCENTAGE</widthType>
        </column>
        <column>
                <fieldname>
                (
                SELECT MIN (SUBLIFECYCLESTATEFROMDATE)
                FROM SUBLIFECYCLESTATEHISTORY
                WHERE   SUBLIFECYCLESTATEHISTORY.SUBID = SUBSCRIPTIONS.SUBID
                AND SUBLIFECYCLESTATEDELETED = 0
                AND LIFECYCLESTATE = 'EFFECTIVE'
            )
                </fieldname>
                <alias>EFFECTIVEDATE</alias>
                <width>8</width>
                <widthType>PERCENTAGE</widthType>
                <header>Effective Date</header>
                    <fieldtype>FTXDATE</fieldtype>
            </column>
        <column>
            <fieldname>
            case when CURRENTSTATE.LIFECYCLESTATE='DRAFT' then 'Draft'
                 when CURRENTSTATE.LIFECYCLESTATE='EFFECTIVE' then 'Effective'
                 when CURRENTSTATE.LIFECYCLESTATE='NOT_EFFECTIVE' then 'Not Effective'
                 when CURRENTSTATE.LIFECYCLESTATE='REGRETTED' then 'Regretted'
                 when CURRENTSTATE.LIFECYCLESTATE='SWAPPED' then 'Swapped'
                 when CURRENTSTATE.LIFECYCLESTATE='REMOVED' then 'Removed'
                 when CURRENTSTATE.LIFECYCLESTATE='IN_RESTING' then 'In Resting'
                 when CURRENTSTATE.LIFECYCLESTATE='CANCELLED' then 'Cancelled'
            end
            </fieldname>
            <alias>LIFECYCLESTATE</alias>
            <header>Life Cycle State</header>
            <widthType>PERCENTAGE</widthType>
            <width>8</width>
        </column>
    </columns>
    <groups>
        <group>
            <groupid>SubscriptionType</groupid>
            <sortorder>SUBTYPENAME</sortorder>
            <groupbyheader>Subscription Type</groupbyheader>
            <header>
                <column>
                    <fieldname>SUBTYPENAME</fieldname>
                    <width>50</width>
                    <widthType>PERCENTAGE</widthType>
                    <header>Subscription Type:</header>
                </column>
            </header>
            <footer>
                <column>
                    <fieldname>=COUNT({SUBNUM})</fieldname>
                    <header>Number of Subscriptions:</header>
                    <width>40</width>
                    <widthType>PERCENTAGE</widthType>
                </column>
            </footer>
        </group>
        <group>
            <groupid>BillingTermScheme</groupid>
            <sortorder>BTSCHEMECODE</sortorder>
            <groupbyheader>Billing Term Scheme</groupbyheader>
            <header>
                <column>
                    <fieldname>BTSCHEMECODE</fieldname>
                    <width>50</width>
                    <widthType>PERCENTAGE</widthType>
                    <header>Billing Term Scheme:</header>
                </column>
            </header>
            <footer>
                <column>
                    <fieldname>=COUNT({SUBNUM})</fieldname>
                    <header>Number of Subscriptions:</header>
                    <width>40</width>
                    <widthType>PERCENTAGE</widthType>
                </column>
            </footer>
        </group>
        <group>
            <groupid>Area</groupid>
            <sortorder>CIADDAREA</sortorder>
            <groupbyheader>Area</groupbyheader>
            <header>
                <column>
                    <fieldname>CIADDAREA</fieldname>
                    <width>50</width>
                    <widthType>PERCENTAGE</widthType>
                    <header>Area:</header>
                </column>
            </header>
            <footer>
                <column>
                    <fieldname>=COUNT({SUBNUM})</fieldname>
                    <header>Number of Subscriptions:</header>
                    <width>40</width>
                    <widthType>PERCENTAGE</widthType>
                </column>
            </footer>
        </group>
        <group>
            <groupid>OwnedByGroup</groupid>
            <sortorder>OUGNAME</sortorder>
            <groupbyheader>Owned By Group</groupbyheader>
            <header>
                <column>
                    <fieldname>OUGNAME</fieldname>
                    <width>50</width>
                    <widthType>PERCENTAGE</widthType>
                    <header>Owned by Group:</header>
                </column>
            </header>
            <footer>
                <column>
                    <fieldname>=COUNT({SUBNUM})</fieldname>
                    <header>Number of Subscriptions:</header>
                    <width>40</width>
                    <widthType>PERCENTAGE</widthType>
                </column>
            </footer>
        </group>
        <group>
            <groupid>LifeCycleState</groupid>
            <sortorder>LIFECYCLESTATE</sortorder>
            <groupbyheader>Life Cycle State</groupbyheader>
            <header>
                <column>
                    <fieldname>
                    case when CURRENTSTATE.LIFECYCLESTATE='DRAFT' then 'Draft'
                         when CURRENTSTATE.LIFECYCLESTATE='EFFECTIVE' then 'Effective'
                         when CURRENTSTATE.LIFECYCLESTATE='NOT_EFFECTIVE' then 'Not Effective'
                         when CURRENTSTATE.LIFECYCLESTATE='REGRETTED' then 'Regretted'
                         when CURRENTSTATE.LIFECYCLESTATE='SWAPPED' then 'Swapped'
                         when CURRENTSTATE.LIFECYCLESTATE='REMOVED' then 'Removed'
                         when CURRENTSTATE.LIFECYCLESTATE='IN_RESTING' then 'In Resting'
                         when CURRENTSTATE.LIFECYCLESTATE='CANCELLED' then 'Cancelled'
                    end
                    </fieldname>
                    <width>50</width>
                    <widthType>PERCENTAGE</widthType>
                    <header>Life Cycle State:</header>
                </column>
            </header>
            <footer>
                <column>
                    <fieldname>=COUNT({SUBNUM})</fieldname>
                    <header>Number of Subscriptions:</header>
                    <width>40</width>
                    <widthType>PERCENTAGE</widthType>
                </column>
            </footer>
        </group>
    </groups>
    <footers>
        <column>
            <fieldname>=COUNT({SUBNUM})</fieldname>
            <header>Total Number of Subscriptions:</header>
            <width>40</width>
            <widthType>PERCENTAGE</widthType>
        </column>
    </footers>
</definition>