Subscription tables in Oracle Fusion Cloud

Short answer

Subscription Management data lives in tables prefixed OSS_. The header is OSS_SUBSCRIPTIONS (key SUBSCRIPTION_ID, business number SUBSCRIPTION_NUMBER); renewal and other operation links are in OSS_OPERATION_LINKS; customer names come from HZ_PARTIES through PRIMARY_PARTY_ID.

Tables

Table	What it holds	Key columns
`OSS_SUBSCRIPTIONS`	Subscription header, one row per version	SUBSCRIPTION_ID, SUBSCRIPTION_NUMBER, STATUS, START_DATE, END_DATE, CURRENCY, BUSINESS_UNIT_ID, PRIMARY_PARTY_ID, BILL_TO_ACCT_ID, MAJOR_VERSION, VERSION_TYPE
`OSS_PRODUCTS`	Subscription product lines	SUBSCRIPTION_PRODUCT_ID, SUBSCRIPTION_ID, PRODUCT_NAME, STATUS, START_DATE, END_DATE
`OSS_PRODUCT_ASSOCIATIONS`	Covered levels: assets, items or sites covered by a coverage product	PRODUCT_ASSOCIATION_ID, SUBSCRIPTION_ID, SUBSCRIPTION_PRODUCT_ID, OBJECT_TYPE, COVERED_LEVEL_NAME
`OSS_OPERATION_LINKS`	Links created by operations such as renewal	SOURCE_SUBSCRIPTION_ID, TARGET_SUBSCRIPTION_ID, OPERATION_CODE
`HZ_PARTIES`	Customer (party) names	PARTY_ID, PARTY_NAME

How they join

OSS_SUBSCRIPTIONS.PRIMARY_PARTY_ID = HZ_PARTIES.PARTY_ID
OSS_PRODUCTS.SUBSCRIPTION_ID = OSS_SUBSCRIPTIONS.SUBSCRIPTION_ID
OSS_PRODUCT_ASSOCIATIONS.SUBSCRIPTION_PRODUCT_ID = OSS_PRODUCTS.SUBSCRIPTION_PRODUCT_ID
OSS_OPERATION_LINKS.SOURCE_SUBSCRIPTION_ID and TARGET_SUBSCRIPTION_ID = OSS_SUBSCRIPTIONS.SUBSCRIPTION_ID
Subscription Term isn't stored: it's END_DATE - START_DATE.

Example queries

One subscription with its customer

SELECT s.subscription_number,
       s.status,
       s.start_date,
       s.end_date,
       s.currency,
       p.party_name AS customer
FROM   oss_subscriptions s
JOIN   hz_parties p ON p.party_id = s.primary_party_id
WHERE  s.subscription_number = :subscription_number

More than one row means you are seeing versions: check MAJOR_VERSION and VERSION_TYPE on your pod and pick the version you want before joining anything else.

Trace a renewal back to the original subscription

SELECT l.operation_code,
       src.subscription_number AS source_subscription,
       tgt.subscription_number AS target_subscription,
       tgt.start_date          AS target_start,
       tgt.status              AS target_status
FROM   oss_operation_links l
JOIN   oss_subscriptions src ON src.subscription_id = l.source_subscription_id
JOIN   oss_subscriptions tgt ON tgt.subscription_id = l.target_subscription_id
WHERE  :subscription_number IN (src.subscription_number, tgt.subscription_number)

Run it again with the source number to walk further back through older renewals.

Covered levels of a subscription

SELECT s.subscription_number,
       pr.product_name,
       pa.object_type,
       pa.covered_level_name
FROM   oss_product_associations pa
JOIN   oss_products pr      ON pr.subscription_product_id = pa.subscription_product_id
JOIN   oss_subscriptions s  ON s.subscription_id = pa.subscription_id
WHERE  s.subscription_number = :subscription_number

Verify on your pod. Table and column names can differ between releases and pods. Check them against the table descriptions on your own pod before you rely on a query, and run SQL read-only.

See every related record in one click - try Querical Studio free for 14 days.

Start free trial

Related guides

Connect a Fusion pod to Querical Studio