/* FSG REPORTS AND COMPONENTS OVERVIEW 
Written by Daniel North,  ORAFINAPPS Limited 2007
Details definitions of FSG reports by component, and includes several small scripts for listing all components across different charts of accounts.
Can be used for SOX and system audits. 
(Tested on Vision 11.5.10.2  June 2007 ) */

-- FSG REPORTS  --------------
SELECT FST.ID_FLEX_STRUCTURE_NAME
,	   R.NAME
,	   R.REPORT_TITLE
,	   R.DESCRIPTION
,	   R.COLUMN_SET"COLUMN SET"
,	   RW2.STRUCTURE_ID
,	   RW2.DESCRIPTION"COL DESC"
,	   R.ROW_SET"ROW SET"
,	   RW.DESCRIPTION"ROW DESC"
,	   R.REPORT_DISPLAY_SET"DISPLAY SET" 
,	   R.CONTENT_SET"CONTENT SET"
,	   R.ROW_ORDER"ROW ORDER"
,	   R.ROUNDING_OPTION "RND"
,	   U.USER_NAME
,	   U.DESCRIPTION
,	   R.CREATION_DATE
FROM RG_REPORTS_V R, FND_ID_FLEX_STRUCTURES_V FST, FND_USER U, RG_REPORT_AXIS_SETS_V RW, RG_REPORT_AXIS_SETS_V RW2
WHERE R.STRUCTURE_ID = FST.ID_FLEX_NUM
AND R.ROW_SET_ID = RW.AXIS_SET_ID
AND R.COLUMN_SET_ID = RW2.AXIS_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
AND R.CREATED_BY = U.USER_ID
ORDER BY 1, 2

-- FSG ROW SETS AND COLUMN SETS ----------------
SELECT  FST.ID_FLEX_STRUCTURE_NAME "COA"
,		DECODE(RW.AXIS_SET_TYPE,'R','ROW SET','C','COLUMN SET','##') "ROW/COLUMN"
,		RW.NAME"SET NAME"
,		RW.AXIS_SET_ID
FROM RG_REPORT_AXIS_SETS_V RW, FND_ID_FLEX_STRUCTURES_V FST
WHERE RW.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
ORDER BY 1,2,3

---- CONTENT SETS ------------------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
,	   CS.NAME
,	   CS.CONTENT_SET_ID
FROM RG_REPORT_CONTENT_SETS CS, FND_ID_FLEX_STRUCTURES_V FST
WHERE CS.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')

----- ROW ORDERS ---------------------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
,	   RO.NAME "ROW ORDER"
,	   RO.DESCRIPTION "DESCRIPTION"
,	   RO.STRUCTURE_ID
,	   RO.ROW_ORDER_ID
FROM RG_ROW_ORDERS RO, FND_ID_FLEX_STRUCTURES_V FST
WHERE RO.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
ORDER BY RO.NAME