Data Movement Entities
Generally data conversion/interfaces include following entities
- Legacy System (Oracle/Other database e.g. DB2/File System)
- Oracle Apps custom staging tables
- Shell scripts to read data files and load them in custom staging tables using SQL*Loader utility
- Oracle Apps custom data validation programs
- Oracle seeded open interface tables (e.g. MTL_TRANSACTIONS_INTERFACE)
- Oracle seeded APIs (e.g. Process Order API)
- Oracle Apps Seeded Import Program (e.g. Item Import, BOM Import)
Legacy System
There can be various types of legacy systems which can be based on databases like Oracle, DB2 etc or based on file systems. Generally it is better to use files extracted from legacy system while development of data conversion program as data volume is very high. DB Links can be used for connecting to other databases while developing interfaces.
- ASCII files :- As a practice most of implementations have ASCII files as an input to oracle. These files can be comma/pipe/tab delimited files with certain file format. If excel files are received them they need to be converted to CSV file manually.
- XML files :-Now days XML (Extended Markup Language) is becoming the standard world wide most of the system accept and understand XML format. Source system can generate XML files based on format defined by DTD (Document Type Definition) or XSD (Xml Schema Definition). DTD/XSD defines the structure/tags present in XML document.
- Legacy Outbound staging tables :- Legacy system/home grown system can be based on databases like Oracle, DB2 etc. Now days, there are drivers available in oracle to connect to other non-oracle databases using DB Links. If the data volume is less then it is better to use DB Links that ASCII file so that we can get read of FTP/SFTP failure issues. Also it is easier to update database tables directly to update the current status of record (Success/Error etc) instead of exchanging files
Oracle Apps 11i E-Business Suite Components
Oracle Apps Custom staging tables :- As a thumb rule the structure of custom staging table should be same as the source data file or source legacy database table. There should be presence of columns like Interface_id, Batch_ID, Process_Flag, Error_Message, WHO columns and Extended WHO columns in the table structure.
Shell Script with SQL*Loader:- Shell script (host) type of concurrent program should be used to load data files in custom staging tables instead of SQL*Loader executable type. Shell script internally uses SQL*Loader command line utility to load data and archives the data files. With usage of shell script the data file path is not hard coded, archiving monitoring bad file is easily achievable which is not the case with SQL*Loader executable type. As a rule there should not be hard coding in shell scripts e.g. directory paths, username, password etc.Inbound data files can be placed in directories like $XXINV_TOP/datafiles/in.
Shell script should read these files and load in custom tables and archive data files in $XXINV_TOP/datafiles/in/archive with date timestamp appended to file name. SQL*Loader log files and bad files can reside in $XXINV_TOP/datafiles/in/log and $XXINV_TOP/datafiles/in/bad folders.
This shell script should also read the bad files generated by SQL*Loader and complete the concurrent program in Success/Warning/Error Status. Sample shell script is given in annexure.
This shell script has following sections
- Extract Apps username and password
- Initialize file names (data, log, bad, archive files)
- Check existence of file
- Run SQL*Loader
- Archive data files
- Check bad files
Shell Script with Java based XML Parser:- Shell script (host) type of concurrent program should be used to call java based/PLSQL XML parser. The directory structure, archiving, logging related rules remain the same mentioned above. In this case we have to develop a custom Java or PLSQL based XML parser which can parse the input xml files and load in custom staging table. PLSQL based parsers user UTL_FILE package to perform file read operations. Building a java based XML parser is a topic in itself, which can be considered as out of scope for this white paper.
PLSQL Package for reading Legacy outbound tables:- This can be a plsql package which can read legacy database tables using database links and writes the inbound custom staging tables in Oracle Apps 11i instance.
Oracle Apps Custom Lookup Table:- This table should be used for storing the lookup values i.e. mapping legacy system values with oracle values. This generally store information like Unit of Measure etc. which can be different on Oracle and Legacy system. The table structure should have columns like Lookup_Type, Language, Legacy System, Legacy System Value, Oracle_Value, effectivity_date, end_date and WHO columns.
PLSQL package for data validation and mapping:- This PLSQL package should perform following activities
- Validate data in custom tables
- Perform data mapping activities using lookup table
- Update the custom staging table Process_Flag column with following values
1 - PENDING
3 - ERROR
7 - PROCESSED/SUCCESS
5 --VALIDATION SUCCESS
2 - PICKED FOR VALIDATION
PLSQL package to write data in Oracle Open Interface tables:- This PLSQL package should read all valid records (Process_Flag=5) and insert data in Oracle Seeded interface table and mark Process_Flag=7
Oracle Open Interface tables:- These are the inbound interface tables provided by oracle for various base tables in oracle applications.
Oracle Open Interface Concurrent Programs:- These are oracle supplied import programs which read oracle open interface tables, validate the data and load oracle apps base tables.
Oracle Supplied Public APIs- Generally for online processing (when batch processing is not acceptable) public APIs are used to load oracle base tables. In this form of interfaces, open interface tables are not used. Public PLSQL APIs accept plsql table type of input parameters and APIs perform validation and updates of oracle base tables.
Other important components:-
Lookups and Profiles :- Custom Profile Options can be used for following purposes
- Enable/Disable Debug Mode
- Store Email addresses to be sent by Monitoring Routines
- Store hard coded values needed by interfaces
- Store file paths in case of outbound interfaces
FND_LOOKUPS (Common Lookups) can be used for following purposes
- Storing mappings if custom lookup table is not utilized
- Store hard coded values
Custom Lookup Table -In case of some mapping between Oracle and Legacy systems a custom lookup table can be designed along with a custom form which will help data entry for mappings
Custom Form to update custom staging tables :-This form is used for performing UPDATE/DELETE operations on custom staging tables. This is used to correct any data errors in custom staging tables and re-submit data for processing. This helps avoiding manual correction of data files. This form can be a simple data entry screen which selects custom table from list of value and displays the data in tabular format on a stacked canvas and allows data updates.
Monitoring:- Monitoring/Error Reporting are an important aspect of data conversion or interface development. There should a notification system to notify users about the issue or problem. Monitoring can be achieved in multiple ways like Alerts, Workflow, and Error Reporting UNIX Emails etc.
Alert based monitoring:- This can be developed using a custom table with columns which are generally required for an email e.g. SUBJECT, TO_EMAIL, EMAIL_MESSAGE, PROCESS_STATUS, INSTANCE and WHO Columns. This custom table can have an event alert (INSERT) which read new records and send out an email and update PROCESS_STATUS column value for current record to 'SENT'
Error Reporting using UNIX Emails :- To build this functionality additional efforts are needed to populate an error table giving more details of the problem, build simple reports on it and submit these report concurrent programs at the end of data conversion routine/interfaces and email the output file to respective users.
Summary
To summarize the data conversion and interface development strategies, one should always have about components like custom staging tables, shell scripts, SQL*Loader control files, validation programs and monitoring in the design. There can be short cuts in data conversion like instead of shell scripts using directly SQL*Loader executable types in concurrent programs, which should definitely avoided because these short cuts involve lot of hard coding also maintenance and reusability of components cannot be achieved. Monitoring and error report is generally ignored considering it as overhead but it helps in long run for debugging and maintenance.
Monitoring and error report is generally ignored considering it as overhead but it helps in long run for debugging and maintenance
Comments
Co uld you please add commands used to execute the script in unix .
Thanks and regards,
Sirees ha.
was curious about your situation; we have developed some nice procedures and we
are looking to swap solutions with other folks, please shoot me an e-mail if interested.
Have a look at my wweb blog :: rs 2007: https://probemas.com/blog/how-much-is-osrs-gold-worth-today
Feel free tto visit my web sitee :: buy OSRS gp (Probemas.com: https://Probemas.com/blog/how-much-is-osrs-gold-worth-today)
thi thhis web page conations really nice funny information too.
Review myy web-site; OSRS: https://probemas.com/blog/how-much-is-osrs-gold-worth-today
Did you create this website yourself or didd you hire someone to
do it for you? Pllz answer bak as I'm lookihg to create my own bog
andd would like to know where u got this from.
kudos
Here is my blog poswt buy RuneScape gold (https://probemas.com: https://probemas.com/blog/how-much-is-osrs-gold-worth-today)
blogs use WYSIWYG editors or if you have osrs gold to usd - probemas.com: https://probemas.com/blog/how-much-is-osrs-gold-worth-today, manually
code with HTML. I'm starting a blog soon but have no coding knowledge so I wanted to get advice from someone with
experience. Any help would be enormously appreciated!
before but after reading through some of the post I realized it's neew to
me. Anyways, I'm definitely glad I found it and I'll be bookmarking and checking back frequently!
Here is my wweb page; rs2007: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
He was entirely right. This post truly made my day. You can not imayine simply how much time I
had spent for this info! Thanks!
Also visit my website :: RS3 gold - https://probemas.com: https://probemas.com/buy-rs3-gold,
might state. This is the very firet time I frequented your web page and so far?
I surprised with the research yyou maade osrs gold
to usd (probemas.com: https://probemas.com/blog/how-much-is-osrs-gold-worth-today) make
this particular put up amazing. Great job!
I'll bookmark your blog and check again here frequently.
I'm reasonably sure I will bbe told many new stuff right here!
Bestt of luck for the next!
Feel free to visit my ebsite ... RS 2007: https://Probemas.com/blog/earn-osrs-bonds-fast
by these.
my web site RuneScape 2007: https://techpages.net/10-best-osrs-minigames-you-should-play/
this weblog, thanks admin of this web site.
Stop by myy page 2007 RuneScape: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
for audio songs existing at this web site is genuinely excellent.
my webpage - Tyras Camp
OSRS: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
Your web site offered uss with valuable information to
work on. You have done a formidable job and our whole community will be
thankful to you.
Here is my page OSRS: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
if yyou could write a litte mre on this subject? I'd be very thankful if you could elaborate a little bit more.
Appreciate it!
my web-site ... Ectofunctus OSRS (Sherlene: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/)
page annd aat the moment this time I am visiting this site and reading very informative
content at this time.
Also visit mmy web site Arandar OSRS: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
for your post. They're very convincing and can definitely work.
Still, the posts are very quiock for beginners. Couldd you please prolong them a bit from subsequent time?
Thank you for the post.
Take a look at my web-site Runescape gold: https://probemas.com/buy-rs3-gold
am getting experience everyy day by reading thes goood articles or reviews.
My blog buy RuneScape 3
gold: https://Probemas.com/buy-rs3-gold
I'm pllanning to start my own blog soon but I'm a little lost on everything.
Would you advise starting with a free platform like Wordpress or ggo forr a paid option? There are so
many choices out there that I'm completely overwhelmed ..
Any ideas? Thanks!
my web blog; buy RuneScape bonds: https://probemas.com/blog/earn-osrs-bonds-fast
The sketch is attractive, your authored material stylish.
nonetheless, you command get got an impatience over that you wish be delivering the following.
unwell unquestionably come further formerly again as exactly tthe same nearly very often inside case you shield
this increase.
Feel free to surf to my web-site; RuneScape 2007: https://probemas.com/blog/earn-osrs-bonds-fast
Look advanced tto far added agreeable from you! However,
how could we communicate?
my web site ... Elf Camp OSRS (webmobistar.co m: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/)
the courage to go ahead and give you a shout out froom Kingwood
Tx! Just wanted too mention keep up the excellent work!
Here is mmy blog - osrs: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
a new project in a commnity in the same niche.
Your blog provided us beneficual information to work on. Yoou have done
a wonderful job!
Alsso visit my blog ... rs2007: https://probemas.com/osrs-questing-services
what you're talking about! Bookmarked. Please also visit my site =).
We can have a link trade arrangement between us
Also vidit my web blog; Guide To
RuneScape: https://Thedailynewyorknews.com/osrs-barrows-guide-for-beginners/
youu know a few of tthe images aren't loading properly.
I'm not sure why but I think its a linking issue. I've
tried it in two diffetent intternet browsers and both show the same results.
My blog: runescape unban guide (Probemas.com: https://Probemas.com/blog/osrs-unban-guide)
I will be waiting for your next write ups thank
you once again.
my homepage rs2007: https://webmobistar.com/most-profitable-osrs-bosses-for-farming-gold/
Bookmarked. Kindly additionally visit my site =). We may have a hyperlink exchange contract among us
Haave a look at my web-site :: Old School
RS: https://probemas.com/blog/osrs-unban-guide
He used to be entirely right. This post ruly madse my day.
You cann't believe just how much time I had sppent for this
info! Thank you!
Heere is mmy site: RS 2007: https://probemas.com/blog/osrs-unban-guide
Did you make this website yourself or did you hire someone to do it for
you? Plz reply ass I'm looking to desiyn my own blkog and would like to find out
wyere u got this from. kudos
my blog :: RuneScape quests (probemas.com: https://probemas.com/osrs-questing-services)
as no one else understand such precise about my difficulty.You' re amazing!
Thanks!
entirely explained, keep it up all the time.
to me. Thank you
your authored material stylish. nonetheless, you command get got an shakiness over that you want
be delivering the following. sick no doubt come more previously again since precisely the similar just about a lot incessantly inside case you protect this
increase.
arguments aand telling all on the topic of that.
boog is in fact amazing.
It was helpful. Keep on posting!
better sex
at this place, I am genuinely enjoying by these.
Feel free to visit my website مسك عود العود: https://alezzoud.com/
RSS feed for comments to this post