VIEW: APPS.PA_PROJECT_CLASS_TOTALS_V

Source

View Text - Preformatted

SELECT pc.class_category , pc.object_id , PA_PROJECTS_MAINT_UTILS.GET_CLASS_CODES(pc.object_id, pc.object_type, pc.class_category) , sum(pc.code_percentage) , PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(pc.object_id, pc.object_type, pc.class_category, 'N') , decode(PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(pc.object_id, pc.object_type, pc.class_category, 'N'), NULL, 'C', 'A') , pc.code_description FROM PA_PROJECT_CLASSES_V pc WHERE pc.object_type = 'PA_PROJECTS' GROUP BY pc.class_category , pc.object_id , PA_PROJECTS_MAINT_UTILS.GET_CLASS_CODES(pc.object_id, pc.object_type, pc.class_category) , PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(pc.object_id, pc.object_type, pc.class_category, 'N') , decode(PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(pc.object_id, pc.object_type, pc.class_category, 'N'), NULL, 'C', 'A') , pc.code_description UNION SELECT vc.class_category , ppa.project_id , NULL , to_number(NULL) , PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(NULL, NULL, NULL, 'Y') , 'B' , NULL FROM PA_PROJECTS_ALL ppa, PA_VALID_CATEGORIES_V vc, PA_PROJECT_TYPES_ALL ppta WHERE vc.mandatory_flag = 'Y' AND ppa.project_type = ppta.project_type AND ppa.org_id = ppta.org_id AND vc.object_type_id = ppta.project_type_id AND NOT EXISTS (SELECT NULL FROM PA_PROJECT_CLASSES pc WHERE pc.project_id = ppa.project_id AND pc.class_category = vc.class_category)

View Text - HTML Formatted

SELECT PC.CLASS_CATEGORY
, PC.OBJECT_ID
, PA_PROJECTS_MAINT_UTILS.GET_CLASS_CODES(PC.OBJECT_ID
, PC.OBJECT_TYPE
, PC.CLASS_CATEGORY)
, SUM(PC.CODE_PERCENTAGE)
, PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(PC.OBJECT_ID
, PC.OBJECT_TYPE
, PC.CLASS_CATEGORY
, 'N')
, DECODE(PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(PC.OBJECT_ID
, PC.OBJECT_TYPE
, PC.CLASS_CATEGORY
, 'N')
, NULL
, 'C'
, 'A')
, PC.CODE_DESCRIPTION
FROM PA_PROJECT_CLASSES_V PC
WHERE PC.OBJECT_TYPE = 'PA_PROJECTS' GROUP BY PC.CLASS_CATEGORY
, PC.OBJECT_ID
, PA_PROJECTS_MAINT_UTILS.GET_CLASS_CODES(PC.OBJECT_ID
, PC.OBJECT_TYPE
, PC.CLASS_CATEGORY)
, PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(PC.OBJECT_ID
, PC.OBJECT_TYPE
, PC.CLASS_CATEGORY
, 'N')
, DECODE(PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(PC.OBJECT_ID
, PC.OBJECT_TYPE
, PC.CLASS_CATEGORY
, 'N')
, NULL
, 'C'
, 'A')
, PC.CODE_DESCRIPTION UNION SELECT VC.CLASS_CATEGORY
, PPA.PROJECT_ID
, NULL
, TO_NUMBER(NULL)
, PA_PROJECTS_MAINT_UTILS.GET_CLASS_EXCEPTIONS(NULL
, NULL
, NULL
, 'Y')
, 'B'
, NULL
FROM PA_PROJECTS_ALL PPA
, PA_VALID_CATEGORIES_V VC
, PA_PROJECT_TYPES_ALL PPTA
WHERE VC.MANDATORY_FLAG = 'Y'
AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
AND PPA.ORG_ID = PPTA.ORG_ID
AND VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
AND NOT EXISTS (SELECT NULL
FROM PA_PROJECT_CLASSES PC
WHERE PC.PROJECT_ID = PPA.PROJECT_ID
AND PC.CLASS_CATEGORY = VC.CLASS_CATEGORY)