This PA Class Category Codes Report query retrieves active project classification category and code configurations, including auto accounting, project intelligence, and allocation rule flags, used in project setup and reporting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This Query Displays project class category and code setup details including validity dates, flags, and descriptions.
CAT.CLASS_CATEGORY_ID AS CLASS_CATEGORY,
CAT1.DESCRIPTION AS CLASS_CATEGORY_DESCRIPTION,
COD.CLASS_CODE_ID AS CLASS_CODE,
COD1.DESCRIPTION AS CLASS_CODE_DESCRIPTION,
GREATEST (CAT.START_DATE_ACTIVE, COD.START_DATE_ACTIVE) AS ACTIVE_START_DATE,
LEAST (NVL (CAT.END_DATE_ACTIVE, COD.END_DATE_ACTIVE),NVL (COD.END_DATE_ACTIVE, CAT.END_DATE_ACTIVE)) AS ACTIVE_END_DATE,
DECODE (
SIGN (
SYSDATE
- GREATEST (CAT.START_DATE_ACTIVE, COD.START_DATE_ACTIVE)),
1, DECODE (
SIGN (
NVL (
LEAST (
NVL (CAT.END_DATE_ACTIVE, COD.END_DATE_ACTIVE),
NVL (COD.END_DATE_ACTIVE, CAT.END_DATE_ACTIVE)),
SYSDATE + 1)
- SYSDATE),
1, 'Y',
'N'),
'N') AS ACTIVE_FLAG,
NVL (CAT.ALL_TYPES_VALID_FLAG, 'N') AS ALL_PROJECT_TYPES_VALID_FLAG,
NVL (CAT.ALLOW_PERCENT_FLAG, 'N') AS ALLOW_PERCENT_ENTRY_FLAG,
CAT.AUTOACCOUNTING_FLAG,
NVL (CAT.INCLUDE_IN_PJI_FLAG, 'N') AS INCL_IN_PROJ_INTELLIGENCE_FLAG,
CAT.MANDATORY_FLAG,
CAT.PICK_ONE_CODE_ONLY_FLAG AS ONE_CODE_ONLY_FLAG,
NVL (CAT.TOTAL_100_PERCENT_FLAG, 'N') AS TOTAL_PERCENT_EQUAL_100_FLAG
FROM PJF_CLASS_CODES_B COD,
PJF_CLASS_CATEGORIES_B CAT ,
PJF_CLASS_CODES_TL COD1 ,
PJF_CLASS_CATEGORIES_TL CAT1
WHERE 1 = 1
AND CAT.CLASS_CATEGORY_ID = COD.CLASS_CATEGORY_ID
AND COD.CLASS_CODE_ID = COD1.CLASS_CODE_ID AND COD1.LANGUAGE(+) = USERENV('LANG')
AND CAT.CLASS_CATEGORY_ID = CAT1.CLASS_CATEGORY_ID AND CAT1.LANGUAGE(+) = USERENV('LANG');