OSS_ASSET_RELATIONSHIPS

Details

Schema: FUSION
Object owner: OSS
Object type: VIEW

Columns

Name
RELATIONSHIP_CODE RELATIONSHIP RELATED_SUBSCRIPTION_NUMBER SUBSCRIPTION_PRODUCT_ID RELATED_LINE_SUBSCRIPTION_ID RELATED_LINE_PUID RELATED_LINE_NUMBER RELATED_LINE_NAME RELATED_PROD_ASS_ID ASSOCIATION_PUID ASSOCIATION_NUMBER EFFECTIVE_DATE RELATED_LINE_STATUS_CODE RELATED_LINE_STATUS AMENDMENT_REASON_CODE AMENDMENT_REASON SUBSCRIPTION_ID PRODUCT_ID PRODUCT_PUID PRODUCT_ASSOCIATION_ID

Name

RELATIONSHIP_CODE

RELATIONSHIP

RELATED_SUBSCRIPTION_NUMBER

SUBSCRIPTION_PRODUCT_ID

RELATED_LINE_SUBSCRIPTION_ID

RELATED_LINE_PUID

RELATED_LINE_NUMBER

RELATED_LINE_NAME

RELATED_PROD_ASS_ID

ASSOCIATION_PUID

ASSOCIATION_NUMBER

EFFECTIVE_DATE

RELATED_LINE_STATUS_CODE

RELATED_LINE_STATUS

AMENDMENT_REASON_CODE

AMENDMENT_REASON

SUBSCRIPTION_ID

PRODUCT_ID

PRODUCT_PUID

PRODUCT_ASSOCIATION_ID

Query

SQL_Statement
SELECT relationship_code, relationship, related_subscription_number, subscription_product_id, related_line_subscription_id, related_line_puid, related_line_number, related_line_name, related_prod_ass_id, association_puid, association_number, effective_date, related_line_status_code, related_line_status, amendment_reason_code, amendment_reason, subscription_id, product_id, product_puid, product_association_id FROM (SELECT DECODE (o.operation_code, 'AMEND', 'ORA_AMENDED_FROM', 'RENEW', 'ORA_RENEWED_FROM', 'TRANSFER', 'ORA_TRANSFER_FROM', 'SPLIT', 'ORA_SPLIT_FROM', 'UPGRADE', 'ORA_UPGRADE_FROM' ) AS relationship_code, oss_util_pub.get_code_meaning ('ORA_OSS_RELATIONSHIPS', DECODE (o.operation_code, 'AMEND', 'ORA_AMENDED_FROM', 'RENEW', 'ORA_RENEWED_FROM', 'TRANSFER', 'ORA_TRANSFER_FROM', 'SPLIT', 'ORA_SPLIT_FROM', 'UPGRADE', 'ORA_UPGRADE_FROM' ) ) AS relationship, s.subscription_number as related_subscription_number, p.subscription_product_id, p.subscription_id as related_line_subscription_id, p.subscription_product_puid AS related_line_puid, p.product_number AS related_line_number, p.product_name as related_line_name, a.product_association_id as related_prod_ass_id, a.association_puid, a.association_number, DECODE (o.operation_code, 'AMEND', trunc(p.amend_effective_date), 'RENEW', trunc(p.renewed_date), 'TRANSFER', NULL, 'SPLIT', NULL, 'UPGRADE', NULL ) AS effective_date, p.status AS related_line_status_code, oss_util_pub.get_code_meaning ('ORA_OSS_STATUS', p.status) AS related_line_status, p.amend_reason AS amendment_reason_code, oss_util_pub.get_code_meaning ('ORA_OSS_CLOSE_REASONS', p.amend_reason) AS amendment_reason, o.target_subscription_id as subscription_id, o.target_subscription_product_id AS product_id, o.target_product_puid AS product_puid, o.target_prod_association_id as product_association_id FROM oss_operation_links o, oss_products p, oss_subscriptions s, oss_product_associations a WHERE o.source_subscription_product_id = p.subscription_product_id AND p.subscription_id = s.subscription_id AND p.subscription_product_id = a.subscription_product_id AND o.source_prod_association_id = a.product_association_id AND o.operation_code in ( 'AMEND', 'RENEW','TRANSFER','SPLIT','UPGRADE') UNION ALL SELECT DECODE (o.operation_code, 'AMEND', 'ORA_AMENDED_TO', 'RENEW', 'ORA_RENEWED_TO', 'TRANSFER', 'ORA_TRANSFER_TO', 'SPLIT', 'ORA_SPLIT_TO', 'UPGRADE', 'ORA_UPGRADE_TO' ) AS relationship_code, oss_util_pub.get_code_meaning ('ORA_OSS_RELATIONSHIPS', DECODE (o.operation_code, 'AMEND', 'ORA_AMENDED_TO', 'RENEW', 'ORA_RENEWED_TO', 'TRANSFER', 'ORA_TRANSFER_TO', 'SPLIT', 'ORA_SPLIT_TO', 'UPGRADE', 'ORA_UPGRADE_TO' ) ) AS relationship, ts.subscription_number as related_subscription_number, p.subscription_product_id, p.subscription_id as related_line_subscription_id, p.subscription_product_puid AS related_line_puid, p.product_number AS related_line_number, p.product_name as related_line_name, a.product_association_id as related_prod_ass_id, a.association_puid, a.association_number, DECODE (o.operation_code, 'AMEND', trunc(s.amend_effective_date), 'RENEW', trunc(s.renewed_date), 'TRANSFER', NULL, 'SPLIT', NULL, 'UPGRADE', NULL ) AS effective_date, p.status AS related_line_status_code, oss_util_pub.get_code_meaning ('ORA_OSS_STATUS', p.status) AS related_line_status, s.amend_reason AS amendment_reason_code, oss_util_pub.get_code_meaning ('ORA_OSS_CLOSE_REASONS', s.amend_reason) AS amendment_reason, o.source_subscription_id as subscription_id, o.source_subscription_product_id AS product_id, o.source_product_puid AS product_puid , o.source_prod_association_id as product_association_id FROM oss_operation_links o, oss_products p, oss_products s, oss_subscriptions ts, oss_product_associations a WHERE o.target_subscription_product_id = p.subscription_product_id AND o.source_subscription_product_id = s.subscription_product_id AND p.subscription_id = ts.subscription_id AND p.subscription_product_id = a.subscription_product_id AND o.target_prod_association_id = a.product_association_id AND o.operation_code in ( 'AMEND', 'RENEW', 'TRANSFER','SPLIT','UPGRADE') )

SQL_Statement

SELECT

relationship_code,

relationship,

related_subscription_number,

subscription_product_id,

related_line_subscription_id,

related_line_puid,

related_line_number,

related_line_name,

related_prod_ass_id,

association_puid,

association_number,

effective_date,

related_line_status_code,

related_line_status,

amendment_reason_code,

amendment_reason,

subscription_id,

product_id,

product_puid,

product_association_id

FROM (SELECT DECODE (o.operation_code,

'AMEND',

'ORA_AMENDED_FROM',

'RENEW',

'ORA_RENEWED_FROM',

'TRANSFER',

'ORA_TRANSFER_FROM',

'SPLIT',

'ORA_SPLIT_FROM',

'UPGRADE',

'ORA_UPGRADE_FROM'

)

AS relationship_code,

oss_util_pub.get_code_meaning ('ORA_OSS_RELATIONSHIPS',

DECODE (o.operation_code,

'AMEND',

'ORA_AMENDED_FROM',

'RENEW',

'ORA_RENEWED_FROM',

'TRANSFER',

'ORA_TRANSFER_FROM',

'SPLIT',

'ORA_SPLIT_FROM',

'UPGRADE',

'ORA_UPGRADE_FROM'

)

AS relationship,

s.subscription_number as related_subscription_number,

p.subscription_product_id,

p.subscription_id as related_line_subscription_id,

p.subscription_product_puid AS related_line_puid,

p.product_number AS related_line_number,

p.product_name as related_line_name,

a.product_association_id as related_prod_ass_id,

a.association_puid,

a.association_number,

DECODE (o.operation_code,

'AMEND',

trunc(p.amend_effective_date),

'RENEW',

trunc(p.renewed_date),

'TRANSFER',

NULL,

'SPLIT',

NULL,

'UPGRADE',

NULL

)

AS effective_date,

p.status AS related_line_status_code,

oss_util_pub.get_code_meaning ('ORA_OSS_STATUS', p.status)

AS related_line_status,

p.amend_reason AS amendment_reason_code,

oss_util_pub.get_code_meaning ('ORA_OSS_CLOSE_REASONS', p.amend_reason) AS amendment_reason,

o.target_subscription_id as subscription_id,

o.target_subscription_product_id AS product_id,

o.target_product_puid AS product_puid,

o.target_prod_association_id as product_association_id

FROM oss_operation_links o, oss_products p, oss_subscriptions s, oss_product_associations a

WHERE o.source_subscription_product_id = p.subscription_product_id

AND p.subscription_id = s.subscription_id

AND p.subscription_product_id = a.subscription_product_id

AND o.source_prod_association_id = a.product_association_id

AND o.operation_code in ( 'AMEND', 'RENEW','TRANSFER','SPLIT','UPGRADE')

UNION ALL

SELECT DECODE (o.operation_code,

'AMEND',

'ORA_AMENDED_TO',

'RENEW',

'ORA_RENEWED_TO',

'TRANSFER',

'ORA_TRANSFER_TO',

'SPLIT',

'ORA_SPLIT_TO',

'UPGRADE',

'ORA_UPGRADE_TO'

)

AS relationship_code,

oss_util_pub.get_code_meaning ('ORA_OSS_RELATIONSHIPS',

DECODE (o.operation_code,

'AMEND',

'ORA_AMENDED_TO',

'RENEW',

'ORA_RENEWED_TO',

'TRANSFER',

'ORA_TRANSFER_TO',

'SPLIT',

'ORA_SPLIT_TO',

'UPGRADE',

'ORA_UPGRADE_TO'

)

AS relationship,

ts.subscription_number as related_subscription_number,

p.subscription_product_id,

p.subscription_id as related_line_subscription_id,

p.subscription_product_puid AS related_line_puid,

p.product_number AS related_line_number,

p.product_name as related_line_name,

a.product_association_id as related_prod_ass_id,

a.association_puid,

a.association_number,

DECODE (o.operation_code,

'AMEND',

trunc(s.amend_effective_date),

'RENEW',

trunc(s.renewed_date),

'TRANSFER',

NULL,

'SPLIT',

NULL,

'UPGRADE',

NULL

)

AS effective_date,

p.status AS related_line_status_code,

oss_util_pub.get_code_meaning ('ORA_OSS_STATUS', p.status)

AS related_line_status,

s.amend_reason AS amendment_reason_code,

oss_util_pub.get_code_meaning ('ORA_OSS_CLOSE_REASONS', s.amend_reason) AS amendment_reason,

o.source_subscription_id as subscription_id,

o.source_subscription_product_id AS product_id,

o.source_product_puid AS product_puid ,

o.source_prod_association_id as product_association_id

FROM oss_operation_links o, oss_products p, oss_products s, oss_subscriptions ts, oss_product_associations a

WHERE o.target_subscription_product_id = p.subscription_product_id

AND o.source_subscription_product_id = s.subscription_product_id

AND p.subscription_id = ts.subscription_id

AND p.subscription_product_id = a.subscription_product_id

AND o.target_prod_association_id = a.product_association_id

AND o.operation_code in ( 'AMEND', 'RENEW', 'TRANSFER','SPLIT','UPGRADE') )