OI_ACCOUNT_VW

(SQL View)
 Index Back

Accounts

Primary record for Account ChartField

SELECT GLA.SETID , GLA.ACCOUNT , GLA.EFFDT , EDR.INEFFDT , GLA.EFF_STATUS , GLA.DESCR , GLA.DESCRSHORT , GLA.BUDG_OVERRIDE_ACCT , GLA.ACCOUNTING_OWNER , GLA.AB_ACCOUNT_SW , GLA.GL_ACCOUNT_SW , GLA.PF_ACCOUNT_SW , GLA.ACCOUNT_TYPE , GLA.UNIT_OF_MEASURE , UT.DESCR UOM_DESCR , UT.DESCRSHORT UOM_DESCRSHORT , GLA.OPEN_ITEM , GLA.OPEN_ITEM_DESCR , GLA.OPEN_ITEM_EDIT_REC , GLA.OPEN_ITEM_EDIT_FLD , GLA.OPEN_ITEM_PROMPT , GLA.OPEN_ITEM_TOL_AMT , GLA.CURRENCY_CD , GLA.OI_RECON_BASE , GLA.STATISTICS_ACCOUNT , GLA.BALANCE_FWD_SW , GLA.CONTROL_FLAG , GLA.BOOK_CODE , BC.DESCR BOOK_DESCR , BC.DESCRSHORT BOOK_DESCRSHORT , GLA.BOOK_CODE_OVERRIDE , GLA.BAL_SHEET_IND , GLA.VAT_ACCOUNT_FLG , GLA.PHYSICAL_NATURE , GLA.BUDGETARY_ONLY , GLA.KK_OVERRIDE_ACCT FROM PS_GL_ACCOUNT_TBL GLA INNER JOIN PS_GL_ACCOUNT_EDR EDR ON EDR.SETID =GLA.SETID AND EDR.ACCOUNT =GLA.ACCOUNT AND EDR.EFFDT=GLA.EFFDT LEFT OUTER JOIN PS_BOOK_CODE_TBL BC ON BC.BOOK_CODE=GLA.BOOK_CODE AND GLA.SETID =BC.SETID LEFT OUTER JOIN PS_UNITS_TBL UT ON UT.UNIT_OF_MEASURE=GLA.UNIT_OF_MEASURE WHERE (BC.EFFDT = ( SELECT MAX(BC_ED.EFFDT) FROM PS_BOOK_CODE_TBL BC_ED WHERE BC_ED.SETID =GLA.SETID AND BC_ED.BOOK_CODE=BC.BOOK_CODE AND BC_ED.EFFDT <= GLA.EFFDT ) OR BC.BOOK_CODE IS NULL) UNION SELECT DISTINCT SETID , ' ' , %DateIn('1900-01-01') , %DateIn('9999-12-31') , 'A' , 'Not Available' , 'NA' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , 0 , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' FROM PS_SETID_TBL UNION SELECT ' ' , ' ' , %DateIn('1900-01-01') , %DateIn('9999-12-31') , 'A' , 'Not Available' , 'NA' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , 0 , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' , '-' FROM PS_INSTALLATION

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

Prompt Table: SP_SETID_NONVW

2 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

Prompt Table: GL_ACCOUNT_TBL

3 EFF_ST_DT Date(10) DATE Effective Start Date
4 EFF_END_DT Date(10) DATE Effective End Date
5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
8 BUDG_OVERRIDE_ACCT Character(1) VARCHAR2(1) NOT NULL Budget Override Account

Y/N Table Edit

Default Value: N

9 ACCOUNTING_OWNER Character(30) VARCHAR2(30) NOT NULL Accounting Owner
10 AB_ACCOUNT_SW Character(1) VARCHAR2(1) NOT NULL ABM Account

Y/N Table Edit

Default Value: N

11 GL_ACCOUNT_SW Character(1) VARCHAR2(1) NOT NULL General Ledger Account

Y/N Table Edit

Default Value: Y

12 PF_ACCOUNT_SW Character(1) VARCHAR2(1) NOT NULL Performance Measurement

Y/N Table Edit

Default Value: N

13 ACCOUNT_TYPE Character(1) VARCHAR2(1) NOT NULL Identifies a category of accounts that appears on the balance sheet or income statement of the enterprise. Each general ledger account (ACCOUNT) is associated with an account type (also called a ""monetary account type""). PeopleSoft delivers standard account types such as asset, liability, expense, and revenue.

Prompt Table: ACCT_TYPE_TBL

14 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: UNITS_TBL

15 UOM_DESCR Character(30) VARCHAR2(30) NOT NULL Unit of measure description
16 UOM_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Unit of measure description
17 OPEN_ITEM Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) to activate processing for tracking debits and credits that post to a general ledger account. Accounts that are not OpenItem track only net posted amounts.

Y/N Table Edit

Default Value: N

18 OPEN_ITEM_DESCR Character(10) VARCHAR2(10) NOT NULL Describes the OpenItem key that must be specified in the reference field on journal entry panels when an open item account number is used. It appears on prompt lists, reports, and online inquiries related to an open item account in the general ledger.
19 OPEN_ITEM_EDIT_REC Character(15) VARCHAR2(15) NOT NULL Specifies the name of the prompt table you want to use to validate the OpenItem search key, and applies only to OpenItem accounts in the general ledger.

Prompt Table: RECDEFN_VW

20 OPEN_ITEM_EDIT_FLD Character(18) VARCHAR2(18) NOT NULL Specifies the name of the field in the OpenItem Edit Record (OPEN_ITEM_EDIT_REC) to use for validating the OpenItem search key, and applies only to OpenItem accounts in the general ledger.

Prompt Table: RECFLD_ACCT_VW

21 OPEN_ITEM_PROMPT Character(15) VARCHAR2(15) NOT NULL Open Item Prompt Table

Prompt Table: RECDEFN_OI_VW

22 OPEN_ITEM_TOL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the limit amount that determines when OpenItem entries can be closed. You can manually close (delete) OpenItem rows if the balance of the OpenItem account is less than or equal to the specified tolerance.
23 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Reconcile Currency

Prompt Table: CURRENCY_CD_TBL

24 OI_RECON_BASE Character(1) VARCHAR2(1) NOT NULL Specifies that open items should get closed only when the sum total for base amount is zero.

Y/N Table Edit

Default Value: N

25 STATISTICS_ACCOUNT Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a general ledger account is used to track statistical amounts rather than monetary amounts. Statistical amounts represent information such as square footage, head count, or number of units. A unit of measure (UNIT_OF_MEASURE) must be specified with a statistical account.

Y/N Table Edit

Default Value: N

26 BALANCE_FWD_SW Character(1) VARCHAR2(1) NOT NULL A flag that indicates (Y or N) whether or not the YTD balance for an account type or for a statistical account will be rolled forward to Period 0 of the next year.
N=Not Carry Forward
O=Other
Y=Carry Forward

Y/N Table Edit

Default Value: Y

27 CONTROL_FLAG Character(1) VARCHAR2(1) NOT NULL This flag in used to desginate an Account or Alternate Account as Control Accounts and Control Alternate Accounts, respectively.

Y/N Table Edit

Default Value: N

28 BOOK_CODE Character(4) VARCHAR2(4) NOT NULL Book Code

Prompt Table: BOOK_CODE_TBL

29 BOOK_DESCR Character(30) VARCHAR2(30) NOT NULL Book Description
30 BOOK_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Book Description
31 BOOK_CODE_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Allow specifying of the Book Code value that is different from the default value as defined in the GL Account table.

Y/N Table Edit

Default Value: Y

32 BAL_SHEET_IND Character(2) VARCHAR2(2) NOT NULL Balance Sheet Indicator
BS=Balance Sheet
OB=Off Balance Sheet
33 VAT_ACCOUNT_FLG Character(1) VARCHAR2(1) NOT NULL A flag that indicates the relationship of a general ledger account to VAT processing. An account can be non-VAT, VAT or VAT Applicable.
A=VAT Applicable Account
N=Non-VAT Related
V=VAT Account

Default Value: N

34 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
G=Goods
S=Services
35 BUDGETARY_ONLY Character(1) VARCHAR2(1) NOT NULL Budgetary Only

Y/N Table Edit

Default Value: N

36 KK_OVERRIDE_ACCT Character(1) VARCHAR2(1) NOT NULL Override Commitment Control Budget Editting.

Y/N Table Edit

Default Value: N