Oracle Apps : Accounting Flex Fields

Script to find Accounting Flex Fields and Number of Segments Used :-

REM $Header: Accounting Segments.sql 1.7 08/15/02 Jaros Analytics $
REM PURPOSE: Diagnose common Accounting Segments
REM FILE NAME: RPT_APPS_ACCTSEG.SQL
REM PRODUCT VERSIONS: 10.7.X 11.0.X 11.5.X
REM PLATFORM: GENERIC
REM ============================================================================

REM ============================================================================
REM USAGE: sqlplus apps/apps @RPT_APPS_ACCTSEG.SQL
REM OUTPUT RPT_APPS_ACCTSEG.LST
REM ============================================================================

set serveroutput on size 1000000
set verify off
set feedback off
set autoprint off
set linesize 1000
set wrap off
spool RPT_APPS_ACCTSEG.LST

Declare
x number;

Cursor C_get_struc Is
Select I.APPLICATION_ID,
I.ID_FLEX_CODE,
I.ID_FLEX_NAME,
I.LAST_UPDATE_DATE LAST_UPDATE_DAET_FLEX ,
I.TABLE_APPLICATION_ID,
I.UNIQUE_ID_COLUMN_NAME,
J.ID_FLEX_NUM,
J.ID_FLEX_STRUCTURE_NAME,
J.LAST_UPDATE_DATE LAST_UPDATE_DATE_STRUC,
J.CONCATENATED_SEGMENT_DELIMITER,
J.DYNAMIC_INSERTS_ALLOWED_FLAG,
J.ENABLED_FLAG STRUC_ENABLED_FLAG
From
FND_ID_FLEXS I,
FND_ID_FLEX_STRUCTURES_VL J
Where
I.Application_Table_Name='GL_CODE_COMBINATIONS'
And I.Application_Id = J.Application_Id
And I.Id_Flex_Code = J.Id_Flex_Code;

Cursor C_get_segs(P_Application_Id Number,P_Id_Flex_Code Varchar2,P_Id_Flex_Num Number) Is
Select APPLICATION_COLUMN_NAME,
SEGMENT_NAME,
LAST_UPDATE_DATE,
SEGMENT_NUM,
ENABLED_FLAG,
REQUIRED_FLAG,
DEFAULT_TYPE
From
FND_ID_FLEX_SEGMENTS K
Where
Application_Id = P_Application_Id
And Id_Flex_Code = P_Id_Flex_Code
And Id_Flex_Num = P_Id_Flex_Num
Order By Id_Flex_Num,Application_Column_Name;
Begin

For seg1 in C_get_struc
Loop
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Accounting Structure Name - '|| seg1.id_flex_structure_name||' , '||'Enabled Flag - '||seg1.Struc_Enabled_Flag);
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------');
For seg2 in C_get_segs(seg1.application_id,seg1.id_flex_code,seg1.id_flex_num)
Loop
DBMS_OUTPUT.PUT_LINE('Segment Name - '||seg2.Segment_Name||' , '||'Application Column Name - '||seg2.Application_Column_Name||' , '||'Enabled Flag - '||seg2.Enabled_Flag);
End Loop;
End Loop;
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('Spooled the file in the local machine and the file name is RPT_APPS_ACCTSEG.LST');
End;
/
spool off
set wrap on