EX_TA_SCHFLU_VW(SQL View) |
Index Back |
|---|
| SELECT c.authorized_oprid , a.travel_auth_id , a.travel_auth_name , b.name , a.emplid , a.travel_auth_status , a.creation_dt , e.descr , d.descr , a.total_amt , a.currency_cd , f.xlatlongname , ' ' AS DESCR100A , %NumToChar(G.CALENDAR_YEAR) , %NumToChar(G.CALENDAR_YEAR) %Concat ', ' %Concat G.PERIOD_ABBRV AS MONTH_CHAR , %NumToChar(G.CALENDAR_YEAR) %Concat ', ' %Concat G.PERIOD_ABBRV AS MONTH_DESCR , A.DATE_FROM , A.DATE_TO FROM ps_ex_tauth_hdr a , ps_personal_data b , ps_ex_ee_auth_tbl c , ps_EX_LOCATION_VW4 d , PS_EX_PURPOSE_TBL e , PSXLATITEM f , PS_WM_DATE_DIM G WHERE a.travel_auth_status = 'PND' AND a.EX_ORIGIN = 'N' AND b.emplid = a.emplid AND c.emplid = a.emplid AND d.setid= a.setid AND d.txn_location = a.txn_location AND G.TRANS_DATE = A.DATE_FROM AND a.setid = e.setid AND a.business_purpose = e.business_purpose AND a.travel_auth_status = f.fieldvalue AND f.fieldname = 'TRAVEL_AUTH_STATUS' AND f.EFFDT = ( SELECT MAX(f1.EFFDT) FROM PSXLATITEM f1 WHERE f.fieldname = f1.fieldname AND f.fieldvalue = f1.fieldvalue AND f1.EFFDT <= %CurrentDateIn) AND f.EFF_STATUS = 'A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRID |
Character(30) | VARCHAR2(30) NOT NULL |
A user's ID (see PSOPRDEFN).
Default Value: PSOPRDEFN.OPRID Prompt Table: PSOPRDEFNVW |
| 2 | TRAVEL_AUTH_ID |
Character(10) | VARCHAR2(10) NOT NULL |
Travel Authorization Identification. Number used to define travel authorizations. Key field on most travel authorization records and exists at level 0 for travel authorization panels.
Prompt Table: EX_TAUTH_HDR_VW |
| 3 | TRAVEL_AUTH_NAME | Character(30) | VARCHAR2(30) NOT NULL | Field used to give a descriptive name to each travel authorization. Associated with the header information of a travel authorization and found on many records and panels within travel authorization objects. |
| 4 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 5 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: EX_EE_AUTH_VW |
| 6 | TRAVEL_AUTH_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Describes the current status of the travel authorization - whether pending, submitted or approved. This field is associated with the header record of travel authorizations and is found on many records and panels within travel authorization objects. The status will change depending on the action taken upon the travel authorization. PeopleCode is used to update the status.
APR=Approved CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold PAR=Approvals in Process PND=Pending PRO=In Process RCN=Reconciled RRT=Transaction Rerouted SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation |
| 7 | CREATION_DT | Date(10) | DATE | Creation Date |
| 8 | BUS_PURP_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Business Purpose |
| 9 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location |
| 10 | TOTAL_AMOUNT | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Total Amount |
| 11 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 12 | STATUS_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Status Description |
| 13 | DESCR100A | Character(100) | VARCHAR2(100) NOT NULL | Description |
| 14 | YEAR | Character(4) | VARCHAR2(4) NOT NULL |
Year
1=NONE |
| 15 | MONTH_CHAR | Character(15) | VARCHAR2(15) NOT NULL | Month |
| 16 | MONTH_DESCR | Character(30) | VARCHAR2(30) NOT NULL | This is the description of the month. |
| 17 | DATE_FROM | Date(10) | DATE | Date From |
| 18 | DATE_TO | Date(10) | DATE | Date To |