[Home] [Help]
View: JA_CN_PS_DFF_ASSIGNMENTS_V
View Text
SELECT NULL ROW_ID
,
COA."CHART_OF_ACCOUNTS_ID" CHART_OF_ACCOUNTS_ID
,
NULL APPLICATION_ID
,
NULL DESCRIPTIVE_FLEXFIELD_NAME
,
NULL DFF_TITLE_CODE
,
NULL CONTEXT_CODE
,
NULL ATTRIBUTE_COLUMN
,
NULL CREATED_BY
,
NULL CREATION_DATE
,
NULL LAST_UPDATED_BY
,
NULL LAST_UPDATE_DATE
,
NULL LAST_UPDATE_LOGIN
,
FLV.LOOKUP_CODE
,
FLV.MEANING
,
FLV.DESCRIPTION
FROM FND_LOOKUP_VALUES FLV
,
(SELECT DISTINCT ID_FLEX_NUM CHART_OF_ACCOUNTS_ID
FROM FND_ID_FLEX_STRUCTURES_TL
WHERE ID_FLEX_CODE = 'GL#') COA
WHERE FLV.LOOKUP_TYPE = 'JA_CN_PS_DFF_TITLE_TYPE'
AND FLV.ENABLED_FLAG = 'Y'
AND FLV.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE
, SYSDATE) AND
NVL(FLV.END_DATE_ACTIVE
, SYSDATE)
AND NOT EXISTS
(SELECT 1
FROM JA_CN_DFF_ASSIGNMENTS
WHERE CHART_OF_ACCOUNTS_ID = COA.CHART_OF_ACCOUNTS_ID
AND DFF_TITLE_CODE = FLV.LOOKUP_CODE)
UNION ALL
SELECT JCDA.ROWID ROW_ID
,
JCDA."CHART_OF_ACCOUNTS_ID"
,
JCDA."APPLICATION_ID"
,
JCDA."DESCRIPTIVE_FLEXFIELD_NAME"
,
JCDA."DFF_TITLE_CODE"
,
JCDA."CONTEXT_CODE"
,
JCDA."ATTRIBUTE_COLUMN"
,
JCDA."CREATED_BY"
,
JCDA."CREATION_DATE"
,
JCDA."LAST_UPDATED_BY"
,
JCDA."LAST_UPDATE_DATE"
,
JCDA."LAST_UPDATE_LOGIN"
,
FLV.LOOKUP_CODE
,
FLV.MEANING
,
FLV.DESCRIPTION
FROM FND_LOOKUP_VALUES FLV
,
JA_CN_DFF_ASSIGNMENTS JCDA
WHERE FLV.LOOKUP_TYPE = 'JA_CN_PS_DFF_TITLE_TYPE'
AND FLV.ENABLED_FLAG = 'Y'
AND FLV.LANGUAGE = USERENV('LANG')
AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE
, SYSDATE) AND
NVL(FLV.END_DATE_ACTIVE
, SYSDATE)
AND FLV.LOOKUP_CODE = JCDA.DFF_TITLE_CODE
ORDER BY CHART_OF_ACCOUNTS_ID
, LOOKUP_CODE
, MEANING