FIN0010_VW(SQL View) |
Index Back |
|---|---|
FIN0010_GL_ACCOUNTS |
| SELECT A.SETID , A.ACCOUNT , A.EFFDT ,A.EFF_STATUS , A.DESCR , A.DESCRSHORT , A.ACCOUNT_TYPE , A.STATISTICS_ACCOUNT , A.UNIT_OF_MEASURE , A.OPEN_ITEM , A.OPEN_ITEM_DESCR , A.OPEN_ITEM_EDIT_REC , A.OPEN_ITEM_EDIT_FLD , A.ACCOUNTING_OWNER , A.BALANCE_FWD_SW , A.VAT_ACCOUNT_FLG , H.CF_ATTRIBUTE , H.CF_ATTRIB_VALUE , I.DESCR60 FROM PS_GL_ACCOUNT_TBL A , PS_ACCT_TYPE_TBL B , PS_CF_ATTRIB_TBL H , PS_CF_ATTRIB_VALUE I WHERE B.ACCOUNT_TYPE = A.ACCOUNT_TYPE AND B.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.SETCNTRLVALUE = A.SETID AND D.RECNAME = 'ACCT_TYPE_TBL') AND A.STATISTICS_ACCOUNT = 'Y' AND A.SETID = H.SETID AND H.SETID = I.SETID AND H.FIELDNAME = I.FIELDNAME AND H.CF_ATTRIBUTE = I.CF_ATTRIBUTE AND H.CF_ATTRIB_VALUE = I.CF_ATTRIB_VALUE AND H.FIELDNAME = 'ACCOUNT' AND H.CHARTFIELD_VALUE = A.ACCOUNT UNION SELECT C.SETID , C.ACCOUNT ,C.EFFDT ,C.EFF_STATUS , C.DESCR , C.DESCRSHORT , C.ACCOUNT_TYPE , C.STATISTICS_ACCOUNT , C.UNIT_OF_MEASURE , C.OPEN_ITEM , C.OPEN_ITEM_DESCR , C.OPEN_ITEM_EDIT_REC , C.OPEN_ITEM_EDIT_FLD , C.ACCOUNTING_OWNER , C.BALANCE_FWD_SW , C.VAT_ACCOUNT_FLG , J.CF_ATTRIBUTE , J.CF_ATTRIB_VALUE , K.DESCR60 FROM PS_GL_ACCOUNT_TBL C , PS_CF_ATTRIB_TBL J , PS_CF_ATTRIB_VALUE K WHERE C.ACCOUNT_TYPE = ' ' AND J.SETID = K.SETID AND J.FIELDNAME = K.FIELDNAME AND J.CF_ATTRIBUTE = K.CF_ATTRIBUTE AND J.CF_ATTRIB_VALUE = K.CF_ATTRIB_VALUE AND J.SETID = C.SETID AND K.FIELDNAME = 'ACCOUNT' AND J.CHARTFIELD_VALUE = C.ACCOUNT UNION SELECT E.SETID , E.ACCOUNT , E.EFFDT ,E.EFF_STATUS , E.DESCR , E.DESCRSHORT , E.ACCOUNT_TYPE , E.STATISTICS_ACCOUNT , E.UNIT_OF_MEASURE , E.OPEN_ITEM , E.OPEN_ITEM_DESCR , E.OPEN_ITEM_EDIT_REC , E.OPEN_ITEM_EDIT_FLD , E.ACCOUNTING_OWNER , F.BALANCE_FWD_SW , E.VAT_ACCOUNT_FLG , M.CF_ATTRIBUTE , M.CF_ATTRIB_VALUE , M.DESCR60 FROM PS_GL_ACCOUNT_TBL E , PS_ACCT_TYPE_TBL F , PS_CF_ATTRIB_TBL L , PS_CF_ATTRIB_VALUE M WHERE F.ACCOUNT_TYPE = E.ACCOUNT_TYPE AND F.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.SETCNTRLVALUE = E.SETID AND G.RECNAME = 'ACCT_TYPE_TBL') AND E.STATISTICS_ACCOUNT = 'N' AND L.SETID = M.SETID AND L.FIELDNAME = M.FIELDNAME AND L.CF_ATTRIBUTE = M.CF_ATTRIBUTE AND L.CF_ATTRIB_VALUE = M.CF_ATTRIB_VALUE AND L.SETID = E.SETID AND L.FIELDNAME = 'ACCOUNT' AND L.CHARTFIELD_VALUE = E.ACCOUNT UNION SELECT N.SETID , N.ACCOUNT ,N.EFFDT ,N.EFF_STATUS , N.DESCR , N.DESCRSHORT , N.ACCOUNT_TYPE , N.STATISTICS_ACCOUNT , N.UNIT_OF_MEASURE , N.OPEN_ITEM , N.OPEN_ITEM_DESCR , N.OPEN_ITEM_EDIT_REC , N.OPEN_ITEM_EDIT_FLD , N.ACCOUNTING_OWNER , N.BALANCE_FWD_SW , N.VAT_ACCOUNT_FLG , ' ' , ' ' , ' ' FROM PS_GL_ACCOUNT_TBL N , PS_ACCT_TYPE_TBL O WHERE N.ACCOUNT_TYPE = O.ACCOUNT_TYPE AND O.SETID = ( SELECT P.SETID FROM PS_SET_CNTRL_REC P WHERE P.RECNAME = 'ACCT_TYPE_TBL' AND P.SETCNTRLVALUE = N.SETID) AND N.STATISTICS_ACCOUNT = 'Y' UNION SELECT Q.SETID , Q.ACCOUNT ,Q.EFFDT ,Q.EFF_STATUS , Q.DESCR , Q.DESCRSHORT , Q.ACCOUNT_TYPE , Q.STATISTICS_ACCOUNT , Q.UNIT_OF_MEASURE , Q.OPEN_ITEM , Q.OPEN_ITEM_DESCR , Q.OPEN_ITEM_EDIT_REC , Q.OPEN_ITEM_EDIT_FLD , Q.ACCOUNTING_OWNER , Q.BALANCE_FWD_SW , Q.VAT_ACCOUNT_FLG , ' ' , ' ' , ' ' FROM PS_GL_ACCOUNT_TBL Q WHERE Q.ACCOUNT_TYPE = ' ' UNION SELECT R.SETID , R.ACCOUNT , R.EFFDT ,R.EFF_STATUS , R.DESCR , R.DESCRSHORT , R.ACCOUNT_TYPE , R.STATISTICS_ACCOUNT , R.UNIT_OF_MEASURE , R.OPEN_ITEM , R.OPEN_ITEM_DESCR , R.OPEN_ITEM_EDIT_REC , R.OPEN_ITEM_EDIT_FLD , R.ACCOUNTING_OWNER , S.BALANCE_FWD_SW , R.VAT_ACCOUNT_FLG , ' ' , ' ' , ' ' FROM PS_GL_ACCOUNT_TBL R , PS_ACCT_TYPE_TBL S WHERE S.SETID = ( SELECT T.SETID FROM PS_SET_CNTRL_REC T WHERE T.SETCNTRLVALUE = R.SETID AND T.RECNAME = 'ACCT_TYPE_TBL') AND S.ACCOUNT_TYPE = R.ACCOUNT_TYPE AND R.STATISTICS_ACCOUNT = 'N' UNION SELECT U.SETID , U.ACCOUNT , U.EFFDT ,U.EFF_STATUS , U.DESCR , U.DESCRSHORT , U.ACCOUNT_TYPE , U.STATISTICS_ACCOUNT , U.UNIT_OF_MEASURE , U.OPEN_ITEM , U.OPEN_ITEM_DESCR , U.OPEN_ITEM_EDIT_REC , U.OPEN_ITEM_EDIT_FLD , U.ACCOUNTING_OWNER , U.BALANCE_FWD_SW , U.VAT_ACCOUNT_FLG , ' ' , ' ' , ' ' FROM PS_GL_ACCOUNT_TBL U WHERE U.STATISTICS_ACCOUNT = 'Y' |
| # | 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 |
| 2 | ACCOUNT |
Character(10) | VARCHAR2(10) NOT NULL | Account |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
| 5 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 6 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 7 | 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. |
| 8 | 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. |
| 9 | 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 |
| 10 | 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. |
| 11 | 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. |
| 12 | 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. |
| 13 | 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. |
| 14 | ACCOUNTING_OWNER | Character(30) | VARCHAR2(30) NOT NULL | Accounting Owner |
| 15 | 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 |
| 16 | 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 |
| 17 | CF_ATTRIBUTE | Character(15) | VARCHAR2(15) NOT NULL | ChartField Attribute |
| 18 | CF_ATTRIB_VALUE | Character(20) | VARCHAR2(20) NOT NULL | ChartField Attribute Value |
| 19 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |