ORACLE APPLICATIONS ARCHIVES

Topicwise collection of
Postings on Mail Lists
ON
GENERAL LEDGER

GL Interface

6.1. AR to GL Interface

Date: Fri, 26 Mar 1999 08:34:37 -0700
From: MANTOSRIDAR@micronpc.com
Subject: RE: Urgently Req. GL Interface !

This can be a two step process.
1. GL transfer Process which will put the accounting journal in the GL_INTERFACE. This process will give posting id for each transfer.
2. Develop custom GL import into legacy GL. Mapping table need to be maintained if the chart of account structure is different.

-----Original Message-----

From: Mujahid Siddeeque [mailto:MSiddeeq@smtp-gw.lsuc2.lsuc.on.ca]
Sent: Friday, March 26, 1999 6:01 AM
Subject: Urgently Req. GL Interface !

Hi Apps.Guru's, We are Implementing Oracle Financial's( AR, Work FLow, Service) and Oracle HR (HR, Training Admin.). This is the entirely new System here, the existing system we have is AS400, on which they are running GL, AR. Now they wanted to implement only Oracle AR over existing AR. But for GL, for time being run infinium GL. My question is

How to write API from Oracle Receivables Sub-Ledger to Infinium General Ledger. I appreciate if any any one can send me a script for this API.

Thank's in advance to every one for your help.
Mujahid

GL Interface Tables

Date: Wed, 31 Mar 1999 07:25:48 -0500 (EST)
From: Charlie Epp CMCICAE@njtransit.state.nj.us
Subject: ?? GL Interface Table ??

Good morning all. I am in the process of reviewing the GL Interface table for information from other Oracle subsystems and have one question. The interface table has a defined set of data. Can it be easily modified to include other information? For example, could you modify to include the PO number (which does not appear in the table). My thought is "no", it is not easy but wish to confirm. Thanks for any help.

Date: Wed, 31 Mar 1999 18:06:25 +0530
From: amareesh amareesha@sohm.soft.net
Subject: RE: ?? GL Interface Table ??

Hi, As GL Interface table provides interface between various Oracle Application Modules and also between Oracle GL and any legacy system, It is recommended that one should not change the structure of GL Interface Table. Amar

Date: Wed, 31 Mar 1999 08:31:17 -0500
From: "Richard Gross" ragross@revenue.com
Subject: RE: ?? GL Interface Table ??

I don't think that modify the table is what you want to do. If I remember correctly one of the reference fields on the je line can be used to look up the AP record that created the line and from their you can look up the PO number. I think the functionality that you are looking for is already in place.

Date: Wed, 31 Mar 1999 10:11:08 -0500
From: "Vallapareddy, Krishna"
To: oraapps-l@cpa.qc.ca

Instead of modifying the GL INTERFACE table you could always store additional information in the context fields.

Krishna Vallapareddy Oracle Financials Consultant Noblestar Systems Corporation (703) 641-2778 ext 2508 or (800) 470-4668 ext 2508 http://www.noblestar.com

GL Interface Columns

"Bill Keenan" wrote:

Hi,
I am writing a script to pull over all G/L information from GL version 10.7SC to 11.0. I need to pull all information in GL_JE_BATCHES, GL_JE_JOURNALS, and GL_JE_LINES, so as to load 11.0 to mirror 10.7.

A Technical Reference Manual is not on site at this time (we've ordered it), so I am not sure where I should put some of the info in the GL_INTERFACE table. If anyone can help me out with what columns the following info maps to, I would appreciate it:

GL_JE_BATCHES
GL_INTERFACE

Control Total
Running_Total_Dr
Running_Total_Cr
Running_Total_Accounted_Dr
Running_Total_Accounted_Cr
GL_JE_HEADERS

Control Total
Running_Total_Dr
Running_Total_Cr
Running_Total_Accounted_Dr
Running_Total_Accounted_Cr
Thank you in advance for your help!
Bill Keenan

Date: 6 Apr 99 17:23:41 EDT
From: Dinesh Chandra dchandra@usa.net
Subject: Re: [GL_INTERFACE columns]

Hi,
Following fields are required to be populated in GL_INTERFACE table
Segment 1 -30----------- as the case may be
STATUS ----------------'NEW'
SET_OF_BOOKS_ID
USER_JE_SOURCE_NAME
USER_JE_CATEGORY_NAME
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG ------------ 'A'
ENTERED_DR
ENTERED_CR
REFERENCE1---------------Batch Name
REFERENCE2--------------- Batch Description
REFERENCE4---------------Journal Entry Name
REFERENCE6--------------- Journal Entry Reference
REFERENCE10---------------Line Description

After loading data into GL_INTERFACE table
\Navigate Journal Inport - Run
- Correct
- Delete
Hope this will help you.
Thanks Dinesh Chandra

Date: Tue, 06 Apr 1999 16:39:17 -0500
From: Nancy Gaudette nancy.gaudette@esoftsolutions.com
Subject: Re: GL_INTERFACE columns

Bill,
These columns do not map to the GL_INTERFACE table. I beleive the running total columns are populated during the journal import process, but I don't think this is true for the control total.

If you have the Release 11 Oracle Applications Documentation Library CD-ROM, the Importing Journals section of the General Ledger reference manual describes the GL_INTERFACE table and columns in detail in addition to describing the journal import process itself.

Nancy Gaudette www.esoftsolutions.com

Modifications to Journals after importing

--- Bill Keenan bkeenan@goodegg.com wrote:

Hi,
I am trying to convert GL journals from GL v10.7SC to v11.02. I have successfully pulled in a journal, however I would now like to go back and populate the created_by and creation_date with the correct data from 10.7 (Oracle automatically inserts these as the user_id you are running the journal import under, as well as current date). I need to find a few attribute fields in the GL_INTERFACE table to insert the old data, and be able to access it either on the GL_JE_BATCHES, GL_JE_HEADERS or GL_JE_LINES table.

My questions are:

1. Does anyone know where I can put additional data in the GL_INTERFACE and be able to access it after the journal is imported and posted. I need to see the info in the columns, but I do not want the users to see this info?

2. If I put it in an Attribute field in the GL_INTERFACE, do I need to define the descriptive flexfield before I do the import, post, and then be able to access the data in the field???

This is very important, and is the last issue before we do our conversion(tests first of course), so I appreciate your help on this one.

Thank you, Bill Keenan

Date: Mon, 12 Apr 1999 21:50:09 -0700 (PDT)
From: Andy Schindler aschindl@yahoo.com
Cc: bkeenan@goodegg.com
Subject: Re: ** GL_INTERFACE/Journal Import question

Bill,
It is always a good idea to have a link back to original system as part of an interface. Personally, I think using it for this requirement is unnecessary....but if the client want to pay for it......:-))

What you are asking is straight forward.

Map the JE_HEADER_ID and LINE_NUMBER from original GL_JE_LINES row to reference22 and reference23 respectively. These will be brought over into GL_JE_LINES reference2 and reference3 by the import process. Oracle does not use these fields on manually imported JE's and they are not visible to the end users. Using these fields, you can get back to the original JE lines from your legacy system.

Now you can pull what ever information you need from the original line and update (not supported by Oracle) as required. Don't forget to update the associated GL_JE_HEADERS and GL_JE_BATCHES during your update.

You could also extend this thought process to bringing over all of the information you need into columns Reference22, 23, 25-29. (21 and 24 are used by Oracle See open interfaces manual). This way you won't have to link to the original database when doing your updates.

Good luck. Andy Schindler NRC, Inc

From: PeriasamyR@mashreqbank.com PeriasamyR@mashreqbank.com
Date: Tuesday, April 13, 1999 2:16 AM
Subject: RE: ** GL_INTERFACE/Journal Import question

Hello Bill,
a) We had used Attributes field in the GL_INTERFACE to bring in additional info from external system.

b) You need to describe a descriptive flexfield only if you want to refer to this data online, after import. If it is for use only by developers or implementers, you could do without defining Desc FFs. You would then be accessing this data directly.

Note however, the Attributes in the GL_INTERFACE table get populated only on the corresponding Attributes fields of the GL_JE_LINES, and hence would not be available at GL_BATCHES or at GL_JOURNALS level. Also, when you run Journal Import, set Import Desc Flexfields as Yes (with or without validation).

Hope it is of help. Regard Peri

Date: Tue, 13 Apr 1999 08:54:59 -0400
From: "Bill Keenan" bkeenan@goodegg.com
Subject: Re: ** GL_INTERFACE/Journal Import question

I missed importing the descriptive flexfields without validation. Thank you very much!!!

Bill

Control GL date on Journal Import

Thomas Matthews wrote:

Hello,
If I load transactions into the GL interface for the same source, and category, for the month of April, each with a different accounting date, when I import these transactions Oracle seems to create one journal entry and uses the earliest of the accounting dates as my effective date.

For example.

Transaction 1 01-APR-99 $500
Transaction 2 02-APR-99 $600
Transaction 3 05-APR-99 $700

When the GL import (in Detail Posting) has been completed it creates one journal entry with an effective date of 01-APR-99, and it has 3 lines.

Because of the way Oracle handles this, it appears that I lose actual accounting date of the transaction.

Does anyone know if there is a way for me to retain that accounting date information?

Thanks, Tom

Date: Tue, 06 Apr 1999 06:35:27 +0100
From: jmcdonald@princesscruises.com (Jeanne R Mcdonald)
Subject: Re: GL: GL Import

Don't check the Create Summary Journals. Or you can use a different group_id for each date.

jeanne!

Journal Import with reference of Created_by

From: Bill Keenan [mailto:bkeenan@goodegg.com]
Sent: Wednesday, April 07, 1999 9:11 AM
Subject: GL Imported Journals "Created By" question

Hi again,
First of all, I have been asking many questions over the past few days, and have received many suggestions/comments/answers from many of you. I would like to thank you very much for your time and help.

I have successfully converted one journal batch/header/lines from GL v10.7SC to GL v11.2. The journal is almost exact, with the exception of a few minor fields (such as the date and request id appended to the batch name). I can live with minor details like that.

One request by the accounting department, however, is to be able to do a "Help - About this Record" and still see Created By "the original user". Currently, the journal is imported from the gl_interface table with the user_id in the created_by column, but Oracle automatically changes that to the user_id you are logged into when running the import.

If I am making any sense, my question is can I somehow get around this default and bring in all the journals with the created_by column being the original user who keyed the journal?

Again, thank you for all your help!!!

Bill Keenan

Rama Krishna wrote:

Hi Bill,
The only to make this happen is by customizing the journal import process, which I do not at all suggest.

In other words, not possible. Rama Krishna

Date: Wed, 07 Apr 1999 10:01:42 +0100
From: jmcdonald@princesscruises.com (Jeanne R Mcdonald)
Subject: Re: GL Imported Journals "Created By" question

I'm going to agree with Rama about not customizing the import process- but you might write a very simple one-time sql script that would go out and update the created by after the journal has been imported.

I'd put the id of the actual created by in a reference field on the new journals and then map back. I haven't thought a lot about this nor have I done it this way (really encouraging, huh) but it should work

What confuses me is that we combined instances and did essntially what you're talking about (but between two 10.7 instances) and our created by looks fine w/the actual created by and created date. It's the last updated by that shows the person who logged in to do the import)

jeanne!

Testing Journal Import

philip@aleytys.pc.my wrote:

Dear All,
I am currently testing my conversion scripts to bring over journals from Financials 9.4 to a test instance of Financials 10.7 SC via the GL open interface table all the way to posting the imported journals. I want to do several iterations so I need to know if it is possible to blow away the data and start with zero balances.

The tables I am looking at are GL_JE_BATCHES, GL_JE_HEADERS and GL_JE_LINES. I could TRUNCATE these tables after each test but it is of course very possible that this could cause data integrity problems. Alternatively I could write zeros into all the DR/CR fields via SQL.

Looking for some feedback even if it's only "Wotta stupid idea"

Philip

--- Jeanne R Mcdonald jmcdonald@princesscruises.com wrote:

It's actually very 'do-able' once you get over the magnitude of the suggestion - the four tables (I've included gl_balances since you mentioned posting) stand on their own. Truncate at will- but make sure you do all of them...

jeanne!

Date: Mon, 3 May 1999 10:20:05 -0700 (PDT)
From: Andy Schindler aschindl@yahoo.com
Subject: Re: GL: 10.7SC Testing Journal Import

Add GL_IMPORT_REFERENCES to your list if you have your journal source set to import references.

Andy Schindler NRC, Inc.

Date: Mon, 3 May 1999 15:23:13 -0400
From: "Joe Maliszewski" jmaliszewski@csi.com
Subject: RE: 10.7SC Testing Journal Import

One other thing to add to the others.
Update gl_sets_of _books
set last_opened_period_name null

Joe Maliszewski Consultant jmaliszewski@csi.com

Date: Mon, 03 May 1999 13:24:53 +0100
From: jmcdonald@princesscruises.com (Jeanne R Mcdonald)
Subject: Re: 10.7SC Testing Journal Import

Hi Joe-
Don't think you have to. We never did. (Probably would have thought more about it, though, if we were going to be posting months of history) But now that I think about it, wouldn't that put gl_sets_of _books in conflict with gl_period_statuses

jeanne!

Date: Mon, 3 May 1999 17:13:00 -0400
From: "Joe Maliszewski" jmaliszewski@csi.com
Subject: RE: 10.7SC Testing Journal Import

Not sure about that. It has been a few years since I did that kind of stuff. I do not recall touching gl_period_statuses but that does not mean I didn't. The first time I converted I pretty much screwed it up. So I refreshed the data this way. Instead of reloading the JE's I just reset them also. Something like setting the STATUS to "U" and VERIFIED to "N". That just saved a bunch of time because I didn't have to reimport the data and it was then available for posting. I did truncate GL_BALANCES though. As always, test, test, test and test some more.

Joe Maliszewski Consultant jmaliszewski@csi.com

philip@aleytys.pc.my wrote:

OK,
How about closing all the periods first? I hadn't thought of GL_SETS_OF_BOOKS. I guess I'll have to go through the TRM again.

Date: Tue, 04 May 1999 18:15:01 +0100
From: jmcdonald@princesscruises.com (Jeanne R Mcdonald)
Subject: Re: 10.7SC Testing Journal Import

I wouldn't. I think the only reason Joe suggested updating sets_of_books is that posting will be faster. But again the best advice of all is test, test, and test some more.

We did this very successfully truncating only the previously mentioned 4 tables (in fact, but then we didn't import references, still I thought only AP and AR posted to gl_import_references and all the other apps STILL posted references in to je_lines)

oh well- (again) test, test, test and then test some more (I still do and I've been doing this 7+ years)

jeanne!

Journal Import automation

Date: Mon, 12 Jul 1999 12:29:47 +1000
From: Jeanine_Bailey@COW.mav.asn.au
Subject: Journal Import automation

Can anyone tell me how to run Journal Import to Oracle GL automatically ie I need to run 7 imports daily from other systems and would like to run these after hours. Also need to select 'without validation' on the Journals/Import/Run screen.

Date: Mon, 12 Jul 1999 16:41:48 +1000
From: Sheila Cruickshank scruickshank@assist.com.au
Subject: RE: Journal Import automation

You can write a host program and register this program to run after hours. In the host program you can use CONCSUB to submit the Journal import or you can use fnd_request.submit_request. I usually use CONCSUB. I have included an example:-

The Journal Import will need to have an interface_run_id parameter parsed.

Here is one way to get the interface_run_id, by populating a request in GL_INTERFACE_CONTROL.

Insert into GL_INTERFACE_CONTROL

insert
into GL_INTERFACE_CONTROL
( je_source_name,
status,
set_of_books_id,
interface_run_id)
select
s.je_source_name,
'S',
&sob_to,
GL_JOURNAL_IMPORT_S.nextval
from
GL_JE_SOURCES s
where s.user_je_source_name = '&cash_source'

Once the shell script is used to call another program via CONCSUB, it will be necessary to capture the concurrent process id to check whether the submitted job has completed with 'SUCCESS' , 'WARNING' or 'ERROR'. This will be needed to determine the next step in the controlling shell script. This is achieved by capturing the standard output of the CONCSUB execution, for example,

conc_request`CONCSUB APPS/APPS $respid '"'$resp'"' $ora_user WAITN CONCURRENT SQLGL GLLEZL $interface_run_id $sob_to '"N"' '""' '""' '"N"' '"N"'` echo $conc_request

The third field of this captured output is the submitted request ID, and this can be used to check the table 'FND_CONCURRENT_REQUESTS" to obtain the status code.

conc_id`echo $conc_request | cut -f3 -d' '`

echo $conc_id

You could write one host to submit all seven jobs

Sheila

Date: Mon, 12 Jul 1999 09:41:08 -0400
From: Pat Burns pat.burns@sheridanc.on.ca
Subject: RE: Journal Import automation

A simpler method:

1) Set your personal profile option "Concurrent: Hold Requests" to Yes.
2) Submit your 7 jobs, they will be on hold on the concurrent manager queue.
3) Modify the "Resubmission" options of each of the 7 jobs to run each night.
4) Release the hold on each of the 7 jobs. They will run and then resubmit themselves each night.
5) Set your personal profile option "Concurrent: Hold Requests" to No.

Pat

GL Data Migration

From: Bill Keenan [SMTP:bkeenan@goodegg.com]
Sent: Thursday, April 01, 1999 1:27 PM
Subject: GL Data Migration Question!!!

Hi, We are currently upgrading from 10.7SC to 11.0, however we are changing the setup of the apps, so we are viewing this as a new install. We want to bring over all data from 10.7, including all Journal Batches, Headers, lines and G/L balances. We have identified 5 tables that we will need to copy from 10.7 to 11.0, those being GL_JE_BATCHES, GL_JE_JOURNALS, GL_JE_LINES, GL_BALANCES, and GL_CODE_COMBINATIONS. We have identified a few column changes in some of these tables and will be making the necessary adjustments to accomodate the 11.0 tables.

My concern is that there are other tables out there that we are not aware of that are hit when a journal is created and posted. Can anyone help me out and/or share their similar experiences with me.

One last bit of info, we have manually keyed in all segment values that were in 10.7 into 11.0, and the final goal is for the users to be able query up any G/L information (journals, balances, etc.) and get the exact same results as they would have in 10.7.

I appreciate any help, guidance, or comments.
Bill Keenan

Date: Thu, 1 Apr 1999 14:50:49 -0500
From: "Vallapareddy, Krishna" kvallapareddy@noblestar.com
Subject: RE: GL Data Migration Question!!!

Have you considered data that comes from subledgers like AR and AP? In that case you need to have those invoices back in the subledgers and run the GL interface.What about tables like GL_JE_HEADERS AND GL_JE_SOURCES?

regards, Krishna Vallapareddy Oracle Financials Consultant Noblestar Systems Corporation (703) 464-4000 ext 2508 or (800) 470-4668 ext 2508 http://www.noblestar.com

Date: Thu, 01 Apr 1999 15:20:14 -0500
From: Rebecca Enonchong rebeccae@iname.com
Subject: Re: GL Data Migration Question!!!

Bill, Since you have already added all your segment values, just add your sources, categories manually and bring in your data through the GL_INTERFACE. Turn dynamic insertion on and all your combinations will be created in GL_CODE_COMBINATIONS.

Post your journals in the new system and GL_BALANCES will be updated.
You should always stay away from copying tables. Indeed, there are quite a few tables that you are missing in your list. It is just best that you steer clear of that option and use the interface instead.

Rebecca Enonchong Senior Principal Consultant Edutech Computer Solutions, Inc.

Date: Thu, 1 Apr 1999 15:26:16 -0500
From: "Bill Keenan" bkeenan@goodegg.com
Cc: rebeccae@iname.com
Subject: Re: GL Data Migration Question!!!

Rebecca, Thank you for the advice. One problem I ran into when attempting to do it that way is that I could not load the Batch NAME in the GL Interface table. There is no column for the name, just the Batch ID. There may be other issues like this when I get to Journal headers. The users want to see all the journal batches, headers, etc. exactly like 10.7 (as if they were entered in 11.0).

Can you please point me in the right direction on this one.
Thank you very much. Bill Keenan

Date: Thu, 1 Apr 1999 13:34:16 -0800
From: Rama Krishna Rama.Krishna@hds.com
Subject: RE: GL Data Migration Question!!!

Hi Bill,
Reference1 is for the Batch Name
Reference2 is for the Batch Description
Reference4 is for the Journal Name
Reference5 is for the Journal Description
Reference10 is for the Line Description.
You need to map the data from the 10.7 with the above columns of the GL_INTERFACE table.
Rama Krishna Applications Consultant Fortuna Technologies Inc, Sunnyvale, CA

Date: Thu, 01 Apr 1999 15:44:49 -0600
From: Nancy Gaudette nancy.gaudette@esoftsolutions.com
Subject: Re: GL Data Migration Question!!!

Bill, The REFERENCE columns in the GL_INTERFACE table are used for Batch Name, Journal Name, Journal Line Description, etc., although the Journal Import process tacks on extra information at the end of some of the fields, such as the import request id, source name, actual flag.

Nancy Gaudette www.esoftsolutions.com

Date: Thu, 01 Apr 1999 13:56:34 +0100
From: jmcdonald@princesscruises.com (Jeanne R Mcdonald)
Subject: Re: GL Data Migration Question!!!

I'm sure you know this but I'll say it anyway: beware making any changes to code_combinations if you have other Oracle products since CCIDs are stored there also.

As for the GL tables- we saved lines, headers, batches and code_combinations when I had a client do a similar activity (actually we were combining instances (it's a long story)) and actually reposted to balances and had no problems. We translated ccids in the lines table (for the instance not being kept) to the actual segments and reloaded thru gl_interface. There are probably a lot easier ways but this did work.

Again, be careful about making changes jeanne!

Date: Thu, 01 Apr 1999 17:34:58 -0500
From: Rebecca Enonchong rebeccae@iname.com
Subject: Re: GL Data Migration Question!!!

Hi! Do not touch the Batch_ID column, that is system generated. The batch name is stored in the REFERENCE1 column. The Batch description goes in REFERENCE2.

Journal name is stored in REFERENCE4. The journal description is held in REFERENCE5.
Hope this will help!!
Rebecca Rebecca Enonchong Senior Principal Consultant Edutech Computer Solutions, Inc.