|
SELECT A.SHEET_ID ,A.LINE_NBR ,A.DISTRIB_LINE_NUM ,A.DST_ACCT_TYPE ,A.BUSINESS_UNIT_GL ,A.ACCOUNT ,A.ALTACCT ,A.DEPTID , %subrec(CF16_AN_SBR,A) ,A.PROJECT_ID ,CASE WHEN B.REIMBURSEMENT_CD = 'N' THEN 0 ELSE A.MONETARY_AMOUNT END ,A.CURRENCY_CD ,CASE WHEN B.REIMBURSEMENT_CD = 'N' THEN 0 ELSE A.TXN_AMOUNT END ,A.TXN_CURRENCY_CD ,A.RATE_MULT ,A.RATE_DIV ,A.RT_TYPE ,A.GL_DISTRIB_STATUS ,A.BI_DISTRIB_STATUS , %subrec(EX_PROJFLDS,A) , %subrec(EX_VAT_DIST,A) ,A.ACCOUNTING_DT ,A.BUDGET_DT ,A.BUDGET_LINE_STATUS ,A.LEDGER_GROUP ,A.LEDGER ,A.KK_PROCESS_PRIOR ,A.KK_CLOSE_PRIOR ,A.TRAVEL_AUTH_ID ,A.VAT_APORT_CNTRL ,A.VAT_RCVRY_PCT_SRC ,A.VAT_REBATE_PCT_SRC ,A.VAT_RECOVERY_PCT ,A.VAT_REBATE_PCT ,CASE WHEN B.REIMBURSEMENT_CD = 'N' THEN 0 ELSE CASE WHEN B.VAT_NRCVR_PRO_FLG = 'N' THEN A.TXN_AMOUNT - A.VAT_CALC_AMT ELSE A.TXN_AMOUNT - A.VAT_RCVRY_AMT - A.VAT_REBATE_AMT - A.VAT_RCLM_AMT END END ,CASE WHEN B.REIMBURSEMENT_CD = 'N' THEN 0 ELSE CASE WHEN B.VAT_NRCVR_PRO_FLG = 'N' THEN A.MONETARY_AMOUNT - A.VAT_CALC_AMT_BSE ELSE A.MONETARY_AMOUNT - A.VAT_RCVRY_AMT_BSE - A.VAT_REBATE_AMT_BSE - A.VAT_RCLM_AMT_BSE END END FROM PS_EX_SHEET_DIST A , PS_EX_SHEET_LINE B ,PS_EX_SHEET_HDR C WHERE A.SHEET_ID = B.SHEET_ID AND B.SHEET_ID = C.SHEET_ID AND A.LINE_NBR = B.LINE_NBR AND A.SHEET_ID = C.SHEET_ID AND B.REIMB_ACTION = ' ' AND B.APPROVE_FLAG = 'Y' AND B.EX_LINE_STATUS Not In ('CLS', 'ESC') AND (A.TRAVEL_AUTH_ID <> ' ' OR (A.TRAVEL_AUTH_ID = ' ' AND B.PERSONAL_EXPENSE ='N' AND B.REIMBURSEMENT_CD <> 'N'))
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 SHEET_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Report ID
|
| 2 |
LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
Line Number:
11/24/08 - Added TARGET label [PC product]
|
| 3 |
DISTRIB_LINE_NUM |
Number(5,0) |
INTEGER NOT NULL |
Distribution Line
Default Value: 1
|
| 4 |
DST_ACCT_TYPE |
Character(4) |
VARCHAR2(4) NOT NULL |
07/20/04 st: added RCN for Advance Reconciliation
02/16/98 ebn CN#CM800-2.0 : Added for MISC type for Landed Cost Enhancement
11/11/99 llr: Added ADVP for Payables Advance Payment
Default Value: DST
|
| 5 |
BUSINESS_UNIT_GL |
Character(5) |
VARCHAR2(5) NOT NULL |
GL Business Unit
Default Value: BUS_UNIT_TBL_EX.BUSINESS_UNIT_GL
Prompt Table:
SP_BU_GL_NONVW
|
| 6 |
ACCOUNT |
Character(10) |
VARCHAR2(10) NOT NULL |
Account
Prompt Table:
GL_ACCT_BUGL_VW
|
| 7 |
ALTACCT |
Character(10) |
VARCHAR2(10) NOT NULL |
Alternate Account
|
| 8 |
DEPTID |
Character(10) |
VARCHAR2(10) NOT NULL |
Department
Prompt Table:
DEPTID_BUGL_VW
|
| 9 |
OPERATING_UNIT |
Character(8) |
VARCHAR2(8) NOT NULL |
Operating Unit ChartField
Prompt Table:
OPERUNT_BUGL_VW
|
| 10 |
PRODUCT |
Character(6) |
VARCHAR2(6) NOT NULL |
Product ChartField
Prompt Table:
PRODUCT_BUGL_VW
|
| 11 |
FUND_CODE |
Character(5) |
VARCHAR2(5) NOT NULL |
Fund Code
Prompt Table:
FUND_BUGL_VW
|
| 12 |
CLASS_FLD |
Character(5) |
VARCHAR2(5) NOT NULL |
Class Field
Prompt Table:
CLASSCF_BUGL_VW
|
| 13 |
PROGRAM_CODE |
Character(5) |
VARCHAR2(5) NOT NULL |
Program Code ChartField
Prompt Table:
PROGRAM_BUGL_VW
|
| 14 |
BUDGET_REF |
Character(8) |
VARCHAR2(8) NOT NULL |
Budget Reference
Prompt Table:
BUD_REF_BUGL_VW
|
| 15 |
AFFILIATE |
Character(5) |
VARCHAR2(5) NOT NULL |
Affiliate
Prompt Table:
AFFILIATE_VW
|
| 16 |
AFFILIATE_INTRA1 |
Character(10) |
VARCHAR2(10) NOT NULL |
IntraUnit Affiliate1
Prompt Table:
%EDIT_INTRA01
|
| 17 |
AFFILIATE_INTRA2 |
Character(10) |
VARCHAR2(10) NOT NULL |
Operating Unit Affiliate
Prompt Table:
%EDIT_INTRA02
|
| 18 |
CHARTFIELD1 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion chartfield 1
Prompt Table:
CF1_BUGL_VW
|
| 19 |
CHARTFIELD2 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion Chartfield 2
Prompt Table:
CF2_BUGL_VW
|
| 20 |
CHARTFIELD3 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion Chartfield 3
Prompt Table:
CF3_BUGL_VW
|
| 21 |
PROJECT_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Project Id ChartField
|
| 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
Prompt Table:
CURRENCY_CD_TBL
|
| 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
Prompt Table:
CURRENCY_CD_TBL
|
| 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
Default Value: 1
|
| 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.
Default Value: BUS_UNIT_OPT_EX.RT_TYPE
|
| 29 |
GL_DISTRIB_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Distribution Status
C=Available to Contracts
D=Distributed
G=Generated
H=Hold
I=Ignored
J=Creating Journals
M=Modified
N=None
P=To be processed
X=Waiting for Reversal
Default Value: N
|
| 30 |
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
Default Value: I
|
| 31 |
BUSINESS_UNIT_PC |
Character(5) |
VARCHAR2(5) NOT NULL |
PC Business Unit
|
| 32 |
ACTIVITY_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Activity ID
|
| 33 |
RESOURCE_CATEGORY |
Character(5) |
VARCHAR2(5) NOT NULL |
Category
|
| 34 |
RESOURCE_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Source Type
|
| 35 |
RESOURCE_SUB_CAT |
Character(5) |
VARCHAR2(5) NOT NULL |
Subcategory
|
| 36 |
ANALYSIS_TYPE |
Character(3) |
VARCHAR2(3) NOT NULL |
Analysis Type
|
| 37 |
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.
|
| 38 |
VAT_BASIS_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Basis Amt in Base Curr
|
| 39 |
VAT_CALC_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Calculated Amount
|
| 40 |
VAT_CALC_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Calculated Amount Base Cur
|
| 41 |
VAT_RCVRY_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Recovery Amt
|
| 42 |
VAT_RCVRY_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Recovery Amt in Base Curr
|
| 43 |
VAT_REBATE_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Rebate Amt
|
| 44 |
VAT_REBATE_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Rebate Amt in Base Curr
|
| 45 |
VAT_TRANS_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Represents the sale or purchase amount against which the VAT is applicable.
|
| 46 |
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
|
| 47 |
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.
|
| 48 |
VAT_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Amount in Base Curr
|
| 49 |
VAT_RCLM_AMT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
VAT Reclaim Amount
|
| 50 |
VAT_RCLM_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Base VAT Reclaim Amount
|
| 51 |
TXN_CURRENCY_CD1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency Display
|
| 52 |
CURRENCY_CD1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 53 |
ACCOUNTING_DT |
Date(10) |
DATE |
The accounting entry construction date for a given transaction (a generic field that crosses multiple
|
| 54 |
BUDGET_DT |
Date(10) |
DATE |
Budget Date
Default Value: EX_SHEET_HDR.ACCOUNTING_DT
|
| 55 |
BUDGET_LINE_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
->
SL - 15-AUG-2002
MISC_CRITERIA label has been added
<-
B=Bypass
E=Error
N=Not Chk'd
V=Valid
W=Warning
|
| 56 |
LEDGER_GROUP |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger Group
|
| 57 |
LEDGER |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger
|
| 58 |
KK_PROCESS_PRIOR |
Character(1) |
VARCHAR2(1) NOT NULL |
Process Prior Document
|
| 59 |
KK_CLOSE_PRIOR |
Character(1) |
VARCHAR2(1) NOT NULL |
Close Prior Transaction
|
| 60 |
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.
|
| 61 |
VAT_APORT_CNTRL |
Character(1) |
VARCHAR2(1) NOT NULL |
VAT Apportionment Control
D=Distribution GL Business Unit
G=Transaction GL Business Unit
T=Transaction Business Unit
|
| 62 |
VAT_RCVRY_PCT_SRC |
Character(1) |
VARCHAR2(1) NOT NULL |
VAT Recovery Percent Source
A=Automatically calculated
M=Manual Entry
|
| 63 |
VAT_REBATE_PCT_SRC |
Character(1) |
VARCHAR2(1) NOT NULL |
VAT Rebate Percent Source
A=Automatically calculated
M=Manual Entry
|
| 64 |
VAT_RECOVERY_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Recovery Percent
|
| 65 |
VAT_REBATE_PCT |
Signed Number(7,2) |
DECIMAL(5,2) NOT NULL |
VAT Rebate Percent
|
| 66 |
NET_AMOUNT |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
The resulting net amount related to settlement cash flows subject to counterparty netting agreements.
|
| 67 |
NET_AMOUNT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Net Amount
|