SELECT
'DATA ACCESS SET',
GL.NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.GL_ACCESS_SETS GL,
FUSION.PER_USERS PU
WHERE
GL.ACCESS_SET_ID = ROLE.ACCESS_SET_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'BUSINESS UNIT',
BU.BU_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_ALL_BUSINESS_UNITS_V BU,
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.PER_USERS PU
WHERE
ROLE.ORG_ID = BU.BU_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'LEDGERS',
LED.NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.GL_LEDGERS LED,
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.PER_USERS PU
WHERE
ROLE.LEDGER_ID = LED.LEDGER_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'ASSET BOOK',
BOOK.BOOK_TYPE_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.FA_BOOK_CONTROLS BOOK,
FUSION.PER_USERS PU
WHERE
BOOK.BOOK_CONTROL_ID = ROLE.BOOK_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'INTERCOMPANY ORGANIZATION',
INTERCO.INTERCO_ORG_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.FUN_INTERCO_ORGANIZATIONS INTERCO,
FUSION.PER_USERS PU
WHERE
INTERCO.INTERCO_ORG_ID = ROLE.INTERCO_ORG_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'COST ORGANIZATION',
COST.COST_ORG_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.CST_COST_ORGS_V COST,
FUSION.PER_USERS PU
WHERE
COST.COST_ORG_ID = ROLE.CST_ORGANIZATION_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'MANUFACTURING PLANT',
MFG.DEF_SUPPLY_SUBINV,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.RCS_MFG_PARAMETERS MFG,
FUSION.PER_USERS PU
WHERE
MFG.ORGANIZATION_ID = ROLE.MFG_ORGANIZATION_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'CONTROL BUDGET',
BUDGET.NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.XCC_CONTROL_BUDGETS BUDGET,
FUSION.PER_USERS PU
WHERE
BUDGET.CONTROL_BUDGET_ID = ROLE.CONTROL_BUDGET_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'REFERENCE DATA SET',
ST.SET_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.FND_SETID_SETS_VL ST,
FUSION.PER_USERS PU
WHERE
ST.SET_ID = ROLE.SET_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'INVENTORY ORGANIZATION',
INV.ORGANIZATION_CODE,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.INV_ORG_PARAMETERS INV,
FUSION.PER_USERS PU
WHERE
INV.ORGANIZATION_ID = ROLE.INV_ORGANIZATION_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'PROJECT ORGANIZATION CLASSIFICATION',
HR.CLASSIFICATION_CODE,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.HR_ORG_UNIT_CLASSIFICATIONS_F HR,
FUSION.PER_USERS PU
WHERE
HR.ORG_UNIT_CLASSIFICATION_ID = ROLE.ORG_ID
AND PU.USER_GUID = ROLE.USER_GUID
'DATA ACCESS SET',
GL.NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.GL_ACCESS_SETS GL,
FUSION.PER_USERS PU
WHERE
GL.ACCESS_SET_ID = ROLE.ACCESS_SET_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'BUSINESS UNIT',
BU.BU_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_ALL_BUSINESS_UNITS_V BU,
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.PER_USERS PU
WHERE
ROLE.ORG_ID = BU.BU_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'LEDGERS',
LED.NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.GL_LEDGERS LED,
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.PER_USERS PU
WHERE
ROLE.LEDGER_ID = LED.LEDGER_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'ASSET BOOK',
BOOK.BOOK_TYPE_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.FA_BOOK_CONTROLS BOOK,
FUSION.PER_USERS PU
WHERE
BOOK.BOOK_CONTROL_ID = ROLE.BOOK_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'INTERCOMPANY ORGANIZATION',
INTERCO.INTERCO_ORG_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.FUN_INTERCO_ORGANIZATIONS INTERCO,
FUSION.PER_USERS PU
WHERE
INTERCO.INTERCO_ORG_ID = ROLE.INTERCO_ORG_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'COST ORGANIZATION',
COST.COST_ORG_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.CST_COST_ORGS_V COST,
FUSION.PER_USERS PU
WHERE
COST.COST_ORG_ID = ROLE.CST_ORGANIZATION_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'MANUFACTURING PLANT',
MFG.DEF_SUPPLY_SUBINV,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.RCS_MFG_PARAMETERS MFG,
FUSION.PER_USERS PU
WHERE
MFG.ORGANIZATION_ID = ROLE.MFG_ORGANIZATION_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'CONTROL BUDGET',
BUDGET.NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.XCC_CONTROL_BUDGETS BUDGET,
FUSION.PER_USERS PU
WHERE
BUDGET.CONTROL_BUDGET_ID = ROLE.CONTROL_BUDGET_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'REFERENCE DATA SET',
ST.SET_NAME,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.FND_SETID_SETS_VL ST,
FUSION.PER_USERS PU
WHERE
ST.SET_ID = ROLE.SET_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'INVENTORY ORGANIZATION',
INV.ORGANIZATION_CODE,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.INV_ORG_PARAMETERS INV,
FUSION.PER_USERS PU
WHERE
INV.ORGANIZATION_ID = ROLE.INV_ORGANIZATION_ID
AND PU.USER_GUID = ROLE.USER_GUID
UNION
SELECT
'PROJECT ORGANIZATION CLASSIFICATION',
HR.CLASSIFICATION_CODE,
ROLE.ROLE_NAME,
PU.USERNAME,
ROLE.CREATION_DATE,
ROLE.LAST_UPDATE_DATE,
ROLE.LAST_UPDATED_BY
FROM
FUSION.FUN_USER_ROLE_DATA_ASGNMNTS ROLE,
FUSION.HR_ORG_UNIT_CLASSIFICATIONS_F HR,
FUSION.PER_USERS PU
WHERE
HR.ORG_UNIT_CLASSIFICATION_ID = ROLE.ORG_ID
AND PU.USER_GUID = ROLE.USER_GUID
No comments:
Post a Comment