GL_KTFLT_DEP_VW

(SQL View)
 Index Back

INSGT: Flatten Tree for Dept

Flatten Tree for Department ChartField in Insights

SELECT B.SETCNTRLVALUE , A.DEPTID , C.TREE_NAME_OTBI , C.TREE_NAME , C.TREE_EFFDT , C.SETID , A.DESCR , (CASE WHEN C.TLVL1_DESC IS NULL OR C.TLVL1_DESC = ' ' THEN A.DESCR ELSE C.TLVL1_DESC END) , (CASE WHEN C.TNODE1_DESC IS NULL OR C.TNODE1_DESC = ' ' THEN A.DESCR ELSE C.TNODE1_DESC END) , (CASE WHEN C.TLVL2_DESC IS NULL OR C.TLVL2_DESC = ' ' THEN A.DESCR ELSE C.TLVL2_DESC END) , (CASE WHEN C.TNODE2_DESC IS NULL OR C.TNODE2_DESC = ' ' THEN A.DESCR ELSE C.TNODE2_DESC END) , (CASE WHEN C.TLVL3_DESC IS NULL OR C.TLVL3_DESC = ' ' THEN A.DESCR ELSE C.TLVL3_DESC END) , (CASE WHEN C.TNODE3_DESC IS NULL OR C.TNODE3_DESC = ' ' THEN A.DESCR ELSE C.TNODE3_DESC END) , (CASE WHEN C.TLVL4_DESC IS NULL OR C.TLVL4_DESC = ' ' THEN A.DESCR ELSE C.TLVL4_DESC END) , (CASE WHEN C.TNODE4_DESC IS NULL OR C.TNODE4_DESC = ' ' THEN A.DESCR ELSE C.TNODE4_DESC END) , (CASE WHEN C.TLVL5_DESC IS NULL OR C.TLVL5_DESC = ' ' THEN A.DESCR ELSE C.TLVL5_DESC END) , (CASE WHEN C.TNODE5_DESC IS NULL OR C.TNODE5_DESC = ' ' THEN A.DESCR ELSE C.TNODE5_DESC END) , (CASE WHEN C.TLVL6_DESC IS NULL OR C.TLVL6_DESC = ' ' THEN A.DESCR ELSE C.TLVL6_DESC END) , (CASE WHEN C.TNODE6_DESC IS NULL OR TNODE6_DESC = ' ' THEN A.DESCR ELSE C.TNODE6_DESC END) , (CASE WHEN C.TLVL7_DESC IS NULL OR C.TLVL7_DESC = ' ' THEN A.DESCR ELSE C.TLVL7_DESC END) , (CASE WHEN C.TNODE7_DESC IS NULL OR C.TNODE7_DESC = ' ' THEN A.DESCR ELSE C.TNODE7_DESC END) , (CASE WHEN C.TLVL8_DESC IS NULL OR C.TLVL8_DESC = ' ' THEN A.DESCR ELSE C.TLVL8_DESC END) , (CASE WHEN C.TNODE8_DESC IS NULL OR C.TNODE8_DESC = ' ' THEN A.DESCR ELSE C.TNODE8_DESC END) , (CASE WHEN C.TLVL9_DESC IS NULL OR C.TLVL9_DESC = ' ' THEN A.DESCR ELSE C.TLVL9_DESC END) , (CASE WHEN C.TNODE9_DESC IS NULL OR C.TNODE9_DESC = ' ' THEN A.DESCR ELSE C.TNODE9_DESC END) , (CASE WHEN C.TLVL10_DESC IS NULL OR C.TLVL10_DESC = ' ' THEN A.DESCR ELSE C.TLVL10_DESC END) , (CASE WHEN C.TNODE10_DESC IS NULL OR C.TNODE10_DESC = ' ' THEN A.DESCR ELSE C.TNODE10_DESC END) , (CASE WHEN C.TLVL11_DESC IS NULL OR C.TLVL11_DESC = ' ' THEN A.DESCR ELSE C.TLVL11_DESC END) , (CASE WHEN C.TNODE11_DESC IS NULL OR C.TNODE11_DESC = ' ' THEN A.DESCR ELSE C.TNODE11_DESC END) , (CASE WHEN C.TLVL12_DESC IS NULL OR C.TLVL12_DESC = ' ' THEN A.DESCR ELSE C.TLVL12_DESC END) , (CASE WHEN C.TNODE12_DESC IS NULL OR C.TNODE12_DESC = ' ' THEN A.DESCR ELSE C.TNODE12_DESC END) , (CASE WHEN C.TLVL13_DESC IS NULL OR C.TLVL13_DESC = ' ' THEN A.DESCR ELSE C.TLVL13_DESC END) , (CASE WHEN C.TNODE13_DESC IS NULL OR C.TNODE13_DESC = ' ' THEN A.DESCR ELSE C.TNODE13_DESC END) , (CASE WHEN C.TLVL14_DESC IS NULL OR C.TLVL14_DESC = ' ' THEN A.DESCR ELSE C.TLVL14_DESC END) , (CASE WHEN C.TNODE14_DESC IS NULL OR C.TNODE14_DESC = ' ' THEN A.DESCR ELSE C.TNODE14_DESC END) , (CASE WHEN C.TLVL15_DESC IS NULL OR C.TLVL15_DESC = ' ' THEN A.DESCR ELSE C.TLVL15_DESC END) , (CASE WHEN C.TNODE15_DESC IS NULL OR C.TNODE15_DESC = ' ' THEN A.DESCR ELSE C.TNODE15_DESC END) FROM (PS_DEPT_TBL A LEFT OUTER JOIN PS_GL_KTDEF_DEP_VW C ON A.DEPTID = C.DEPTID AND (A.SETID = C.SETID OR C.SETID = ' ')) , PS_SET_CNTRL_GROUP B WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL A_ED WHERE A.SETID = A_ED.SETID AND A.DEPTID = A_ED.DEPTID AND A_ED.EFFDT <= %CurrentDateIn) AND A.SETID = B.SETID AND B.REC_GROUP_ID = 'FS_06' UNION SELECT B.SETCNTRLVALUE , (CASE WHEN C.DEPTID IS NULL THEN ' ' ELSE C.DEPTID END) , C.TREE_NAME_OTBI , C.TREE_NAME , C.TREE_EFFDT , C.SETID , (CASE WHEN (C.FIELD_DESC IS NULL OR C.FIELD_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.FIELD_DESC END) , (CASE WHEN (C.TLVL1_DESC IS NULL OR C.TLVL1_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL1_DESC END) , (CASE WHEN (C.TNODE1_DESC IS NULL OR C.TNODE1_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE1_DESC END) , (CASE WHEN (C.TLVL2_DESC IS NULL OR C.TLVL2_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL2_DESC END) , (CASE WHEN (C.TNODE2_DESC IS NULL OR C.TNODE2_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE2_DESC END) , (CASE WHEN (C.TLVL3_DESC IS NULL OR C.TLVL3_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL3_DESC END) , (CASE WHEN (C.TNODE3_DESC IS NULL OR C.TNODE3_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE3_DESC END) , (CASE WHEN (C.TLVL4_DESC IS NULL OR C.TLVL4_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL4_DESC END) , (CASE WHEN (C.TNODE4_DESC IS NULL OR C.TNODE4_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE4_DESC END) , (CASE WHEN (C.TLVL5_DESC IS NULL OR C.TLVL5_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL5_DESC END) , (CASE WHEN (C.TNODE5_DESC IS NULL OR C.TNODE5_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE5_DESC END) , (CASE WHEN (C.TLVL6_DESC IS NULL OR C.TLVL6_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL6_DESC END) , (CASE WHEN (C.TNODE6_DESC IS NULL OR C.TNODE6_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE6_DESC END) , (CASE WHEN (C.TLVL7_DESC IS NULL OR C.TLVL7_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL7_DESC END) , (CASE WHEN (C.TNODE7_DESC IS NULL OR C.TNODE7_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE7_DESC END) , (CASE WHEN (C.TLVL8_DESC IS NULL OR C.TLVL8_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL8_DESC END) , (CASE WHEN (C.TNODE8_DESC IS NULL OR C.TNODE8_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE8_DESC END) , (CASE WHEN (C.TLVL9_DESC IS NULL OR C.TLVL9_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL9_DESC END) , (CASE WHEN (C.TNODE9_DESC IS NULL OR C.TNODE9_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE9_DESC END) , (CASE WHEN (C.TLVL10_DESC IS NULL OR C.TLVL10_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL10_DESC END) , (CASE WHEN (C.TNODE10_DESC IS NULL OR C.TNODE10_DESC = ' ') THEN D.MESSAGE_TEXT ELSE C.TNODE10_DESC END) , (CASE WHEN (C.TLVL11_DESC IS NULL OR C.TLVL11_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL11_DESC END) , (CASE WHEN (C.TNODE11_DESC IS NULL OR C.TNODE11_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE11_DESC END) , (CASE WHEN (C.TLVL12_DESC IS NULL OR C.TLVL12_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL12_DESC END) , (CASE WHEN (C.TNODE12_DESC IS NULL OR C.TNODE12_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE12_DESC END) , (CASE WHEN (C.TLVL13_DESC IS NULL OR C.TLVL13_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL13_DESC END) , (CASE WHEN (C.TNODE13_DESC IS NULL OR C.TNODE13_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE13_DESC END) , (CASE WHEN (C.TLVL14_DESC IS NULL OR C.TLVL14_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL14_DESC END) , (CASE WHEN (C.TNODE14_DESC IS NULL OR C.TNODE14_DESC = ' ') THEN D.MESSAGE_TEXT ELSE C.TNODE14_DESC END) , (CASE WHEN (C.TLVL15_DESC IS NULL OR C.TLVL15_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TLVL15_DESC END) , (CASE WHEN (C.TNODE15_DESC IS NULL OR C.TNODE15_DESC = ' ' ) THEN D.MESSAGE_TEXT ELSE C.TNODE15_DESC END) FROM (PS_SET_CNTRL_GROUP B LEFT OUTER JOIN PS_GL_KTDEF_DEP_VW C ON (B.SETID = C.SETID OR C.SETID = ' ') AND C.DEPTID = ' ' AND B.REC_GROUP_ID = 'FS_06') , PSMSGCATDEFN D WHERE B.REC_GROUP_ID = 'FS_06' AND D.MESSAGE_SET_NBR = 262 AND D.MESSAGE_NBR = 1600

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETCNTRLVALUE Character(20) VARCHAR2(20) NOT NULL Tree Definition User Key Value
2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
7 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department ID Description
8 TLVL1_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
9 TNODE1_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
10 TLVL2_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
11 TNODE2_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
12 TLVL3_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
13 TNODE3_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
14 TLVL4_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
15 TNODE4_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
16 TLVL5_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
17 TNODE5_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
18 TLVL6_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
19 TNODE6_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
20 TLVL7_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
21 TNODE7_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
22 TLVL8_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
23 TNODE8_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
24 TLVL9_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
25 TNODE9_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
26 TLVL10_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
27 TNODE10_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
28 TLVL11_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
29 TNODE11_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
30 TLVL12_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
31 TNODE12_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
32 TLVL13_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
33 TNODE13_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
34 TLVL14_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
35 TNODE14_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description
36 TLVL15_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Level Description
37 TNODE15_DESC Character(30) VARCHAR2(30) NOT NULL OTBI: Tree Node Description