EX_PER_DIEM_VW(SQL View) |
Index Back |
|---|---|
Per Diem Detail Table |
| SELECT B.SETID ,B.EXPENSE_TYPE ,B.COUNTRY ,B.RANGE_CATEGORY ,B.CURRENCY_CD ,B.TXN_LOCATION ,B.EFFDT ,E.RANGE_ID ,D.RANGE_TYPE ,E.FROM_VALUE_1 ,E.TO_VALUE_1 ,E.FROM_VALUE_2 ,E.TO_VALUE_2 ,E.FROM_VALUE_3 ,E.TO_VALUE_3 ,E.FROM_VALUE_4 ,E.TO_VALUE_4 ,B.AMT1 ,B.AMT2 ,B.AMT3 ,B.AMT4 ,B.PD_LODGING_AMT ,B.EX_CALC_CD1 ,B.EX_CALC_CD2 ,B.EX_CALC_CD3 ,B.EX_CALC_CD4 ,B.AMT_GOVT1 ,B.AMT_GOVT2 ,B.AMT_GOVT3 ,B.AMT_GOVT4 FROM PS_EX_PER_DIEM_TBL A ,PS_EX_PER_DIEM_DTL B ,PS_EX_RANGE_DTL E ,PS_EX_RANGE_TBL D WHERE A.SETID = B.SETID AND A.EXPENSE_TYPE = B.EXPENSE_TYPE AND A.COUNTRY = B.COUNTRY AND A.CURRENCY_CD = B.CURRENCY_CD AND A.EFFDT = B.EFFDT AND E.EFFDT = D.EFFDT AND A.SETID = E.SETID AND E.SETID = D.SETID AND D.RANGE_ID = A.RANGE_ID AND E.RANGE_ID = D.RANGE_ID AND E.RANGE_CATEGORY = D.RANGE_CATEGORY AND B.RANGE_CATEGORY = D.RANGE_CATEGORY AND A.COUNTRY = E.COUNTRY AND E.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_EX_RANGE_DTL C1 WHERE E.SETID = C1.SETID AND E.RANGE_ID = C1.RANGE_ID AND E.RANGE_CATEGORY = C1.RANGE_CATEGORY AND C1.EFFDT <= %CurrentDateIn) AND E.EFF_STATUS = 'A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL |
SetID
Default Value: OPR_DEF_TBL_FS.SETID Prompt Table: SP_SETID_NONVW |
| 2 | EXPENSE_TYPE |
Character(7) | VARCHAR2(7) NOT NULL |
Expense Type
Prompt Table: EX_TYPES_TBL |
| 3 | COUNTRY |
Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: COUNTRY_TBL |
| 4 | RANGE_CATEGORY | Character(5) | VARCHAR2(5) NOT NULL | Range Category |
| 5 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table: CURRENCY_CD_TBL |
| 6 | TXN_LOCATION |
Character(5) | VARCHAR2(5) NOT NULL |
Expense Location
Prompt Table: EX_LOCATION_TBL |
| 7 | EFFDT |
Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
| 8 | RANGE_ID | Character(5) | VARCHAR2(5) NOT NULL | Range ID |
| 9 | RANGE_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Range Type
D=Daily H=Hourly |
| 10 | FROM_VALUE_1 | Number(3,0) | SMALLINT NOT NULL | From Value |
| 11 | TO_VALUE_1 | Number(3,0) | SMALLINT NOT NULL | To Value |
| 12 | FROM_VALUE_2 | Number(3,0) | SMALLINT NOT NULL | From Value |
| 13 | TO_VALUE_2 | Number(3,0) | SMALLINT NOT NULL | To Value |
| 14 | FROM_VALUE_3 | Number(3,0) | SMALLINT NOT NULL | From Value |
| 15 | TO_VALUE_3 | Number(3,0) | SMALLINT NOT NULL | To Value |
| 16 | FROM_VALUE_4 | Number(3,0) | SMALLINT NOT NULL | From Value |
| 17 | TO_VALUE_4 | Number(3,0) | SMALLINT NOT NULL | To Value |
| 18 | AMT1 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Used in Virtual Approver to store the various rule amounts that go on each approval step |
| 19 | AMT2 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Used in Virtual Approver to store the various rule amounts that go on each approval step |
| 20 | AMT3 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Used in Virtual Approver to store the various rule amounts that go on each approval step |
| 21 | AMT4 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Used in Virtual Approver to store the various rule amounts that go on each approval step |
| 22 | PD_LODGING_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Per Diem Lodging Amount |
| 23 | EX_CALC_CD1 | Character(5) | VARCHAR2(5) NOT NULL | Calculation Code 1 |
| 24 | EX_CALC_CD2 | Character(5) | VARCHAR2(5) NOT NULL | Calculation Code 2 |
| 25 | EX_CALC_CD3 | Character(5) | VARCHAR2(5) NOT NULL | Calculation Code 3 |
| 26 | EX_CALC_CD4 | Character(5) | VARCHAR2(5) NOT NULL | Calculation Code 4 |
| 27 | AMT_GOVT1 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Government Amount 1 |
| 28 | AMT_GOVT2 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Government Amount 2 |
| 29 | AMT_GOVT3 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Government Amount 3 |
| 30 | AMT_GOVT4 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Government Amount 4 |