SELECT PARENT_FIRST_PTY_ORG_ID,
TAX_REGIME_CODE,
VIEW_OPTIONS_CODE,
UPDATE_ACCESS,
DUPLICATE_ACCESS,
PFPO_NUM
FROM
(SELECT PARENT_FIRST_PTY_ORG_ID,
TAX_REGIME_CODE,
VIEW_OPTIONS_CODE,
DECODE(TO_CHAR(PARENT_FIRST_PTY_ORG_ID),'-99',
DECODE(FND_PROFILE.VALUE('ZX_GCO_WRITE_ACCESS'),'Y','Y','N'),
DECODE(VIEW_OPTIONS_CODE,'VFC','Y','N')) AS UPDATE_ACCESS,
DECODE(TO_CHAR(PARENT_FIRST_PTY_ORG_ID),'-99',
DECODE(VIEW_OPTIONS_CODE,'VFD','Y','VFR','Y','N'), 'N') AS DUPLICATE_ACCESS,
ROW_NUMBER()
OVER (PARTITION BY TAX_REGIME_CODE,PARENT_FIRST_PTY_ORG_ID
ORDER BY VIEW_OPTIONS_CODE DESC) PFPO_NUM
FROM ZX_SUBSCRIPTION_DETAILS ZSD
WHERE ((FIRST_PTY_ORG_ID = -99 AND FND_PROFILE.VALUE('ZX_GCO_WRITE_ACCESS') = 'Y')
OR (FIRST_PTY_ORG_ID <> -99))
)
WHERE PFPO_NUM = 1
SELECT PARENT_FIRST_PTY_ORG_ID
,
TAX_REGIME_CODE
,
VIEW_OPTIONS_CODE
,
UPDATE_ACCESS
,
DUPLICATE_ACCESS
,
PFPO_NUM
FROM
(SELECT PARENT_FIRST_PTY_ORG_ID
,
TAX_REGIME_CODE
,
VIEW_OPTIONS_CODE
,
DECODE(TO_CHAR(PARENT_FIRST_PTY_ORG_ID)
, '-99'
,
DECODE(FND_PROFILE.VALUE('ZX_GCO_WRITE_ACCESS')
, 'Y'
, 'Y'
, 'N')
,
DECODE(VIEW_OPTIONS_CODE
, 'VFC'
, 'Y'
, 'N')) AS UPDATE_ACCESS
,
DECODE(TO_CHAR(PARENT_FIRST_PTY_ORG_ID)
, '-99'
,
DECODE(VIEW_OPTIONS_CODE
, 'VFD'
, 'Y'
, 'VFR'
, 'Y'
, 'N')
, 'N') AS DUPLICATE_ACCESS
,
ROW_NUMBER()
OVER (PARTITION BY TAX_REGIME_CODE
, PARENT_FIRST_PTY_ORG_ID
ORDER BY VIEW_OPTIONS_CODE DESC) PFPO_NUM
FROM ZX_SUBSCRIPTION_DETAILS ZSD
WHERE ((FIRST_PTY_ORG_ID = -99
AND FND_PROFILE.VALUE('ZX_GCO_WRITE_ACCESS') = 'Y')
OR (FIRST_PTY_ORG_ID <> -99))
)
WHERE PFPO_NUM = 1
|
|
|