SPA_EXP_APR_VW

(SQL View)

SELECT X.BUSINESS_UNIT ,X.WORK_ORDER_ID ,X.DISTRIB_LINE_NUM ,X.SHEET_ID ,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 FROM PS_SPA_EXP_XREF X , PS_SPA_SHEET_HDR E , PS_SPA_SHEET_LINE L WHERE E.SHEET_ID = L.SHEET_ID AND X.SHEET_ID = L.SHEET_ID GROUP BY X.BUSINESS_UNIT ,X.WORK_ORDER_ID ,X.DISTRIB_LINE_NUM ,X.SHEET_ID ,E.EXP_HDR_STATUS ,E.CREATION_DT ,L.CURRENCY_CD ,L.CURRENCY_CD_BASE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
Prompt Table: SP_BU_SP_NONVW

2 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID

3 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line

4 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID

5 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

6 CREATION_DT Date(10) DATE Creation Date

7 SPF_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total expense amount for base currency

8 SPF_EXPENSE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total expense amount.

9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Prompt Table: CURRENCY_CD_TBL

10 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BU_SP_NONVW
2	WORK_ORDER_ID	Character(15)	VARCHAR2(15) NOT NULL	Work Order ID
3	DISTRIB_LINE_NUM	Number(5,0)	INTEGER NOT NULL	Distribution Line
4	SHEET_ID	Character(10)	VARCHAR2(10) NOT NULL	Report ID
5	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
6	CREATION_DT	Date(10)	DATE	Creation Date
7	SPF_EXP_AMT_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total expense amount for base currency
8	SPF_EXPENSE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total expense amount.
9	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Prompt Table: CURRENCY_CD_TBL
10	CURRENCY_CD_BASE	Character(3)	VARCHAR2(3) NOT NULL	Business Unit Base Currency