GPFR_DSN_FIL_VW(SQL View) |
Index Back |
|---|---|
DSN FileDSN File view from GPFR_AF_EXTRACT |
| SELECT TEMP.GPFR_AF_EXTRACT_ID, TEMP.GPFR_AF_NODE_SET, TEMP.COMPANY, TEMP.EMPLID, TEMP.EMPL_RCD, TEMP.GPFR_AF_SIT_BGN_DT, TEMP.GPFR_AF_SIT_END_DT, TEMP.GPFR_AF_KEY_EXTRCT, TEMP.GPFR_AF_KEY_NODE, TEMP.GPFR_AF_KEY_ATTR, TEMP.GPFR_AF_APPL_VER, TEMP.GPFR_AF_NODE_NAME, TEMP.GPFR_AF_ATTR_NAME, TEMP.DESCR100, TEMP.DESCR60, TEMP.ESTABID, TEMP.GPFR_AF_CHAR_VAL, TEMP.GPFR_AF_DATE_VAL, TEMP.GPFR_AF_NUM_VAL, CASE WHEN GPFR_AF_ATTR_TYPE = 'T' THEN %Substring(GPFR_AF_VALUE, 1, TEMP.GPFR_AF_ATR_LENGTH) WHEN GPFR_AF_ATTR_TYPE = 'D' THEN GPFR_AF_VALUE WHEN GPFR_AF_ATTR_TYPE IN ('M', 'N') THEN (CASE WHEN TEMP.GPFR_AF_NODE_NAME = 'S21.G00.50' AND TEMP.GPFR_AF_ATTR_NAME = '003' AND TEMP.GPFR_AF_NUM_VAL > 0 AND TEMP.GPFR_AF_NUM_VAL <= 9 THEN '0' END) %Concat LTRIM (TO_CHAR (TO_NUMBER (GPFR_AF_VALUE, '999999999999999990D000000'), '99999999999999990' %Concat(CASE WHEN TEMP.GPFR_AF_ATR_DECIML > 0 THEN 'D' %Concat RPAD ('0', TEMP.GPFR_AF_ATR_DECIML, '0') END))) END, TEMP.GPFR_AF_ATTR_TYPE, TEMP.GPFR_AF_ATR_LENGTH, TEMP.GPFR_AF_ATR_DECIML, TEMP.GPFR_AF_RND_NM_VAL FROM (SELECT X.GPFR_AF_EXTRACT_ID, X.GPFR_AF_NODE_SET, X.COMPANY, X.EMPLID, X.EMPL_RCD, X.GPFR_AF_SIT_BGN_DT, X.GPFR_AF_SIT_END_DT, X.GPFR_AF_KEY_EXTRCT, X.GPFR_AF_KEY_NODE, X.GPFR_AF_KEY_ATTR, A.GPFR_AF_APPL_VER, X.GPFR_AF_NODE_NAME, X.GPFR_AF_ATTR_NAME, N.DESCR100, A.DESCR60, X.ESTABID, X.GPFR_AF_CHAR_VAL, X.GPFR_AF_DATE_VAL, X.GPFR_AF_NUM_VAL, CASE WHEN A.GPFR_AF_ATTR_TYPE = 'T' THEN LTRIM (UPPER (TRANSLATE (X.GPFR_AF_CHAR_VAL, (SELECT LISTAGG (GPFR_DT_XVALUES, '') WITHIN GROUP ( ORDER BY GPFR_DA_CODE) AS concatenated_string FROM PS_GPFR_DT_XVALUES WHERE COUNTRY = 'FRA' AND GPFR_AF_APPL = 'DSN' AND GPFR_DA_DATTYP = 'DSNCHRCNV') %Concat '/'',',(SELECT LISTAGG (GPFR_DA_CODE, '') WITHIN GROUP ( ORDER BY GPFR_DA_CODE) AS concatenated_string FROM PS_GPFR_DT_XVALUES WHERE COUNTRY = 'FRA' AND GPFR_AF_APPL = 'DSN' AND GPFR_DA_DATTYP = 'DSNCHRCNV') %Concat ' '))) WHEN A.GPFR_AF_ATTR_TYPE = 'D' THEN %Sql(GPFR_DSN_DDMMYY, X.GPFR_AF_DATE_VAL) WHEN A.GPFR_AF_ATTR_TYPE IN ('M', 'N') THEN LTRIM (TO_CHAR ((CASE WHEN X.GPFR_AF_RND_NM_VAL = 1 THEN ROUND (X.GPFR_AF_NUM_VAL, A.GPFR_AF_ATR_DECIML) WHEN X.GPFR_AF_RND_NM_VAL = 2 THEN ROUND (X.GPFR_AF_NUM_VAL, 0) WHEN X.GPFR_AF_RND_NM_VAL = 3 THEN %Truncate(X.GPFR_AF_NUM_VAL + 0.5, A.GPFR_AF_ATR_DECIML) ELSE %Truncate(X.GPFR_AF_NUM_VAL, A.GPFR_AF_ATR_DECIML) END), '999999999999999990d' ||RPAD ('0', 6, '0'))) END GPFR_AF_VALUE, A.GPFR_AF_ATTR_TYPE, A.GPFR_AF_ATR_LENGTH, A.GPFR_AF_ATR_DECIML, X.GPFR_AF_RND_NM_VAL FROM PS_GPFR_AF_EXTRACT X, PS_GPFR_AF_NODE N, PS_GPFR_AF_ATTR A, PS_GPFR_AF_RUN_PRM P WHERE X.COUNTRY = 'FRA' AND X.GPFR_AF_APPL = 'DSN' AND P.GPFR_AF_EXTRACT_ID = X.GPFR_AF_EXTRACT_ID AND P.GPFR_AF_FUNCT_NM = 'RUN_CTL' AND P.GPFR_AF_PARM_NM = 'VERSION' AND N.COUNTRY = X.COUNTRY AND N.GPFR_AF_APPL = X.GPFR_AF_APPL AND N.GPFR_AF_KEY_NODE = X.GPFR_AF_KEY_NODE AND A.COUNTRY = X.COUNTRY AND A.GPFR_AF_APPL = X.GPFR_AF_APPL AND A.GPFR_AF_KEY_NODE = X.GPFR_AF_KEY_NODE AND A.GPFR_AF_KEY_ATTR = X.GPFR_AF_KEY_ATTR AND A.GPFR_AF_APPL_VER = (SELECT MAX(A2.GPFR_AF_APPL_VER) FROM PS_GPFR_AF_ATTR A2 WHERE A2.COUNTRY = A.COUNTRY AND A2.GPFR_AF_APPL = A.GPFR_AF_APPL AND A2.GPFR_AF_KEY_NODE = A.GPFR_AF_KEY_NODE AND A2.GPFR_AF_KEY_ATTR = A.GPFR_AF_KEY_ATTR AND A2.GPFR_AF_APPL_VER <= %Sql(GPFR_CHAR_TO_NUM, P.GPFR_AF_CHAR_VAL)) AND A.GPFR_AF_ATTR_STAT = 'A' AND A.GPFR_AF_RPT_ONLY <> 'Y' AND (A.GPFR_AF_MANDATORY = 'Y' OR (X.GPFR_AF_NUM_VAL <> 0 OR X.GPFR_AF_CHAR_VAL <> ' ' OR X.GPFR_AF_DATE_VAL IS NOT NULL)) ORDER BY X.GPFR_AF_LVL_1_ORD, X.GPFR_AF_LVL_1_VAL, X.GPFR_AF_LVL_2_ORD, X.GPFR_AF_LVL_2_VAL, X.GPFR_AF_LVL_3_ORD, X.GPFR_AF_LVL_3_VAL, X.GPFR_AF_LVL_4_ORD, X.GPFR_AF_LVL_4_VAL, X.GPFR_AF_LVL_5_ORD, X.GPFR_AF_LVL_5_VAL, X.GPFR_AF_LVL_6_ORD, X.GPFR_AF_LVL_6_VAL, X.GPFR_AF_LVL_7_ORD, X.GPFR_AF_LVL_7_VAL, X.GPFR_AF_LVL_8_ORD, X.GPFR_AF_LVL_8_VAL, X.GPFR_AF_LVL_9_ORD, X.GPFR_AF_LVL_9_VAL, X.GPFR_AF_LVL_10_ORD, X.GPFR_AF_LVL_10_VAL, N.GPFR_AF_ORDER, X.GPFR_AF_KEY_EXTRCT, A.GPFR_AF_ATTR_SEQ) TEMP |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | GPFR_AF_EXTRACT_ID | Character(18) | VARCHAR2(18) NOT NULL | Application Framework - Extraction ID |
| 2 | GPFR_AF_NODE_SET | Character(16) | VARCHAR2(16) NOT NULL | Application Framework - Node Set |
| 3 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 4 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 5 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 6 | GPFR_AF_SIT_BGN_DT | Date(10) | DATE NOT NULL | Application Framework - Situation Begin Date |
| 7 | GPFR_AF_SIT_END_DT | Date(10) | DATE NOT NULL | Application Framework - Situation End Date |
| 8 | GPFR_AF_KEY_EXTRCT | Character(50) | VARCHAR2(50) NOT NULL | Application Framework - Key Extract |
| 9 | GPFR_AF_KEY_NODE | Number(8,0) | INTEGER NOT NULL | Application Framework - Key Node |
| 10 | GPFR_AF_KEY_ATTR | Number(8,0) | INTEGER NOT NULL | Application Framework - Key Attribute |
| 11 | GPFR_AF_APPL_VER | Number(8,0) | INTEGER NOT NULL | Application Framework - Application |
| 12 | GPFR_AF_NODE_NAME | Character(20) | VARCHAR2(20) NOT NULL | Application Framework - Node Name |
| 13 | GPFR_AF_ATTR_NAME | Character(12) | VARCHAR2(12) NOT NULL | Application Framework - Attribute Name |
| 14 | DESCR100 | Character(100) | VARCHAR2(100) NOT NULL | Length 100 Description |
| 15 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |
| 16 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 17 | GPFR_AF_CHAR_VAL | Character(50) | VARCHAR2(50) NOT NULL | Application Framework - Character |
| 18 | GPFR_AF_DATE_VAL | Date(10) | DATE | Application Framework - Date |
| 19 | GPFR_AF_NUM_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Application Framework - Number |
| 20 | GPFR_AF_VALUE | Character(60) | VARCHAR2(60) NOT NULL | Application Framework - Value |
| 21 | GPFR_AF_ATTR_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Application Framework - *Attribute Type
D=Date M=Monetary N=Numeric T=Text |
| 22 | GPFR_AF_ATR_LENGTH | Number(3,0) | SMALLINT NOT NULL | Application Framework - *Attribute Length |
| 23 | GPFR_AF_ATR_DECIML | Number(2,0) | SMALLINT NOT NULL | Application Framework - Attribute Decimal |
| 24 | GPFR_AF_RND_NM_VAL | Number(12,0) | DECIMAL(12) NOT NULL | Application Framework - Round Number Value |