JRNL_AF_ACCT_VW (SQL View)	Index Back
Journal Account Fields For AF This View contains GL actuals/standard budget journal account-related fields by selecting data from JRNL_HEADER, JRNL_LN and GL_ACCOUNT_TBL tables. User can pick up the fields in this view to set up the rules on account-related fields to be used by GL Journal Approval process. The key-structure matches the search-key-structure of Journal Entry Component JOURNAL_ENTRY_IE.

JRNL_AF_ACCT_VW

(SQL View)

Journal Account Fields For AF

This View contains GL actuals/standard budget journal account-related fields by selecting data from JRNL_HEADER, JRNL_LN and GL_ACCOUNT_TBL tables. User can pick up the fields in this view to set up the rules on account-related fields to be used by GL Journal Approval process. The key-structure matches the search-key-structure of Journal Entry Component JOURNAL_ENTRY_IE.

SELECT DISTINCT H.BUSINESS_UNIT_IU , H.JOURNAL_ID , H.JOURNAL_DATE , H.BUSINESS_UNIT , A.ACCOUNT_TYPE , A.OPEN_ITEM , A.STATISTICS_ACCOUNT , A.BAL_SHEET_IND FROM PS_JRNL_HEADER H , PS_JRNL_LN L , PS_GL_ACCOUNT_TBL A , PS_SET_CNTRL_REC S WHERE H.JRNL_HDR_STATUS='V' AND H.BUDGET_HDR_STATUS='V' AND H.JRNL_PROCESS_REQST NOT IN ('P','W') AND EXISTS ( SELECT 'X' FROM PS_JRNL_HEADER H1 WHERE H1.BUSINESS_UNIT_IU=H.BUSINESS_UNIT_IU AND H1.BUSINESS_UNIT=H.BUSINESS_UNIT_IU AND H1.JOURNAL_ID=H.JOURNAL_ID AND H1.JOURNAL_DATE=H.JOURNAL_DATE AND H1.UNPOST_SEQ=H.UNPOST_SEQ) AND H.BUSINESS_UNIT=L.BUSINESS_UNIT AND H.JOURNAL_ID=L.JOURNAL_ID AND H.JOURNAL_DATE=L.JOURNAL_DATE AND H.UNPOST_SEQ=L.UNPOST_SEQ AND S.SETCNTRLVALUE=L.BUSINESS_UNIT AND S.RECNAME='GL_ACCOUNT_TBL' AND S.SETID=A.SETID AND A.ACCOUNT=L.ACCOUNT AND A.EFFDT=( SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL A1 WHERE A1.SETID=A.SETID AND A1.ACCOUNT=A.ACCOUNT AND A1.EFFDT<=L.JOURNAL_DATE) AND A.EFF_STATUS='A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 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.

3 JOURNAL_DATE Date(10) DATE Specifies the date the journal was created.

4 BUSINESS_UNIT_LN Character(5) VARCHAR2(5) NOT NULL Line Business Unit

5 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.

6 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.

7 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.

8 BAL_SHEET_IND Character(2) VARCHAR2(2) NOT NULL Balance Sheet Indicator
BS=Balance Sheet
OB=Off Balance Sheet

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	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.
3	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
4	BUSINESS_UNIT_LN	Character(5)	VARCHAR2(5) NOT NULL	Line Business Unit
5	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.
6	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.
7	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.
8	BAL_SHEET_IND	Character(2)	VARCHAR2(2) NOT NULL	Balance Sheet Indicator BS=Balance Sheet OB=Off Balance Sheet