|
SELECT HDR.BUSINESS_UNIT_IU , HDR.JOURNAL_ID , HDR.BUSINESS_UNIT , HDR.JOURNAL_DATE , HDR.UNPOST_SEQ , HDR.LEDGER_GROUP , HDR.FISCAL_YEAR , HDR.ACCOUNTING_PERIOD , HDR.JRNL_HDR_STATUS , HDR.BUDGET_HDR_STATUS , HDR.JRNL_CREATE_DTTM , HDR.OPRID , HDR.CURRENCY_CD , HDR.JRNL_TOTAL_DEBITS , HDR.JRNL_TOT_CREDITS , HDR.LEDGER , HDR.ADB_DATE , HDR.REVERSAL_CD , HDR.REVERSAL_ADJ_PER , HDR.REVERSAL_CD_ADB , HDR.REVERSAL_DATE_ADB , CASE WHEN HDR.JRNL_PROCESS_REQST = 'Q' THEN UPDT.UNPOST_JRNL_DATE WHEN HDR.JRNL_PROCESS_REQST = 'W' THEN UPDT.UNPOST_JRNL_DATE WHEN HDR.JRNL_PROCESS_REQST = 'E' THEN UPDT.UNPOST_JRNL_DATE ELSE HDR.UNPOST_JRNL_DATE END , HDR.DOC_SEQ_NBR , HDR.SJE_TYPE , HDR.DATE_CODE_JRNL , HDR.JRNL_TOTAL_LINES , HDR.SOURCE ,HDR.SYSTEM_SOURCE , HDR.DESCR , HDR.JRNL_PROCESS_REQST , HDR.JRNL_PROCESS_REQST ,HDR.JRNL_PROCESS_REQST , HDR.ADJUSTING_ENTRY , HDR.SUSP_RECON_STATUS , HDR.ATTACHMENT_EXIST ,HDR.JOURNAL_CLASS , HDR.DOC_TYPE ,'N' , CASE WHEN ((A.JRNL_APPRVL_OPTN = 'S' AND A.JRNL_APP_UP_OPTN ='Y') OR (A.JRNL_APPRVL_OPTN = 'D' AND B.JRNL_APPRVL_OPTN = 'S' AND B.JRNL_APP_UP_OPTN ='Y') OR(A.JRNL_APPRVL_OPTN = 'D' AND B.JRNL_APPRVL_OPTN = 'D' AND C.JRNL_APPRVL_OPTN = 'S' AND C.JRNL_APP_UP_OPTN ='Y')) THEN 'Y' ELSE 'N' END ,G.APPR_IU_OPT , UPDT.COMMENTS FROM PS_SOURCE_TBL A , PS_BU_LED_GRP_TBL B , PS_BUS_UNIT_TBL_GL C , PS_JRNL_HEADER HDR LEFT OUTER JOIN PS_GL_WC_UNPOST_DT UPDT ON UPDT.BUSINESS_UNIT = HDR.BUSINESS_UNIT_IU AND UPDT.JOURNAL_ID=HDR.JOURNAL_ID AND UPDT.JOURNAL_DATE=HDR.JOURNAL_DATE AND UPDT.UNPOST_SEQ= HDR.UNPOST_SEQ AND UPDT.BUSINESS_UNIT_LN = HDR.BUSINESS_UNIT , PS_SET_CNTRL_REC S ,PS_INSTALLATION_GL G WHERE (HDR.JRNL_HDR_STATUS = 'P' AND HDR.UNPOST_SEQ = 0) AND HDR.KK_AMOUNT_TYPE IN ('1','7') AND HDR.SYSTEM_SOURCE <> 'AMR' AND HDR.JRNL_PROCESS_REQST IN ('N','Q','W','U','E') AND A.SETID=S.SETID AND S.SETCNTRLVALUE = HDR.BUSINESS_UNIT AND S.RECNAME = 'SOURCE_TBL' AND A.SOURCE=HDR.SOURCE AND A.EFFDT=( SELECT MAX(EFFDT) FROM PS_SOURCE_TBL WHERE SETID=A.SETID AND SOURCE=A.SOURCE AND EFFDT<=HDR.JOURNAL_DATE) AND A.EFF_STATUS='A' AND B.BUSINESS_UNIT=HDR.BUSINESS_UNIT AND B.LEDGER_GROUP=HDR.LEDGER_GROUP AND C.BUSINESS_UNIT=HDR.BUSINESS_UNIT
|
| # |
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 |
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 |
BUSINESS_UNIT_LN |
Character(5) |
VARCHAR2(5) NOT NULL |
Line Business Unit
Prompt Table:
SP_BU_GL_NONVW
|
| 4 |
JOURNAL_DATE |
Date(10) |
DATE |
Specifies the date the journal was created.
|
| 5 |
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".
|
| 6 |
LEDGER_GROUP |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger Group
Prompt Table:
SP_BULGRP_NONVW
|
| 7 |
FISCAL_YEAR |
Number(4,0) |
SMALLINT NOT NULL |
Fiscal Year
Prompt Table:
CAL_FY_VW
|
| 8 |
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
|
| 9 |
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
|
| 10 |
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
|
| 11 |
JRNL_CREATE_DTTM |
DateTime(26) |
TIMESTAMP |
Journal Creation Date Time
|
| 12 |
OPRID |
Character(30) |
VARCHAR2(30) NOT NULL |
A user's ID (see PSOPRDEFN).
Prompt Table:
OPRID_VW
|
| 13 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table:
CURRENCY_CD_TBL
|
| 14 |
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.
|
| 15 |
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.
|
| 16 |
LEDGER |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger
|
| 17 |
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).
|
| 18 |
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
|
| 19 |
REVERSAL_ADJ_PER |
Number(3,0) |
SMALLINT NOT NULL |
Adjustment Period
|
| 20 |
REVERSAL_CD_ADB |
Character(1) |
VARCHAR2(1) NOT NULL |
ADB Reversal Code
D=On Date Specified By User
S=Same As Journal Reversal
|
| 21 |
REVERSAL_DATE_ADB |
Date(10) |
DATE |
ADB Reversal Date
|
| 22 |
UNPOST_JRNL_DATE |
Date(10) |
DATE |
Unpost Date
|
| 23 |
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.
|
| 24 |
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
|
| 25 |
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.
|
| 26 |
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.
|
| 27 |
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
|
| 28 |
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.
|
| 29 |
DESCR |
Character(30) |
VARCHAR2(30) NOT NULL |
Description
|
| 30 |
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
|
| 31 |
JRNL_PRCS_REQST_UP |
Character(1) |
VARCHAR2(1) NOT NULL |
This field is used to display status related to unposting.
A=Ready to Unpost(Approved)
D=Default
E=Denied
G=Pending Approval
M=Submit for Approval
N=Mark to Unpost
P=Approved to Post
Q=Pending for Approval
R=Replace
S=Security Denied
T=Terminated
U=Ready to Unpost
W=Waiting-Approved to Unpost
X=Delete
|
| 32 |
JRNL_PRCS_UP_FLTR |
Character(1) |
VARCHAR2(1) NOT NULL |
This field is used to display status related to unposting.
E=Denied
N=Mark/Submit for Approval
Q=Pending for Approval
U=Ready to Unpost
W=Waiting-Approved to Unpost
|
| 33 |
ADJUSTING_ENTRY |
Character(1) |
VARCHAR2(1) NOT NULL |
Adjusting Entry
C=Fiscal Year Close
N=Non-Adjusting Entry
Y=Adjusting Entry
|
| 34 |
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
|
| 35 |
ATTACHMENT_EXIST |
Character(1) |
VARCHAR2(1) NOT NULL |
Attachments Exist
N=N
Y=Y
|
| 36 |
JOURNAL_CLASS |
Character(10) |
VARCHAR2(10) NOT NULL |
Journal Class
|
| 37 |
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.
|
| 38 |
SELECT_FIELD |
Character(18) |
VARCHAR2(18) NOT NULL |
Select
Y/N Table Edit
Default Value: N
|
| 39 |
JRNL_APP_UP_OPTN |
Character(1) |
VARCHAR2(1) NOT NULL |
Journal Unpost Approval Option
Y/N Table Edit
|
| 40 |
APPR_IU_OPT |
Character(1) |
VARCHAR2(1) NOT NULL |
Approve IU Journal Option
|
| 41 |
COMMENTS |
Long Character |
CLOB |
Comment
|