JRNL_OPENITM_VW

(SQL View)
 Index Back

Open Item Journal View


SELECT a.business_unit ,a.journal_id ,a.journal_date ,a.unpost_seq ,a.journal_line ,a.ledger ,a.account ,a.altacct , A.DEPTID ,%subrec(cf12_an_sbr, a) ,a.project_id ,a.currency_cd ,b.open_item_key ,b.openitem_recon_nbr ,b.open_item_status ,b.open_dt ,b.closed_dt ,a.monetary_amount ,a.movement_flag ,a.jrnl_ln_ref ,a.line_descr ,a.jrnl_line_status ,a.journal_line_date ,a.foreign_currency ,a.foreign_amount ,a.rate_div ,a.rate_mult ,a.process_instance FROM PS_JRNL_LN a ,ps_open_item_gl b WHERE a.journal_id = b.journal_id AND a.journal_date = b.journal_date AND a.journal_line = b.journal_line AND a.ledger = b.ledger AND a.business_unit = b.business_unit AND a.unpost_seq = b.unpost_seq

  • Parent record: JRNL_HEADER

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL 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.

    Default Value: JRNL_HEADER.JOURNAL_ID

    3 JOURNAL_DATE Date(10) DATE NOT NULL Specifies the date the journal was created.

    Default Value: JRNL_HEADER.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_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.
    6 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger

    Default Value: BU_LED_P_VW.LEDGER

    Prompt Table: LED_GRP_LED_TBL

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

    Prompt Table: GL_ACCOUNT_TBL

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

    Prompt Table: ALTACCT_TBL

    9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    10 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField
    11 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField
    12 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code
    13 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field
    14 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField
    15 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference
    16 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate
    17 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1
    18 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate
    19 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1
    20 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2
    21 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3
    22 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    23 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL

    24 OPEN_ITEM_KEY Character(30) VARCHAR2(30) NOT NULL Open Item Key
    25 OPENITEM_RECON_NBR Number(9,0) DECIMAL(9) NOT NULL Open Item Reconcile Number
    26 OPEN_ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Open Item Status
    C=Closed Item
    N=Not an Open Item Account
    O=Open Item
    27 OPEN_DT Date(10) DATE Date Opened
    28 CLOSED_DT Date(10) DATE Date Closed
    29 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.
    30 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

    Default Value: N

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

    Default Value: GL_ACCOUNT_TBL.DESCR

    33 JRNL_LINE_STATUS Character(1) VARCHAR2(1) NOT NULL A flag that indicates the stage of processing of a journal entry.
    0=OK - No Errors
    1=Uncorrected Error(s)

    Default Value: 0

    34 JOURNAL_LINE_DATE Date(10) DATE Specifies the date of a journal line. This date must be the same as the journal date on the header (JOURNAL_DATE) in order for the Parent/Child relationship to remain intact.

    Default Value: JRNL_HEADER.TRANSACTION_DATE

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

    Prompt Table: FRGN_CURR_VW

    36 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.
    37 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
    38 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
    39 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance