OSS_RELATIONSHIPS_V

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 EFFECTIVE_DATE RELATED_LINE_STATUS_CODE RELATED_LINE_STATUS AMENDMENT_REASON_CODE AMENDMENT_REASON SUBSCRIPTION_ID PRODUCT_ID PRODUCT_PUID

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, effective_date, related_line_status_code, related_line_status, amendment_reason_code, amendment_reason, subscription_id, product_id, product_puid FROM (SELECT DECODE (o.operation_code, 'AMEND', 'ORA_AMENDED_FROM', 'RENEW', 'ORA_RENEWED_FROM', 'SUSPENDED', 'ORA_RESUMED_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', 'SUSPENDED', 'ORA_RESUMED_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, DECODE (o.operation_code, 'AMEND', trunc(p.amend_effective_date), 'RENEW', trunc(p.renewed_date), 'SUSPENDED', trunc(p.SUSPENDED_DATE) ) 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 FROM oss_operation_links o, oss_products p, oss_subscriptions s WHERE o.source_subscription_product_id = p.subscription_product_id AND p.subscription_id = s.subscription_id AND o.source_prod_association_id IS NULL AND o.operation_code in ( 'AMEND', 'RENEW', 'SUSPENDED') UNION ALL SELECT DECODE (o.operation_code, 'AMEND', 'ORA_AMENDED_TO', 'RENEW', 'ORA_RENEWED_TO', 'SUSPENDED', 'ORA_RESUMED_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', 'SUSPENDED', 'ORA_RESUMED_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, DECODE (o.operation_code, 'AMEND', trunc(s.amend_effective_date), 'RENEW', trunc(s.renewed_date), 'SUSPENDED', trunc(p.SUSPENDED_DATE) ) 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 FROM oss_operation_links o, oss_products p, oss_products s, oss_subscriptions ts 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 o.source_prod_association_id IS NULL AND o.operation_code in ( 'AMEND', 'RENEW', 'SUSPENDED') UNION ALL SELECT DISTINCT DECODE (o.operation_code, 'RENEW', 'ORA_RENEWED_FROM' ) AS relationship_code, oss_util_pub.get_code_meaning ('ORA_OSS_RELATIONSHIPS', DECODE (o.operation_code, 'RENEW', 'ORA_RENEWED_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, DECODE (o.operation_code, 'AMEND', trunc(p.amend_effective_date), 'RENEW', trunc(p.renewed_date) ) 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, NULL AS amendment_reason_code, NULL 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 FROM oss_operation_links o, oss_products p, oss_subscriptions s WHERE o.source_subscription_product_id = p.subscription_product_id AND p.subscription_id = s.subscription_id AND o.source_prod_association_id IS NOT NULL AND o.operation_code = 'RENEW' UNION ALL SELECT DISTINCT DECODE (o.operation_code, 'RENEW', 'ORA_RENEWED_TO' ) AS relationship_code, oss_util_pub.get_code_meaning ('ORA_OSS_RELATIONSHIPS', DECODE (o.operation_code, 'RENEW', 'ORA_RENEWED_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, DECODE (o.operation_code, 'RENEW', trunc(s.renewed_date) ) 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, NULL AS amendment_reason_code, NULL 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 FROM oss_operation_links o, oss_products p, oss_products s, oss_subscriptions ts 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 o.source_prod_association_id IS NOT NULL AND o.operation_code = 'RENEW' )