Created by Corina C Larsen, last modified by Miki Yanagi on Nov 20, 2019

This view is a subset of UM_JRNL_APPR_VW, and does not have fields related to approval steps. Since it brings back less data than UM_JRNL_APPR_VW, queries using this view run faster. This view is live data of FNPRD.

For more detail journal data information, please see GL Inquiry and journal source.

KeyRecord.FieldnameFormatXLATHeading TextSource Table
 ACCOUNT - AccountChar10 AccountJRNL_LN
 ACCOUNTING_PERIOD - Accounting PeriodNum3.0 PeriodJRNL_HEADER
 ACTIVITY_ID - ActivityChar15 ActivityJRNL_LN
 ADB_DATE - Average Daily Balance DateDate ADB DateJRNL_HEADER
 ADJUSTING_ENTRY - Adjusting EntryChar1NAdj EntryJRNL_HEADER
 BUDGET_DT - Budget DateDate Budg DtJRNL_LN
 BUDGET_HDR_STATUS - Budget Checking Header StatusChar1NBudget StatusJRNL_HEADER
 BUDGET_LINE_STATUS - Budget Checking Line StatusChar1NBudget StatusJRNL_LN
 BUDGET_PERIOD - Budget PeriodChar8 Budget PeriodJRNL_LN
 BUDGET_REF - Budget ReferenceChar8 Bud RefJRNL_LN
 BUSINESS_UNIT - Business UnitChar5 UnitJRNL_HEADER
 BUSINESS_UNIT_IU - Business UnitChar5 UnitJRNL_HEADER
 BUSINESS_UNIT_PC - PC Business UnitChar5 PC Bus UnitJRNL_LN
 CLASS_FLD - Class FieldChar5 ClassJRNL_LN
 CLOSING_STATUS - Closing StatusChar1NClosing StatusJRNL_LN
 CONTROL_TOTAL_STAT - Control Total StatusChar1NControlsJRNL_HEADER
 DEPTID - DepartmentChar10 DeptJRNL_LN
 DESCR - DescriptionChar30 DescrJRNL_HEADER
 DESCR1 - DescrChar30 DescriptionGL_ACCOUNT_TBL
 DESCR254 - Long DescriptionChar254 Long DescrJRNL_HEADER
 DESCR_DEPTID - (Department Name)Char30 DepartmentDEPT_TBL
 DESCR_OP_UNIT - Descr Operating UnitChar30 Descr Op UnitOPER_UNIT_TBL
 DESCR_PROGRAM - Descr Program CodeChar30 Descr Program CPROGRAM_TBL
 DESCR_PROJECT - Descr Project IdChar30 Descr Proj IdPROJECT
 DOC_SEQ_DATE - Document Sequence DateDate Doc Seq DateJRNL_LN
 DOC_SEQ_NBR - Document Sequence NumberChar12 Doc Sequence #JRNL_LN
 DOC_SEQ_STATUS - Document Sequencing StatusChar1NStatusJRNL_LN
 DOC_TYPE - Document TypeChar8 Document TypeJRNL_LN
 DTTM_STAMP_SEC - Last Update DateTimeDateTm DateTimeJRNL_HEADER
 EE_HDR_STATUS - Entry Event Header StatusChar1 Header StatusJRNL_HEADER
 EE_PROC_STATUS - Entry Event Line StatusChar1 EE Line StatusJRNL_LN
 ENTRY_EVENT - Entry EventChar10 EventJRNL_LN
 FISCAL_YEAR - Fiscal YearNum4.0 YearJRNL_HEADER
 FOREIGN_AMOUNT - Foreign AmountSNm25.3 AmountJRNL_LN
 FUND_CODE - Fund CodeChar5 FundJRNL_LN
 GL_ADJUST_TYPE - Adjustment TypeChar4 AdjustmentJRNL_LN
 IU_SYS_TRAN_CD - System TransactionChar8 TransactionJRNL_HEADER
 IU_TRAN_CD - Transaction CodeChar8 CodeJRNL_HEADER
 JOURNAL_CLASS - Journal ClassChar10 ClassJRNL_HEADER
 JOURNAL_DATE - Journal DateDate DateJRNL_HEADER
 JOURNAL_DATE_ORIG - Orignial Journal DateDate Original DateJRNL_HEADER
 JOURNAL_ID - Journal IDChar10 Journal IDJRNL_HEADER
 JOURNAL_LINE - GL Journal Line NumberNum9.0 Line #JRNL_LN
 JOURNAL_LINE_DATE - Journal Line DateDate Line DateJRNL_LN
 JOURNAL_LOCKED - Journal Locked for ChangeChar1 Journal LockedJRNL_HEADER
 JRNL_BALANCE_STAT - Journal Balance StatusChar1NBalancedJRNL_HEADER
 JRNL_EDIT_ERR_STAT - Journal Edit Error StatusChar1NErrorsJRNL_HEADER
 JRNL_HDR_STATUS - Journal Header StatusChar1NStatusJRNL_HEADER
 JRNL_LINE_SOURCE - Journal Line SourceChar3NSourceJRNL_LN
 JRNL_LINE_STATUS - Journal Line StatusChar1NStatusJRNL_LN
 JRNL_LN_REF - Journal Line ReferenceChar10 RefJRNL_LN
 JRNL_PROCESS_REQST - Journal Processing RequestChar1NProcessJRNL_HEADER
 JRNL_SUMLED_REQST - Post to Summary LedgerChar1 SL PostJRNL_HEADER
 JRNL_TOTAL_DEBITS - Journal Total DebitsSNm25.3 DebitsJRNL_HEADER
 JRNL_TOTAL_LINES - Journal Total LinesNum9.0 LinesJRNL_HEADER
 JRNL_TOT_CREDITS - Journal Total CreditsSNm25.3 CreditsJRNL_HEADER
 KK_AMOUNT_TYPE - Commitment Control Amount TypeChar1NAmount TypeJRNL_HEADER
 KK_TRAN_BYPAS_FLAG - Bypass Budget CheckingChar1 BypassJRNL_HEADER
 LEDGER - LedgerChar10 LedgerJRNL_HEADER
 LEDGER_2 - LedgerChar10 LedgerJRNL_LN
 LEDGER_GROUP - Ledger GroupChar10 Ledger GrpJRNL_HEADER
 LINE_DESCR - Journal Line DescriptionChar30 Line DescrJRNL_LN
 MONETARY_AMOUNT - Monetary AmountSNm25.3 AmountJRNL_LN
 MOVEMENT_FLAG - Movement FlagChar1NN/RJRNL_LN
 NAME - NameChar50 NamePERSONAL_DATA
 NAME50 - NameChar50 NamePERSONAL_DATA
 OPERATING_UNIT - Operating UnitChar8 Oper UnitJRNL_LN
 OPRID - User IDChar30 UserJRNL_HEADER
 PC_DISTRIB_STATUS - PC Distribution StatusChar1NPC StatusJRNL_LN
 POSTED_DATE - Date PostedDate PostedJRNL_HEADER
 PROCESS_INSTANCE - Process InstanceNum10.0 InstanceJRNL_HEADER
 PROCESS_INSTANCE2 - Process InstanceNum10.0 InstanceJRNL_LN
 PROGRAM_CODE - Program CodeChar5 ProgramJRNL_LN
 PROJECT_ID - ProjectChar15 ProjectJRNL_LN
 REVERSAL_ADJ_PER - Adjustment PeriodNum3.0 PeriodJRNL_HEADER
 REVERSAL_CD - Reversal CodeChar1NReversalJRNL_HEADER
 REVERSAL_CD_ADB - ADB Reversal CodeChar1NADB ReversalJRNL_HEADER
 REVERSAL_DATE - Reversal DateDate DateJRNL_HEADER
 REVERSAL_DATE_ADB - ADB Reversal DateDate ADB ReversalJRNL_HEADER
 SCENARIO - ScenarioChar10 ScenarioJRNL_LN
 SOURCE - SourceChar3 SourceJRNL_HEADER
 SOURCE_INSTANCE - Source InstanceNum11.4 InstanceJRNL_HEADER
 STATISTICS_CODE - Statistics CodeChar3 StatJRNL_LN
 STATISTIC_AMOUNT - Statistic AmountSNm15.2 Stat AmtJRNL_LN
 SUSPENDED_LINE - Suspended Journal Line NumberNum9.0 Suspended LineJRNL_LN
 SUSP_RECON_STATUS - Suspense Reconciliation StatusChar1NSuspense StatusJRNL_HEADER
 SYSTEM_SOURCE - System SourceChar3NSys SourceJRNL_HEADER
 TRANSACTION_DATE - Transaction DateDate Trans DateJRNL_HEADER
 TRANS_REF_NUM - Reference NumberChar8 Ref NoJRNL_HEADER
 UNPOST_JRNL_DATE - Unpost DateDate Unpost DateJRNL_HEADER
 UNPOST_SEQ - UnPost SequenceNum2.0 SeqJRNL_HEADER

FAQs for UM_JOURNAL_VW

What is UM_JOURNAL_VW?

Why was this view created?

  • Some users have experienced performance issues with queries using UM_JRNL_APPR_VW. The queries ran very slow or ended with an error.

Which queries are using UM_JRNL_APPR_VW?

  • You can get the list of queries by using the advanced search function in Query Viewer or Query Manager.

Have any public queries been recreated to move from UM_JRNL_APPR_VW to UM_JOURNAL_VW?

  • These queries have been re-created with UM_JOURNAL_VW. DARTS will re-create more upon request.
    • UMS_DTS_OPERATING_DETAIL
    • UMS_DTS_JOURNAL_ACTIVITY
    • UMS_DTS_ATHLETICS_DETAIL

Should I re-create all of my private queries?

  • The queries with UM_JRNL_APPR_VW should still work. If you have not experienced issues, you don't have to re-create the queries.

Where can I find help for re-creating queries?

Can I see the code?

Code for UM_JOURNAL_VW 
SELECT DISTINCT a.business_unit
 , a.journal_id
 , a.journal_date
 , a.unpost_seq
 , a.business_unit_iu
 , a.adjusting_entry
 , a.fiscal_year
 , a.accounting_period
 , a.adb_date
 , a.ledger_group
 , a.ledger
 , a.reversal_cd
 , a.reversal_date
 , a.reversal_adj_per
 , a.reversal_cd_adb
 , a.reversal_date_adb
 , a.unpost_jrnl_date
 , a.jrnl_total_lines
 , a.jrnl_total_debits
 , a.jrnl_tot_credits
 , a.source
 , a.trans_ref_num
 , a.jrnl_balance_stat
 , a.control_total_stat
 , a.jrnl_edit_err_stat
 , a.jrnl_hdr_status
 , a.susp_recon_status
 , a.jrnl_process_reqst
 , a.jrnl_sumled_reqst
 , a.posted_date
 , a.process_instance
 , a.source_instance
 , a.transaction_date
 , a.oprid
 , c.name
 , a.dttm_stamp_sec
 , a.descr
 , a.system_source
 , b.pc_distrib_status
 , a.budget_hdr_status
 , a.kk_amount_type
 , a.journal_locked
 , a.journal_date_orig
 , a.journal_class
 , a.kk_tran_bypas_flag
 , a.ee_hdr_status
 , a.iu_sys_tran_cd
 , a.iu_tran_cd
 , a.descr254
 , b.journal_line
 , b.ledger
 , b.account
 , e.descr
 , b.deptid
 , d.descr
 , b.operating_unit
 , h.descr
 , b.fund_code
 , b.class_fld
 , b.program_code
 , f.descr
 , b.budget_ref
 , b.project_id
 , g.descr
 , b.gl_adjust_type
 , b.budget_period
 , b.statistics_code
 , b.scenario
 , b.monetary_amount
 , b.movement_flag
 , b.statistic_amount
 , b.jrnl_ln_ref
 , b.suspended_line
 , b.line_descr
 , b.jrnl_line_status
 , b.journal_line_date
 , b.foreign_amount
 , b.jrnl_line_source
 , b.process_instance
 , b.doc_type
 , b.doc_seq_nbr
 , b.doc_seq_date
 , b.doc_seq_status
 , b.business_unit_pc
 , b.activity_id
 , b.budget_dt
 , b.budget_line_status
 , b.closing_status
 , b.entry_event
 , b.ee_proc_status
 , c.name
  FROM ps_jrnl_header a
  , ps_jrnl_ln b
  , ps_personal_data c
  , PS_DEPT_TBL D
  , ps_gl_account_tbl e
  , ps_program_tbl f
  , ps_project g
  , ps_oper_unit_tbl h
 WHERE a.business_unit = b.business_unit
   AND a.journal_id = b.journal_id
   AND a.journal_date = b.journal_date
   AND a.unpost_seq = b.unpost_seq
   AND a.oprid = c.emplid(+)
   AND D.setid = 'UMSYS'
   AND b.deptid = d.deptid
   AND d.effdt = (
 SELECT MAX(d_ed.effdt)
  FROM ps_dept_tbl d_ed
 WHERE d.setid = d_ed.setid
   AND d.deptid = d_ed.deptid
   AND d_ed.effdt <= a.journal_date)
   AND e.setid = 'UMSYS'
   AND e.account = b.account
   AND e.effdt = (
 SELECT MAX(e_ed.effdt)
  FROM ps_gl_account_tbl e_ed
 WHERE e.setid = e_ed.setid
   AND e.account = e_ed.account
   AND e_ed.effdt <= a.journal_date)
   AND f.setid(+) = 'UMSYS'
   AND f.program_code(+) = b.program_code
   AND (f.effdt = (
 SELECT MAX(f_ed.effdt)
  FROM ps_program_tbl f_ed
 WHERE f.setid = f_ed.setid
   AND f.program_code = f_ed.program_code
   AND f_ed.effdt <= a.journal_date)
    OR f.effdt IS NULL)
   AND g.business_unit(+) = 'UMSYS'
   AND g.project_id (+) = b.project_id
   AND h.setid(+) = 'UMSYS'
   AND h.operating_unit(+) = b.operating_unit
   AND (h.effdt = (
 SELECT MAX(h_ed.effdt)
  FROM ps_oper_unit_tbl h_ed
 WHERE h.setid = h_ed.setid
   AND h.operating_unit = h_ed.operating_unit
   AND h_ed.effdt <= a.journal_date)
    OR h.effdt IS NULL)

Attachments:

image2019-8-26_10-56-2.png (image/png)