|
SELECT A.SHEET_ID , A.EMPLID , B.LINE_NBR , C.DISTRIB_LINE_NUM , A.UNPOST_SEQ , A.BUSINESS_UNIT_GL , C.BUSINESS_UNIT_GL , A.SHEET_STATUS , B.EX_LINE_STATUS , A.CREATION_DT , A.APPROVAL_DT , A.REVERSAL_DATE , A.DOC_TYPE , A.DOC_SEQ_NBR , A.DOC_SEQ_DATE , A.APPL_JRNL_ID , A.ACCOUNTING_DT , A.DST_CNTRL_ID , B.EXPEND_MTHD , B.REIMBURSEMENT_CD , A.POST_STATUS_EX , C.MONETARY_AMOUNT , C.CURRENCY_CD , C.TXN_AMOUNT , C.TXN_CURRENCY_CD , C.RATE_MULT , C.RATE_DIV , C.RT_TYPE , C.BI_DISTRIB_STATUS , A.ADVANCE_ID , A.PROCESS_INSTANCE ,%subrec(EX_VAT_LINE,B) , C.VAT_BASIS_AMT , C.VAT_BASIS_AMT_BSE , C.VAT_CALC_AMT , C.VAT_CALC_AMT_BSE , C.VAT_RCVRY_AMT , C.VAT_RCVRY_AMT_BSE , C.VAT_REBATE_AMT , C.VAT_REBATE_AMT_BSE , C.VAT_TRANS_AMT , C.VAT_TRANS_AMT_BSE ,%subrec(EX_PROJFLDS,C) ,A.PROJ_MGR_FLAG FROM PS_EX_SHEET_HDR A , PS_EX_SHEET_LINE B , PS_EX_SHEET_DIST C WHERE B.SHEET_ID = A.SHEET_ID AND C.SHEET_ID = B.SHEET_ID AND C.LINE_NBR = B.LINE_NBR
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 SHEET_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Report ID
|
| 2 |
EMPLID |
Character(11) |
VARCHAR2(11) NOT NULL |
Employee ID
|
| 3 |
LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
Line Number:
11/24/08 - Added TARGET label [PC product]
|
| 4 |
DISTRIB_LINE_NUM |
Number(5,0) |
INTEGER NOT NULL |
Distribution Line
|
| 5 |
UNPOST_SEQ |
Number(2,0) |
SMALLINT NOT NULL |
Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".
|
| 6 |
BUSINESS_UNIT_GL |
Character(5) |
VARCHAR2(5) NOT NULL |
GL Business Unit
|
| 7 |
BUSINESS_UNIT_CHRG |
Character(5) |
VARCHAR2(5) NOT NULL |
Charged Business Unit
|
| 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 |
EX_LINE_STATUS |
Character(3) |
VARCHAR2(3) NOT NULL |
The Line Status of an Expenses transaction (i.e. Expense Report, Cash Advance, Travel Authorization, Time Report)
ADJ=Adjusted
APR=Approved
APY=Approved for Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNC=Denied
DNU=Denied by Auditor
ESC=Escheated Payment
HDA=Hold by Approver
HDU=Hold by Auditor
HLD=Hold
MFS=Marked For Submit
OPN=Open
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
RAP=Approved
STG=Staged
SUB=Submitted
XML=Submitted, Pending Validation
|
| 10 |
CREATION_DT |
Date(10) |
DATE |
Creation Date
|
| 11 |
APPROVAL_DT |
Date(10) |
DATE |
Date of Approval
|
| 12 |
REVERSAL_DATE |
Date(10) |
DATE |
Specifies the date for an automatic reversal journal entry. The date must be within the calendar of the target (posting) ledger. PeopleSoft General Ledger automatically creates and populates a journal entry with this date.
|
| 13 |
DOC_TYPE |
Character(8) |
VARCHAR2(8) NOT NULL |
Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
|
| 14 |
DOC_SEQ_NBR |
Character(12) |
VARCHAR2(12) NOT NULL |
Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
|
| 15 |
DOC_SEQ_DATE |
Date(10) |
DATE |
Specifies the date that a document sequence number is assigned to a document or the date the document was created.
|
| 16 |
APPL_JRNL_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Journal Template
|
| 17 |
ACCOUNTING_DT |
Date(10) |
DATE |
The accounting entry construction date for a given transaction (a generic field that crosses multiple
|
| 18 |
DST_CNTRL_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Accounting Template
|
| 19 |
EXPEND_MTHD |
Character(3) |
VARCHAR2(3) NOT NULL |
Payment Type
|
| 20 |
REIMBURSEMENT_CD |
Character(1) |
VARCHAR2(1) NOT NULL |
Deny
N=Nonreimburseable
P=Prepaid
R=Reimburseable
|
| 21 |
POST_STATUS_EX |
Character(1) |
VARCHAR2(1) NOT NULL |
Expenses Post Status
C=Closed
M=Marked for Unpost
N=Not Applied
O=Unpost In Process
P=Posted
S=Close In Process
U=Unposted
|
| 22 |
MONETARY_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
|
| 23 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 24 |
TXN_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Transaction Amount
|
| 25 |
TXN_CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency
|
| 26 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 27 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
|
| 28 |
RT_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
|
| 29 |
BI_DISTRIB_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Billing Distribution Status
D=Distributed
I=Ignore
N=Not Distributed
P=Priced
U=Unbillable/Non-billable
W=Billing Worksheet
|
| 30 |
ADVANCE_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Advance ID
|
| 31 |
PROCESS_INSTANCE |
Number(10,0) |
DECIMAL(10) NOT NULL |
Process Instance
|
| 32 |
COUNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Country
Prompt Table:
COUNTRY_TBL
|
| 33 |
STATE |
Character(6) |
VARCHAR2(6) NOT NULL |
State
Prompt Table:
STATE_TBL
|
| 34 |
VAT_RECEIPT |
Character(1) |
VARCHAR2(1) NOT NULL |
No VAT Receipt
Y/N Table Edit
Default Value: N
|
| 35 |
VAT_ENTRD_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Entered VAT Amount
|
| 36 |
VAT_NRCVR_PRO_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Prorate Non-Recoverable VAT
Y/N Table Edit
|
| 37 |
VAT_NRCVR_ALL_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Allocate Non-Recoverable VAT
Y/N Table Edit
|
| 38 |
TAX_CD_VAT |
Character(8) |
VARCHAR2(8) NOT NULL |
Defines the VAT (Value Added Tax) percentage (via link to the Tax Authority) and is used to retrieve VAT accounting ChartFields. The VAT code determines how the VAT amount is calculated on a transaction and how that amount is accounted and reported for.
Prompt Table:
VAT_HEADER_VW
|
| 39 |
TAX_CD_VAT_PCT |
Signed Number(9,4) |
DECIMAL(7,4) NOT NULL |
Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.
|
| 40 |
VAT_TREATMENT |
Character(4) |
VARCHAR2(4) NOT NULL |
VAT Treatment
|
| 41 |
VAT_TXN_TYPE_CD |
Character(4) |
VARCHAR2(4) NOT NULL |
Specifies a user-defined category of business transaction that is subject to VAT accounting and reporting. The VAT code and the VAT transaction type are used in conjunction with the VAT account type to obtain the ChartFields for accounting entries. Some examples of VAT Transaction Types are Exempt Sales Exempt Purchases Triangulation EU Sales and Domestic Sales.
Prompt Table:
VAT_TXN_CD
|
| 42 |
VAT_RECOVERY_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Recovery Percent
|
| 43 |
VAT_REBATE_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Rebate Percent
|
| 44 |
VAT_ROUND_RULE |
Character(1) |
VARCHAR2(1) NOT NULL |
" Rounding rule to be applied to VAT calculations. Options are 'Natural'
D=Round Down
N=Natural Round
U=Round Up
|
| 45 |
VAT_APPLICABILITY |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates whether or not a transaction is eligible for VAT (Value Added Tax) taxation or why it is not eligible.
E=Exempt
N=Not Applicable
O=Outside of Scope of VAT
S=Suspended
T=Taxable
V=VAT Only
X=Exonerated
|
| 46 |
VAT_USE_ID |
Character(6) |
VARCHAR2(6) NOT NULL |
VAT Use Type
Prompt Table:
VAT_USE_TBL
|
| 47 |
VAT_RECLAIM_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Reclaim Percent
|
| 48 |
VAT_RCLM_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Reclaim Amount
|
| 49 |
VAT_RCLM_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Base VAT Reclaim Amount
|
| 50 |
VAT_NONTX_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Non-Taxable Amount
|
| 51 |
VAT_NONTX_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Base Non-Taxable Amount
|
| 52 |
VAT_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the amount of VAT associated with a transaction such as an invoice or a purchase order. In PeopleSoft Receivables this field is populated when the VAT declaration point is set to Invoice.
|
| 53 |
VAT_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Amount in Base Curr
|
| 54 |
VAT_AUTO_CALC_FLG |
Character(1) |
VARCHAR2(1) NOT NULL |
Automatically Calculate VAT
N=No
Y=Yes
Y/N Table Edit
|
| 55 |
TXN_CURRENCY_CD1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency Display
|
| 56 |
CURRENCY_CD1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 57 |
VAT_BASIS_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Specifies the amount on a sale (receivables item) or purchase (Payables voucher) transaction that is subject to VAT taxation. Depending on other parameters this amount may represent the gross or net amount of the transaction and may or may not include freight charges.
|
| 58 |
VAT_BASIS_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Basis Amt in Base Curr
|
| 59 |
VAT_CALC_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Calculated Amount
|
| 60 |
VAT_CALC_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Calculated Amount Base Cur
|
| 61 |
VAT_RCVRY_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Recovery Amt
|
| 62 |
VAT_RCVRY_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Recovery Amt in Base Curr
|
| 63 |
VAT_REBATE_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Rebate Amt
|
| 64 |
VAT_REBATE_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Rebate Amt in Base Curr
|
| 65 |
VAT_TRANS_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Represents the sale or purchase amount against which the VAT is applicable.
|
| 66 |
VAT_TRANS_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Represents the sale or purchase amount in base currency against which the VAT is applicable
|
| 67 |
BUSINESS_UNIT_PC |
Character(5) |
VARCHAR2(5) NOT NULL |
PC Business Unit
|
| 68 |
ACTIVITY_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Activity ID
|
| 69 |
RESOURCE_CATEGORY |
Character(5) |
VARCHAR2(5) NOT NULL |
Category
|
| 70 |
RESOURCE_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Source Type
|
| 71 |
RESOURCE_SUB_CAT |
Character(5) |
VARCHAR2(5) NOT NULL |
Subcategory
|
| 72 |
ANALYSIS_TYPE |
Character(3) |
VARCHAR2(3) NOT NULL |
Analysis Type
|
| 73 |
PROJ_MGR_FLAG |
Character(1) |
VARCHAR2(1) NOT NULL |
Project Manager Review
|