How we Leveraged Reference Columns in GL_INTERFACE to Group Data into Journal Batches and Journal He

User502553-Oracle

JohnHunter

 Thanks for the Document.

How did you sequence the journal lines so that they reflected the line sequence from your legacy system?

We are thinking of this for our web ADI Imports, but have yet to fully test (R12):

-- since all imports have a unique group_id, -- we created a table to hold/generate the line sequence number by group_id CREATE TABLE custom.coop_gl_interface_line_seq (group_id NUMBER (12,0), je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically custom.coop_gl_interface_line_seq (group_id NUMBER (12,0), je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically

(group_id NUMBER (12,0), je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically

je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically

/  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically CREATE OR REPLACE TRIGGER custom.coop_gl_interface_seq BEFORE INSERT ON gl.gl_interface

REPLACE TRIGGER custom.coop_gl_interface_seq BEFORE INSERT ON gl.gl_interface

BEFORE INSERT ON gl.gl_interface gl.gl_interface REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW WHEN ( new .USER_JE_SOURCE_NAME in ('Spreadsheet','Conversion') ) ( new .USER_JE_SOURCE_NAME in ('Spreadsheet','Conversion') ) BEGIN DECLARE v_next_num NUMBER (12) := 0; NUMBER (12) := 0; BEGIN BEGIN SELECT je_line_num + 1 INTO v_next_num FROM custom.coop_gl_interface_line_seq WHERE je_line_num + 1 INTO v_next_num FROM custom.coop_gl_interface_line_seq WHERE group_id = :new.group_id; = :new.group_id; EXCEPTION WHEN NO_DATA_FOUND THEN NO_DATA_FOUND THEN v_next_num := 1; = 1; INSERT INTO custom.coop_gl_interface_line_seq VALUES (:new.group_id, v_next_num); .coop_gl_interface_line_seq VALUES (:new.group_id, v_next_num); END; ; :new.reference21 := v_next_num; .reference21 := v_next_num; UPDATE custom.coop_gl_interface_line_seq SET je_line_num = v_next_num WHERE group_id = :new.group_id; custom.coop_gl_interface_line_seq SET je_line_num = v_next_num WHERE group_id = :new.group_id; :new.reference21 := NULL ; .reference21 := NULL ; END; ; END; / -- since reference1 is populated with the true sequence line number from the web ADI journal, we renumber the -- je_line_num using reference1 ; / -- since reference1 is populated with the true sequence line number from the web ADI journal, we renumber the -- je_line_num using reference1 CREATE OR REPLACE TRIGGER custom.coop_gl_je_lines_seq BEFORE INSERT OR UPDATE ON gl.gl_je_lines

REPLACE TRIGGER custom.coop_gl_je_lines_seq BEFORE INSERT OR UPDATE ON gl.gl_je_lines

BEFORE INSERT OR UPDATE ON gl.gl_je_lines gl.gl_je_lines REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :new.JE_LINE_NUM := NVL (:NEW.reference_1,:new.JE_LINE_NUM); .JE_LINE_NUM := NVL (:NEW.reference_1,:new.JE_LINE_NUM); :new.reference_1 := NULL ; .reference_1 := NULL ; END; / Any thoughts to this madness? Should we even attempt to go down this road? ; / Any thoughts to this madness? Should we even attempt to go down this road?

IGSI User

 Thanks for the Document.

How did you sequence the journal lines so that they reflected the line sequence from your legacy system?

We are thinking of this for our web ADI Imports, but have yet to fully test (R12):

-- since all imports have a unique group_id, -- we created a table to hold/generate the line sequence number by group_id CREATE TABLE custom.coop_gl_interface_line_seq (group_id NUMBER (12,0), je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically custom.coop_gl_interface_line_seq (group_id NUMBER (12,0), je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically

(group_id NUMBER (12,0), je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically

je_line_num NUMBER (12,0)) /  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically

/  -- we poplulate reference21 with the generated sequence number as web ADI imports into the gl_interface table -- later reference21 becomes reference1 in the gl_je_lines table automatically CREATE OR REPLACE TRIGGER custom.coop_gl_interface_seq BEFORE INSERT ON gl.gl_interface

REPLACE TRIGGER custom.coop_gl_interface_seq BEFORE INSERT ON gl.gl_interface

BEFORE INSERT ON gl.gl_interface gl.gl_interface REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW WHEN ( new .USER_JE_SOURCE_NAME in ('Spreadsheet','Conversion') ) ( new .USER_JE_SOURCE_NAME in ('Spreadsheet','Conversion') ) BEGIN DECLARE v_next_num NUMBER (12) := 0; NUMBER (12) := 0; BEGIN BEGIN SELECT je_line_num + 1 INTO v_next_num FROM custom.coop_gl_interface_line_seq WHERE je_line_num + 1 INTO v_next_num FROM custom.coop_gl_interface_line_seq WHERE group_id = :new.group_id; = :new.group_id; EXCEPTION WHEN NO_DATA_FOUND THEN NO_DATA_FOUND THEN v_next_num := 1; = 1; INSERT INTO custom.coop_gl_interface_line_seq VALUES (:new.group_id, v_next_num); .coop_gl_interface_line_seq VALUES (:new.group_id, v_next_num); END; ; :new.reference21 := v_next_num; .reference21 := v_next_num; UPDATE custom.coop_gl_interface_line_seq SET je_line_num = v_next_num WHERE group_id = :new.group_id; custom.coop_gl_interface_line_seq SET je_line_num = v_next_num WHERE group_id = :new.group_id; :new.reference21 := NULL ; .reference21 := NULL ; END; ; END; / -- since reference1 is populated with the true sequence line number from the web ADI journal, we renumber the -- je_line_num using reference1 ; / -- since reference1 is populated with the true sequence line number from the web ADI journal, we renumber the -- je_line_num using reference1 CREATE OR REPLACE TRIGGER custom.coop_gl_je_lines_seq BEFORE INSERT OR UPDATE ON gl.gl_je_lines

REPLACE TRIGGER custom.coop_gl_je_lines_seq BEFORE INSERT OR UPDATE ON gl.gl_je_lines

BEFORE INSERT OR UPDATE ON gl.gl_je_lines gl.gl_je_lines REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN :new.JE_LINE_NUM := NVL (:NEW.reference_1,:new.JE_LINE_NUM); .JE_LINE_NUM := NVL (:NEW.reference_1,:new.JE_LINE_NUM); :new.reference_1 := NULL ; .reference_1 := NULL ; END; / Any thoughts to this madness? Should we even attempt to go down this road? ; / Any thoughts to this madness? Should we even attempt to go down this road?

Hi,

This is really an excellent document.  

But we need exactly the opposite functionality in R12.  In R12 whenever the Journals are transferred from AP to GL a seperate Journal Header is being created for each Invoice.  But we want all the Journal lines from all the Invoices should go under a Single Journal Entry.

Please let me know if you have any idea on how to acheive the same.

Thanks,

Lokesh.

JohnHunter

Hi,

This is really an excellent document.  

But we need exactly the opposite functionality in R12.  In R12 whenever the Journals are transferred from AP to GL a seperate Journal Header is being created for each Invoice.  But we want all the Journal lines from all the Invoices should go under a Single Journal Entry.

Please let me know if you have any idea on how to acheive the same.

Thanks,

Lokesh.

Try this from payables:

Accounting Setups >  Ledger Setup > Define > Accounting Setups

Find your Ledger

Update Accounting Options for the Ledger

Update Subledger Accounting Options

Update Accounting Options for Payables

Take a look at * General Ledger Journal Entry Summarization

There are 3 options, my guess is that you have it set at 'No Summary'  

We have ours set to 'Summary by Period', you could try that, or try setting at 'Summary by GL Date'

IGSI User

Try this from payables:

Accounting Setups >  Ledger Setup > Define > Accounting Setups

Find your Ledger

Update Accounting Options for the Ledger

Update Subledger Accounting Options

Update Accounting Options for Payables

Take a look at * General Ledger Journal Entry Summarization

There are 3 options, my guess is that you have it set at 'No Summary'  

We have ours set to 'Summary by Period', you could try that, or try setting at 'Summary by GL Date'

Thanks John for your reply.

If we summarize then the individual Journal lines will get summarized and we do not want that.  What we want is only One Journal Header for the all the Journal lines transferred from AP to GL for any given day. 

Assume that there are 1000 Journal lines in AP and all these 1000 lines belongs to 10 different invoices.  In this Scenerio, if we transfer the Journals from AP to GL, in 11i Oracle will create 1 Journal Header and 1000 Journal Lines. 

But in R12, since these 1000 lines belongs to 10 different Invoices, Oracle is creating 10 different Journal Headers.  We do not want so many Journal Headers in the system due to the performance reasons.  So, please note that we want all the 1000 Journals to be transferred to GL with out summarization.  We want only one Journal Header instead of 10 Journal Headers.

Thanks,

Lokesh.