Enterprise Software Development

EnterpriseOne SQL Samples

Here are some JDE EnterpriseOne (OneWorld) SQL examples for extrating various data.

You will need some tools

General Ledger

select * from F0911usually you will want to select only posted records, do this by adding a

where glpost='P'

to the end of the statement

Purchasing Headers / Subcontract Headers

select * from f4301

Purchasing Detail / Subcontract Detail

select * from f4311

usually you will want to select only purchase order or subcontracts that are in a ‘printed’ state.. youll need to see what status code your system is setup for printed status po’s here is an example of ours

select * from f4311 where pdnxtr >= '440'

Accounts Payable

select * from f0411

Address Book

select * from f0101

Sometimes I laugh, and don’t know why

http://www.theregister.com/2006/03/09/apple_tv/

I love google’s stuff, but leave it to the register to put everything in perspective

I especially like this line
“the Google Video Store looks like it was designed by a hemorrhaging five-year-old with a predilection for the small box of crayons and bad code.”

Converting Julian Date to .NET DateTime

Want to convert a Julian Date to a .NET DateTime? Heres an easy (C#) way to do that

///

/// Will Convert a 7 digit Julian Date to a .NET DateTime.
/// 7 digit Julian dates are common in some legacy applications
/// such as JDE World, OneWorld, EnterpriseOne. You can easily make this
/// convert 8 digit julians (more common) just by removing the line that adds
/// 1900000 to the input date.
///
public static DateTime JulianToDateTime(int julianDate)
{
   int RealJulian = julianDate + 1900000;
   int Year = Convert.ToInt32(RealJulian.ToString().Substring(0,4));
   int DoY = Convert.ToInt32(RealJulian.ToString().Substring(4));
   DateTime dtOut = new DateTime(Year,1,1);
   return dtOut.AddDays(DoY-1);
}

Converting Videos For Tivo ToGo using FFMPEG

In order to use the Tivo ToGo (galleon calls this the GoBack) feature, your video files not only need to be mpeg files, but they need to meet DVD standards. Here is what tivo recommends. Note: you may be able to encode your files in AC3 even if you are on a standalone tivo (not just the DVD burner tivo’s).

I found a really easy way (and free) to integrate the conversion into your Windows Explorer.

Software Requirements:

FFMPEG (you can download CVS windows binaries here and here) - Check out the book A Practical Guide to Video and Audio Compression: From Sprockets and Rasters to Macro Blocks for more tips on using FFMPEG

Tivo Desktop (or Galleon, which is what I use)

1. Extract FFMPEG to a folder (like c:\cmdutils)
2. Either create a folder for moviesForTivo or edit the next line so that the output files go into the standard c:\tivo recordings directory
3. start notepad and paste this text into it

   c:\cmdutils\ffmpeg.exe -i %1 -target ntsc-dvd -b 2500 -s 352x480 -hq -ab 160 "c:\data\MoviesForTivo\%~n1.mpg"

Dissecting the actual command line here for FFMPEG

ffmpeg.exe -i %1 -target ntsc-dvd -b 2500 -s 352x480 -hq -ab 160 "f:\data\MoviesForTivo\%~n1.mpg"

The %1 is the first parameter to the batch file. (for example if your batch file is named movToGo.cmd… the call to it would be movToGo fileName.avi

then we see -target ntsc-dvd : this is what presets all the codecs to be DVD compatable, but this will default to 720×480 and 8000 bitrate, so you can change that (like I did with the next arguments)

-b 2500 : bitrate 2500 (this will encode pretty fast as it is a pretty low bitrate, tivo recommends 4000 as a high quality setting, although most DVDs are between 5000-8000)

-s 352×480 : picture size 352×480 (see the specs on the tivo site, there are several different sizes you can use, I use this because a Tivo High Quality setting results in a video file of this size)

-hq : High Quality

-ab 160 : Audio-Bitrate 160 (instead of the standard 448)

then the last command is the output file, to see the modifiers (like the one i am using here %~n1) go to a command prompt and type “help for” a few screens down talk about modifiers to the %I variable…. this is applicable to command arguments too…. this is the only place I have found documentation on these extended command line options for the windows nt command shell.

This command will produce a MPEG-2 file that is encoded with AC3 as the audio codec (this works on my standalone tivo)

Then all you have to do is associate this to your .AVI’s and .MOV’s and anything else you want to be able to convert from.

RSS for Corporate Data / Intranets

A RSS Revelation

While I was rewriting a small web application for work (a phone book application called peoplefinder that will also show you on map, where the persons cube is located), a revelation came to me. Why not create a RSS feed for the basic phone, name, department, location information… With the link pointing to the map page!

We also are testing dotNetNuke as an intranet portal server. So each department has a ‘home page’, along with anything else that helps other departments interact with them, like forms, org charts and a department employee list. So rather than use the standard Text/HTML block, we use the News Feed (RSS) block… Walla! Now we have a department employee list that gets automatically updated with the peopleFinder database that the receptionist updates.

A further Data Warehouse / Data Aggregation Idea

Using RSS in the workplace, with your custom or even off the shelf databases, by creating you own RSS feeds. You can provide the users customized live data pages. You might even call them dashboards.

So RSS is not just for external uses like blogs and news. It is exactly what it stands for “Really Simple Syndication” … or you can think of it as a generalized way to look dissimilar data.

The data aggregation possibilities are endless. Imagine this; You work for a construction company, your CFO cares most about the data contained in; 3 financial reports from your ERP (JDE, Oracle, SAP), 2 project status reports from all the active construction projects, a phone list, his email inbox, his Outlook calendar, and the wall street journal headlines…. Well by using RSS queries to get the data he really wants from all those systems, and using the external feed from wall street journal… (in addition if you wanted the Outlook data you would probably need to compiled all of this into an Outlook Today Template)… So you setup a simple webpage that pulls the data from all these feeds, and displays them to him… or he just uses a RSS news aggregator to view it.

You can see that this is really a simple way to get data organized for people, thus allowing for a productive enjoyable intranet experience.

News Aggregators that I have personally used

Google Personalized home page http://www.google.com/ic - I Highly recommend this one especially if you use gmail, but even if you do not, this is a great, simple, intuitive page that allows you to add custom RSS feeds to it.

Firefox Browser - This works great, puts the articles in as active bookmarks

The Reality of QAM HDTV Recording

I recently procured a FusionHDTV 5 Lite. And last night recorded my very first episode of the new seas0n of Lost!

Here’s the hardware setup

• Pentium 4 1.2GHz
• 1 GB RAM
• 80GB dedicated 7200RPM ATA hard drive
• FusionHDTV 5 Lite!

Software Used

• FusionHDTV recording software and scheduler
• Womble MPEG VCR - for commercial cutting
• FFMPEG (compiled for windows) for conversion to DVD (or whatever you want!)

The video source was a Comcast (seattle) cable feed. This FusionHDTV card can decode clear QAM signal on the cable, AND from what I am told, it is one of the few cards that can do this.

The Result

It worked beautifully!!!
• 8GB of MPEG data from the 1 hour show
• I then chopped the commercials out with MPEG VCR, 10 mins and at 5GB now
• Then I converted the file to a NTSC-DVD format with Ffmpeg, 3.5 hours and 1.2GB !!! The re-encoding time will drop if you throw a burlier processor at it… or from what i am reading now the FusionHDTV 5 GOLD card has a MPEG 2 ENCODER on it and the software that is included with it can re-encode the file, thus eliminated the need for ffmpeg… I have NOT confirmed this, and I am unsure of the ease of use to get it into an acceptable DVD format.

command line used for ffmpeg conversion - HDTV transport stream (TP) to NTSC-DVD program stream (PS)
ffmpeg.exe -i lost.tp -target ntsc-dvd lost.mpg

m0n0wall

After rolling my own firewall / internet gateway / SMB media server / torrent machine. I decided to give m0n0wall a try out.

pretty darn slick if you ask me. It was a breeze to setup, the network auto-detection is a nice feature, and the SVG based traffic graph is cool eye candy…

In fact I am now in the market for a small fanless low powered PC… top on my canidate list is a VIA CL6000, IDE -> CF adapter, no floppy, no cdrom, no harddrive … this is probably overkill for strictly a firewall router. but who knows I could always recommission it as a set top box… plus were only talking about around 300$ total, and it is totally silent!!

What happened to Code Genie?

What in the world happened to Code-Genie ?

I finially love a code editor and they go AWOL ?

Quest 2005

I just attended Quest 2005 at the Gaylord Texan resort in Grapevine Texas. This is a user conference for JDE OneWorld and World users (now known as EnterpriseOne). There is nothing really new here since PeopleSoft Connect 2004, except that EnterpriseOne 8.11 was released. Oracle extended the support for OneWorld XE until 2007. And Oracle was spouting off about Project Fusion, which will be released in 2013. This is going to be a composition of all the software products: Oracle Financials, PeopleSoft Enterprise, EnterpriseOne, and World. It will have strong JAVA support

The Quest 2005 Website is here www.questdirect.org

Instant Messengers with Translation built in

Wouldn’t it be great to have Instant Messengers with built-in translation?

This question kept me up all night last night. So this morning I decided to look for a messenger or utility to do this. Guess what I came up with nothing.

I was thinking why is that? Is it because the majority of people using Instant Messengers are friends talking to each other? So if they were already friends wouldn’t they already speak the same language? Also, what about the accuracy? Word for word a programmatic translation is accurate, but it can often have different meanings and connotations.

Never the less, I want a utility or messenger to have this ability.

So I set out to program a C# Microsoft .NET Windows forms application to post a paragraph to Altavista’s babelfish, then to return the results in another text box. I did this for a testbed prototype to see how easy it is. Turns out, it was really easy! (hip-hip-wooray! for C# and .NET)

After messing with the WebClient library and a few UI niceties. I had a working prototype translation program, using babelfish.altavista.com for the translation.

I then started out on the Microsoft Messenger API’s (they have been a part of MS Messenger and MSN Messenger since the 4.7 days). This API is great, it allows you to enumerate your contacts, see what there status is, and even start a InstantMessenger window. Unfortunately it seems Microsoft never completed the API, or they saw an apparent security risk here, and they stopped short of providing an API to actually send and receive messages with messenger. Perhaps they didn’t like what trillian or some of the other multi-messengers were doing back in 2001, I don’t know if they were using the API anyway. Probably not, especially not GAIM as this one appears to be written for Linux mostly.

Anyway, I have a working prototype translation windows forms application. I am still working with the Messenger APIs a little more, I am playing with some pinvoke stuff to see if I can hook into the messenger window, and SendKeys and also listen for new messages in the history window.

My goal is to have a small application that will allow you to select your language, and the language of the person that you are chatting with. when you it send it will send them the translated text and the original text in parenthesis perhaps:

Tony Says: hallo, wie Sie heute sind? (Hello how are you today?)

Anyway thats my pet project for the week.

On a side note: I have not looked into Messenger Plus much. I will be looking into this next, maybe my application will evolve into a plugin for Messenger Plus. You never know.
I did not search for plugins for this feature, this is my next step. I am thinking that there may be a GAIM plugin already in existance for this, as GAIM has a strong open source community.

I will post my source code to what I have done, tomorrow when I get back into the office. Maybe the source would be usefull for someone that wants to see some examples of WebClient, RegEx, HtmlDecoding (for special characters).