SPA_EXPSUP_A_VW

(SQL View)
 Index Back

Active Expenses View-Supplier

Active Expenses View for Supplier

SELECT P.OPRID , W.WO_CONID , X.BUSINESS_UNIT ,X.WORK_ORDER_ID ,X.DISTRIB_LINE_NUM ,X.SHEET_ID , S.NAME ,E.EXP_HDR_STATUS ,E.CREATION_DT ,SUM(L.MONETARY_AMOUNT) ,SUM(L.AMOUNT_BSE) AS AMOUNT_BSE ,L.CURRENCY_CD ,L.CURRENCY_CD_BASE , B.DESCRSHORT , W.WO_NAME , D.DESCR FROM PS_SPA_EXP_XREF X , PS_SPA_SHEET_HDR E , PS_SPA_SHEET_LINE L ,PS_SPF_WOPO_DISTRB D , PS_SPF_WORDERREC W , PS_BUS_UNIT_TBL_FS B , PSOPRALIAS P , PS_SPB_PERSON_TBL S , PS_SPB_SERVICE_PRV R , PS_SET_CNTRL_GROUP G WHERE E.SHEET_ID = L.SHEET_ID AND L.SHEET_ID = X.SHEET_ID AND D.BUSINESS_UNIT = W.BUSINESS_UNIT AND B.BUSINESS_UNIT = W.BUSINESS_UNIT AND W.BUSINESS_UNIT = X.BUSINESS_UNIT AND D.WORK_ORDER_ID = W.WORK_ORDER_ID AND W.WORK_ORDER_ID = X.WORK_ORDER_ID AND W.SERVICE_METHOD = 'R' AND E.EXP_HDR_STATUS IN ('OPN','DEN') AND D.DISTRIB_TYPE = 'EXP' AND P.PERSON_ID = R.PERSON_ID AND P.OPRALIASTYPE = 'SPP' AND S.PERSON_ID = W.WO_CONID AND G.SETCNTRLVALUE = W.BUSINESS_UNIT AND G.REC_GROUP_ID = 'FS_38' AND R.VENDOR_SETID = G.SETID AND R.VENDOR_ID = W.VENDOR_ID AND R.LOCATION = W.VNDR_LOC AND R.SP_FUNCTION_TYPE = 'PRCT' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_SPB_SERVICE_PRV R1 WHERE R1.PERSON_ID = R.PERSON_ID AND R1.SP_FUNCTION_TYPE = R.SP_FUNCTION_TYPE AND R1.EFFDT <= %CurrentDateIn) GROUP BY P.OPRID, W.WO_CONID,X.BUSINESS_UNIT ,X.WORK_ORDER_ID ,X.DISTRIB_LINE_NUM ,X.SHEET_ID ,S.NAME, E.EXP_HDR_STATUS ,E.CREATION_DT ,L.CURRENCY_CD ,L.CURRENCY_CD_BASE, B.DESCRSHORT , W.WO_NAME , D.DESCR

# 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 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID
5 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
6 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 SHEET_STATUS Character(3) VARCHAR2(3) NOT NULL Report Status
APY=Approved for Payment
APZ=Approved For Zero Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
ESC=Escheated Payment
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
MFS=Marked for Submit
OPN=Open
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
RAP=Approved
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation
9 CREATION_DT Date(10) DATE Creation Date
10 SPF_EXPENSE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total expense amount.
11 SPF_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total expense amount for base currency
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

13 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
14 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
15 WO_NAME Character(30) VARCHAR2(30) NOT NULL Work Order Name
16 DESCR Character(30) VARCHAR2(30) NOT NULL Description