GM_PT_EXP_T_VW

(SQL View)
Index Back

Sponsor Expenditure


SELECT D.OPRID , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.ACTIVITY_ID , A.TRANS_ID , F.BUSINESS_UNIT , F.CONTRACT_NUM , A.BUDGET_PERIOD_GM , A.BUDGET_CATEGORY , A.ACCOUNTING_DT , A.TRANSACTION_DT , A.ACCOUNT , B.DESCR , A.EXPENDED_AMT , A.CURRENCY_CD , A.VENDOR_ID , A.PO_ID , A.VOUCHER_ID , A.JOURNAL_ID , A.DEPTID , %subrec(CF12_AN_SBR, A) , A.RESOURCE_TYPE , A.RESOURCE_CATEGORY , A.RESOURCE_SUB_CAT FROM PS_GM_PT_PRJ_TRN A , PS_GL_ACCOUNT_TBL B , PS_GM_PT_ACC_LVL3 C , PSOPRDEFN D , PS_GM_PT_PROJECT F WHERE A.AMOUNT_TYPE= 'TCS' AND A.AMOUNT_TYPE1 = '1' AND F.BUSINESS_UNIT_PC = A.BUSINESS_UNIT_PC AND F.PROJECT_ID = A.PROJECT_ID AND C.BUSINESS_UNIT_PC = A.BUSINESS_UNIT_PC AND C.PROJECT_ID = A.PROJECT_ID AND D.EMPLID = C.EMPLID AND B.ACCOUNT = A.ACCOUNT AND B.SETID = ( SELECT CTRL.SETID FROM PS_SET_CNTRL_REC CTRL WHERE A.BUSINESS_UNIT_PC = CTRL.SETCNTRLVALUE AND CTRL.RECNAME = 'GL_ACCOUNT_TBL' ) AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_GL_ACCOUNT_TBL B1 WHERE B1.SETID = B.SETID AND B1.ACCOUNT = B.ACCOUNT AND B1.EFFDT <= %CurrentDateIn AND B1.EFF_STATUS = 'A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
4 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
5 TRANS_ID Character(50) VARCHAR2(50) NOT NULL Transaction ID
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
8 BUDGET_PERIOD_GM Number(3,0) SMALLINT NOT NULL BUDGET_PERIOD_GM
9 BUDGET_CATEGORY Character(15) VARCHAR2(15) NOT NULL BUDGET_CATEGORY
10 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
11 TRANSACTION_DT Date(10) DATE Transaction date (often used as trade date)
12 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
14 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
17 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
18 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
19 JOURNAL_ID Character(10) VARCHAR2(10) NOT NULL Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier.
20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
21 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
22 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
23 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
24 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
25 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
26 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
27 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
28 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
29 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
30 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
31 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
32 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
33 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
34 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
35 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory