View: MSD_SR_PRD_PF_V

Product:	MSD - Demand Planning
Description:	This is the Source View that associates the Product level to Product Family level in the Product Dimension. This view would be applied to an Oracle Applications 11i instance from which the Product to Product Family level association inform
Implementation/DBA Data:	APPS.MSD_SR_PRD_PF_V

View Text

SELECT DISTINCT CHILD.INVENTORY_ITEM_ID
, CHILD.CONCATENATED_SEGMENTS
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, DECODE(PARENT.ORGANIZATION_ID
, MORG.PARAMETER_VALUE
, PARENT.CONCATENATED_SEGMENTS
, MSD_SR_UTIL.GET_NULL_DESC)
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_SYSTEM_ITEMS_KFV CHILD
, MTL_SYSTEM_ITEMS_KFV PARENT
, BOM_INVENTORY_COMPONENTS_V BICV
, BOM_BILL_OF_MATERIALS_V BBOMV
, MSD_SETUP_PARAMETERS MORG
, MSD_SETUP_PARAMETERS MORG2
, MSD_APP_INSTANCE_ORGS VORGS
WHERE MORG2.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE( CHILD.ATO_FORECAST_CONTROL
, 1
, 1
, 2
, 1
, 3
, DECODE(NVL(MORG2.PARAMETER_VALUE
, 1)
, 1
, 2
, 2
, 2
, 3
, 2
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL (BICV.COMPONENT_ITEM_ID ))) = 1
AND DECODE( NVL(MORG2.PARAMETER_VALUE
, 1)
, 3
, DECODE( CHILD.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND BBOMV.BOM_ITEM_TYPE = 5
AND BBOMV.ORGANIZATION_ID = VORGS.ORGANIZATION_ID
AND BBOMV.BILL_SEQUENCE_ID = BICV.BILL_SEQUENCE_ID
AND BICV.COMPONENT_ITEM_ID = CHILD.INVENTORY_ITEM_ID
AND BBOMV.ORGANIZATION_ID = CHILD.ORGANIZATION_ID
AND CHILD.MRP_PLANNING_CODE <> 6
AND BBOMV.ORGANIZATION_ID = PARENT.ORGANIZATION_ID
AND BBOMV.ASSEMBLY_ITEM_ID = PARENT.INVENTORY_ITEM_ID
AND PARENT.ATO_FORECAST_CONTROL <> 3
AND PARENT.MRP_PLANNING_CODE <> 6
AND MORG.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND ( (PARENT.ORGANIZATION_ID = MORG.PARAMETER_VALUE ) OR (PARENT.ORGANIZATION_ID <> MORG.PARAMETER_VALUE
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_KFV CHILD1
, MTL_SYSTEM_ITEMS_KFV PARENT1
, BOM_BILL_OF_MATERIALS_V BBOMV1
, BOM_INVENTORY_COMPONENTS_V BICV1
, MSD_SETUP_PARAMETERS MORG1
, MSD_SETUP_PARAMETERS MORG4
WHERE BBOMV1.BOM_ITEM_TYPE = 5
AND BBOMV1.BILL_SEQUENCE_ID = BICV1.BILL_SEQUENCE_ID
AND BICV1.COMPONENT_ITEM_ID = CHILD1.INVENTORY_ITEM_ID
AND BBOMV1.ORGANIZATION_ID = CHILD1.ORGANIZATION_ID
AND MORG4.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND DECODE( CHILD1.ATO_FORECAST_CONTROL
, 1
, 1
, 2
, 1
, 3
, DECODE(NVL(MORG4.PARAMETER_VALUE
, 1)
, 1
, 2
, 2
, 2
, 3
, 2
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_LVL (BICV1.COMPONENT_ITEM_ID ))) = 1
AND DECODE( NVL(MORG4.PARAMETER_VALUE
, 1)
, 3
, DECODE( CHILD1.BOM_ITEM_TYPE
, 2
, 2
, 1)
, 1) = 1
AND CHILD1.MRP_PLANNING_CODE <> 6
AND BBOMV1.ORGANIZATION_ID = PARENT1.ORGANIZATION_ID
AND BBOMV1.ASSEMBLY_ITEM_ID = PARENT1.INVENTORY_ITEM_ID
AND PARENT1.ATO_FORECAST_CONTROL <> 3
AND PARENT1.MRP_PLANNING_CODE <> 6
AND MORG1.PARAMETER_NAME = 'MSD_MASTER_ORG'
AND PARENT1.ORGANIZATION_ID = MORG1.PARAMETER_VALUE
AND CHILD1.INVENTORY_ITEM_ID = CHILD.INVENTORY_ITEM_ID ) ) )

Columns