BOC license report

This BTV Or Cod License report brings data from BOC_LICENSE table.

Report columns

Column name	Source DB field	Description
Creation Date	BOC_LICENSE. NOT NULL DATE	Creation date of licence record
SPID	BOC_LICENSE.LIC_SPID	Service Provider ID.
Account	BOC_LICENSE.ACCOUNT_NUMBER	Account Number of the transaction.
Content Type	BOC_LICENSE.CONTENT_TYPE	Content type purchased in the transaction.
CAS Id	BOC_LICENSE.	CAS ID associated to the policy group.
CA/SN	BOC_LICENSE.STB_CA_SN	CASN of the STB that made the transaction.
NUID	BOC_LICENSE.STB_NUID	NUID of the STB that made the transaction.
License Type	BOC_LICENSE.LICENSE_TYPE	License Type granted in the transaction.
Status	BOC_LICENSE.LICENSE_STATUS	License Status of the transaction.
Media Player Id	BOC_LICENSE.MEDIAPLAYER_ID	Media Player ID of the device in the transaction.
Content Id	BOC_LICENSE.CONTENT_ID	Content ID involved in the transaction.
Polgrp Id	BOC_LICENSE.POLGRP_ORIGIN_KEY	Product/Policy Group ID in the transaction.
License Start	BOC_LICENSE.START_DATE	License validity start date.
License End	BOC_LICENSE.END_DATE	License validity end date.
ACL Expiry	BOC_LICENSE.ACL_EXPIRY_DATE	Expiry date of the ACL that allowed this license.

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	The report can be generated only for a specific service provider.

Data extraction file

boc_license_report_kettle.ktr

Query

CODE


  CREATE OR REPLACE FORCE VIEW "KBROSDP3_O"."BOC_LICENSE_CSV_RPT_V" ("BOC_LICENSE_CSV", "CREATION_DATE", "SPID") AS
  SELECT
      bl.creation_date                || ',' ||
      NVL(TO_CHAR(bl.lic_spid), ' ')  || ',' ||
      NVL(bl.account_number, ' ')     || ',' ||
      NVL(bl.content_type, ' ')       || ',' ||
      NVL(bl.content_id, ' ')         || ',' ||
      NVL(bl.stb_ca_sn, ' ')          || ',' ||
      NVL(bl.stb_nuid, ' ')           || ',' ||
      NVL(bl.license_type, ' ')       || ',' ||
      NVL(bl.mediaplayer_id, ' ')     || ',' ||
      NVL(bl.content_origin_key, ' ') || ',' ||
      NVL(bl.polgrp_origin_key, ' ')  || ',' ||
      NVL(bl.license_status, ' ')     || ',' ||
      NVL(bl.license_type, ' ')       || ',' ||
      bl.start_date                   || ',' ||
      bl.end_date                     || ',' ||
      bl.acl_expiry_date AS BOC_LICENSE_CSV,
      bl.creation_date AS creation_date,
      bl.lic_spid AS spid
FROM BOC_LICENSE BL;

BIRT design template file

boc-license-report.rptdesign

Sample report