The answer depends on the application. Programs must be "codec-enabled" to take advantage of the decoders in the FastPictureViewer Codec Pack and the codec subset that ship with FastPictureViewer Professional. You need to ask the software vendor if their product is compatible with Windows Imaging Component (WIC) codecs.
A number of Microsoft and 3rd party applications are already able to open new formats through the use of WIC codecs. Examples includes Windows Explorer, Windows Photo Viewer, Windows Photo Gallery and Windows Media Center 7, but also Microsoft Image Composite Editor and some 3rd party packages such as IMatch, The Panorama Factory, Sony Vegas Pro and probably others. All those applications can open files they originally don't handle when the proper codecs are installed.
For Photoshop, we have an experimental "Import" plug-in that can use most of our codecs to import files in Photoshop CS. The plug-in comes in both 32-bit and 64-bit versions. This plug-in is available for download from the codecs page and will let you import the "preview JPEG" or an image converted from the raw data, depending on the codec's configuration.
Out Photoshop import plug-in is merely a proof of concept and is certainly not a substitute for proper support in ACR, but it can be used in "emergency". It was tested with recent CS versions but it may also work with other Adobe and non-Adobe applications that support Photoshop Import plug-ins.