MSC_DERIVED_ITEM_SUBST

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
PLAN_ID ORGANIZATION_ID MASTER_ORG_ID SUBSTITUTE_ID PART_KEY1 LOWER_ITEM_ID HIGHER_ITEM_ID HIGHEST_ITEM_ID CUSTOMER_ID CUSTOMER_SITE_ID EFFECTIVE_DATE DISABLE_DATE RELATIONSHIP_TYPE RECIPROCAL_FLAG SUBSTITUTION_SET PARTIAL_FULFILLMENT_FLAG RANK USAGE_RATIO INFERRED_FLAG PLANNING_ENABLED_FLAG ORG_INDEPENDENT_FLAG RELATIONSHIP_DESC INHERITED_SUBST_FLAG REFRESH_NUMBER LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN CREATION_DATE CREATED_BY

Name

PLAN_ID

ORGANIZATION_ID

MASTER_ORG_ID

SUBSTITUTE_ID

PART_KEY1

LOWER_ITEM_ID

HIGHER_ITEM_ID

HIGHEST_ITEM_ID

CUSTOMER_ID

CUSTOMER_SITE_ID

EFFECTIVE_DATE

DISABLE_DATE

RELATIONSHIP_TYPE

RECIPROCAL_FLAG

SUBSTITUTION_SET

PARTIAL_FULFILLMENT_FLAG

RANK

USAGE_RATIO

INFERRED_FLAG

PLANNING_ENABLED_FLAG

ORG_INDEPENDENT_FLAG

RELATIONSHIP_DESC

INHERITED_SUBST_FLAG

REFRESH_NUMBER

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

CREATION_DATE

CREATED_BY

Query

SQL_Statement
/* fusion ODS master + org_specific = yes (no explosion) / SELECT PLAN_ID, ORGANIZATION_ID, MASTER_ORG_ID, SUBSTITUTE_ID, PART_KEY1, LOWER_ITEM_ID, HIGHER_ITEM_ID, HIGHEST_ITEM_ID, CUSTOMER_ID, CUSTOMER_SITE_ID, EFFECTIVE_DATE, DISABLE_DATE, RELATIONSHIP_TYPE, RECIPROCAL_FLAG, SUBSTITUTION_SET, PARTIAL_FULFILLMENT_FLAG, RANK, USAGE_RATIO, INFERRED_FLAG, PLANNING_ENABLED_FLAG, ORG_INDEPENDENT_FLAG, RELATIONSHIP_DESC, INHERITED_SUBST_FLAG, REFRESH_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY FROM MSC_DERIVED_ITEM_SUBST WHERE plan_id = -1 AND ORGANIZATION_ID > 0 UNION ALL / fusion PDS master + org_specific = yes (no explosion) / / external PDS data / SELECT subst.PLAN_ID, mpo.ORGANIZATION_ID, subst.MASTER_ORG_ID, SUBSTITUTE_ID, subst.PART_KEY1, LOWER_ITEM_ID, HIGHER_ITEM_ID, HIGHEST_ITEM_ID, CUSTOMER_ID, CUSTOMER_SITE_ID, EFFECTIVE_DATE, DISABLE_DATE, RELATIONSHIP_TYPE, RECIPROCAL_FLAG, SUBSTITUTION_SET, PARTIAL_FULFILLMENT_FLAG, RANK, USAGE_RATIO, INFERRED_FLAG, PLANNING_ENABLED_FLAG, ORG_INDEPENDENT_FLAG, RELATIONSHIP_DESC, INHERITED_SUBST_FLAG, subst.REFRESH_NUMBER, subst.LAST_UPDATE_DATE, subst.LAST_UPDATED_BY, subst.LAST_UPDATE_LOGIN, subst.CREATION_DATE, subst.CREATED_BY FROM MSC_DERIVED_ITEM_SUBST subst, MSC_PLAN_ORGANIZATIONS mpo, MSC_SYSTEM_ITEMS lowerItem, MSC_SYSTEM_ITEMS higherItem WHERE subst.plan_id = mpo.plan_id AND subst.plan_id = lowerItem.plan_id AND subst.plan_id = higherItem.plan_id AND nvl(mpo.parent_org_id, mpo.organization_id) = subst.organization_id AND subst.lower_item_id = lowerItem.inventory_item_id AND mpo.organization_id = lowerItem.organization_id AND subst.higher_item_id = higherItem.inventory_item_id AND mpo.organization_id = higherItem.organization_id UNION ALL / PDS fusion with explosion / SELECT subst.PLAN_ID, mpo.ORGANIZATION_ID, subst.ORGANIZATION_ID MASTER_ORG_ID, SUBSTITUTE_ID, subst.PART_KEY1, LOWER_ITEM_ID, HIGHER_ITEM_ID, HIGHEST_ITEM_ID, CUSTOMER_ID, CUSTOMER_SITE_ID, EFFECTIVE_DATE, DISABLE_DATE, RELATIONSHIP_TYPE, RECIPROCAL_FLAG, SUBSTITUTION_SET, PARTIAL_FULFILLMENT_FLAG, RANK, USAGE_RATIO, INFERRED_FLAG, PLANNING_ENABLED_FLAG, ORG_INDEPENDENT_FLAG, RELATIONSHIP_DESC, 1 INHERITED_SUBST_FLAG, subst.REFRESH_NUMBER, subst.LAST_UPDATE_DATE, subst.LAST_UPDATED_BY, subst.LAST_UPDATE_LOGIN, subst.CREATION_DATE, subst.CREATED_BY FROM MSC_DERIVED_ITEM_SUBST subst, MSC_PARAMETERS orgs, MSC_INSTANCE_ORGS instance, MSC_APPS_INSTANCES apps, MSC_PLAN_ORGANIZATIONS mpo, MSC_SYSTEM_ITEMS lowerItem, MSC_SYSTEM_ITEMS higherItem WHERE instance.organization_id = orgs.organization_id AND apps.instance_id = instance.sr_instance_id AND apps.apps_ver = 2 AND instance.enabled_flag = 1 AND NVL(orgs.org_specific_subst_exists, 2) = 2 AND subst.organization_id = orgs.master_organization_id AND orgs.organization_id != NVL(orgs.master_organization_id, -23453) AND mpo.plan_id = subst.plan_id AND subst.plan_id = lowerItem.plan_id AND subst.plan_id = higherItem.plan_id AND nvl(mpo.parent_org_id, mpo.organization_id) = orgs.organization_id AND mpo.organization_id = lowerItem.organization_id AND mpo.organization_id = higherItem.organization_id AND subst.lower_item_id = lowerItem.inventory_item_id AND subst.higher_item_id = higherItem.inventory_item_id UNION ALL / fusion ODS explosion / SELECT subst.PLAN_ID, orgs.ORGANIZATION_ID, subst.ORGANIZATION_ID MASTER_ORG_ID, SUBSTITUTE_ID, subst.PART_KEY1, LOWER_ITEM_ID, HIGHER_ITEM_ID, HIGHEST_ITEM_ID, CUSTOMER_ID, CUSTOMER_SITE_ID, EFFECTIVE_DATE, DISABLE_DATE, RELATIONSHIP_TYPE, RECIPROCAL_FLAG, SUBSTITUTION_SET, PARTIAL_FULFILLMENT_FLAG, RANK, USAGE_RATIO, INFERRED_FLAG, PLANNING_ENABLED_FLAG, ORG_INDEPENDENT_FLAG, RELATIONSHIP_DESC, 1 INHERITED_SUBST_FLAG, subst.REFRESH_NUMBER, subst.LAST_UPDATE_DATE, subst.LAST_UPDATED_BY, subst.LAST_UPDATE_LOGIN, subst.CREATION_DATE, subst.CREATED_BY FROM MSC_DERIVED_ITEM_SUBST subst, MSC_PARAMETERS orgs, MSC_INSTANCE_ORGS instance, MSC_APPS_INSTANCES apps, MSC_SYSTEM_ITEMS lowerItem, MSC_SYSTEM_ITEMS higherItem WHERE instance.organization_id = orgs.organization_id AND apps.instance_id = instance.sr_instance_id AND apps.apps_ver = 2 AND instance.enabled_flag = 1 AND NVL(orgs.org_specific_subst_exists, 2) = 2 AND subst.organization_id = orgs.master_organization_id AND orgs.organization_id != NVL(orgs.master_organization_id, -23453) AND subst.plan_id = -1 AND subst.plan_id = lowerItem.plan_id AND subst.plan_id = higherItem.plan_id AND subst.part_key1 = lowerItem.part_key1 AND subst.part_key1 = higherItem.part_key1 AND orgs.organization_id = lowerItem.organization_id AND orgs.organization_id = higherItem.organization_id AND subst.lower_item_id = lowerItem.inventory_item_id AND subst.higher_item_id = higherItem.inventory_item_id UNION ALL / external ODS explosion / SELECT subst.PLAN_ID, instance.ORGANIZATION_ID, instance.ORGANIZATION_ID MASTER_ORG_ID, SUBSTITUTE_ID, subst.PART_KEY1, LOWER_ITEM_ID, HIGHER_ITEM_ID, HIGHEST_ITEM_ID, CUSTOMER_ID, CUSTOMER_SITE_ID, EFFECTIVE_DATE, DISABLE_DATE, RELATIONSHIP_TYPE, RECIPROCAL_FLAG, SUBSTITUTION_SET, PARTIAL_FULFILLMENT_FLAG, RANK, USAGE_RATIO, INFERRED_FLAG, PLANNING_ENABLED_FLAG, ORG_INDEPENDENT_FLAG, RELATIONSHIP_DESC, 2 INHERITED_SUBST_FLAG, subst.REFRESH_NUMBER, subst.LAST_UPDATE_DATE, subst.LAST_UPDATED_BY, subst.LAST_UPDATE_LOGIN, subst.CREATION_DATE, subst.CREATED_BY FROM MSC_DERIVED_ITEM_SUBST subst, MSC_INSTANCE_ORGS instance, MSC_APPS_INSTANCES apps, MSC_SYSTEM_ITEMS lowerItem, MSC_SYSTEM_ITEMS higherItem WHERE apps.instance_id = instance.sr_instance_id AND apps.apps_ver != 2 / external / AND instance.enabled_flag = 1 AND apps.short_instance_id = -1 subst.organization_id AND subst.plan_id = -1 AND subst.plan_id = lowerItem.plan_id AND subst.plan_id = higherItem.plan_id AND instance.organization_id = lowerItem.organization_id AND instance.organization_id = higherItem.organization_id AND subst.lower_item_id = lowerItem.inventory_item_id AND subst.higher_item_id = higherItem.inventory_item_id AND subst.part_key1 = lowerItem.part_key1 AND subst.part_key1 = higherItem.part_key1

SQL_Statement

/* fusion ODS master + org_specific = yes (no explosion) */

SELECT PLAN_ID,

ORGANIZATION_ID,

MASTER_ORG_ID,

SUBSTITUTE_ID,

PART_KEY1,

LOWER_ITEM_ID,

HIGHER_ITEM_ID,

HIGHEST_ITEM_ID,

CUSTOMER_ID,

CUSTOMER_SITE_ID,

EFFECTIVE_DATE,

DISABLE_DATE,

RELATIONSHIP_TYPE,

RECIPROCAL_FLAG,

SUBSTITUTION_SET,

PARTIAL_FULFILLMENT_FLAG,

RANK,

USAGE_RATIO,

INFERRED_FLAG,

PLANNING_ENABLED_FLAG,

ORG_INDEPENDENT_FLAG,

RELATIONSHIP_DESC,

INHERITED_SUBST_FLAG,

REFRESH_NUMBER,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_LOGIN,

CREATION_DATE,

CREATED_BY

FROM MSC_DERIVED_ITEM_SUBST

WHERE plan_id = -1

AND ORGANIZATION_ID > 0

UNION ALL

/* fusion PDS master + org_specific = yes (no explosion) */

/* external PDS data */

SELECT subst.PLAN_ID,

mpo.ORGANIZATION_ID,

subst.MASTER_ORG_ID,

SUBSTITUTE_ID,

subst.PART_KEY1,

LOWER_ITEM_ID,

HIGHER_ITEM_ID,

HIGHEST_ITEM_ID,

CUSTOMER_ID,

CUSTOMER_SITE_ID,

EFFECTIVE_DATE,

DISABLE_DATE,

RELATIONSHIP_TYPE,

RECIPROCAL_FLAG,

SUBSTITUTION_SET,

PARTIAL_FULFILLMENT_FLAG,

RANK,

USAGE_RATIO,

INFERRED_FLAG,

PLANNING_ENABLED_FLAG,

ORG_INDEPENDENT_FLAG,

RELATIONSHIP_DESC,

INHERITED_SUBST_FLAG,

subst.REFRESH_NUMBER,

subst.LAST_UPDATE_DATE,

subst.LAST_UPDATED_BY,

subst.LAST_UPDATE_LOGIN,

subst.CREATION_DATE,

subst.CREATED_BY

FROM MSC_DERIVED_ITEM_SUBST subst,

MSC_PLAN_ORGANIZATIONS mpo,

MSC_SYSTEM_ITEMS lowerItem,

MSC_SYSTEM_ITEMS higherItem

WHERE subst.plan_id = mpo.plan_id

AND subst.plan_id = lowerItem.plan_id

AND subst.plan_id = higherItem.plan_id

AND nvl(mpo.parent_org_id, mpo.organization_id) = subst.organization_id

AND subst.lower_item_id = lowerItem.inventory_item_id

AND mpo.organization_id = lowerItem.organization_id

AND subst.higher_item_id = higherItem.inventory_item_id

AND mpo.organization_id = higherItem.organization_id

UNION ALL

/* PDS fusion with explosion */

SELECT subst.PLAN_ID,

mpo.ORGANIZATION_ID,

subst.ORGANIZATION_ID MASTER_ORG_ID,

SUBSTITUTE_ID,

subst.PART_KEY1,

LOWER_ITEM_ID,

HIGHER_ITEM_ID,

HIGHEST_ITEM_ID,

CUSTOMER_ID,

CUSTOMER_SITE_ID,

EFFECTIVE_DATE,

DISABLE_DATE,

RELATIONSHIP_TYPE,

RECIPROCAL_FLAG,

SUBSTITUTION_SET,

PARTIAL_FULFILLMENT_FLAG,

RANK,

USAGE_RATIO,

INFERRED_FLAG,

PLANNING_ENABLED_FLAG,

ORG_INDEPENDENT_FLAG,

RELATIONSHIP_DESC,

1 INHERITED_SUBST_FLAG,

subst.REFRESH_NUMBER,

subst.LAST_UPDATE_DATE,

subst.LAST_UPDATED_BY,

subst.LAST_UPDATE_LOGIN,

subst.CREATION_DATE,

subst.CREATED_BY

FROM MSC_DERIVED_ITEM_SUBST subst,

MSC_PARAMETERS orgs,

MSC_INSTANCE_ORGS instance,

MSC_APPS_INSTANCES apps,

MSC_PLAN_ORGANIZATIONS mpo,

MSC_SYSTEM_ITEMS lowerItem,

MSC_SYSTEM_ITEMS higherItem

WHERE instance.organization_id = orgs.organization_id

AND apps.instance_id = instance.sr_instance_id

AND apps.apps_ver = 2

AND instance.enabled_flag = 1

AND NVL(orgs.org_specific_subst_exists, 2) = 2

AND subst.organization_id = orgs.master_organization_id

AND orgs.organization_id != NVL(orgs.master_organization_id, -23453)

AND mpo.plan_id = subst.plan_id

AND subst.plan_id = lowerItem.plan_id

AND subst.plan_id = higherItem.plan_id

AND nvl(mpo.parent_org_id, mpo.organization_id) = orgs.organization_id

AND mpo.organization_id = lowerItem.organization_id

AND mpo.organization_id = higherItem.organization_id

AND subst.lower_item_id = lowerItem.inventory_item_id

AND subst.higher_item_id = higherItem.inventory_item_id

UNION ALL

/* fusion ODS explosion */

SELECT subst.PLAN_ID,

orgs.ORGANIZATION_ID,

subst.ORGANIZATION_ID MASTER_ORG_ID,

SUBSTITUTE_ID,

subst.PART_KEY1,

LOWER_ITEM_ID,

HIGHER_ITEM_ID,

HIGHEST_ITEM_ID,

CUSTOMER_ID,

CUSTOMER_SITE_ID,

EFFECTIVE_DATE,

DISABLE_DATE,

RELATIONSHIP_TYPE,

RECIPROCAL_FLAG,

SUBSTITUTION_SET,

PARTIAL_FULFILLMENT_FLAG,

RANK,

USAGE_RATIO,

INFERRED_FLAG,

PLANNING_ENABLED_FLAG,

ORG_INDEPENDENT_FLAG,

RELATIONSHIP_DESC,

1 INHERITED_SUBST_FLAG,

subst.REFRESH_NUMBER,

subst.LAST_UPDATE_DATE,

subst.LAST_UPDATED_BY,

subst.LAST_UPDATE_LOGIN,

subst.CREATION_DATE,

subst.CREATED_BY

FROM MSC_DERIVED_ITEM_SUBST subst,

MSC_PARAMETERS orgs,

MSC_INSTANCE_ORGS instance,

MSC_APPS_INSTANCES apps,

MSC_SYSTEM_ITEMS lowerItem,

MSC_SYSTEM_ITEMS higherItem

WHERE instance.organization_id = orgs.organization_id

AND apps.instance_id = instance.sr_instance_id

AND apps.apps_ver = 2

AND instance.enabled_flag = 1

AND NVL(orgs.org_specific_subst_exists, 2) = 2

AND subst.organization_id = orgs.master_organization_id

AND orgs.organization_id != NVL(orgs.master_organization_id, -23453)

AND subst.plan_id = -1

AND subst.plan_id = lowerItem.plan_id

AND subst.plan_id = higherItem.plan_id

AND subst.part_key1 = lowerItem.part_key1

AND subst.part_key1 = higherItem.part_key1

AND orgs.organization_id = lowerItem.organization_id

AND orgs.organization_id = higherItem.organization_id

AND subst.lower_item_id = lowerItem.inventory_item_id

AND subst.higher_item_id = higherItem.inventory_item_id

UNION ALL

/* external ODS explosion */

SELECT subst.PLAN_ID,

instance.ORGANIZATION_ID,

instance.ORGANIZATION_ID MASTER_ORG_ID,

SUBSTITUTE_ID,

subst.PART_KEY1,

LOWER_ITEM_ID,

HIGHER_ITEM_ID,

HIGHEST_ITEM_ID,

CUSTOMER_ID,

CUSTOMER_SITE_ID,

EFFECTIVE_DATE,

DISABLE_DATE,

RELATIONSHIP_TYPE,

RECIPROCAL_FLAG,

SUBSTITUTION_SET,

PARTIAL_FULFILLMENT_FLAG,

RANK,

USAGE_RATIO,

INFERRED_FLAG,

PLANNING_ENABLED_FLAG,

ORG_INDEPENDENT_FLAG,

RELATIONSHIP_DESC,

2 INHERITED_SUBST_FLAG,

subst.REFRESH_NUMBER,

subst.LAST_UPDATE_DATE,

subst.LAST_UPDATED_BY,

subst.LAST_UPDATE_LOGIN,

subst.CREATION_DATE,

subst.CREATED_BY

FROM MSC_DERIVED_ITEM_SUBST subst,

MSC_INSTANCE_ORGS instance,

MSC_APPS_INSTANCES apps,

MSC_SYSTEM_ITEMS lowerItem,

MSC_SYSTEM_ITEMS higherItem

WHERE apps.instance_id = instance.sr_instance_id

AND apps.apps_ver != 2 /* external */

AND instance.enabled_flag = 1

AND apps.short_instance_id = -1 * subst.organization_id

AND subst.plan_id = -1

AND subst.plan_id = lowerItem.plan_id

AND subst.plan_id = higherItem.plan_id

AND instance.organization_id = lowerItem.organization_id

AND instance.organization_id = higherItem.organization_id

AND subst.lower_item_id = lowerItem.inventory_item_id

AND subst.higher_item_id = higherItem.inventory_item_id

AND subst.part_key1 = lowerItem.part_key1

AND subst.part_key1 = higherItem.part_key1