Billing by account

The billing report provides the purchase transactions made for each account, with the billing transaction details.

Report columns

Column name	Source DB field	Description
Billing UID	COR_BILLING.BILLING_UID	UID of the billing transaction.
Account	COR_ACCOUNT.ACC_NUM	Account Number of the customer.
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.
CR/DR	COR_BILLING.CREDIT_DEBIT	Credit Status of the bill. Can be one of the following values: `CR` - Credit `DB` - DEBIT
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
Incurred Date	COR_BILLING.INCURRED_DATE	Date of the transaction.
User	COR_BILLING.BILL_USER	UserName of the account holder.
Device	COR_BILLING.DEV_UID	Unique ID for the device to which the billing is made.
Smartcard	COR_BILLING.SMARTCARD_ID	SmartCard ID of the device's smartcard.
Title	COR_BILLING.BILL_ITEM_NAME	Name of the billed item.
Frequency	COR_BILLING.PRICE_FREQ	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).
Origin key	COR_BILLING.ORIGIN_KEY	External user identifier or Service Provider Specific value
Ext Ref1	COR_BILLING.EXT_REF1	Optional external reference specific to operator.
Ext Ref 2	COR_BILLING.EXT_REF2	Optional external reference specific to operator.
Offer Price	COR_BILLING.OFFER_PRICE	Offer price of the product, if applicable.
Offer Id	COR_ACL_OFFER.OFFER_ID	Offer ID of the give offer, 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

billing_report_kettle.ktr

Query

CODE

CREATE OR REPLACE FORCE VIEW "KB_10JUL_O"."COR_BILLING_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", "EXT_REF1", "EXT_REF2", "DEVICE_UID")
   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,
    ACL.SMARTCARD      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,
    BIL.EXT_REF1       AS EXT_REF1,
    BIL.EXT_REF2       AS EXT_REF2,
    BIL.DEV_UID        AS DEVICE_UID,
    BIL.OFFER_PRICE    AS OFFER_PRICE,
    OFF.OFFER_ID       AS OFFER_ID
  FROM COR_BILLING BIL
  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
  LEFT OUTER JOIN COR_ACL_OFFER OFF ON BIL.ACL_UID = OFF.ACL_UID;

BIRT design template file

billing.rptdesign

Sample report