PPV billing

This report show the billing data for BTV events (Pay Per View).

REPORT COLUMNS

Column name	Source DB field	Description
ACCOUNT_NUM	COR_ACCOUNT.ACC_NUM	Account Number of 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.
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 the transaction.
BILL_USER	COR_BILLING.BILL_USER	UserName of the account holder.
TITLE	COR_BILLING.BILL_ITEM_NAME	Name of the billed item.
SMARTCARD_ID	COR_BILLING.SMARTCARD_ID	SmartCard ID of the device's smartcard.
PRICE_FREQ	COR_BILLING.PRICE_FREQ	Frequency Type of the billing time. Can be one of the following values: `Impulse` - 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

btv_ppv_billing.ktr

Query

CODE

CREATE OR REPLACE FORCE VIEW "KB_10JUL_O"."BTV_PPV_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", "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.DEV_UID        AS DEVICE_UID
  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
  INNER JOIN COR_ACL ACL
  ON BIL.ACL_UID = ACL.ACL_UID
  WHERE EXISTS
    (SELECT '1' FROM BTV_EVENT_POLICY WHERE POLGRP_UID = ACL.POLGRP_UID
    )
  ORDER BY ACC.ACC_NUM,
    BIL.CREATION_DATE ;

BIRT design template file

btv_ppv_billing.rptdesign

Sample report