VIEW: APPS.CST_PAC_PROCESS_PHASES_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )