View: MSD_NPI_RELATED_PROD_DETAILS_V

Product:	MSD - Demand Planning
Description:	This API holds DETAILS information for New Product Introductions.
Implementation/DBA Data:	APPS.MSD_NPI_RELATED_PROD_DETAILS_V

View Text

SELECT ROW_ID
, INSTANCE
, EVENT_ID
, SEQ_ID
, DETAIL_ID
, RELATION_ID
, PRODUCT_LVL_ID
, PRODUCT_LVL_NAME
, PRODUCT_LVL_VAL
, PRODUCT_LVL_PK
, SR_PRODUCT_LVL_PK
, PRODUCT_WEIGHT
, LAG_TIME
, START_TIME
, END_TIME
, QTY_MODIFICATION_TYPE_ID
, QTY_MODIFICATION_TYPE
, QTY_MODIFICATION_FACTOR
, NPI_PROD_RELATIONSHIP_CODE
, NPI_PROD_RELATIONSHIP
, GEOGRAPHY_LVL_ID
, GEOGRAPHY_LVL_NAME
, GEOGRAPHY_LVL_VAL
, GEOGRAPHY_LVL_PK
, SR_GEOGRAPHY_LVL_PK
, SALESCHANNEL_LVL_ID
, SALESCHANNEL_LVL_NAME
, SALESCHANNEL_LVL_VAL
, SALESCHANNEL_LVL_PK
, SR_SALESCHANNEL_LVL_PK
, SALESREP_LVL_ID
, SALESREP_LVL_NAME
, SALESREP_LVL_VAL
, SALESREP_LVL_PK
, SR_SALESREP_LVL_PK
, ORGANIZATION_LVL_ID
, ORGANIZATION_LVL_NAME
, ORGANIZATION_LVL_VAL
, ORGANIZATION_LVL_PK
, SR_ORGANIZATION_LVL_PK
, USER_DEFINED1_LVL_ID
, USER_DEFINED1_LVL_NAME
, USER_DEFINED1_LVL_VAL
, USER_DEFINED1_LVL_PK
, USER_DEFINED2_LVL_ID
, USER_DEFINED2_LVL_NAME
, USER_DEFINED2_LVL_VAL
, USER_DEFINED2_LVL_PK
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
FROM ( SELECT MED.ROWID ROW_ID
, MED.INSTANCE
, MED.EVENT_ID
, MED.SEQ_ID
, MED.DETAIL_ID
, MPR.RELATION_ID
, MPR.PRODUCT_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MEP.PRODUCT_LVL_ID) PRODUCT_LVL_NAME
, PRD.LEVEL_VALUE PRODUCT_LVL_VAL
, PRD.LEVEL_PK PRODUCT_LVL_PK
, MPR.SR_PRODUCT_LVL_PK
, PRD.SR_LEVEL_PK PRD_SR_LEVEL_PK
, (100 - MPR.QTY_MODIFICATION_FACTOR) PRODUCT_WEIGHT
, MPR.LAG LAG_TIME
, MPR.START_TIME
, MPR.END_TIME
, MPR.QTY_MODIFICATION_TYPE QTY_MODIFICATION_TYPE_ID
, FLV1.MEANING QTY_MODIFICATION_TYPE
, MPR.QTY_MODIFICATION_FACTOR
, MPR.NPI_PROD_RELATIONSHIP NPI_PROD_RELATIONSHIP_CODE
, FLV.MEANING NPI_PROD_RELATIONSHIP
, MED.GEOGRAPHY_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.GEOGRAPHY_LVL_ID) GEOGRAPHY_LVL_NAME
, GEO.LEVEL_VALUE GEOGRAPHY_LVL_VAL
, GEO.LEVEL_PK GEOGRAPHY_LVL_PK
, MED.SR_GEOGRAPHY_LVL_PK
, GEO.SR_LEVEL_PK GEO_SR_LEVEL_PK
, MED.SALESCHANNEL_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.SALESCHANNEL_LVL_ID) SALESCHANNEL_LVL_NAME
, CHN.LEVEL_VALUE SALESCHANNEL_LVL_VAL
, CHN.LEVEL_PK SALESCHANNEL_LVL_PK
, MED.SR_SALESCHANNEL_LVL_PK
, CHN.SR_LEVEL_PK CHN_SR_LEVEL_PK
, MED.SALESREP_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.SALESREP_LVL_ID) SALESREP_LVL_NAME
, REP.LEVEL_VALUE SALESREP_LVL_VAL
, REP.LEVEL_PK SALESREP_LVL_PK
, MED.SR_SALESREP_LVL_PK
, REP.SR_LEVEL_PK REP_SR_LEVEL_PK
, MED.ORGANIZATION_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.ORGANIZATION_LVL_ID) ORGANIZATION_LVL_NAME
, ORG.LEVEL_VALUE ORGANIZATION_LVL_VAL
, ORG.LEVEL_PK ORGANIZATION_LVL_PK
, MED.SR_ORGANIZATION_LVL_PK
, ORG.SR_LEVEL_PK ORG_SR_LEVEL_PK
, MED.USER_DEFINED1_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.USER_DEFINED1_LVL_ID) USER_DEFINED1_LVL_NAME
, MED.USER_DEFINED1_LVL_VAL
, MED.USER_DEFINED1_LVL_PK
, MED.USER_DEFINED2_LVL_ID
, MSD_COMMON_UTILITIES.GET_LEVEL_NAME(MED.USER_DEFINED2_LVL_ID) USER_DEFINED2_LVL_NAME
, MED.USER_DEFINED2_LVL_VAL
, MED.USER_DEFINED2_LVL_PK
, MED.LAST_UPDATE_DATE
, MED.LAST_UPDATED_BY
, MED.CREATION_DATE
, MED.CREATED_BY
, MED.LAST_UPDATE_LOGIN
, MED.REQUEST_ID
, MED.PROGRAM_APPLICATION_ID
, MED.PROGRAM_ID
, MED.PROGRAM_UPDATE_DATE
FROM FND_LOOKUP_VALUES_VL FLV1
, FND_LOOKUP_VALUES_VL FLV
, MSD_EVENTS ME
, MSD_EVENT_PRODUCTS MEP
, MSD_EVT_PRODUCT_DETAILS MED
, MSD_EVT_PROD_RELATIONSHIPS MPR
, MSD_LEVEL_VALUES PRD
, MSD_LEVEL_VALUES ORG
, MSD_LEVEL_VALUES GEO
, MSD_LEVEL_VALUES CHN
, MSD_LEVEL_VALUES REP
WHERE FLV1.LOOKUP_CODE (+) = MPR.QTY_MODIFICATION_TYPE
AND FLV1.LOOKUP_TYPE (+) = 'MSD_MODIFICATION_TYPE'
AND ME.EVENT_ID = MEP.EVENT_ID
AND MPR.EVENT_ID = MEP.EVENT_ID
AND MPR.SEQ_ID = MEP.SEQ_ID
AND MED.RELATION_ID = MPR.RELATION_ID
AND MED.EVENT_ID = MPR.EVENT_ID
AND MED.SEQ_ID = MPR.SEQ_ID
AND MED.RELATION_ID IS NOT NULL
AND FLV.LOOKUP_CODE = MPR.NPI_PROD_RELATIONSHIP
AND FLV.LOOKUP_TYPE = 'MSD_RELATIONSHIP_TYPE'
AND PRD.INSTANCE (+) = MPR.INSTANCE
AND PRD.LEVEL_ID (+) = MPR.PRODUCT_LVL_ID
AND PRD.SR_LEVEL_PK (+) = MPR.SR_PRODUCT_LVL_PK
AND ORG.INSTANCE (+) = MED.INSTANCE
AND ORG.LEVEL_ID (+) = MED.ORGANIZATION_LVL_ID
AND ORG.SR_LEVEL_PK (+) = MED.SR_ORGANIZATION_LVL_PK
AND GEO.INSTANCE (+) = MED.INSTANCE
AND GEO.LEVEL_ID (+) = MED.GEOGRAPHY_LVL_ID
AND GEO.SR_LEVEL_PK (+) = MED.SR_GEOGRAPHY_LVL_PK
AND CHN.INSTANCE (+) = MED.INSTANCE
AND CHN.LEVEL_ID (+) = MED.SALESCHANNEL_LVL_ID
AND CHN.SR_LEVEL_PK (+) = MED.SR_SALESCHANNEL_LVL_PK
AND REP.INSTANCE (+) = MED.INSTANCE
AND REP.LEVEL_ID (+) = MED.SALESREP_LVL_ID
AND REP.SR_LEVEL_PK (+) = MED.SR_SALESREP_LVL_PK )
WHERE (PRD_SR_LEVEL_PK = SR_PRODUCT_LVL_PK OR SR_PRODUCT_LVL_PK IS NULL)
AND (ORG_SR_LEVEL_PK = SR_ORGANIZATION_LVL_PK OR SR_ORGANIZATION_LVL_PK IS NULL)
AND (GEO_SR_LEVEL_PK = SR_GEOGRAPHY_LVL_PK OR SR_GEOGRAPHY_LVL_PK IS NULL)
AND (CHN_SR_LEVEL_PK = SR_SALESCHANNEL_LVL_PK OR SR_SALESCHANNEL_LVL_PK IS NULL)
AND (REP_SR_LEVEL_PK = SR_SALESREP_LVL_PK OR SR_SALESREP_LVL_PK IS NULL)

Columns