0 Replies Latest reply: Aug 26, 2013 5:02 PM by KP RSS

    Cell Comment and Supporting Detail with User ID

    KP

      Hi,

       

      I have created couple of SQL to generate the report on Cell Comments and Supporting detail from Planning repository tables 11.1.2.1.

      This is working as expected Now I have additional requirement to provide the User information who added the cell comments or Supporting detail

       

      Please let me know if anyone has this before - I wanted to know where the user information stored in these tables when someone entered cell comments

      --Cell Text

      SELECT

      HSP_UNIQUE_NAMES.OBJECT_NAME AS Scenario,

      HSP_UNIQUE_NAMES4.OBJECT_NAME AS Version,

      HSP_UNIQUE_NAMES2.OBJECT_NAME AS Department,

      HSP_UNIQUE_NAMES5.OBJECT_NAME AS YEAR,

      HSP_UNIQUE_NAMES3.OBJECT_NAME AS Period,

      HSP_UNIQUE_NAMES1.OBJECT_NAME AS Account,

      HSP_CELL_NOTE.NOTE_ID,

      HSP_CELL_NOTE_ITEM.CONTENTS

       

      FROM HSP_CELL_NOTE

      INNER JOIN HSP_CELL_NOTE_ITEM

      ON HSP_CELL_NOTE.NOTE_ID = HSP_CELL_NOTE_ITEM.NOTE_ID

      LEFT JOIN HSP_UNIQUE_NAMES

      ON HSP_CELL_NOTE.DIM1 = HSP_UNIQUE_NAMES.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES1

      ON HSP_CELL_NOTE.DIM2 = HSP_UNIQUE_NAMES1.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES2

      ON HSP_CELL_NOTE.DIM3 = HSP_UNIQUE_NAMES2.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES3

      ON HSP_CELL_NOTE.DIM4 = HSP_UNIQUE_NAMES3.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES4

      ON HSP_CELL_NOTE.DIM5 = HSP_UNIQUE_NAMES4.OBJECT_ID

      INNER JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES5

      ON HSP_CELL_NOTE.DIM6 = HSP_UNIQUE_NAMES5.OBJECT_ID

       

       

      --Supporting Detail

      SELECT

      HSP_UNIQUE_NAMES.OBJECT_NAME AS Scenario,

      HSP_UNIQUE_NAMES4.OBJECT_NAME AS Version,

      HSP_UNIQUE_NAMES2.OBJECT_NAME AS Department,

      HSP_UNIQUE_NAMES5.OBJECT_NAME AS YEAR,

      HSP_UNIQUE_NAMES3.OBJECT_NAME AS Period,

      HSP_UNIQUE_NAMES1.OBJECT_NAME AS Account,

      HSP_COLUMN_DETAIL_ITEM.LABEL,

      HSP_COLUMN_DETAIL_ITEM.POSITION,

      HSP_COLUMN_DETAIL_ITEM.OPERATOR,

      HSP_COLUMN_DETAIL_ITEM.VALUE,

      HSP_COLUMN_DETAIL.DETAIL_ID,

      HSP_COLUMN_DETAIL_ITEM.GENERATION

       

      FROM HSP_COLUMN_DETAIL

      INNER JOIN HSP_COLUMN_DETAIL_ITEM

      ON HSP_COLUMN_DETAIL.DETAIL_ID = HSP_COLUMN_DETAIL_ITEM.DETAIL_ID

      LEFT JOIN HSP_UNIQUE_NAMES

      ON HSP_COLUMN_DETAIL.DIM1 = HSP_UNIQUE_NAMES.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES1

      ON HSP_COLUMN_DETAIL.DIM2 = HSP_UNIQUE_NAMES1.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES2

      ON HSP_COLUMN_DETAIL.DIM3 = HSP_UNIQUE_NAMES2.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES3

      ON HSP_COLUMN_DETAIL.DIM4 = HSP_UNIQUE_NAMES3.OBJECT_ID

      LEFT JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES4

      ON HSP_COLUMN_DETAIL.DIM5 = HSP_UNIQUE_NAMES4.OBJECT_ID

      INNER JOIN HSP_UNIQUE_NAMES HSP_UNIQUE_NAMES5

      ON HSP_COLUMN_DETAIL.DIM6 = HSP_UNIQUE_NAMES5.OBJECT_ID

       

       

      Regards,

      KP