GL_SRCH_JRNL_VW (SQL View)	Index Back
SrchRec Journal Hdrs

GL_SRCH_JRNL_VW

(SQL View)

SrchRec Journal Hdrs

SELECT A.BUSINESS_UNIT_IU ,A.JOURNAL_ID ,(%Cast(A.JOURNAL_DATE ,DATE ,CHARACTER)) ,A.UNPOST_SEQ ,A.JOURNAL_DATE ,A.JOURNAL_DATE ,%Substring(%Cast(A.JOURNAL_DATE, Date, Character), 1, 4) ,%Substring(%Cast(A.JOURNAL_DATE, Date, Character), 6, 2) ,%Substring(%Cast(A.JOURNAL_DATE, Date, Character), 6, 2) ,%Substring(%Cast(A.JOURNAL_DATE, Date, Character), 9, 2) ,A.BUSINESS_UNIT ,A.LEDGER_GROUP ,A.SOURCE ,A.SYSTEM_SOURCE ,A.CURRENCY_CD ,A.FISCAL_YEAR ,A.ACCOUNTING_PERIOD ,A.OPRID ,A.OPRID ,A.JRNL_HDR_STATUS ,A.BUDGET_HDR_STATUS ,A.SUSP_RECON_STATUS ,A.DESCR ,A.DESCR ,A.DOC_TYPE ,A.DOC_SEQ_NBR ,A.DOC_SEQ_DATE ,A.DOC_SEQ_STATUS ,A.JRNL_TOTAL_LINES ,A.JRNL_TOTAL_DEBITS ,A.JRNL_NET_UNITS ,A.ATTACHMENT_EXIST ,A.DTTM_STAMP_SEC ,A.BUSINESS_UNIT_IU ,' ' ,A.DESCR254 ,A.JOURNAL_CLASS FROM PS_JRNL_HEADER A WHERE EXISTS ( SELECT 'X' FROM PS_JRNL_HEADER B WHERE B.BUSINESS_UNIT_IU = A.BUSINESS_UNIT_IU AND B.BUSINESS_UNIT = A.BUSINESS_UNIT_IU AND B.JOURNAL_ID = A.JOURNAL_ID AND B.JOURNAL_DATE = A.JOURNAL_DATE AND A.UNPOST_SEQ = B.UNPOST_SEQ)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.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_CHAR Character(10) VARCHAR2(10) NOT NULL Journal Date

4 UNPOST_SEQ Number(2,0) SMALLINT NOT NULL Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".

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

6 SES_DOCUMENT_DATE Date(10) DATE Document Date

7 SES_YEAR Character(4) VARCHAR2(4) NOT NULL Document Date Hierarchy

8 SES_MONTH Character(2) VARCHAR2(2) NOT NULL Month
01=01: January
02=02: February
03=03: March
04=04: April
05=05: May
06=06: June
07=07: July
08=08: August
09=09: September
10=10: October
11=11: November
12=12: December

9 SES_MONTH2 Character(2) VARCHAR2(2) NOT NULL Month
01=01: January
02=02: February
03=03: March
04=04: April
05=05: May
06=06: June
07=07: July
08=08: August
09=09: September
10=10: October
11=11: November
12=12: December

10 SES_DAY Character(2) VARCHAR2(2) NOT NULL Day

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

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

13 SOURCE Character(3) VARCHAR2(3) NOT NULL Identifies the origin of a journal entry and defines journal entry error handling options. The journal source provides a means of selectively tracking, reporting, and inquiring on journal entries. It can be almost anything within the enterprise - a subsystem that generates transactions, a department, or even an individual.
Default Value: OPR_DEF_TBL_FS.SOURCE

14 SYSTEM_SOURCE Character(3) VARCHAR2(3) NOT NULL Identifies the application or source system that generated a journal entry. Release 8.80 - 11/14/2002 - RVlasic - Removed GDM (JrnlGen - Deduction Management) from Translate Values. SUJ---Included a label as Product. Release 8.9 - 05/11/2005 - RVlasic - Added PRV (Variance Pricing) and PKK (KK Budget Journal) and deleted GLK for Project Costing per Brian Cohen.

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

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

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

18 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

19 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes

20 JRNL_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates the processing status of a journal entry.
D=Deleted
E=Journal Has Errors
I=Posting Incomplete
M=Valid SJE Model -- Do Not Post
N=No Status - Needs to be Edited
P=Posted to Ledger(s)
T=Journal Entry Incomplete
U=Unposted
V=Valid Journal - Edits Complete
X=Cancelled
Z=Upgrade Journal - Can't Unpost
Default Value: N

21 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

22 SUSP_RECON_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not there are suspense transactions in a journal, and if so, whether or not they have been corrected and posted.
0=No Suspense Transactions
1=Suspense Transactions
2=Correction Journal Created
3=Correction Journal Posted
Default Value: 0

23 DESCR Character(30) VARCHAR2(30) NOT NULL Description

24 JRNL_DESCR Character(30) VARCHAR2(30) NOT NULL Journal Description

25 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.

26 DOC_SEQ_NBR Character(12) VARCHAR2(12) NOT NULL Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.

27 DOC_SEQ_DATE Date(10) DATE Specifies the date that a document sequence number is assigned to a document or the date the document was created.

28 DOC_SEQ_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates a document sequencing error. If there is no error the field is blank and document is considered valid for posting.
1=Document Sequencing Disabled
A=GL BU Undefined
B=GL BU Blank
C=Accounting Date blank
D=Doc Type Blank
E=Jrnl Type, Code, Doc Undefine
F=Sequence Range Undefine
G=Max. Sequence Number Reached
H=Manual Nbr for Auto Doc Type
I=No Manual Number Entered
J=Duplicate Manual Number
K=Invalid Ledger
L=Invalid Ledger Group
N=No Status

29 JRNL_TOTAL_LINES Number(9,0) DECIMAL(9) NOT NULL Represents a running total of the number of lines in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered.

30 JRNL_TOTAL_DEBITS Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents a running total of the debit amounts in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered.

31 JRNL_NET_UNITS Signed Number(27,2) DECIMAL(25,2) NOT NULL Represents a running total of the statistical units in a journal entry. It is a hash total since there may be different types of units of measure reflected in a single journal. It is maintained by PeopleSoft General Ledger as lines are entered.

32 ATTACHMENT_EXIST Character(1) VARCHAR2(1) NOT NULL Attachments Exist
N=N
Y=Y

33 SYNCDTTM DateTime(26) TIMESTAMP Synchronization Last Update Date Time

34 DESCR120 Character(120) VARCHAR2(120) NOT NULL Description

35 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

36 COMMENTS_2000 Long Character CLOB Comment Text

37 JOURNAL_CLASS Character(10) VARCHAR2(10) NOT NULL Journal Class

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_FS.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_CHAR	Character(10)	VARCHAR2(10) NOT NULL	Journal Date
4	UNPOST_SEQ	Number(2,0)	SMALLINT NOT NULL	Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".
5	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
6	SES_DOCUMENT_DATE	Date(10)	DATE	Document Date
7	SES_YEAR	Character(4)	VARCHAR2(4) NOT NULL	Document Date Hierarchy
8	SES_MONTH	Character(2)	VARCHAR2(2) NOT NULL	Month 01=01: January 02=02: February 03=03: March 04=04: April 05=05: May 06=06: June 07=07: July 08=08: August 09=09: September 10=10: October 11=11: November 12=12: December
9	SES_MONTH2	Character(2)	VARCHAR2(2) NOT NULL	Month 01=01: January 02=02: February 03=03: March 04=04: April 05=05: May 06=06: June 07=07: July 08=08: August 09=09: September 10=10: October 11=11: November 12=12: December
10	SES_DAY	Character(2)	VARCHAR2(2) NOT NULL	Day
11	BUSINESS_UNIT_LN	Character(5)	VARCHAR2(5) NOT NULL	Line Business Unit
12	LEDGER_GROUP	Character(10)	VARCHAR2(10) NOT NULL	Ledger Group
13	SOURCE	Character(3)	VARCHAR2(3) NOT NULL	Identifies the origin of a journal entry and defines journal entry error handling options. The journal source provides a means of selectively tracking, reporting, and inquiring on journal entries. It can be almost anything within the enterprise - a subsystem that generates transactions, a department, or even an individual. Default Value: OPR_DEF_TBL_FS.SOURCE
14	SYSTEM_SOURCE	Character(3)	VARCHAR2(3) NOT NULL	Identifies the application or source system that generated a journal entry. Release 8.80 - 11/14/2002 - RVlasic - Removed GDM (JrnlGen - Deduction Management) from Translate Values. SUJ---Included a label as Product. Release 8.9 - 05/11/2005 - RVlasic - Added PRV (Variance Pricing) and PKK (KK Budget Journal) and deleted GLK for Project Costing per Brian Cohen.
15	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
16	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
17	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.
18	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
19	OPRID_ENTERED_BY	Character(30)	VARCHAR2(30) NOT NULL	Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
20	JRNL_HDR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates the processing status of a journal entry. D=Deleted E=Journal Has Errors I=Posting Incomplete M=Valid SJE Model -- Do Not Post N=No Status - Needs to be Edited P=Posted to Ledger(s) T=Journal Entry Incomplete U=Unposted V=Valid Journal - Edits Complete X=Cancelled Z=Upgrade Journal - Can't Unpost Default Value: N
21	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
22	SUSP_RECON_STATUS	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not there are suspense transactions in a journal, and if so, whether or not they have been corrected and posted. 0=No Suspense Transactions 1=Suspense Transactions 2=Correction Journal Created 3=Correction Journal Posted Default Value: 0
23	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
24	JRNL_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Journal Description
25	DOC_TYPE	Character(8)	VARCHAR2(8) NOT NULL	Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
26	DOC_SEQ_NBR	Character(12)	VARCHAR2(12) NOT NULL	Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
27	DOC_SEQ_DATE	Date(10)	DATE	Specifies the date that a document sequence number is assigned to a document or the date the document was created.
28	DOC_SEQ_STATUS	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates a document sequencing error. If there is no error the field is blank and document is considered valid for posting. 1=Document Sequencing Disabled A=GL BU Undefined B=GL BU Blank C=Accounting Date blank D=Doc Type Blank E=Jrnl Type, Code, Doc Undefine F=Sequence Range Undefine G=Max. Sequence Number Reached H=Manual Nbr for Auto Doc Type I=No Manual Number Entered J=Duplicate Manual Number K=Invalid Ledger L=Invalid Ledger Group N=No Status
29	JRNL_TOTAL_LINES	Number(9,0)	DECIMAL(9) NOT NULL	Represents a running total of the number of lines in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered.
30	JRNL_TOTAL_DEBITS	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents a running total of the debit amounts in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered.
31	JRNL_NET_UNITS	Signed Number(27,2)	DECIMAL(25,2) NOT NULL	Represents a running total of the statistical units in a journal entry. It is a hash total since there may be different types of units of measure reflected in a single journal. It is maintained by PeopleSoft General Ledger as lines are entered.
32	ATTACHMENT_EXIST	Character(1)	VARCHAR2(1) NOT NULL	Attachments Exist N=N Y=Y
33	SYNCDTTM	DateTime(26)	TIMESTAMP	Synchronization Last Update Date Time
34	DESCR120	Character(120)	VARCHAR2(120) NOT NULL	Description
35	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
36	COMMENTS_2000	Long Character	CLOB	Comment Text
37	JOURNAL_CLASS	Character(10)	VARCHAR2(10) NOT NULL	Journal Class