Yes, but you'll have to tell us what it is that needs to be validated. It might be of further help if you could provide the table structure of your staging and GL_INTERFACE tables.
Main Topics
Browse All TopicsCan anybody give me any pl/sql validation code for staging table before loading data from staging table to GL_INTERFACE table?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
you could try something like...
begin
select null into v_dummy from gl_interface where col1=x and col2 = y and rownum = 1;
v_record_exists := TRUE;
exception
when no_data_found then
v_record_exists := FALSE;
end;
but even better is to use MERGE statement to insert when missing or update if already there. If you are using 10gR2 or later, you don't have to include an update clause if you don't want to
Hi Swathiponna,
Following are the basic validations you can look for when you are uploading gl transactions from interface table
1. Are you trying to load the entires in batchmode.. ? if yes then your batch total must match.. ie. the entered debit must match with entered credit.
2. If you are simply loading individual entries then you have to check whether the code combination id or the code combination you have specified exists in GL_CODE_COMBINATIONS for the given Set of Books ID (found in GL_SETS_OF_BOOKS
3. Check whether any cross validation is put on use of code combinations.. (although the interface checks this while loading the interfaces into the table) but still if you want to put less load on the interface prog then you can use this validation in your code.
4. Check whether the GL period is open and defined in the base table.. if the gl period is closed then even the standard interface prog would error out. (GL_PERIOD_STATUSES) (GL_PERIOD_TYPES)
5. Check whether the account type is valid and exists and is enabled for the current responsibility.
6. If the transaction is in MRC, check whether a conversion rate is being defined in GL_DAILY_RATES.
7. For checking duplicate entries check the header information of the gl entry in GL_JE_HEADERS for the same header description, date and account or transaction type.
8. The same validation would matter if you are looking in GL_JE_LINES.
9. Most important validation is that header total must match with lines total.
The list goes on and on.. you have to co-ordinate with your functional consultant for GL or the business for their logic.
for even better clarity i would advice you to do a manual test entry from front end and see all validations..
I Hope i tried to answer most of your queries here.
Regards
Hi Swatiponna,
Sorry i went overboard with my explanation above..
but you can track with creation date and process flag which i normally do, as soon as i have sent the data to interface table i update the process flag in my staging table with appropriate flag so that next time i check with the combination of current sysdate which gets populated in the creation date in my staging table with the process flag..
this way i avoid sending the same records up to the interface table.
hope this works.
and sorry for the extra information i posted above ;) silly isnt it..
Regards
SwathiPonna,
I assumed this question was answered 6 weeks ago based on http:#25555803
if you need further assistance please reply to the other contributors.
if not, please close the question
Business Accounts
Answer for Membership
by: sdstuberPosted on 2009-10-12 at 10:06:29ID: 25552907
you'll need to define what your validation rules are