View: CST_PAC_PROCESS_PHASES_V

Product:	BOM - Bills of Material
Description:	To store acquisition and cost process status information
Implementation/DBA Data:	APPS.CST_PAC_PROCESS_PHASES_V

View Text

SELECT CPPP1.PAC_PERIOD_ID PAC_PERIOD_ID
, CPPP1.COST_GROUP_ID COST_GROUP_ID
, CCG.COST_GROUP COST_GROUP
, CCG.DISABLE_DATE DISABLE_DATE
, CPPP1.PROCESS_PHASE FIRST_PHASE
, CPPP1.PROCESS_STATUS FIRST_PHASE_STATUS
, ML1.MEANING FIRST_PHASE_STATUS_NAME
, NVL(LEAST(CPPP1.PROCESS_DATE
, CPPP1.PROCESS_UPTO_DATE)
, CPPP1.PROCESS_DATE) FIRST_PHASE_PROCESS_DATE
, CPPP2.PROCESS_PHASE CURRENT_PHASE
, CPPP2.PROCESS_STATUS CURRENT_PHASE_STATUS
, ML2.MEANING CURRENT_PHASE_STATUS_NAME
, NVL(LEAST(CPPP2.PROCESS_DATE
, CPPP2.PROCESS_UPTO_DATE)
, CPPP2.PROCESS_DATE) CURRENT_PHASE_PROCESS_DATE
, CPPP3.PROCESS_PHASE DIST_PHASE
, CPPP3.PROCESS_STATUS DIST_PHASE_STATUS
, ML3.MEANING DIST_PHASE_STATUS_NAME
, NVL(LEAST(CPPP2.PROCESS_DATE
, CPPP2.PROCESS_UPTO_DATE)
, CPPP3.PROCESS_DATE) DIST_PHASE_PROCESS_DATE
, 7 INTERORG_PHASE
, DECODE(CPPP5.PROCESS_PHASE
, 7
, CPPP5.PROCESS_STATUS
, 0) INTERORG_PHASE_STATUS
, DECODE(CPPP5.PROCESS_PHASE
, 7
, ML4.MEANING
, ML5.MEANING) INTERORG_PHASE_STATUS_NAME
, DECODE(CPPP5.PROCESS_PHASE
, 7
, NVL(LEAST(CPPP2.PROCESS_DATE
, CPPP2.PROCESS_UPTO_DATE)
, CPPP5.PROCESS_DATE)
, NULL) INTERORG_PHASE_PROCESS_DATE
FROM MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
, MFG_LOOKUPS ML5
, CST_PAC_PROCESS_PHASES CPPP1
, CST_PAC_PROCESS_PHASES CPPP2
, CST_PAC_PROCESS_PHASES CPPP3
, CST_PAC_PROCESS_PHASES CPPP5
, CST_COST_GROUPS CCG
WHERE CCG.COST_GROUP_ID = CPPP1.COST_GROUP_ID
AND CCG.COST_GROUP_TYPE = 2
AND ML1.LOOKUP_TYPE = 'CST_PAC_PROCESS_STATUS'
AND ML1.LOOKUP_CODE = CPPP1.PROCESS_STATUS
AND ML1.ENABLED_FLAG = 'Y'
AND ML2.LOOKUP_TYPE = 'CST_PAC_PROCESS_STATUS'
AND ML2.LOOKUP_CODE = CPPP2.PROCESS_STATUS
AND ML2.ENABLED_FLAG = 'Y'
AND ML3.LOOKUP_TYPE = 'CST_PAC_PROCESS_STATUS'
AND ML3.LOOKUP_CODE = CPPP3.PROCESS_STATUS
AND ML3.ENABLED_FLAG = 'Y'
AND ML4.LOOKUP_TYPE = 'CST_PAC_PROCESS_STATUS'
AND ML4.LOOKUP_CODE = CPPP5.PROCESS_STATUS
AND ML4.ENABLED_FLAG = 'Y'
AND ML5.LOOKUP_TYPE = 'CST_PAC_PROCESS_STATUS'
AND ML5.LOOKUP_CODE = 0
AND ML5.ENABLED_FLAG = 'Y'
AND CPPP1.PROCESS_PHASE = 1
AND CPPP2.COST_GROUP_ID = CPPP1.COST_GROUP_ID
AND CPPP2.PAC_PERIOD_ID = CPPP1.PAC_PERIOD_ID
AND CPPP2.PROCESS_PHASE = ( SELECT NVL(MIN(CPPP4.PROCESS_PHASE)
, 5)
FROM CST_PAC_PROCESS_PHASES CPPP4
WHERE CPPP4.PROCESS_PHASE > 1
AND CPPP4.PROCESS_PHASE < 6
AND CPPP4.PROCESS_STATUS != 4
AND CPPP4.COST_GROUP_ID = CPPP2.COST_GROUP_ID
AND CPPP4.PAC_PERIOD_ID = CPPP2.PAC_PERIOD_ID )
AND CPPP3.PROCESS_PHASE = 6
AND CPPP3.COST_GROUP_ID = CPPP1.COST_GROUP_ID
AND CPPP3.PAC_PERIOD_ID = CPPP1.PAC_PERIOD_ID
AND CPPP5.COST_GROUP_ID = CPPP1.COST_GROUP_ID
AND CPPP5.PAC_PERIOD_ID = CPPP1.PAC_PERIOD_ID
AND CPPP5.PROCESS_PHASE = (SELECT DECODE(MAX(CPPP6.PROCESS_PHASE)
, 7
, 7
, 6)
FROM CST_PAC_PROCESS_PHASES CPPP6
WHERE CPPP6.COST_GROUP_ID = CPPP5.COST_GROUP_ID
AND CPPP6.PAC_PERIOD_ID = CPPP5.PAC_PERIOD_ID )

Columns