|
SELECT D.BUSINESS_UNIT_IU , D.LEDGER_GROUP , D.FISCAL_YEAR , D.ACCOUNTING_PERIOD , D.JRNL_HDR_STATUS , D.BUDGET_HDR_STATUS , D.BUSINESS_UNIT , D.JRNL_CREATE_DTTM , D.OPRID , D.CURRENCY_CD , D.JRNL_TOTAL_DEBITS , D.JRNL_TOT_CREDITS , D.LEDGER , D.ADB_DATE , D.REVERSAL_CD , D.REVERSAL_ADJ_PER , D.REVERSAL_CD_ADB , D.REVERSAL_DATE_ADB , D.UNPOST_JRNL_DATE , D.DOC_SEQ_NBR , D.SJE_TYPE , D.DATE_CODE_JRNL , D.JRNL_TOTAL_LINES , D.JOURNAL_DATE , D.SOURCE , D.JOURNAL_ID , D.DESCR , D.JRNL_PROCESS_REQST , D.ADJUSTING_ENTRY , D.SUSP_RECON_STATUS , D.ATTACHMENT_EXIST , D.DOC_TYPE FROM PS_SOURCE_TBL A , PS_BU_LED_GRP_TBL B , PS_BUS_UNIT_TBL_GL C , PS_JRNL_HEADER D , PS_SET_CNTRL_REC S ,PS_INSTALLATION_GL G WHERE A.SETID=S.SETID AND S.SETCNTRLVALUE = D.BUSINESS_UNIT AND S.RECNAME = 'SOURCE_TBL' AND A.SOURCE=D.SOURCE AND A.EFFDT=( SELECT MAX(EFFDT) FROM PS_SOURCE_TBL WHERE SETID=A.SETID AND SOURCE=A.SOURCE AND EFFDT<=D.JOURNAL_DATE) AND A.EFF_STATUS='A' AND B.BUSINESS_UNIT=D.BUSINESS_UNIT AND B.LEDGER_GROUP=D.LEDGER_GROUP AND C.BUSINESS_UNIT=D.BUSINESS_UNIT AND (A.JRNL_APPRVL_OPTN = 'S' OR (A.JRNL_APPRVL_OPTN = 'D' AND B.JRNL_APPRVL_OPTN = 'S') OR(A.JRNL_APPRVL_OPTN = 'D' AND B.JRNL_APPRVL_OPTN = 'D' AND C.JRNL_APPRVL_OPTN = 'S')) AND ((G.APPR_IU_OPT = 'Y' AND D.BUSINESS_UNIT_IU = D.BUSINESS_UNIT ) OR G.APPR_IU_OPT = 'N')
|
| # |
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
Prompt Table:
SP_BU_GL_NONVW
|
| 2 |
LEDGER_GROUP |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger Group
Prompt Table:
SP_BULGRP_NONVW
|
| 3 |
FISCAL_YEAR |
Number(4,0) |
SMALLINT NOT NULL |
Fiscal Year
Prompt Table:
CAL_FY_VW
|
| 4 |
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.
Prompt Table:
CAL_AP_VW
|
| 5 |
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
|
| 6 |
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
|
| 7 |
BUSINESS_UNIT_LN |
Character(5) |
VARCHAR2(5) NOT NULL |
Line Business Unit
Prompt Table:
SP_BU_GL_NONVW
|
| 8 |
JRNL_CREATE_DTTM |
DateTime(26) |
TIMESTAMP |
Journal Creation Date Time
|
| 9 |
OPRID |
Character(30) |
VARCHAR2(30) NOT NULL |
A user's ID (see PSOPRDEFN).
Prompt Table:
OPRID_VW
|
| 10 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table:
CURRENCY_CD_TBL
|
| 11 |
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.
|
| 12 |
JRNL_TOT_CREDITS |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Represents a running total of the credit amounts in a journal entry. It is maintained by PeopleSoft General Ledger as lines are entered.
|
| 13 |
LEDGER |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger
|
| 14 |
ADB_DATE |
Date(10) |
DATE |
Specifies the daily date to use when Average Daily Balancing is activated. The date is determined by the daily calendar associated with the General Ledger Business Unit to which the journal is posted. When ADB processing is not activated, the ADB_Date defaults to the date on the Journal Header (JOURNAL_DATE).
|
| 15 |
REVERSAL_CD |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates if a journal entry should be automatically reversed, and if so, with what date. When associated with a reversal entry, it indicates that the entry was created automatically by the reversal process.
B=Beginning of Next Period
D=On Date Specified By User
E=End of Next Period
N=Do Not Generate Reversal
R=Created by Reversal Process
U=Adjustment Period
X=Next Day
|
| 16 |
REVERSAL_ADJ_PER |
Number(3,0) |
SMALLINT NOT NULL |
Adjustment Period
|
| 17 |
REVERSAL_CD_ADB |
Character(1) |
VARCHAR2(1) NOT NULL |
ADB Reversal Code
D=On Date Specified By User
S=Same As Journal Reversal
|
| 18 |
REVERSAL_DATE_ADB |
Date(10) |
DATE |
ADB Reversal Date
|
| 19 |
UNPOST_JRNL_DATE |
Date(10) |
DATE |
Unpost Date
|
| 20 |
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.
|
| 21 |
SJE_TYPE |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates the type of Standard Journal Entry (SJE), which can be either Template or Recurring. If the type is Recurring you must also specify a schedule (SCHEDULE).
M=Model -- Do Not Post
P=Spread Daily Proration
R=Recurring on a Schedule
S=Spread Amounts from SJE Model
T=Template to be Completed
|
| 22 |
DATE_CODE_JRNL |
Character(1) |
VARCHAR2(1) NOT NULL |
This field defines codes for Date field used in Journal lines record.
The Different date fields can be used as the basis for alternate Posting to satisfy Trade Date/Settlement date requirements.
|
| 23 |
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.
|
| 24 |
JOURNAL_DATE |
Date(10) |
DATE |
Specifies the date the journal was created.
|
| 25 |
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
Prompt Table:
SOURCE_TBL
|
| 26 |
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.
|
| 27 |
DESCR |
Character(30) |
VARCHAR2(30) NOT NULL |
Description
|
| 28 |
JRNL_PROCESS_REQST |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that determines when a journal is ready for posting. Some examples of the request flag are Approved to Post, Denied, and Pending Approval.
D=Default
E=Denied
G=Pending Approval
N=None
P=Approved to Post
Q=Pending for Unpost Approval
R=Replace
S=Security Denied
T=Terminated
U=Pending Request to Unpost
W=Approved to Post
X=Delete
|
| 29 |
ADJUSTING_ENTRY |
Character(1) |
VARCHAR2(1) NOT NULL |
Adjusting Entry
C=Fiscal Year Close
N=Non-Adjusting Entry
Y=Adjusting Entry
|
| 30 |
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
|
| 31 |
ATTACHMENT_EXIST |
Character(1) |
VARCHAR2(1) NOT NULL |
Attachments Exist
N=N
Y=Y
|
| 32 |
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.
|