FAI_ACCTG_VW (SQL View)	Index Back
External Accounting Entries

FAI_ACCTG_VW

(SQL View)

External Accounting Entries

SELECT A.EXT_SYSTEM_ID ,A.BUSINESS_UNIT ,A.LEDGER_GROUP ,A.FAI_TRANSACTION_ID ,A.FAI_LINE_NBR ,A.IU_BAL_LINE_NBR ,A.LEDGER ,A.APPL_JRNL_ID ,A.ACCOUNTING_DT ,A.BUSINESS_UNIT_CHRG ,A.ACCOUNT ,A.ALTACCT ,A.DEPTID ,A.PROJECT_ID ,%subrec(CF12_AN_SBR, A) ,A.CURRENCY_CD ,A.MONETARY_AMOUNT ,A.FOREIGN_AMOUNT ,A.FOREIGN_CURRENCY ,A.RT_TYPE ,A.RATE_MULT ,A.RATE_DIV ,A.MOVEMENT_FLAG ,A.JRNL_LN_REF ,A.LINE_DESCR ,A.IU_ANCHOR_FLG ,A.IU_SYS_TRAN_CD ,A.IU_TRAN_CD ,A.IU_ENTRY_TYPE ,A.IU_TRAN_GRP_NBR ,A.CF_MCHG_GROUP ,A.COMBO_EDIT_TMPL ,A.COMBO_EDIT_STATUS ,A.CF_MCHG_RULE ,A.CF_MANUAL_FLG ,A.LAST_MAINT_DTTM ,A.LAST_MAINT_OPRID ,A.GL_DISTRIB_STATUS ,A.FISCAL_YEAR ,A.ACCOUNTING_PERIOD ,A.JOURNAL_ID ,A.JOURNAL_DATE ,A.JOURNAL_LINE ,A.IN_PROCESS_FLG ,A.PROCESS_INSTANCE ,A.LASTUPDDTTM ,A.LASTUPDOPRID FROM PS_FAI_ACCTG_LINE A , PS_FAI_ACCTG_HDR B WHERE A.EXT_SYSTEM_ID = B.EXT_SYSTEM_ID AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.FAI_TRANSACTION_ID = B.FAI_TRANSACTION_ID AND A.LEDGER_GROUP = B.LEDGER_GROUP AND B.POST_STATUS_FAI = 'P' AND A.GL_DISTRIB_STATUS <> 'D'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EXT_SYSTEM_ID Character(10) VARCHAR2(10) NOT NULL External System ID

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

3 LEDGER_GROUP Character(10) VARCHAR2(10) NOT NULL Ledger Group

4 FAI_TRANSACTION_ID Character(10) VARCHAR2(10) NOT NULL FAI Transaction ID

5 FAI_LINE_NBR Number(9,0) DECIMAL(9) NOT NULL FAI Line Number

6 IU_BAL_LINE_NBR Number(10,0) DECIMAL(10) NOT NULL Inter/Intra Unit Balancing Lin

7 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger

8 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template

9 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

10 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

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

12 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account

13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

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

15 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

16 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

17 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

18 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

19 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

20 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

21 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

22 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

23 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

24 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

25 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

26 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

27 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

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

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

30 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code

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

32 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

33 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

34 MOVEMENT_FLAG Character(1) VARCHAR2(1) NOT NULL A flag associated with a journal line that indicates whether its amount is in the natural sign or the reversal sign.
N=Natural
R=Reversal

35 JRNL_LN_REF Character(10) VARCHAR2(10) NOT NULL Identifies a document, person, invoice, date, or any other piece of information that is associated with a journal line. It can be manually entered, or be defaulted from an accounting entry definition or from a Journal Generator Template. The journal line reference is optional, but helpful if you need to trace back to the source of the transaction.

36 LINE_DESCR Character(30) VARCHAR2(30) NOT NULL Describes the nature of a journal line. It is defaulted from the description of the Account ChartField or manually entered.

37 IU_ANCHOR_FLG Character(1) VARCHAR2(1) NOT NULL Y/N flag used to specify which transaction line is the Inter/IntraUnit anchor flag (the line that contains the anchor Business Unit and ChartFields).

38 IU_SYS_TRAN_CD Character(8) VARCHAR2(8) NOT NULL System Transactions are delivered system data, representing the activities that can require interunit or intraunit accounting entries.

39 IU_TRAN_CD Character(8) VARCHAR2(8) NOT NULL Transaction Codes are user-defined. They are mapped to the delivered System Transactions. InterUnit and IntraUnit Templates are keyed by Transaction code, allowing users to vary their accounting by transaction.

40 IU_ENTRY_TYPE Character(4) VARCHAR2(4) NOT NULL Entry Type
AEAP=IntraEntity Accrued Payable
AECG=IntraEntity Cost of Goods
AECS=IntraEntity Customer Shipments
AEEX=IntraEntity Expense
AEPY=IntraEntity Payable
AERC=IntraEntity Receivable
AERV=IntraEntity Revenue
AETR=IntraEntity In Transit
AUEX=IntraUnit Expense
AUPY=IntraUnit Payable
AURC=IntraUnit Receivable
AURV=IntraUnit Revenue
AUTR=IntraUnit In Transit
IEAP=InterEntity Accrued Payable
IECG=InterEntity Cost of Goods
IECS=InterEntity Customer Shipments
IEEX=InterEntity Expense
IEPY=InterEntity Payable
IERC=InterEntity Receivable
IERV=InterEntity Revenue
IETR=InterEntity In Transit
IUAP=InterUnit Accrued Payable
IUCG=InterUnit Cost of Goods
IUCS=InterUnit Customer Shipments
IUEX=InterUnit Expense
IUPY=InterUnit Payable
IURC=InterUnit Receivable
IURV=InterUnit Revenue
IUTR=InterUnit In Transit

41 IU_TRAN_GRP_NBR Number(4,0) SMALLINT NOT NULL To support multiple InterUnit or IntraUnit groups in a single journal.

42 CF_MCHG_GROUP Character(10) VARCHAR2(10) NOT NULL Chartfield Mass Change Group

43 COMBO_EDIT_TMPL Character(10) VARCHAR2(10) NOT NULL Record Template

44 COMBO_EDIT_STATUS Character(2) VARCHAR2(2) NOT NULL Combination Edit Status
E=Error
I=Ignore
V=Valid

45 CF_MCHG_RULE Character(10) VARCHAR2(10) NOT NULL Chartfield Mass Change Rule

46 CF_MANUAL_FLG Character(1) VARCHAR2(1) NOT NULL Chartfield Manually Changed

47 LAST_MAINT_DTTM DateTime(26) TIMESTAMP A system generated value that reflects the date and time a transaction was last modified

48 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By Operator ID

49 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

50 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year

51 ACCOUNTING_PERIOD Number(3,0) SMALLINT NOT NULL Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.

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

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

54 JOURNAL_LINE Number(9,0) DECIMAL(9) NOT NULL Uniquely identifies a journal line with a sequence number. Within a single journal entry, the line sequence begins with 1 and automatically increments by 1 for each succeeding journal line. There can be any number of lines associated with a journal header.

55 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag

56 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

57 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

58 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EXT_SYSTEM_ID	Character(10)	VARCHAR2(10) NOT NULL	External System ID
2	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
3	LEDGER_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Ledger Group
4	FAI_TRANSACTION_ID	Character(10)	VARCHAR2(10) NOT NULL	FAI Transaction ID
5	FAI_LINE_NBR	Number(9,0)	DECIMAL(9) NOT NULL	FAI Line Number
6	IU_BAL_LINE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	Inter/Intra Unit Balancing Lin
7	LEDGER	Character(10)	VARCHAR2(10) NOT NULL	Ledger
8	APPL_JRNL_ID	Character(10)	VARCHAR2(10) NOT NULL	Journal Template
9	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
10	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
11	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
12	ALTACCT	Character(10)	VARCHAR2(10) NOT NULL	Alternate Account
13	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
14	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField
15	OPERATING_UNIT	Character(8)	VARCHAR2(8) NOT NULL	Operating Unit ChartField
16	PRODUCT	Character(6)	VARCHAR2(6) NOT NULL	Product ChartField
17	FUND_CODE	Character(5)	VARCHAR2(5) NOT NULL	Fund Code
18	CLASS_FLD	Character(5)	VARCHAR2(5) NOT NULL	Class Field
19	PROGRAM_CODE	Character(5)	VARCHAR2(5) NOT NULL	Program Code ChartField
20	BUDGET_REF	Character(8)	VARCHAR2(8) NOT NULL	Budget Reference
21	AFFILIATE	Character(5)	VARCHAR2(5) NOT NULL	Affiliate
22	AFFILIATE_INTRA1	Character(10)	VARCHAR2(10) NOT NULL	IntraUnit Affiliate1
23	AFFILIATE_INTRA2	Character(10)	VARCHAR2(10) NOT NULL	Operating Unit Affiliate
24	CHARTFIELD1	Character(10)	VARCHAR2(10) NOT NULL	Expansion chartfield 1
25	CHARTFIELD2	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 2
26	CHARTFIELD3	Character(10)	VARCHAR2(10) NOT NULL	Expansion Chartfield 3
27	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
28	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.
29	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.
30	FOREIGN_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Foreign Currency Code
31	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.
32	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
33	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor
34	MOVEMENT_FLAG	Character(1)	VARCHAR2(1) NOT NULL	A flag associated with a journal line that indicates whether its amount is in the natural sign or the reversal sign. N=Natural R=Reversal
35	JRNL_LN_REF	Character(10)	VARCHAR2(10) NOT NULL	Identifies a document, person, invoice, date, or any other piece of information that is associated with a journal line. It can be manually entered, or be defaulted from an accounting entry definition or from a Journal Generator Template. The journal line reference is optional, but helpful if you need to trace back to the source of the transaction.
36	LINE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Describes the nature of a journal line. It is defaulted from the description of the Account ChartField or manually entered.
37	IU_ANCHOR_FLG	Character(1)	VARCHAR2(1) NOT NULL	Y/N flag used to specify which transaction line is the Inter/IntraUnit anchor flag (the line that contains the anchor Business Unit and ChartFields).
38	IU_SYS_TRAN_CD	Character(8)	VARCHAR2(8) NOT NULL	System Transactions are delivered system data, representing the activities that can require interunit or intraunit accounting entries.
39	IU_TRAN_CD	Character(8)	VARCHAR2(8) NOT NULL	Transaction Codes are user-defined. They are mapped to the delivered System Transactions. InterUnit and IntraUnit Templates are keyed by Transaction code, allowing users to vary their accounting by transaction.
40	IU_ENTRY_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Entry Type AEAP=IntraEntity Accrued Payable AECG=IntraEntity Cost of Goods AECS=IntraEntity Customer Shipments AEEX=IntraEntity Expense AEPY=IntraEntity Payable AERC=IntraEntity Receivable AERV=IntraEntity Revenue AETR=IntraEntity In Transit AUEX=IntraUnit Expense AUPY=IntraUnit Payable AURC=IntraUnit Receivable AURV=IntraUnit Revenue AUTR=IntraUnit In Transit IEAP=InterEntity Accrued Payable IECG=InterEntity Cost of Goods IECS=InterEntity Customer Shipments IEEX=InterEntity Expense IEPY=InterEntity Payable IERC=InterEntity Receivable IERV=InterEntity Revenue IETR=InterEntity In Transit IUAP=InterUnit Accrued Payable IUCG=InterUnit Cost of Goods IUCS=InterUnit Customer Shipments IUEX=InterUnit Expense IUPY=InterUnit Payable IURC=InterUnit Receivable IURV=InterUnit Revenue IUTR=InterUnit In Transit
41	IU_TRAN_GRP_NBR	Number(4,0)	SMALLINT NOT NULL	To support multiple InterUnit or IntraUnit groups in a single journal.
42	CF_MCHG_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Chartfield Mass Change Group
43	COMBO_EDIT_TMPL	Character(10)	VARCHAR2(10) NOT NULL	Record Template
44	COMBO_EDIT_STATUS	Character(2)	VARCHAR2(2) NOT NULL	Combination Edit Status E=Error I=Ignore V=Valid
45	CF_MCHG_RULE	Character(10)	VARCHAR2(10) NOT NULL	Chartfield Mass Change Rule
46	CF_MANUAL_FLG	Character(1)	VARCHAR2(1) NOT NULL	Chartfield Manually Changed
47	LAST_MAINT_DTTM	DateTime(26)	TIMESTAMP	A system generated value that reflects the date and time a transaction was last modified
48	LAST_MAINT_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Last Maintained By Operator ID
49	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
50	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
51	ACCOUNTING_PERIOD	Number(3,0)	SMALLINT NOT NULL	Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.
52	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.
53	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
54	JOURNAL_LINE	Number(9,0)	DECIMAL(9) NOT NULL	Uniquely identifies a journal line with a sequence number. Within a single journal entry, the line sequence begins with 1 and automatically increments by 1 for each succeeding journal line. There can be any number of lines associated with a journal header.
55	IN_PROCESS_FLG	Character(1)	VARCHAR2(1) NOT NULL	In process flag
56	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
57	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
58	LASTUPDOPRID	Character(30)	VARCHAR2(30) NOT NULL	Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.