PL/SQL code for validation

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.

Which version of E-Business Suite do you have?  (The structure of the GL_INTERFACE table may be different in different versions of the product.)

I am using 11i. Before loading data from staging table to GL_INTERFACE table, I want to check whether particular record exists or not, if it doesn't exists then I want to load data into GL_INTERFACE table.
how can I do this validation using pl/sql code?

Depending on how many rows you will be loading, and on how many rows the GL_INTERFACE table contains now (or will contain near the end of your load) this process may go much faster if you create on index on whatever column(s) you use to determine if the record already exists in the table, or not.

sdstuber,
Thanks for your pl/sql code.

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

merge into gl_interface g
using staging_table s
on (g.keyfield = s.keyfield)
when not matched then
insert into g(col1, col2, etc.) values
(s.col1,s.col2, s.etc.);

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