USERIDPERSON_IDBUSINESS_UNITDefault Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
Prompt Table: SP_BU_SP_NONVW
TE_TEMPLATE_IDPrompt Table: SPA_SP_RTMPL_VW
YEAR_PERIOD_STRINGPrompt Table: SPA_TIME_TMPL
SPA_SUP_RC_PD_V(SQL View) |
Index Back |
|---|
| SELECT DISTINCT O.ROLEUSER , W.WO_CONID ,W.BUSINESS_UNIT ,W.TE_TEMPLATE_ID , %NumToChar(C.FISCAL_YEAR) %Concat %NumToChar(C.ACCOUNTING_PERIOD) , C.FISCAL_YEAR , C.ACCOUNTING_PERIOD , C.BEGIN_DT , C.END_DT , ' ' FROM PS_SPF_WORDERREC W , PS_BUS_UNIT_TBL_FS B , PS_SPA_TIME_TMPL T , PS_SET_CNTRL_GROUP S , PS_CAL_DETP_TBL C , PS_SET_CNTRL_GROUP S2 , PS_ROLEXLATOPR O , PSOPRALIAS A WHERE W.WO_STATUS IN ('C','N','L','T') AND W.SERVICE_METHOD = 'R' AND W.BUSINESS_UNIT = B.BUSINESS_UNIT AND S.REC_GROUP_ID = 'SP_12' AND S.SETCNTRLVALUE = W.BUSINESS_UNIT AND T.SETID = S.SETID AND T.TE_TEMPLATE_ID = W.TE_TEMPLATE_ID AND S2.REC_GROUP_ID = 'FS_02' AND S2.SETCNTRLVALUE = T.SETID AND C.SETID = S2.SETID AND C.CALENDAR_ID = T.CALENDAR_ID AND C.BEGIN_DT <= ( SELECT MAX(W2.ACT_END_DT) FROM PS_SPF_WORDERREC W2 WHERE W2.BUSINESS_UNIT = W.BUSINESS_UNIT AND W2.TE_TEMPLATE_ID = W.TE_TEMPLATE_ID AND W2.WO_STATUS IN ('C','N','L','T') AND W2.SERVICE_METHOD = 'R' AND W2.WO_CONID = W.WO_CONID ) AND C.END_DT >= ( SELECT MIN(W3.ACT_START_DT) FROM PS_SPF_WORDERREC W3 WHERE W3.BUSINESS_UNIT = W.BUSINESS_UNIT AND W3.TE_TEMPLATE_ID = W.TE_TEMPLATE_ID AND W3.WO_STATUS IN ('C','N','L','T') AND W3.SERVICE_METHOD = 'R' AND W3.WO_CONID = W.WO_CONID ) AND A.OPRID IN ( SELECT B.ROLEUSER FROM PS_SPB_SERVICE_PRV A , PS_ROLEXLATOPR B , PS_SPB_OPRALIAS_VW C ,PS_SET_CNTRL_GROUP S WHERE (A.SP_FUNCTION_TYPE = 'PRCT' OR A.SP_FUNCTION_TYPE = 'INDP') AND A.VENDOR_ID = W.VENDOR_ID AND A.LOCATION = W.VNDR_LOC AND C.OPRALIASTYPE = 'SPP' AND C.PERSON_ID = A.PERSON_ID AND B.ROLEUSER = C.OPRID AND S.SETCNTRLVALUE = W.BUSINESS_UNIT AND S.REC_GROUP_ID = 'FS_38' AND S.SETID = A.VENDOR_SETID) AND A.OPRID = O.ROLEUSER |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | USERID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID |
| 2 | PERSON_ID |
Character(15) | VARCHAR2(15) NOT NULL | Person ID |
| 3 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BU_SP_NONVW |
| 4 | TE_TEMPLATE_ID |
Character(8) | VARCHAR2(8) NOT NULL |
Used to define a particular type of travel, i.e. company relocation, internal travel, etc. Determines what type of expenses and expenditure limits are associated with that particular type of travel. Established in the Structure Expenses system and chosen on the header panel of cash expense sheets.
Prompt Table: SPA_SP_RTMPL_VW |
| 5 | YEAR_PERIOD_STRING |
Character(8) | VARCHAR2(8) NOT NULL | Year plus Period |
| 6 | FISCAL_YEAR | Number(4,0) | SMALLINT NOT NULL | Fiscal Year |
| 7 | ACCOUNTING_PERIOD | Number(3,0) | SMALLINT NOT NULL | Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table. |
| 8 | BEGIN_DT | Date(10) | DATE | Begin Date |
| 9 | END_DT | Date(10) | DATE | End Date |
| 10 | SPA_DATE_RANGE | Character(23) | VARCHAR2(23) NOT NULL |
Date Range
Prompt Table: SPA_TIME_TMPL |