OSS_BI_SNAPSHOTS_CUST

Details

Schema: FUSION
Object owner: OSS
Object type: VIEW

Columns

Name
PARTY_NAME PARTY_NUMBER PRIMARY_PARTY_ID PERIOD_TYPE PERIOD_MONTH PERIOD_QUARTER PERIOD_YEAR PERIOD_START_DATE PERIOD_END_DATE PERIOD_CODE PERIOD_NAME CLOSED_AMOUNT CREDITED_AMOUNT DISCOUNT_AMOUNT ESTIMATED_TAX INVOICED_AMOUNT MONTHLY_RECURRING_REVENUE MRR_AMEND_CONTRACTIONS MRR_AMEND_EXPANSIONS MRR_CHURN_RENEW_LAPSES MRR_CLOSED_SUBSCRIPTIONS MRR_DUE_RENEWAL MRR_NEW_SUBSCRIPTIONS MRR_RENEW_ACTIVE MRR_RENEW_CHURN NUM_CLOSED_SUBSCRIPTIONS NUM_NEW_SUBSCRIPTIONS NUM_RENEWED_SUBSCRIPTIONS NUM_SUBSCRIPTIONS PRODUCT_NET_PRICE PRODUCT_TOTAL SUPPRESSED_CREDIT_AMOUNT TOTAL_QUANTITY REQUEST_ID OBJECT_VERSION_NUMBER CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN BI_SNAPSHOTS_CUST_ID

Name

PARTY_NAME

PARTY_NUMBER

PRIMARY_PARTY_ID

PERIOD_TYPE

PERIOD_MONTH

PERIOD_QUARTER

PERIOD_YEAR

PERIOD_START_DATE

PERIOD_END_DATE

PERIOD_CODE

PERIOD_NAME

CLOSED_AMOUNT

CREDITED_AMOUNT

DISCOUNT_AMOUNT

ESTIMATED_TAX

INVOICED_AMOUNT

MONTHLY_RECURRING_REVENUE

MRR_AMEND_CONTRACTIONS

MRR_AMEND_EXPANSIONS

MRR_CHURN_RENEW_LAPSES

MRR_CLOSED_SUBSCRIPTIONS

MRR_DUE_RENEWAL

MRR_NEW_SUBSCRIPTIONS

MRR_RENEW_ACTIVE

MRR_RENEW_CHURN

NUM_CLOSED_SUBSCRIPTIONS

NUM_NEW_SUBSCRIPTIONS

NUM_RENEWED_SUBSCRIPTIONS

NUM_SUBSCRIPTIONS

PRODUCT_NET_PRICE

PRODUCT_TOTAL

SUPPRESSED_CREDIT_AMOUNT

TOTAL_QUANTITY

REQUEST_ID

OBJECT_VERSION_NUMBER

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

BI_SNAPSHOTS_CUST_ID

Query

SQL_Statement
SELECT p.PARTY_NAME, p.PARTY_NUMBER, s.PRIMARY_PARTY_ID, DECODE(s.PERIOD_TYPE,'GM','MNTH','GQ','QRTR','YEAR') as PERIOD_TYPE, TO_NUMBER(TO_CHAR(t.period_end_date,'MM')) as PERIOD_MONTH, TO_NUMBER(TO_CHAR(t.period_end_date,'Q')) as PERIOD_QUARTER, TO_NUMBER(TO_CHAR(t.period_end_date,'YYYY')) as PERIOD_YEAR, t.PERIOD_START_DATE, t.PERIOD_END_DATE, s.PERIOD_CODE, t.PERIOD_NAME, s.CLOSED_AMOUNT, s.CREDITED_AMOUNT, s.DISCOUNT_AMOUNT, s.ESTIMATED_TAX, s.INVOICED_AMOUNT, s.MONTHLY_RECURRING_REVENUE, s.MRR_AMEND_CONTRACTIONS, s.MRR_AMEND_EXPANSIONS, s.MRR_CHURN_RENEW_LAPSES, s.MRR_CLOSED_SUBSCRIPTIONS, s.MRR_DUE_RENEWAL, s.MRR_NEW_SUBSCRIPTIONS, s.MRR_RENEW_ACTIVE, s.MRR_RENEW_CHURN, s.NUM_CLOSED_SUBSCRIPTIONS, s.NUM_NEW_SUBSCRIPTIONS, s.NUM_RENEWED_SUBSCRIPTIONS, s.NUM_SUBSCRIPTIONS, s.PRODUCT_NET_PRICE, s.PRODUCT_TOTAL, s.SUPPRESSED_CREDIT_AMOUNT, s.TOTAL_QUANTITY, s.REQUEST_ID, s.OBJECT_VERSION_NUMBER, s.CREATED_BY, s.CREATION_DATE, s.LAST_UPDATED_BY, s.LAST_UPDATE_DATE, s.LAST_UPDATE_LOGIN, s.BI_SNAPSHOTS_CUST_ID FROM oss_bi_snapshot_periods t, oss_bi_snapshots_cust s, hz_parties p WHERE s.primary_party_id = p.party_id AND t.bi_snapshot_period_id = s.bi_snapshot_period_id ORDER BY s.PERIOD_TYPE, t.period_end_date, p.PARTY_NAME

SQL_Statement

SELECT

p.PARTY_NAME,

p.PARTY_NUMBER,

s.PRIMARY_PARTY_ID,

DECODE(s.PERIOD_TYPE,'GM','MNTH','GQ','QRTR','YEAR') as PERIOD_TYPE,

TO_NUMBER(TO_CHAR(t.period_end_date,'MM')) as PERIOD_MONTH,

TO_NUMBER(TO_CHAR(t.period_end_date,'Q')) as PERIOD_QUARTER,

TO_NUMBER(TO_CHAR(t.period_end_date,'YYYY')) as PERIOD_YEAR,

t.PERIOD_START_DATE,

t.PERIOD_END_DATE,

s.PERIOD_CODE,

t.PERIOD_NAME,

s.CLOSED_AMOUNT,

s.CREDITED_AMOUNT,

s.DISCOUNT_AMOUNT,

s.ESTIMATED_TAX,

s.INVOICED_AMOUNT,

s.MONTHLY_RECURRING_REVENUE,

s.MRR_AMEND_CONTRACTIONS,

s.MRR_AMEND_EXPANSIONS,

s.MRR_CHURN_RENEW_LAPSES,

s.MRR_CLOSED_SUBSCRIPTIONS,

s.MRR_DUE_RENEWAL,

s.MRR_NEW_SUBSCRIPTIONS,

s.MRR_RENEW_ACTIVE,

s.MRR_RENEW_CHURN,

s.NUM_CLOSED_SUBSCRIPTIONS,

s.NUM_NEW_SUBSCRIPTIONS,

s.NUM_RENEWED_SUBSCRIPTIONS,

s.NUM_SUBSCRIPTIONS,

s.PRODUCT_NET_PRICE,

s.PRODUCT_TOTAL,

s.SUPPRESSED_CREDIT_AMOUNT,

s.TOTAL_QUANTITY,

s.REQUEST_ID,

s.OBJECT_VERSION_NUMBER,

s.CREATED_BY,

s.CREATION_DATE,

s.LAST_UPDATED_BY,

s.LAST_UPDATE_DATE,

s.LAST_UPDATE_LOGIN,

s.BI_SNAPSHOTS_CUST_ID

FROM oss_bi_snapshot_periods t, oss_bi_snapshots_cust s, hz_parties p

WHERE s.primary_party_id = p.party_id

AND t.bi_snapshot_period_id = s.bi_snapshot_period_id

ORDER BY s.PERIOD_TYPE, t.period_end_date, p.PARTY_NAME