Billing report

This report groups all the billing data by each account.

REPORT COLUMNS

COlumn name	Source DB field	Description
Account Number	COR_ACCOUNT.ACC_NUM	Account Number of customers for the billing records.
Status	COR_BILLING.BILL_STATUS	Defines the status of the bill. Can be one of the following values: `P` - Paid `U` - Unbilled `A` - Active `D` - Soft delete
Value	COR_BILLING.BILL_VALUE	Actual price incurred on this bill.
Credit/Debit	COR_BILLING.CREDIT_DEBIT	Credit Status of the bill. Can be one of the following values: `CR` - Credit `DB` - Debit
Billing Type	COR_BILLING.BILL_TYPE	Type of the purchased item. Can be one of the following values: `SUB` - Subscription `SVC` - Service `PKG` - Package `EVT` - Event `PLG` - Policy Group `CAT` - Catalog `CHN` - Channel `AGR` - AssetGroup `AST` - Asset
Billed Item Uid	COR_BILLING.BILL_ITEM_UID	UID of the billed item.
Incurred Date	COR_BILLING.INCURRED_DATE	Date of transaction.
User Name	COR_BILLING.BILL_USER	UserName of the account holder.
Smartcard	COR_BILLING.SMARTCARD_ID	SmartCard id ID the device's smartcard.
Frequency	COR_BILLING.BILL_ITEM_NAME	Frequency Type of the billing time. Can be one of the following values: `Impluse` - billed once `Recurring` - billed repeatedly
SMS ID	COR_BILLING.SMS_ID	Service Provider Name/ID (Origin Key of the Service Provider).
Offer Price	COR_BILLING.OFFER_PRICE	Offer price of the product, if applicable.

Input parameters for report generation

Parameter	Type	Description
Lower date threshold of report period	DateTime	Start Date for the report, including the specified value.
Upper threshold of report period	DateTime	End Date for the report, including the specified value.
Service Provider ID	String	Report can be generated only for a specific service provider.

Data extraction file

billingByAccount_report_kettle.ktr

Query

CODE

CREATE OR REPLACE FORCE VIEW "KB_10JUL_O"."COR_BILLING_BYACCT_RPT_V" ("BILLING_UID", "SPID", "ACCOUNT_UID", "ACCOUNT_NUM", "ALACARTE", "CREATION_DATE", "STATUS", "VALUE", "CREDIT_DEBIT", "BILLING_TYPE", "BILLED_ITEM_UID", "INCURRED_DATE", "BILL_USER", "TITLE", "DESCRIPTION", "SMARTCARD_ID", "PRICE_FREQ", "SMS_ID", "ORIGIN_UID", "ORIGIN_KEY", "MODIFIED_DATE")
                       AS
  SELECT BIL.BILL_UID  AS BILLING_UID,
    BIL.BILL_SPID      AS SPID,
    BIL.ACC_UID        AS ACCOUNT_UID,
    ACC.ACC_NUM        AS ACCOUNT_NUM,
    ACL.ACL_ALACARTE   AS ALACARTE,
    BIL.CREATION_DATE  AS CREATION_DATE,
    BIL.BILL_STATUS    AS STATUS,
    BIL.BILL_VALUE     AS VALUE,
    BIL.CREDIT_DEBIT   AS CREDIT_DEBIT,
    BIL.BILL_TYPE      AS BILLING_TYPE,
    BIL.BILL_ITEM_UID  AS BILLED_ITEM_UID,
    BIL.INCURRED_DATE  AS INCURRED_DATE,
    BIL.BILL_USER      AS BILL_USER,
    BIL.BILL_ITEM_NAME AS TITLE,
    BIL.BILL_DESC      AS DESCRIPTION,
    'More than one'    AS SMARTCARD_ID,
    BIL.PRICE_FREQ     AS PRICE_FREQ,
    SPR.ORIGIN_KEY     AS SMS_ID,
    BIL.ORIGIN_UID     AS ORIGIN_UID,
    BIL.ORIGIN_KEY     AS ORIGIN_KEY,
    BIL.MODIFIED_DATE  AS MODIFIED_DATE
  FROM COR_BILLING BIL
  INNER JOIN COR_DEVICE_STB STB
  ON BIL.ACC_UID = STB.ACC_UID
  INNER JOIN COR_SERVICE_PROVIDER SPR
  ON BIL.BILL_SPID = SPR.SPID_UID
  INNER JOIN COR_ACCOUNT ACC
  ON ACC.ACC_UID = BIL.ACC_UID
  LEFT OUTER JOIN COR_ACL ACL
  ON BIL.ACL_UID = ACL.ACL_UID
  GROUP BY BIL.BILL_UID,
    BIL.BILL_SPID,
    BIL.ACC_UID,
    ACC.ACC_NUM,
    ACL.ACL_ALACARTE,
    BIL.CREATION_DATE,
    BIL.BILL_STATUS,
    BIL.BILL_VALUE,
    BIL.CREDIT_DEBIT,
    BIL.BILL_TYPE,
    BIL.BILL_ITEM_UID,
    BIL.INCURRED_DATE,
    BIL.BILL_USER,
    BIL.BILL_ITEM_NAME,
    BIL.BILL_DESC,
    BIL.PRICE_FREQ,
    SPR.ORIGIN_KEY,
    BIL.ORIGIN_UID,
    BIL.ORIGIN_KEY,
    BIL.MODIFIED_DATE
  HAVING COUNT(STB.SMARTCARD_ID) > 1
  UNION
  SELECT BIL.BILL_UID     AS BILLING_UID,
    BIL.BILL_SPID         AS SPID,
    BIL.ACC_UID           AS ACCOUNT_UID,
    ACC.ACC_NUM           AS ACCOUNT_NUM,
    ACL.ACL_ALACARTE      AS ALACARTE,
    BIL.CREATION_DATE     AS CREATION_DATE,
    BIL.BILL_STATUS       AS STATUS,
    BIL.BILL_VALUE        AS VALUE,
    BIL.CREDIT_DEBIT      AS CREDIT_DEBIT,
    BIL.BILL_TYPE         AS BILLING_TYPE,
    BIL.BILL_ITEM_UID     AS BILLED_ITEM_UID,
    BIL.INCURRED_DATE     AS INCURRED_DATE,
    BIL.BILL_USER         AS BILL_USER,
    BIL.BILL_ITEM_NAME    AS TITLE,
    BIL.BILL_DESC         AS DESCRIPTION,
    MIN(STB.SMARTCARD_ID) AS SMARTCARD_ID,
    BIL.PRICE_FREQ        AS PRICE_FREQ,
    SPR.ORIGIN_KEY        AS SMS_ID,
    BIL.ORIGIN_UID        AS ORIGIN_UID,
    BIL.ORIGIN_KEY        AS ORIGIN_KEY,
    BIL.MODIFIED_DATE     AS MODIFIED_DATE
  FROM COR_BILLING BIL
  INNER JOIN COR_DEVICE_STB STB
  ON BIL.ACC_UID = STB.ACC_UID
  INNER JOIN COR_SERVICE_PROVIDER SPR
  ON BIL.BILL_SPID = SPR.SPID_UID
  INNER JOIN COR_ACCOUNT ACC
  ON ACC.ACC_UID = BIL.ACC_UID
  LEFT OUTER JOIN COR_ACL ACL
  ON BIL.ACL_UID = ACL.ACL_UID
  GROUP BY BIL.BILL_UID,
    BIL.BILL_SPID,
    BIL.ACC_UID,
    ACC.ACC_NUM,
    ACL.ACL_ALACARTE,
    BIL.CREATION_DATE,
    BIL.BILL_STATUS,
    BIL.BILL_VALUE,
    BIL.CREDIT_DEBIT,
    BIL.BILL_TYPE,
    BIL.BILL_ITEM_UID,
    BIL.INCURRED_DATE,
    BIL.BILL_USER,
    BIL.BILL_ITEM_NAME,
    BIL.BILL_DESC,
    BIL.PRICE_FREQ,
    SPR.ORIGIN_KEY,
    BIL.ORIGIN_UID,
    BIL.ORIGIN_KEY,
    BIL.MODIFIED_DATE
  HAVING COUNT(BIL.BILL_UID) = 1 ;

BIRT design template file

billing-by-account.rptdesign

Sample report