VIEW: APPS.PA_ACCUM_PERIOD_EXCEPTIONS_V

Source

View Text - Preformatted

SELECT DISTINCT MEANING STATUS , PAH.PROJECT_ID PROJECT_ID , PRJ.SEGMENT1 PROJECT_NUMBER , PRJ.NAME PROJECT_NAME , PAH.ACCUM_PERIOD ACCUM_PERIOD FROM PA_PERIODS_V PP, PA_IMPLEMENTATIONS PI, PA_LOOKUPS PL, PA_PROJECT_ACCUM_HEADERS PAH, PA_PROJECTS PRJ WHERE PAH.ACCUM_PERIOD = DECODE(PI.ACCUMULATION_PERIOD_TYPE, 'PA', PP.PERIOD_NAME,'GL',PP.GL_PERIOD_NAME) AND PAH.TASK_ID = 0 AND PAH.RESOURCE_LIST_ID = 0 AND PAH.RESOURCE_LIST_MEMBER_ID = 0 AND PAH.RESOURCE_ID = 0 AND DECODE(PI.ACCUMULATION_PERIOD_TYPE,'PA', PP.PA_END_DATE, 'GL', PP.GL_END_DATE) > PA_ACCUM_UTILS.GET_CHECK_REPORTING_END_DATE AND PL.LOOKUP_TYPE = 'ACCUM_PERIOD_EXCEPTION_STATUS' AND PL.LOOKUP_CODE = 'PROJECT_BEYOND_REP_DATE' AND PAH.PROJECT_ID = PRJ.PROJECT_ID AND NVL(PRJ.TEMPLATE_FLAG,'N') <> 'Y' UNION SELECT DISTINCT MEANING STATUS, PAH.PROJECT_ID PROJECT_ID, PRJ.SEGMENT1 PROJECT_NUMBER, PRJ.NAME PROJECT_NAME, PAH.ACCUM_PERIOD ACCUM_PERIOD FROM PA_PERIODS_V PP, PA_IMPLEMENTATIONS PI, PA_PROJECT_ACCUM_HEADERS PAH, PA_LOOKUPS PL, PA_PROJECTS PRJ WHERE PAH.ACCUM_PERIOD = DECODE(PI.ACCUMULATION_PERIOD_TYPE, 'PA', PP.PERIOD_NAME,'GL',PP.GL_PERIOD_NAME) AND PAH.TASK_ID = 0 AND PAH.RESOURCE_LIST_ID = 0 AND PAH.RESOURCE_LIST_MEMBER_ID = 0 AND PAH.RESOURCE_ID = 0 AND DECODE(PI.ACCUMULATION_PERIOD_TYPE,'PA', PP.PA_END_DATE, 'GL', PP.GL_END_DATE) < PA_ACCUM_UTILS.GET_CHECK_REPORTING_END_DATE AND PL.LOOKUP_TYPE = 'ACCUM_PERIOD_EXCEPTION_STATUS' AND PL.LOOKUP_CODE = 'PROJECT_PRIOR_REP_DATE' AND PAH.PROJECT_ID = PRJ.PROJECT_ID AND NVL(PRJ.TEMPLATE_FLAG,'N') <> 'Y' UNION SELECT DISTINCT MEANING STATUS, PPR.PROJECT_ID PROJECT_ID, PPR.SEGMENT1 PROJECT_NUMBER, PPR.NAME PROJECT_NAME, NULL ACCUM_PERIOD FROM PA_PROJECTS PPR, PA_LOOKUPS PL WHERE NVL(PPR.TEMPLATE_FLAG,'N') <> 'Y' AND PL.LOOKUP_TYPE = 'ACCUM_PERIOD_EXCEPTION_STATUS' AND PL.LOOKUP_CODE = 'PROJECT_NEVER_SUMMARIZED' AND NOT EXISTS (SELECT 'x' FROM PA_PROJECT_ACCUM_HEADERS PAH WHERE PAH.PROJECT_ID = PPR.PROJECT_ID AND PAH.TASK_ID = 0 AND PAH.RESOURCE_LIST_ID = 0 AND PAH.RESOURCE_LIST_MEMBER_ID = 0 AND PAH.RESOURCE_ID = 0 )

View Text - HTML Formatted

SELECT DISTINCT MEANING STATUS
, PAH.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PRJ.NAME PROJECT_NAME
, PAH.ACCUM_PERIOD ACCUM_PERIOD
FROM PA_PERIODS_V PP
, PA_IMPLEMENTATIONS PI
, PA_LOOKUPS PL
, PA_PROJECT_ACCUM_HEADERS PAH
, PA_PROJECTS PRJ
WHERE PAH.ACCUM_PERIOD = DECODE(PI.ACCUMULATION_PERIOD_TYPE
, 'PA'
, PP.PERIOD_NAME
, 'GL'
, PP.GL_PERIOD_NAME)
AND PAH.TASK_ID = 0
AND PAH.RESOURCE_LIST_ID = 0
AND PAH.RESOURCE_LIST_MEMBER_ID = 0
AND PAH.RESOURCE_ID = 0
AND DECODE(PI.ACCUMULATION_PERIOD_TYPE
, 'PA'
, PP.PA_END_DATE
, 'GL'
, PP.GL_END_DATE) > PA_ACCUM_UTILS.GET_CHECK_REPORTING_END_DATE
AND PL.LOOKUP_TYPE = 'ACCUM_PERIOD_EXCEPTION_STATUS'
AND PL.LOOKUP_CODE = 'PROJECT_BEYOND_REP_DATE'
AND PAH.PROJECT_ID = PRJ.PROJECT_ID
AND NVL(PRJ.TEMPLATE_FLAG
, 'N') <> 'Y' UNION SELECT DISTINCT MEANING STATUS
, PAH.PROJECT_ID PROJECT_ID
, PRJ.SEGMENT1 PROJECT_NUMBER
, PRJ.NAME PROJECT_NAME
, PAH.ACCUM_PERIOD ACCUM_PERIOD
FROM PA_PERIODS_V PP
, PA_IMPLEMENTATIONS PI
, PA_PROJECT_ACCUM_HEADERS PAH
, PA_LOOKUPS PL
, PA_PROJECTS PRJ
WHERE PAH.ACCUM_PERIOD = DECODE(PI.ACCUMULATION_PERIOD_TYPE
, 'PA'
, PP.PERIOD_NAME
, 'GL'
, PP.GL_PERIOD_NAME)
AND PAH.TASK_ID = 0
AND PAH.RESOURCE_LIST_ID = 0
AND PAH.RESOURCE_LIST_MEMBER_ID = 0
AND PAH.RESOURCE_ID = 0
AND DECODE(PI.ACCUMULATION_PERIOD_TYPE
, 'PA'
, PP.PA_END_DATE
, 'GL'
, PP.GL_END_DATE) < PA_ACCUM_UTILS.GET_CHECK_REPORTING_END_DATE
AND PL.LOOKUP_TYPE = 'ACCUM_PERIOD_EXCEPTION_STATUS'
AND PL.LOOKUP_CODE = 'PROJECT_PRIOR_REP_DATE'
AND PAH.PROJECT_ID = PRJ.PROJECT_ID
AND NVL(PRJ.TEMPLATE_FLAG
, 'N') <> 'Y' UNION SELECT DISTINCT MEANING STATUS
, PPR.PROJECT_ID PROJECT_ID
, PPR.SEGMENT1 PROJECT_NUMBER
, PPR.NAME PROJECT_NAME
, NULL ACCUM_PERIOD
FROM PA_PROJECTS PPR
, PA_LOOKUPS PL
WHERE NVL(PPR.TEMPLATE_FLAG
, 'N') <> 'Y'
AND PL.LOOKUP_TYPE = 'ACCUM_PERIOD_EXCEPTION_STATUS'
AND PL.LOOKUP_CODE = 'PROJECT_NEVER_SUMMARIZED'
AND NOT EXISTS (SELECT 'X'
FROM PA_PROJECT_ACCUM_HEADERS PAH
WHERE PAH.PROJECT_ID = PPR.PROJECT_ID
AND PAH.TASK_ID = 0
AND PAH.RESOURCE_LIST_ID = 0
AND PAH.RESOURCE_LIST_MEMBER_ID = 0
AND PAH.RESOURCE_ID = 0 )