GL Interface API to Import Journals

Please find below an API for creating Journals in Oracle General Ledger ( GL ) . I had developed this API in year 2002, and I have used this for three of my clients.

The idea behind this API is that you do not need to program for:-
1. Inserts to GL Interface ( GL_INTERFACE )
2. The validations required for Oracle GL Import
3. Submission of GL Import

Features of this Oracle GL Interface API are:-
1. It will ensure that the credit equals debit
2. It will submit GL Import when parameter p_submit_gl_interface is Y

For the source code for these API's click on the links below:-
GL Import API Package Header
GL Import API Package Body

Usage of this API
DECLARE
BEGIN
xx_import_xfer_gl_iface_api.transfer_to_gl
(p_sob_id => --Set of Books Id
,p_batch_header_id => --header id of your feed
,p_trx_header_id => --sub header id of your feed
--or leave this to be the same as above
,p_trx_line_id => --line id of your feed
,p_date => --effective date of the journal
,p_currency_code => --currency code of the journal
,p_amount => --journal line amount
,p_dr_account => --GL CCID of account to be credited
,p_cr_account => --GL CCID of account to be debited
,p_journal_name => --Journal Name to be created
,p_header_description => --Journal Description to be created
,p_line_description => --Journal Line descriptionn
,p_success_flag => b_success_flag--boolean returned to show if errored
,p_first_record_in_batch => v_first_record_in_batch --pass Y for First Record in batch
,p_submit_gl_interface => v_is_last_trx_line --Pass Y submit for last line of batch
--when Y it will submit the GL Import
,p_gl_user_je_source_name => --user_je_source_name
,p_gl_user_je_category_name => --user_je_category_name
,p_trx_number => --trx_number
);
END;

Set as favorite

Bookmark

Email This

Comments (26)

Subscribe to this comment's feed

...
written by Rohit Naphade , February 16, 2007

Hello Anil,
Please accept my hearty congratulations for developing a very very good and useful site. Not a single day has passed since I cam to know abt ur site without visiting it. I have one question.What are the validations that must be done during journal Import?.

Regards
RN

report abuse

vote down

vote up

Votes: -1

...
written by jotheeskumarnair , May 05, 2007

Hi Anil

Thanks for creating such a wonderful platform to share the knowledge. Anil can use post some documents about Trnasfer the data from the subledgers to GL and What all are the things to be taken care while doing this.?

Regards

Jothees Kumar Nair

report abuse

vote down

vote up

Votes: +1

...
written by Girish Bajaj , July 04, 2007

Hi Anil,

First of thanks alot for such a wonderful pool of knowledge and help.

Anil I have the requirement from the client for the Drill Down report for all the Control Accounts, similar to the Account Inquiry Form where in you can drill down to the last source, are there any reports available in Oracle GL Module, I dont know as I am a Supply Chain Consultant, but would appreciate if you can help me out with this.

There is also a requirement for showing the Vendorwise Balances for all the vendors for a given period, which will be drill down of the Total GL Balance of that Accounts.

So there are two different requirements, Kindly update me how can I achieve this.

Thanking you in anticipation.

Regards,
Girish Bajaj.

report abuse

vote down

vote up

Votes: +0

...
written by Raja Ramesh , July 07, 2007

Hi Anil

U done a good job for developing a gl interface API. But iam not getting how i have to use this one.

Please explain me briefly. what are the parameters i have to pass to this api.

Thanks in Advance
Raja Ramesh

report abuse

vote down

vote up

Votes: +0

...
written by Anil Passi , July 08, 2007

Hi Raja,

You have to get hold of the package header and packagebody in scripts4oracle website [link provided above]

Next, you need to use parameters similar to as shown for xx_import_xfer_gl_iface_api.transfer_to_gl

In the process, feel free to modify the code as you wish. Also feel free to add new parameters to the given code, as per your business requirement.

Thanks,
Anil Passi

report abuse

vote down

vote up

Votes: +1

...
written by priya , August 03, 2007

Hi
Yor work is really appreciable.
Dou you have any Technical design documnet for Migraton of legacy assets data into
oracle fixed assets
Regarsd

report abuse

vote down

vote up

Votes: +0

...
written by Anil Passi , August 03, 2007

Hi Priya

I am afraid I dont have the piece of code that you are looking for.

THanks,
Anil

report abuse

vote down

vote up

Votes: -1

...
written by aneel , August 27, 2007

Hi Anil,

Can you please write some more articles in Oracle Projects and integration with other modules. I am excited to publish some of the papers on Oracle HRMS, please let me know the process.

Thanks,
Aneel

report abuse

vote down

vote up

Votes: -1

...
written by Krishnab , August 28, 2007

How You Will display the key flexifields in your report?

report abuse

vote down

vote up

Votes: +0

xma gateway code
written by krishna , November 14, 2007

pls provide xml gateway code

report abuse

vote down

vote up

Votes: +0

Kindly send me GL, AP and AR Pratice doucments
written by sravanapatasatishkumar , November 27, 2007

Hi Anil,

My satish is satish kumar and i have recently completed Oracle Financials.kindly send me some Pratice documents on GL, AP, AR and Purchasing.

Regards
S.Satish Kumar

report abuse

vote down

vote up

Votes: +0

Small Question in g_debug_header_context
written by Praveen , March 27, 2008

Hi Anil,

Praveen Here. Very Very Nice Artical. Good Job.

In the Package Body I have Seen the below Declaration.

xx_import_xfer_gl_iface_api is the Package Name. Can you Please let me know what is xx_gl_feed.plsql.

g_debug_header_context CONSTANT VARCHAR2(80) := 'xx_gl_feed.plsql.xx_import_xfer_gl_iface_api.';

And Why Do we need Batch Prefix.

Thanks
Praveen

report abuse

vote down

vote up

Votes: +0

Need Clarification in GL Import API Package Header
written by Praveen , March 27, 2008

Hi Anil,

And also I have Some Other Questions in the pakage Header "GL Import API Package Header". Can you Please clarify my Doubts.

1) XX_OVERRIDE_GL_JE_SOURCE_NAME
2) XX_OVERRIDE_JE_CATEGORY_NAME
3) XX_OVERRIDE_GL_RATE_CONVERSION_TYPE
4) XX_OVERRIDE_GL_BATCH_PREFIX

Are the above once are profile Options. If So How to check if there are any Profile Options in our company. I mean to say What SQL Query do I need to Use
to find these profile Options.

5) What is xx_gl_feed.plsql in header Context?

Thanks Anil,
Praveen.

report abuse

vote down

vote up

Votes: +1

...
written by Anil Passi , March 27, 2008

Hi Praveen,

In answer to your questions

1. Instead of xx_gl_feed% you can use any naming convention for debug prefix.
All you have to make sure, if you wish to debug this GL Interface program using FND Logging, then set the profile
"FND: Debug Log Module" to xx% or xx_gl_feed%

2. These profile options are used to overcome the hardcoding in the program.
Although some variables are hard-coded, but I override the hardcoded value if profile option XX_OVERRIDE_GL_JE_SOURCE_NAME is defined and assigned a value at respective level.
For example, for below code, if the profile XX_OVERRIDE_GL_JE_SOURCE_NAME does not exist, then Manual will be assigned to the respective g_gl_user_je_source_name variable.

g_gl_user_je_source_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_JE_SOURCE_NAME')
,'Manual');

Thanks,
Anil passi

report abuse

vote down

vote up

Votes: +0

Need Clarification in GL Imoport API Package Header
written by Praveen , March 27, 2008

Hi Anil,

One More Question.

We are using Employee Number Only to Populate in Reference1. If we Dont want that, can we remove it or is that any type of validation.

Thanks
Praveen

report abuse

vote down

vote up

Votes: +0

...
written by Anil Passi , March 27, 2008

Hi Paveen,

You are free to enter whatever you wish to enter into that field.
In this example, I am using this reference column to store id/descriptions of source transactions/ids and also the Emp Number of the person that initiated this transaciton in source system.

Thanks,
Anil Passi

report abuse

vote down

vote up

Votes: +0

...
written by Praveen , March 28, 2008

Anil.

I got Your Point on GL API. Thanks a Lot. This Document helped me a lot. Get back to you If I have anything else. Thanks Again.

Client wants to Implement Revalueation. Can you Please Publish Revalueation Document (if you Have any) which clearly Describes
Step By Step Process Of Revaluation.

Thanks
Praveen.

report abuse

vote down

vote up

Votes: +0

Debugging Standard API.
written by Praveen , April 15, 2008

Hi Anil,

We are Using fnd_flex_keyval.validate_segs to create Combination. Its Working Fine some Times and some Times it is not able to create a Combination.
The Same Combination If I manually Create It is Allowing us so, there are no Cross Validation Rule for that combination.

So, Now I want exactly at what point the error Occurs. But I dont know How to Debug Procedure. Can you Please Give me the sample Code which uses the
Standard API and Debugging Procedures.

Thanks & Regards,
Praveen

report abuse

vote down

vote up

Votes: +0

Debugging fnd_flex_keyval.validate_segs
written by Praveen , April 21, 2008

Hi Anil,

Can you Please Let me know How to debug Oracle Supplied Standard API from coding. I want to Debug fnd_flex_keyval.validate_segs.

Please let me know. Your are the only hope for me.

Regards,
Praveen

report abuse

vote down

vote up

Votes: +0

Debugging technique
written by Anil Passi- , April 21, 2008

Hi Praveen

I had to debug exactly the same API some years ago.
those days we did not have fnd logging.
So i created a custom autonomous transaction procedure with commit.

In this api that you mentioned, there aren't many debug statements.
reason is for efficiency Oracle has kept debug stmts to minimal.
hence you can edit that API[only on development instance please] and add your
debug messages.

Thanks,
Anil Passi

report abuse

vote down

vote up

Votes: +0

Oracle General Ledger Web ADI Custom Validations.
written by Praveen , May 02, 2008

Anil,

Do you Have any Idea of How to Add custom Validations to the Web ADI. If Yes Can you Please send us the Information.

A Specific Segment Value Should not be entered in Web ADI or If entered Upload Should fail and give a message for so and so reason this segment should not Enter.

Regards,
Praveen.

report abuse

vote down

vote up

Votes: +0

GL Trial Balance Report
written by Saritha Rani , June 28, 2008

Hi Anil,

I have done GL Conversion in R12 .

User ran GL Trial balance report for testing but we found the amounts are twice the actual figures.

The report that we ran is "Trial Balance - Additional Segment Detail".

IS this the right report for testing?

And what is the diff between Ap trial balance and GL trial Balance?

Which trial balance report should we run for testing?

Is this a bug in Oracle R12..Any idea???

report abuse

vote down

vote up

Votes: +0

...
written by kishor nagbhire , December 09, 2008

hi anil i'm a new user of ur site
can u help me on that topic
i want record in GL

'''''' undefined accounting periods in the current accounting calendar '''''''''''

help me
thanks

report abuse

vote down

vote up

Votes: +0

api
written by geetar , April 23, 2009

hi,
can i know steps to implement file upload api using forms in purchaisng

report abuse

vote down

vote up

Votes: +0

GL drilldown report
written by Srinisusen1 , July 08, 2009

Hi Anil,

Do we have any Oracle standard report which fetches all the details which is fetched during GL drilldown in 11.5.10.2.

Thanks
Srini

report abuse

vote down

vote up

Votes: +0

PTO options in Sales order
written by Srinisusen1 , July 08, 2009

I have set the Customer order enabled item attribute to 'NO',but still i was able to select the item in the configurator (in the options list).Pls advice how to remove the same.

Thanks
Srini

report abuse

vote down

vote up

Votes: +0

Write comment

Name

Title

Comment