|
SELECT A.BUSINESS_UNIT ,A.CUST_ID ,A.ITEM ,A.ITEM_LINE ,A.APPL_JRNL_ID ,A.CURRENCY_CD ,A.PRCS_INST_RVL ,CASE WHEN A.LEDGER = ' ' THEN B.LEDGER ELSE A.LEDGER END ,A.FOREIGN_CURRENCY ,A.ENTRY_CURRENCY ,A.ACCOUNT ,A.ALTACCT ,A.DEPTID , %subrec(CF16_AN_SBR, A) , %subrec(PC_CF1_N_SBR, A) ,A.BUDGET_DT ,A.BUSINESS_UNIT_GL ,A.STATISTICS_CODE ,A.MONETARY_AMOUNT ,A.STATISTIC_AMOUNT ,A.CF_EDIT_STATUS ,A.JRNL_LN_REF ,A.LINE_DESCR ,A.JOURNAL_ID ,A.JOURNAL_DATE ,A.GL_DISTRIB_STATUS ,A.PROCESS_INSTANCE ,A.ACCOUNTING_DT ,A.FOREIGN_AMOUNT ,A.RT_TYPE ,A.RATE_MULT ,A.RATE_DIV ,CASE WHEN A.LEDGER_GROUP = ' ' THEN B.LEDGER_GROUP ELSE A.LEDGER_GROUP END ,A.FISCAL_YEAR ,A.ACCOUNTING_PERIOD ,A.JOURNAL_LINE ,A.MOVEMENT_FLAG ,A.DOC_TYPE ,A.DOC_SEQ_NBR ,A.DOC_SEQ_DATE ,A.DOC_SEQ_STATUS ,A.RT_TYPE_2 ,A.RATE_MULT_2 ,A.RATE_DIV_2 ,A.DTTM_STAMP ,CASE WHEN C.JRNL_HDR_STATUS IS NULL THEN ' ' ELSE C.JRNL_HDR_STATUS END FROM PS_RVL_ACCTG_LN_AR A LEFT OUTER JOIN PS_JRNL_HEADER C ON A.BUSINESS_UNIT_GL = C.BUSINESS_UNIT AND A.JOURNAL_ID = C.JOURNAL_ID AND A.JOURNAL_DATE = C.JOURNAL_DATE , PS_OI_BULEDGRP_VW B WHERE A.BUSINESS_UNIT=B.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
|
| 2 |
CUST_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
|
| 3 |
ITEM |
Character(30) |
VARCHAR2(30) NOT NULL |
Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.
|
| 4 |
ITEM_LINE |
Number(6,0) |
INTEGER NOT NULL |
Item Line
|
| 5 |
APPL_JRNL_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Journal Template
|
| 6 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 7 |
PRCS_INST_RVL |
Number(10,0) |
DECIMAL(10) NOT NULL |
Revaluation Process Instance
|
| 8 |
LEDGER |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger
|
| 9 |
FOREIGN_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Foreign Currency Code
|
| 10 |
ENTRY_CURRENCY |
Character(3) |
VARCHAR2(3) NOT NULL |
Specifies the currency associated with the monetary amount of a pending item.
|
| 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 |
OPERATING_UNIT |
Character(8) |
VARCHAR2(8) NOT NULL |
Operating Unit ChartField
|
| 15 |
PRODUCT |
Character(6) |
VARCHAR2(6) NOT NULL |
Product ChartField
|
| 16 |
FUND_CODE |
Character(5) |
VARCHAR2(5) NOT NULL |
Fund Code
|
| 17 |
CLASS_FLD |
Character(5) |
VARCHAR2(5) NOT NULL |
Class Field
|
| 18 |
PROGRAM_CODE |
Character(5) |
VARCHAR2(5) NOT NULL |
Program Code ChartField
|
| 19 |
BUDGET_REF |
Character(8) |
VARCHAR2(8) NOT NULL |
Budget Reference
|
| 20 |
AFFILIATE |
Character(5) |
VARCHAR2(5) NOT NULL |
Affiliate
|
| 21 |
AFFILIATE_INTRA1 |
Character(10) |
VARCHAR2(10) NOT NULL |
IntraUnit Affiliate1
|
| 22 |
AFFILIATE_INTRA2 |
Character(10) |
VARCHAR2(10) NOT NULL |
Operating Unit Affiliate
|
| 23 |
CHARTFIELD1 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion chartfield 1
|
| 24 |
CHARTFIELD2 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion Chartfield 2
|
| 25 |
CHARTFIELD3 |
Character(10) |
VARCHAR2(10) NOT NULL |
Expansion Chartfield 3
|
| 26 |
BUSINESS_UNIT_PC |
Character(5) |
VARCHAR2(5) NOT NULL |
PC Business Unit
Prompt Table:
%EDIT_BU_PC
|
| 27 |
PROJECT_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Project Id ChartField
Prompt Table:
%EDIT_PROJECT
|
| 28 |
ACTIVITY_ID |
Character(15) |
VARCHAR2(15) NOT NULL |
Activity ID
Prompt Table:
%EDIT_ACTIVITY
|
| 29 |
RESOURCE_TYPE |
Character(5) |
VARCHAR2(5) NOT NULL |
Source Type
Prompt Table:
PROJ_RESTYPE_FS
Set Control Field: BUSINESS_UNIT_PC
|
| 30 |
RESOURCE_CATEGORY |
Character(5) |
VARCHAR2(5) NOT NULL |
Category
Prompt Table:
%EDIT_RES_CAT
Set Control Field: BUSINESS_UNIT_PC
|
| 31 |
RESOURCE_SUB_CAT |
Character(5) |
VARCHAR2(5) NOT NULL |
Subcategory
Prompt Table:
%EDIT_RES_SUB
Set Control Field: BUSINESS_UNIT_PC
|
| 32 |
ANALYSIS_TYPE |
Character(3) |
VARCHAR2(3) NOT NULL |
Analysis Type
Prompt Table:
PC_GL_AN_GRP_VW
Set Control Field: BUSINESS_UNIT_PC
|
| 33 |
BUDGET_DT |
Date(10) |
DATE |
Budget Date
|
| 34 |
BUSINESS_UNIT_GL |
Character(5) |
VARCHAR2(5) NOT NULL |
GL Business Unit
|
| 35 |
STATISTICS_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Statistics Code
|
| 36 |
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.
|
| 37 |
STATISTIC_AMOUNT |
Signed Number(17,2) |
DECIMAL(15,2) NOT NULL |
Specifies the amount associated with a statistical account on a journal line or a distribution line. It represents a quantity rather than a monetary amount and is qualified by the Unit of Measure associated with the STATISTICS_CODE or statistics ACCOUNT.
|
| 38 |
CF_EDIT_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
A flag that indicates whether or not accounting entries have been edited for valid ChartField combinations. If an edit has taken place the status indicates whether or not it was successful. The ChartField Combination Edit program populates the field as Complete Errors or Not Edited.
C=Complete
E=Errors
N=Not Edited
Default Value: N
|
| 39 |
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.
|
| 40 |
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.
|
| 41 |
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.
|
| 42 |
JOURNAL_DATE |
Date(10) |
DATE |
Specifies the date the journal was created.
|
| 43 |
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
|
| 44 |
PROCESS_INSTANCE |
Number(10,0) |
DECIMAL(10) NOT NULL |
Process Instance
|
| 45 |
ACCOUNTING_DT |
Date(10) |
DATE NOT NULL |
The accounting entry construction date for a given transaction (a generic field that crosses multiple
|
| 46 |
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.
|
| 47 |
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.
|
| 48 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 49 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
Default Value: 1
|
| 50 |
LEDGER_GROUP |
Character(10) |
VARCHAR2(10) NOT NULL |
Ledger Group
|
| 51 |
FISCAL_YEAR |
Number(4,0) |
SMALLINT NOT NULL |
Fiscal Year
|
| 52 |
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.
|
| 53 |
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.
|
| 54 |
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
|
| 55 |
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.
|
| 56 |
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.
|
| 57 |
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.
|
| 58 |
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
|
| 59 |
RT_TYPE_2 |
Character(5) |
VARCHAR2(5) NOT NULL |
Rate Type
|
| 60 |
RATE_MULT_2 |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 61 |
RATE_DIV_2 |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
|
| 62 |
DTTM_STAMP |
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.
Default Value: %Datetime
|
| 63 |
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
|