SETCNTRLVALUEACCOUNTTREE_NAME_KIBTREE_NAMEEFFDTDefault Value: %date
GL_KTFLT_ACT_VW(SQL View) |
Index Back |
|---|---|
INSGT: Flatten Tree for AcctFlatten Tree for Account ChartField in Insights |
| SELECT B.SETCNTRLVALUE , A.ACCOUNT , C.TREE_NAME_OTBI , C.TREE_NAME , C.TREE_EFFDT , C.SETID , A.ACCOUNT_DESCR , A.ACCOUNT_TYPE , (CASE WHEN A.ACCT_TYPE_DESCR = ' ' THEN A.ACCOUNT_TYPE ELSE A.ACCT_TYPE_DESCR END) , (CASE WHEN (C.TLVL1_DESC IS NULL OR C.TLVL1_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL1_DESC END) , (CASE WHEN (C.TNODE1_DESC IS NULL OR C.TNODE1_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE1_DESC END) , (CASE WHEN (C.TLVL2_DESC IS NULL OR C.TLVL2_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL2_DESC END) , (CASE WHEN (C.TNODE2_DESC IS NULL OR C.TNODE2_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE2_DESC END) , (CASE WHEN (C.TLVL3_DESC IS NULL OR C.TLVL3_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL3_DESC END) , (CASE WHEN (C.TNODE3_DESC IS NULL OR C.TNODE3_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE3_DESC END) , (CASE WHEN (C.TLVL4_DESC IS NULL OR C.TLVL4_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL4_DESC END) , (CASE WHEN (C.TNODE4_DESC IS NULL OR C.TNODE4_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE4_DESC END) , (CASE WHEN (C.TLVL5_DESC IS NULL OR C.TLVL5_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL5_DESC END) , (CASE WHEN (C.TNODE5_DESC IS NULL OR C.TNODE5_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE5_DESC END) , (CASE WHEN (C.TLVL6_DESC IS NULL OR C.TLVL6_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL6_DESC END) , (CASE WHEN (C.TNODE6_DESC IS NULL OR C.TNODE6_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE6_DESC END) , (CASE WHEN (C.TLVL7_DESC IS NULL OR C.TLVL7_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL7_DESC END) , (CASE WHEN (C.TNODE7_DESC IS NULL OR C.TNODE7_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE7_DESC END) , (CASE WHEN (C.TLVL8_DESC IS NULL OR C.TLVL8_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL8_DESC END) , (CASE WHEN (C.TNODE8_DESC IS NULL OR C.TNODE8_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE8_DESC END) , (CASE WHEN (C.TLVL9_DESC IS NULL OR C.TLVL9_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL9_DESC END) , (CASE WHEN (C.TNODE9_DESC IS NULL OR C.TNODE9_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE9_DESC END) , (CASE WHEN (C.TLVL10_DESC IS NULL OR C.TLVL10_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL10_DESC END) , (CASE WHEN (C.TNODE10_DESC IS NULL OR C.TNODE10_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE10_DESC END) , (CASE WHEN (C.TLVL11_DESC IS NULL OR C.TLVL11_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL11_DESC END) , (CASE WHEN (C.TNODE11_DESC IS NULL OR C.TNODE11_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE11_DESC END) , (CASE WHEN (C.TLVL12_DESC IS NULL OR C.TLVL12_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL12_DESC END) , (CASE WHEN (C.TNODE12_DESC IS NULL OR C.TNODE12_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE12_DESC END) , (CASE WHEN (C.TLVL13_DESC IS NULL OR C.TLVL13_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL13_DESC END) , (CASE WHEN (C.TNODE13_DESC IS NULL OR C.TNODE13_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE13_DESC END) , (CASE WHEN (C.TLVL14_DESC IS NULL OR C.TLVL14_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL14_DESC END) , (CASE WHEN (C.TNODE14_DESC IS NULL OR C.TNODE14_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE14_DESC END) , (CASE WHEN (C.TLVL15_DESC IS NULL OR C.TLVL15_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TLVL15_DESC END) , (CASE WHEN (C.TNODE15_DESC IS NULL OR C.TNODE15_DESC = ' ') THEN A.ACCOUNT_DESCR ELSE C.TNODE15_DESC END) FROM (PS_GL_K_ACCT_VW A LEFT OUTER JOIN PS_GL_KTDEF_ACT_VW C ON A.ACCOUNT = C.ACCOUNT AND(A.SETID = C.SETID OR C.SETID = ' ')) , PS_SET_CNTRL_GROUP B WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_GL_K_ACCT_VW A_ED WHERE A.SETID = A_ED.SETID AND A.ACCOUNT = A_ED.ACCOUNT AND A_ED.EFFDT <= %CurrentDateIn) AND A.SETID = B.SETID AND B.REC_GROUP_ID = 'FS_05' |
| # | 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 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 7 | ACCOUNT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Account Description |
| 8 | ACCOUNT_TYPE | Character(1) | VARCHAR2(1) NOT NULL | Identifies a category of accounts that appears on the balance sheet or income statement of the enterprise. Each general ledger account (ACCOUNT) is associated with an account type (also called a ""monetary account type""). PeopleSoft delivers standard account types such as asset, liability, expense, and revenue. |
| 9 | ACCT_TYPE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Account Type Description |
| 10 | TLVL1_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 11 | TNODE1_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 12 | TLVL2_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 13 | TNODE2_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 14 | TLVL3_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 15 | TNODE3_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 16 | TLVL4_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 17 | TNODE4_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 18 | TLVL5_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 19 | TNODE5_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 20 | TLVL6_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 21 | TNODE6_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 22 | TLVL7_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 23 | TNODE7_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 24 | TLVL8_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 25 | TNODE8_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 26 | TLVL9_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 27 | TNODE9_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 28 | TLVL10_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 29 | TNODE10_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 30 | TLVL11_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 31 | TNODE11_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 32 | TLVL12_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 33 | TNODE12_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 34 | TLVL13_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 35 | TNODE13_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 36 | TLVL14_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 37 | TNODE14_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |
| 38 | TLVL15_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Level Description |
| 39 | TNODE15_DESC | Character(30) | VARCHAR2(30) NOT NULL | OTBI: Tree Node Description |