VIEW: APPS.PA_CP_GENERATE_ASSET_V

Source

View Text - Preformatted

SELECT PP.PROJECT_ID , PP.SEGMENT1 , PP.NAME , L1.MEANING , PT.ORG_ID FROM PA_PROJECTS_ALL PP, PA_PROJECT_TYPES PT, PA_LOOKUPS L1 WHERE PP.TEMPLATE_FLAG <> 'Y' AND PP.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON' AND L1.LOOKUP_CODE||'' = 'NO_ASSET_ASSIGN' AND PT.ORG_ID= PP.ORG_ID AND NOT EXISTS (SELECT 'X' FROM PA_PROJECT_ASSET_ASSIGNMENTS PPAA WHERE PPAA.PROJECT_ID = PP.PROJECT_ID) UNION SELECT PP.PROJECT_ID, PP.SEGMENT1, PP.NAME, L1.MEANING, PT.ORG_ID FROM PA_PROJECTS_ALL PP, PA_PROJECT_TYPES PT, PA_LOOKUPS L1 WHERE PP.TEMPLATE_FLAG <> 'Y' AND PP.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON' AND L1.LOOKUP_CODE||'' = 'NO_ELIG_COSTS' AND PT.ORG_ID = PP.ORG_ID AND NOT EXISTS (SELECT 'X' FROM PA_TASKS T, PA_EXPENDITURE_ITEMS_ALL EI, PA_COST_DISTRIBUTION_LINES_ALL CDL WHERE T.PROJECT_ID = PP.PROJECT_ID AND T.TASK_ID = EI.TASK_ID AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID AND CDL.BILLABLE_FLAG = 'Y' ) UNION SELECT PP.PROJECT_ID, PP.SEGMENT1, PP.NAME, L1.MEANING, PT.ORG_ID FROM PA_PROJECTS_ALL PP, PA_PROJECT_TYPES PT, PA_LOOKUPS L1 WHERE PP.TEMPLATE_FLAG <> 'Y' AND PP.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON' AND L1.LOOKUP_CODE||'' = 'NO_VALID_DATES' AND PT.ORG_ID = PP.ORG_ID AND NOT EXISTS (SELECT 'X' FROM PA_PROJECT_ASSETS A WHERE A.PROJECT_ID = PP.PROJECT_ID AND A.DATE_PLACED_IN_SERVICE <= PA_FAXFACE.GET_IN_SERVICE_THRU_DATE) UNION SELECT PP.PROJECT_ID, PP.SEGMENT1, PP.NAME, L1.MEANING, PT.ORG_ID FROM PA_PROJECTS_ALL PP, PA_PROJECT_TYPES PT, PA_LOOKUPS L1 WHERE PP.TEMPLATE_FLAG <> 'Y' AND PP.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON' AND L1.LOOKUP_CODE||'' = 'NO_PROJ_STAT_ACTION_ALLOW' AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(PP.PROJECT_STATUS_CODE, 'CAPITALIZE') <> 'Y' AND PT.ORG_ID = PP.ORG_ID UNION SELECT PP.PROJECT_ID, PP.SEGMENT1, PP.NAME, L1.MEANING, PT.ORG_ID FROM PA_PROJECTS PP, PA_PROJECT_TYPES PT, PA_LOOKUPS L1 WHERE PP.TEMPLATE_FLAG <> 'Y' AND PP.PROJECT_TYPE = PT.PROJECT_TYPE AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL' AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON' AND L1.LOOKUP_CODE||'' = 'PRJ_TSK_ASSET_ASSIGN' AND EXISTS (SELECT 'X' FROM PA_PROJECT_ASSET_ASSIGNMENTS PPAA1, PA_PROJECT_ASSET_ASSIGNMENTS PPAA2 WHERE PPAA1.PROJECT_ID = PP.PROJECT_ID AND PPAA1.PROJECT_ID = PPAA2.PROJECT_ID AND ((PPAA1.PROJECT_ID <> 0 AND PPAA1.TASK_ID = 0) AND (PPAA2.PROJECT_ID <> 0 AND PPAA2.TASK_ID <> 0))) AND PT.ORG_ID = PP.ORG_ID

View Text - HTML Formatted

SELECT PP.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, L1.MEANING
, PT.ORG_ID
FROM PA_PROJECTS_ALL PP
, PA_PROJECT_TYPES PT
, PA_LOOKUPS L1
WHERE PP.TEMPLATE_FLAG <> 'Y'
AND PP.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON'
AND L1.LOOKUP_CODE||'' = 'NO_ASSET_ASSIGN'
AND PT.ORG_ID= PP.ORG_ID
AND NOT EXISTS (SELECT 'X'
FROM PA_PROJECT_ASSET_ASSIGNMENTS PPAA
WHERE PPAA.PROJECT_ID = PP.PROJECT_ID) UNION SELECT PP.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, L1.MEANING
, PT.ORG_ID
FROM PA_PROJECTS_ALL PP
, PA_PROJECT_TYPES PT
, PA_LOOKUPS L1
WHERE PP.TEMPLATE_FLAG <> 'Y'
AND PP.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON'
AND L1.LOOKUP_CODE||'' = 'NO_ELIG_COSTS'
AND PT.ORG_ID = PP.ORG_ID
AND NOT EXISTS (SELECT 'X'
FROM PA_TASKS T
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_COST_DISTRIBUTION_LINES_ALL CDL
WHERE T.PROJECT_ID = PP.PROJECT_ID
AND T.TASK_ID = EI.TASK_ID
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL.BILLABLE_FLAG = 'Y' ) UNION SELECT PP.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, L1.MEANING
, PT.ORG_ID
FROM PA_PROJECTS_ALL PP
, PA_PROJECT_TYPES PT
, PA_LOOKUPS L1
WHERE PP.TEMPLATE_FLAG <> 'Y'
AND PP.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON'
AND L1.LOOKUP_CODE||'' = 'NO_VALID_DATES'
AND PT.ORG_ID = PP.ORG_ID
AND NOT EXISTS (SELECT 'X'
FROM PA_PROJECT_ASSETS A
WHERE A.PROJECT_ID = PP.PROJECT_ID
AND A.DATE_PLACED_IN_SERVICE <= PA_FAXFACE.GET_IN_SERVICE_THRU_DATE) UNION SELECT PP.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, L1.MEANING
, PT.ORG_ID
FROM PA_PROJECTS_ALL PP
, PA_PROJECT_TYPES PT
, PA_LOOKUPS L1
WHERE PP.TEMPLATE_FLAG <> 'Y'
AND PP.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON'
AND L1.LOOKUP_CODE||'' = 'NO_PROJ_STAT_ACTION_ALLOW'
AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(PP.PROJECT_STATUS_CODE
, 'CAPITALIZE') <> 'Y'
AND PT.ORG_ID = PP.ORG_ID UNION SELECT PP.PROJECT_ID
, PP.SEGMENT1
, PP.NAME
, L1.MEANING
, PT.ORG_ID
FROM PA_PROJECTS PP
, PA_PROJECT_TYPES PT
, PA_LOOKUPS L1
WHERE PP.TEMPLATE_FLAG <> 'Y'
AND PP.PROJECT_TYPE = PT.PROJECT_TYPE
AND PT.PROJECT_TYPE_CLASS_CODE = 'CAPITAL'
AND L1.LOOKUP_TYPE = 'GEN_ASSET_REJECTION_REASON'
AND L1.LOOKUP_CODE||'' = 'PRJ_TSK_ASSET_ASSIGN'
AND EXISTS (SELECT 'X'
FROM PA_PROJECT_ASSET_ASSIGNMENTS PPAA1
, PA_PROJECT_ASSET_ASSIGNMENTS PPAA2
WHERE PPAA1.PROJECT_ID = PP.PROJECT_ID
AND PPAA1.PROJECT_ID = PPAA2.PROJECT_ID
AND ((PPAA1.PROJECT_ID <> 0
AND PPAA1.TASK_ID = 0)
AND (PPAA2.PROJECT_ID <> 0
AND PPAA2.TASK_ID <> 0)))
AND PT.ORG_ID = PP.ORG_ID