EGL: Journal Generator Process Leaves the GL_DISTRIB_STATUS = 'J' in PS_JGEN_ACCT_ENTRY, for Data Loaded Via SQL Using the GENERIC Accounting Definition Template (Doc ID 3009433.1)

Last updated on MARCH 10, 2024

Applies to:

Symptoms

Data is loaded directly to PS_JGEN_ACCT_ENTRY using SQL statements. Journal Generator runs and the process completes successfully. However, the balancing line leaves the PS_JGEN_ACCT_ENTRY.GL_DISTRIB_STATUS = 'J' (Creating Journals) when it should be 'D' (Distributed), and the journal created was balanced through Create/Update Journal Entries.


STEPS
-----------------------

1. Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Define Values > Define ChartField Value
    a. Add Account 400001
    b. Monetary Account Type = Revenue
    c. Box checked for General Ledger Account

2. Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Define Values > Define ChartField Value
    a. Add Account 713001
    b. Monetary Account Type = Asset
    c. Box checked for General Ledger Account

3. Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Define Values > Define ChartField Value > Add Department = ‘P’

4. Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > Define Values > Define ChartField Value > Add 3 Products: 61121, 61122, 61610 

5. General Ledger > Journals > Subsystem Journals > Journal Generator Template > Add Template = REVACC
    - Summarization tab > radio buttons selected for Summarize to All ChartFields, All Account Values, Retain Detail (grayed out) 

6.General Ledger > Journals > Subsystem Journals > Accounting Entry Definition > GENERIC definition - no changes 

7. Six different SQL's are used to load PS_JGEN_ACCT_ENTRY > below is the balancing line SQL which leaves the PS_JGEN_ACCT_ENTRY.GL_DISTRIB_STATUS = 'J'

Insert into PS_JGEN_ACCT_ENTRY (BUSINESS_UNIT,TRANSACTION_ID,LEDGER_GROUP,LEDGER,ACCOUNTING_DT,APPL_JRNL_ID,BUSINESS_UNIT_GL,FISCAL_YEAR,ACCOUNTING_PERIOD,JOURNAL_ID,JOURNAL_DATE,JOURNAL_LINE,ACCOUNT,ALTACCT,OPERATING_UNIT,DEPTID,PRODUCT,PROJECT_ID,AFFILIATE,CURRENCY_CD,STATISTICS_CODE,FOREIGN_CURRENCY,RT_TYPE,RATE_MULT,RATE_DIV,MONETARY_AMOUNT,FOREIGN_AMOUNT,STATISTIC_AMOUNT,MOVEMENT_FLAG,DOC_TYPE,DOC_SEQ_NBR,DOC_SEQ_DATE,JRNL_LN_REF,LINE_DESCR,GL_DISTRIB_STATUS,PROCESS_INSTANCE,FUND_CODE,CLASS_FLD,PROGRAM_CODE,BUDGET_REF,AFFILIATE_INTRA1,AFFILIATE_INTRA2,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,IU_SYS_TRAN_CD,IU_TRAN_CD,IU_ANCHOR_FLG,SEQUENCENO,DTTM_STAMP) values ('US001','POR0207892','RECORDING','LOCAL',to_date('31-DEC-23','DD-MON-RR'),'REVACC','US001',2023,12,' ',null,0,'173001',' ',' ','  ',' ',' ',' ','USD',' ','USD','CRRNT',1,1,4053068.83,4053068.83,0,' ',' ',' ',null,' ','US001 Revenue Accrual','N',0,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',0,to_timestamp('16-FEB-24 11.11.30.844402000 AM','DD-MON-RR HH.MI.SSXFF AM'));

8. General Ledger > Journals > Subsystem Journals > Generate Jrnl from Subsystem
    a. Box checked for Edit, Post
    b. Accounting Definition Name = GENERIC
    c. Template = REVACC

9. Query JGEN_ACCT_ENTRY
    a. TRANSACTION_ID = POR0207892
    b. JOURNAL_ID = blank
    c. JOURNAL_DATE = (null)
    d. GL_DISTRIB_STATUS = ‘J’ 

10. General Ledger > Journals > Journal Entry > Create/Update Journal Entries > Journal is balanced, Journal Status = ‘P’

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.