Prepay Biller

The examples creates a series of memory stores named StaticStore, CDRsStore, AccountCDRsStore, AccountSummariesStore, AuthsStore, AccountAuthStore, and AccountAuthsMinsStore.

CREATE  MEMORY  STORE StaticStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;

 CREATE  MEMORY  STORE CDRsStore PROPERTIES  INDEXTYPE ='tree',  INDEXSIZEHINT =8;

The example creates two input windows named Accounts and CallPlans, and an output window named AccountPlans, all of which reference StaticStore. AccountPlans creates a join between Accounts and CallPlans using their call plan and plan type values.

CREATE  OUTPUT  WINDOW AccountPlans
SCHEMA (AccountId INTEGER, MonthlyRate FLOAT, 
    PlanMinutes FLOAT, AddlMinutesRate FLOAT, PrepaidTotal FLOAT)
PRIMARY KEY (AccountId)
 STORE StaticStore
 AS 
SELECT  Accounts.AccountID  AS AccountId,  CallPlans.MonthlyRate  AS MonthlyRate,  
	CallPlans.PlanMinutes  AS PlanMinutes,  CallPlans.AddlMinutesRate  AS AddlMinutesRate,
    Accounts.PrepaidTotal  AS PrepaidTotal 
  FROM Accounts JOIN CallPlans
 ON Accounts.CallPlan = CallPlans.CallPlanType;

The example creates an input window named CDRs that references CDRsStore, and an output window named AccountSummariesJoin that references AccountCDRsStore. CDRs refers to call data records. AccountSummariesJoin creates a join between CDRs and AccountPlans using their bill type code (BillTypCd) and account ID values.

The example creates an output window named AccountSummaries that summarizes AccountSummariesStore. AccountSummaries uses SELECT and FROM clauses to pull data from AccountSummariesJoin, and groups the data by account plan ID.

CREATE  OUTPUT  WINDOW AccountSummaries
SCHEMA (AccountId INTEGER, MonthlyRate FLOAT, TotalRatedUsage FLOAT, TotalMinutes FLOAT, CallCount INTEGER)
 PRIMARY KEY DEDUCED 
 STORE AccountSummariesStore
 AS 
SELECT AccountSummariesJoin.AccountPlansAccountId AS AccountId, 
	AccountSummariesJoin.AccountPlansMonthlyRate AS MonthlyRate, 
	(( ( (sum(AccountSummariesJoin.CDRsCallDuration) > AccountSummariesJoin.AccountPlansPlanMinutes) ) *AccountSummariesJoin.AccountPlansAddlMinutesRate) * (sum(AccountSummariesJoin.CDRsCallDuration) -AccountSummariesJoin.AccountPlansPlanMinutes)) AS TotalRatedUsage,
	sum(AccountSummariesJoin.CDRsCallDuration) AS TotalMinutes, 
	count(AccountSummariesJoin.CDRsCallDuration) AS CallCount
FROM AccountSummariesJoin
GROUP BY AccountSummariesJoin.AccountPlansAccountId;

The example creates an output window named AccountAuthsMinsJoin that references AccountAuthsStore. AccountAuthsMinsJoin creates a join between AccountPlans and AccountSummaries using their bill type and account ID values.

The example creates an output window named AccountAuthsMins that references AccountAuthsMinsStore. AccountAuthsMins uses SELECT and FROM clauses to pull data from AccountAuthsMinsJoin, and groups the data by account plan ID.

The example concludes by attaching File XML Input adapters to Accounts, CallPlans, CDRs, and Auths.