<definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../xsd/reports.xsd">
<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 <= CURRENT_TIMESTAMP
AND (
SUBLIFECYCLESTATETODATE > 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 <= CURRENT_TIMESTAMP
AND (
BTEXPIRATIONDATE > 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> | 