This discussion is archived
0 Replies Latest reply: Aug 26, 2013 3:02 PM by KP RSS

Cell Comment and Supporting Detail with User ID

KP Explorer
Currently Being Moderated

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

 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points