OI_TFLT_ACCT_VW

(SQL View)
 Index Back

INSGT: Flatten Tree for Acct

OTBI: Flatten Tree for Account ChartField in GL Insights Dashboard

SELECT A.SETCNTRLVALUE , B.ACCOUNT , B.TREE_NAME_OTBI , B.TREE_NAME , B.TREE_EFFDT , (CASE WHEN B.FIELD_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.FIELD_DESC END) , (CASE WHEN B.TLVL1_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL1_DESC END) , (CASE WHEN B.TNODE1_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE1_DESC END) , (CASE WHEN B.TLVL2_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL2_DESC END) , (CASE WHEN B.TNODE2_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE2_DESC END) , (CASE WHEN B.TLVL3_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL3_DESC END) , (CASE WHEN B.TNODE3_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE3_DESC END) , (CASE WHEN B.TLVL4_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL4_DESC END) , (CASE WHEN B.TNODE4_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE4_DESC END) , (CASE WHEN B.TLVL5_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL5_DESC END) , (CASE WHEN B.TNODE5_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE5_DESC END) , (CASE WHEN B.TLVL6_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL6_DESC END) , (CASE WHEN B.TNODE6_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE6_DESC END) , (CASE WHEN B.TLVL7_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL7_DESC END) , (CASE WHEN B.TNODE7_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE7_DESC END) , (CASE WHEN B.TLVL8_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL8_DESC END) , (CASE WHEN B.TNODE8_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE8_DESC END) , (CASE WHEN B.TLVL9_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL9_DESC END) , (CASE WHEN B.TNODE9_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE9_DESC END) , (CASE WHEN B.TLVL10_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL10_DESC END) , (CASE WHEN B.TNODE10_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE10_DESC END) , (CASE WHEN B.TLVL11_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL11_DESC END) , (CASE WHEN B.TNODE11_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE11_DESC END) , (CASE WHEN B.TLVL12_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL12_DESC END) , (CASE WHEN B.TNODE12_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE12_DESC END) , (CASE WHEN B.TLVL13_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL13_DESC END) , (CASE WHEN B.TNODE13_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE13_DESC END) , (CASE WHEN B.TLVL14_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL14_DESC END) , (CASE WHEN B.TNODE14_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE14_DESC END) , (CASE WHEN B.TLVL15_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TLVL15_DESC END) , (CASE WHEN B.TNODE15_DESC = ' ' THEN C.MESSAGE_TEXT ELSE B.TNODE15_DESC END) FROM PS_SET_CNTRL_GROUP A , PS_OI_TFLT_ACCOUNT B , PSMSGCATDEFN C WHERE A.REC_GROUP_ID = 'FS_05' AND A.SETID = B.SETID AND A.SETID = B.SETID_TREE AND C.MESSAGE_SET_NBR = 262 AND C.MESSAGE_NBR = 1600 UNION SELECT A.SETCNTRLVALUE , B.ACCOUNT , D.MESSAGE_TEXT , D.MESSAGE_TEXT , B.EFFDT , B.DESCR , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT , C.MESSAGE_TEXT FROM PS_SET_CNTRL_GROUP A , PS_GL_ACCOUNT_TBL B , PSMSGCATDEFN C , PSMSGCATDEFN D WHERE A.REC_GROUP_ID = 'FS_05' AND A.SETID = B.SETID AND C.MESSAGE_SET_NBR = 262 AND C.MESSAGE_NBR = 1600 AND D.MESSAGE_SET_NBR = 5000 AND D.MESSAGE_NBR = 505 AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_GL_ACCOUNT_TBL B1 WHERE B1.SETID = B.SETID AND B1.ACCOUNT = B.ACCOUNT AND B1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETCNTRLVALUE Character(20) VARCHAR2(20) NOT NULL Tree Definition User Key Value
2 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
3 TREE_NAME_KIB Character(30) VARCHAR2(30) NOT NULL Tree Name
4 TREE_NAME Character(18) VARCHAR2(18) NOT NULL Tree Name
5 EFFDT Date(10) DATE Effective Date

Default Value: %date

6 FIELD_DESC Character(30) VARCHAR2(30) NOT NULL Field Description
7 TLVL1_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
8 TNODE1_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
9 TLVL2_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
10 TNODE2_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
11 TLVL3_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
12 TNODE3_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
13 TLVL4_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
14 TNODE4_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
15 TLVL5_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
16 TNODE5_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
17 TLVL6_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
18 TNODE6_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
19 TLVL7_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
20 TNODE7_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
21 TLVL8_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
22 TNODE8_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
23 TLVL9_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
24 TNODE9_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
25 TLVL10_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
26 TNODE10_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
27 TLVL11_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
28 TNODE11_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
29 TLVL12_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
30 TNODE12_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
31 TLVL13_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
32 TNODE13_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
33 TLVL14_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
34 TNODE14_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
35 TLVL15_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
36 TNODE15_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description