Thursday, 9 July 2015

How to find Table Information and Application Name

SELECT   FT.TABLE_NAME,FT.USER_TABLE_
NAME,
           FT.DESCRIPTION TABLE_DESC,
           FAL.APPLICATION_NAME,
           FA.APPLICATION_SHORT_NAME,
           FC.COLUMN_SEQUENCE,
           FC.COLUMN_NAME,
           FC.USER_COLUMN_NAME,
           (SELECT FC.COLUMN_NAME FROM FND_PRIMARY_KEY_COLUMNS FPK
           WHERE FPK.APPLICATION_ID= FC.APPLICATION_ID
           AND   FC.TABLE_ID = FPK.TABLE_ID
           AND   FC.COLUMN_ID  = FPK.COLUMN_ID) PRIMARY_KEY_COLUMNS,
           FC.COLUMN_TYPE,
           FC.WIDTH,
           FC.PRECISION,
           FC.SCALE,
           FC.DEFAULT_VALUE,
           FC.NULL_ALLOWED_FLAG,
           FC.TRANSLATE_FLAG,
           FC.DESCRIPTION COL_DESCRIPTION,
           FC.FLEXFIELD_APPLICATION_ID,
           FC.FLEXFIELD_NAME,
           FLEXFIELD_USAGE_CODE
    FROM   FND_TABLES FT
         , FND_COLUMNS FC
         , FND_APPLICATION FA
         , FND_APPLICATION_TL FAL
   WHERE   FAL.APPLICATION_NAME = NVL(:P_APPLICATION_NAME,0) --Receivables --FC.APPLICATION_ID = 222)
   AND     FT.TABLE_NAME       = NVL(:P_TABLE_NAME,0)       --RA_CUSTOMERS --(FC.TABLE_ID = 52559)
   AND     FT.APPLICATION_ID = FC.APPLICATION_ID
   AND     FT.TABLE_ID       = FC.TABLE_ID
   AND     FT.APPLICATION_ID = FAL.APPLICATION_ID
   AND     FA.APPLICATION_ID = FAL.APPLICATION_ID
   AND     FT.TABLE_TYPE= 'T' --Transaction Data
ORDER BY   COLUMN_SEQUENCE

No comments:

Post a Comment