ARRE_BI_REV_VW

(SQL View)
 Index Back

Billing Account Entry table

View to retrieve the revenue lines from Billing

SELECT B.BUSINESS_UNIT , B.INVOICE , C.INVOICE_LINE , B.LINE_SEQ_NUM , B.ACCOUNTING_DT , B.ACCT_ENTRY_TYPE , B.DISC_SUR_LVL , B.DISC_SUR_ID , B.LINE_DST_SEQ_NUM , B.TAX_AUTHORITY_CD , B.PROCESS_INSTANCE , A.AR_LVL , A.BILLING_FREQUENCY , A.TEMPLATE_INVOICE , B.BUDGET_HDR_STATUS , A.BILL_TO_CUST_ID , B.LEDGER_GROUP , B.LEDGER , B.BUSINESS_UNIT_GL , B.ACCOUNT , B.ALTACCT , B.DEPTID , %subrec(CF12_AN_SBR, B) , %subrec(PC_CF1_N_SBR, B) , B.STATISTICS_CODE , B.MONETARY_AMOUNT , B.STATISTIC_AMOUNT , B.GL_DISTRIB_STATUS , B.CURRENCY_CD , B.FOREIGN_CURRENCY , B.FOREIGN_AMOUNT , B.RT_TYPE , B.RATE_MULT , B.RATE_DIV , B.BUDGET_DT FROM PS_BI_HDR A , PS_BI_ACCT_ENTRY B , PS_BI_LINE C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.INVOICE = B.INVOICE AND A.GL_LVL = 'B' AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.INVOICE = C.INVOICE AND B.LINE_SEQ_NUM = C.LINE_SEQ_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
3 INVOICE_LINE Number(5,0) INTEGER NOT NULL Invoice Line
4 LINE_SEQ_NUM Number(5,0) INTEGER NOT NULL Sequence
5 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
6 ACCT_ENTRY_TYPE Character(3) VARCHAR2(3) NOT NULL Account Entry Type
7 DISC_SUR_LVL Number(2,0) SMALLINT NOT NULL Discount/Surcharge Level
8 DISC_SUR_ID Character(15) VARCHAR2(15) NOT NULL Identifier
9 LINE_DST_SEQ_NUM Number(3,0) SMALLINT NOT NULL Revenue Sequence Number
10 TAX_AUTHORITY_CD Character(3) VARCHAR2(3) NOT NULL Tax Authority
11 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
12 AR_LVL Character(1) VARCHAR2(1) NOT NULL AR Level
H=Header is AR Open Item
L=Bill Line is AR Open Item
N=No AR Open Items
13 BILLING_FREQUENCY Character(3) VARCHAR2(3) NOT NULL Billing Frequency
INS=Installment
ONC=Once
REC=Recurring
14 TEMPLATE_INVOICE Character(22) VARCHAR2(22) NOT NULL Template Invoice ID
15 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
16 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
17 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group
18 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger
19 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
20 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
21 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
22 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
23 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
24 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
25 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
26 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
27 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
28 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
29 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
30 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
31 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
32 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
33 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
34 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
35 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit

Prompt Table: %EDIT_BU_PC

36 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: %EDIT_PROJECT

37 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: %EDIT_ACTIVITY

38 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC

39 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: %EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC

40 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: %EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC

41 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

Prompt Table: %EDIT_ANALYSIS
Set Control Field: BUSINESS_UNIT_PC

42 STATISTICS_CODE Character(3) VARCHAR2(3) NOT NULL Statistics Code
43 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.
44 STATISTIC_AMOUNT Signed Number(17,2) DECIMAL(15,2) NOT NULL Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.
45 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
46 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
47 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code
48 FOREIGN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item.
49 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.
50 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
51 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
52 BUDGET_DT Date(10) DATE Budget Date