COD license

This reports shows valid COD License data. This includes the details of the each licence along with the account device details.

Report columns

Column name	Source DB field	Description
Account Number	COR_ACCOUNT.ACC_NUM	Account Number to which the device belongs.
SPID	BOC_LICENSE.LIC_SPID	Service Provider ID.
Device ID	COR_DEVICE_STB.CA_SN	CASN (Serial Number) of the STB.
Nagra ID	COR_DEVICE_STB.NUID	NUID (Nagra UID) of the STB.
Content ID	ASSET.CAS_ID	Content ID involved in the transaction.
Policy Group ID	COR_POLGROUP.ORIGIN_KEY	Product/Policy Group ID in the transaction.
License Reference	COD_LICENSE_REF	Purchase Reference ID for the licence.
License Start Date	COD_LICENSE.START_DATE	Start date of the license validity.
License End Date	MAX(COR_ACL.EXPIRY_DATE)	Expiry date of the license validity.
Purchase Item	PURCHASE_ITEM	Title of the purchased product.
Purchase Expiry	PURCHASE_EXPIRY	Expiry date of the purchased item.
License Status	LICENSE_STATUS	Status of the licence; can be "ACTIVE" or "REVOKED".
Creation Date	CREATION_DATE	Date on which the record was created.
Origin Key	ORIGIN_KEY	External user identifier or Service Provider specific value.
Origin UID	ORIGIN_UID	UID for external Origin_Key.
Modified date	MODIFIED _DATE	Date on which the record was last modified.
License Type	NVL2(COR_ACL.CNSM_WN,'Relative','Absolute')	License Type granted in the transaction.

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

cod_license_report_kettle.ktr

Query

CODE

CREATE OR REPLACE FORCE VIEW "KB_10JUL_O"."COD_LICENSE_RPT_V" ("ACCOUNT_NUMBER", "SPID", "DEVICE_ID", "NUID", "CONTENT_ID", "POL_GRP_ID", "LICENSE_REFERENCE", "LICENSE_START_DATE", "LICENSE_END_DATE", "PURCHASE_ITEM", "PURCHASE_EXPIRY", "LICENSE_STATUS", "CREATION_DATE", "ORIGIN_KEY", "ORIGIN_UID", "MODIFIED_DATE", "LICENSE_TYPE")
                                            AS
  SELECT DISTINCT ACC.ACC_NUM               AS ACCOUNT_NUMBER,
    LIC.LIC_SPID                            AS SPID,
    STB.CA_SN                               AS DEVICE_ID,
    STB.NUID                                AS NUID,
    ASS.CAS_ID                              AS CONTENT_ID,
    PGP.ORIGIN_KEY                          AS POL_GRP_ID,
    LIC.LICENSE_REF                         AS LICENSE_REFERENCE,
    LIC.START_DATE                          AS LICENSE_START_DATE,
    LIC.END_DATE                            AS LICENSE_END_DATE,
    ASS.ORIGIN_KEY                          AS PURCHASE_ITEM,
    MAX(ACL.EXPIRY_DATE)                    AS PURCHASE_EXPIRY,
    LIC.LIC_STATUS                          AS LICENSE_STATUS,
    LIC.CREATION_DATE                       AS CREATION_DATE,
    LIC.ORIGIN_KEY                          AS ORIGIN_KEY,
    LIC.ORIGIN_UID                          AS ORIGIN_UID,
    LIC.MODIFIED_DATE                       AS MODIFIED_DATE,
    NVL2(ACL.CNSM_WN,'Relative','Absolute') AS LICENSE_TYPE
  FROM cod_license LIC
  LEFT OUTER JOIN COR_DEVICE_STB STB
  ON LIC.DEV_UID = STB.DEV_UID
  LEFT OUTER JOIN COR_ACCOUNT ACC
  ON ACC.ACC_UID = LIC.ACC_UID
  LEFT OUTER JOIN COD_ASSET ASS
  ON LIC.ASS_UID = ASS.ASS_UID
  LEFT OUTER JOIN COR_ACL ACL
  ON ACL.ACC_UID        = LIC.ACC_UID
  AND ACL.SMARTCARD     = STB.SMARTCARD_ID
  AND ASS.ASS_UID       = ACL.ACL_ITEM_UID
  AND ACL.ACL_ITEM_TYPE = 'AST'
  AND ACL.ACL_STATUS    = 'S'
  LEFT OUTER JOIN COR_POLGROUP PGP
  ON PGP.POLGRP_UID   = ACL.POLGRP_UID
  WHERE LIC.END_DATE >= SYSDATE
  GROUP BY ACC.ACC_NUM,
    LIC.LIC_SPID,
    STB.CA_SN,
    STB.NUID,
    ASS.CAS_ID,
    LIC.LICENSE_REF,
    LIC.START_DATE,
    LIC.END_DATE,
    ASS.ORIGIN_KEY,
    LIC.LIC_STATUS,
    LIC.CREATION_DATE,
    LIC.ORIGIN_KEY,
    LIC.ORIGIN_UID,
    LIC.MODIFIED_DATE,
    PGP.ORIGIN_KEY,
    ACL.CNSM_WN
  ORDER BY ACC.ACC_NUM,
    STB.CA_SN,
    ASS.ORIGIN_KEY ;

BIRT design template file

cod-license-report.rptdesign

Sample report

The following image shows an example of a Cod License report: