ZAKIR's Blog

WebUtil: How to Read an Excel file into an Oracle Form

2012-02-06T12:09:00.003+06:00

Description of included files (listed below):
Read Excel to Forms.dox – This document in Microsoft Word 2003 format
create_planets.sql – script to create the table used by this sample code
planets_ole_excel_read.fmb – sample form which demonstrates the concept
planets.xls – Excel (version 2003) sample spreadsheet

Setup steps:
Log into your database and run the “create_planets.sql” script
Ensure your environment is properly configured to run WebUtil. This document does not cover this configuration – there is plenty of information available in the OTN forums as well on the Internet that covers configuring WebUtil so I will not included it here.
Place “planets.xls’ some place on the client computer. I have modified Oracle’s original form to display a “File Select dialog” rather than hard code the file name and location in the code as Oracle demo did.
Ensure you have configured the Forms Builder to allow running a form from the Forms Builder. This document does not cover how to perform this configuration.
Run the form from the Forms Builder or on the client computer and push the “Read from Excel” button. Choose the ‘planets.xls’ file from the “Select Client filename to Open” dialog and the data will be read from the Excel spreadsheet into the form. You can then push the save button in the toolbar to commit the data to the table.
Here is the code behind the “Read from Excel” button with some explanatory comments:
DECLARE
application Client_OLE2.Obj_Type;
workbooks Client_OLE2.Obj_Type;
workbook Client_OLE2.Obj_Type;
worksheets Client_OLE2.Obj_Type;
worksheet Client_OLE2.Obj_Type;
worksheet2 Client_OLE2.Obj_Type;
cell Client_OLE2.OBJ_TYPE;
args Client_OLE2.OBJ_TYPE;
cell_value varchar2(100);
num_wrkshts NUMBER;
wksht_name VARCHAR2(250);
eod Boolean := false;
j integer := 1;
v_fName VARCHAR2(250);
BEGIN
-- Get the name of the file to open
--v_fName :=
'D:\MyDevelopment\Forms\Samples\WebUtil\Read_Excel\planets3.xls';
-- My Way: Use a File Open Dialog to let the user select the file.
v_fName := WebUtil_File.File_Open_Dialog(
directory_name => 'C:\'
,File_Filter => null
,Title => 'Select Client filename to Open.'
);

-- Make sure the user selected a file
IF ( v_fName IS NOT NULL ) THEN
-- The following sets up communication with the excel spreadsheet
-- --------------------------------------------------------------

-- Open the OLE application
application := Client_OLE2.create_obj('Excel.Application');
-- Keep the application hidden
Client_OLE2.set_property(application,'Visible','false');

workbooks := Client_OLE2.Get_Obj_Property(application, 'Workbooks');
args := Client_OLE2.CREATE_ARGLIST;

-- Open the selected File
-- ----------------------
Client_OLE2.add_arg(args,v_fName);
workbook := Client_OLE2.GET_OBJ_PROPERTY(workbooks,'Open',args);
Client_OLE2.destroy_arglist(args);

worksheets := Client_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

-- Get number of worksheets
-- ------------------------
num_wrkshts := Client_OLE2.GET_NUM_PROPERTY(worksheets, 'Count');
worksheet := Client_OLE2.GET_OBJ_PROPERTY(
application,'activesheet');

--Go to the first record
go_block('planets');
first_record;

-- Loop through the Block and create a new row if needed.
loop
If :system.record_status <> 'NEW' then
create_record;
end if;

-- Exit when the last row of the spreadsheet is reached.
exit when eod;

-- Loop through the spreadsheet and get cell values
for k in 1..3 loop --3 fields per record
-- You have to know fields there are
args:= Client_OLE2.create_arglist;
Client_OLE2.add_arg(args, j);
Client_OLE2.add_arg(args, k);
cell:= Client_OLE2.get_obj_property(worksheet, 'Cells', args);
Client_OLE2.destroy_arglist(args);
cell_value :=Client_OLE2.get_char_property(cell, 'Value');

-- Check for End of Data…
if upper(cell_value) = 'EOD' then
eod:=true;
Message('End of Data');
exit;
end if;

-- Copy the value from Excel to the Forms block item
-- This is how the Oracle example copied values
/*if k =1 then
:dept.deptno:=cell_value;
end if;

if k =2 then
:dept.dname:=cell_value;
end if;

if k =3 then
:dept.loc:=cell_value;
end if;
*/

-- This is my way; which is more efficient and less code
copy(cell_value,name_in('system.cursor_item'));
next_item;

end loop; --for

j:=j+1;
end loop; --main loop

-- Release the Client_OLE2 object handles
IF (cell IS NOT NULL) THEN
Client_OLE2.release_obj(cell);
END IF;
IF (worksheet IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet);
END IF;
IF (worksheets IS NOT NULL) THEN
Client_OLE2.release_obj(worksheets);
END IF;
IF (worksheet2 IS NOT NULL) THEN
Client_OLE2.release_obj(worksheet2);
END IF;
IF (workbook IS NOT NULL) THEN
Client_OLE2.release_obj(workbook);
END IF;
IF (workbooks IS NOT NULL) THEN
Client_OLE2.release_obj(workbooks);
END IF;
Client_OLE2.invoke(application,'Quit');
Client_OLE2.release_obj(application);
ELSE
Message('No File selected.');
message(' ');
RAISE Form_Trigger_Failure;
END IF;
END;

2011-10-31T14:13:00.000+06:00

Oracle BI Tutorial - Getting Started with Oracle BI Publisher 11g

2011-10-02T17:17:00.000+06:00

Getting Started with Oracle BI Publisher 11g

This tutorial contains the following sections:

Getting Started with BI Publisher

Creating a Data Model

Creating a Report

Building a Layout Using Layout Editor

Scheduling a Report

Summary

Resources

Purpose

This tutorial covers how to get started with Oracle BI Publisher 11g to create simple reports based on Oracle Database. Also, it teaches you how to create report layouts using the Layout Editor online, and how to schedule reports.

Time to Complete

Approximately 1 hour.

Overview

Oracle BI Publisher is a strategic enterprise reporting product from Oracle that provides the ability to create and manage highly formatted reports from a wide range of data sources. Oracle BI Publisher is available as part of the Oracle BI Enterprise Edition Suite and also as a stand alone reporting tool. Oracle BI Publisher report formats can be designed using Microsoft Word or Adobe Acrobat, the tools that are easy to use, and most users are already familiar with. Oracle BI Publisher also allows you to create reports from different types of data sources such as Oracle Database, Files, BI EE, Web services, and Discoverer.

Release 11g of Oracle BI Publisher has enhanced, easy- to-use user interface (UI), many enhanced features, and newly introduced salient features such as - Data Model Editor - a graphical user interface for building data models within the BI Publisher interface; and Layout Editor - a design tool that enables you to create report layouts within the BI Publisher interface.

In this tutorial you will be guided to create simple reports in BI Publisher based on Oracle Database. You will also be guided to work with the Layout Editor to create the layouts for the reports.

Prerequisites

Before starting this tutorial, you should:

1.	Have access to Oracle BI Publisher 11g or installed BI Publisher 11g as stand alone or as part of Oracle BI Enterprise Edition 11g. Also, it is recommended to use the following versions of borrowers - Internet Explorer 7.0 (or above), Mozilla Firefox 3.6.3 (or above).
2.	Have access to an Oracle Database11g (or 10g) with the sample schemas HR and OE installed and unlocked. The exercises in this tutorial use the HR and OE schemas included in the Oracle Database.
3 .	Have executed the Repository Creation Utility ( RCU) scripts in Oracle Database 11g. This should be done before installing Oracle BI EE. RCU creates the BI Publisher scheduler repository. Note: BI Enterprise Edition (BI EE) installer comes with these out of the box RCU scripts, that create the necessary schemas and the repositories in the Database for various components of BI EE including BI Publisher. You need an Oracle 11g Database to run these RCU scripts. However, you can establish a JDBC connection and create data models and reports through BI publisher using both 10g and 11g Databases.

Getting Started with BI Publisher

This topic guides you to login to BI Publisher and get started. It has the following subtopics:

Logging in to BI Publisher and Navigating the Home Page
Browsing the catalog and Viewing reports
Setting Preferences

	Logging in to Oracle BI Publisher and Navigating the Home Page
	Browsing the Catalog and Viewing the Reports
	Setting Preferences
	Configuring the Data Sources
	Types of Supported Data Sources
	Defining a JDBC Connection
	Configuring File Data Location
	Defining a JNDI (Java Naming and Directory Interface) Connection

Logging in to Oracle BI Publisher and Navigating the Home Page

1 .	Enter the URL for BI Publisher in the browser window, which is of the format- http://: /xmlpserver/. For example- http:/localhost:9704/xmlpserver/ This opens the Oracle BI Publisher Enterprise login page-
2 .	Login as a user with BI Administrator privileges. When you login to BI publisher, the Home page is displayed-
3 .	The Home page is a task-oriented, centralized workspace combined with a global header, allowing access to Oracle BI Publisher objects, their respective editors, and help documentation. Observe the various sections of the Home page ( greatly enhanced in 11g release to enable the users get started quickly with BI Publisher) : For example you can see - A Create section on the left that has options to help you create reports, data models, and other objects. A Browse/Manage section on the left to help you browse and manage the catalog, jobs, and job history. A Get Started section on the left to help you get started with BI Publisher, with links to various BI Publisher tools, Online Help, and Oracle Technology Network. A Recent section on the right, personalized to each user so that the users can open/view the reports that have been accessed recently (when you login initially, you may not see any objects listed in the Recent section). Also, try the enhanced Search capability that enables you to search for objects by type.
4 .	On the upper right side you can see the links for the Home page, Catalog, drop-down lists New, and Open. Note: All these features make the navigation within BI Publisher easy for report developers and users.

Browsing the Catalog and Viewing the Reports

1 .	Follow the steps listed here to browse the catalog and open a sample report (predefined). Click the Catalog link in the Browse/ Manage section ( or Click the Catalog link found on the right side of the Home page).
2 .	The Catalog page is displayed as a tree structure on the left side of the page with the details on the right. See that the objects such as reports, data models are organized into folders. Note: The Samples folder found in Shared Folders contains a set of predefined sample reports. You will open one of these reports.
3 .	Also see the tool bar with New drop-down menu that enables you to create new reports, scheduled jobs, data models and so on. Note that the tool bar also has other options such as copy, cut, paste, and other options that can be used when creating or managing the catalog objects such as data models, and reports.
4 .	Navigate to the Human Resources folder in Samples. (This displays all the objects in the Human Resources folder.) You can also search for a report (or other objects) using some key word and view it. For example, in the Catalog page, select the Reports check box from the Search drop-down list, and enter a key word in the Text field beside it. In the example below, the search results in a list of reports that have "Sales" in name.
5 .	Click Open link below the Salary Report.
6 .	The Salary Report is opened in view mode. The report data is displayed using the Default layout. All the predefined layouts for the report are displayed as different tabs. You can also view the report using any of the supported formats. Click View icon displayed on the top of the report viewer towards the right. (Highlighted in the screen below).
7 .	Also, note the parameters defined in the report -Department and Employee, that display drop down lists for department names and the employees working in the corresponding departments. For example, when you select Finance as the Department, and the Employee list shows the employees working in Finance department.
8 .	You can click Financial Style, and Pivot Table tabs to view the data in report using these predefined layouts. The following screens shows data for the Finance department using Financial Style layout: The followings screen displays data for the Sales department using Pivot Table layout.

Setting Preferences

1 .	To set the Preferences , Click on the link Signed in as , and select My Account from the drop-down list.
2 .	This displays the Preferences screen with the General, My Group tabs/ links as shown in the screen below: You can set the Report Locale, UI Language, TIme Zone, and Accessibility Mode in the General Preferences section. Report Locale- A locale is a language and territory combination (for example, English (United States) or French (Canada)). BI Publisher uses the report locale selection to determine the template translation to apply , the number formatting and date formatting to apply to the report data. .UI Language- The UI language is the language that your user interface displays in. The language that you selected at login will be selected as the default. However, you can choose from the languages that are available for your installation through this option. Time Zone - Select the time zone to apply to your reports. Reports run by you (this user) will display the time according to the time zone preference selected here. Accessibility Mode- Setting this to "On" will display the report catalog in a tree structure that is accessible via keyboard strokes Note: If running BI Publisher integrated with a Security Model other than BI Publisher, these preferences may be inherited from the other security model and you will not be able to change these values.
3 .	Click the My Groups tab to view a list of the application roles to which you are assigned. You cannot modify this list. To change your password, click the Password tab of the My Account dialog. Note: Password tab is visible only when you use BI Publisher Security.

Configuring the Data Sources

Types of Supported Data Sources

Oracle BI Publisher supports various types of Data Sources such as JDBC data sources (Oracle Database, and other Databases), XML Files, BI EE Reports (Answers), JNDI data sources, OLAP data sources, Fusion Application Data sources, Web Services, HTTP data sources, and Discoverer.
Before you create a data model based on these data sources, you need to configure a connection to these data sources first. In this topic, you will learn how to configure a JDBC connection to an Oracle Database, and also you learn how to define a File data source.
This topic has the following subtopics-

Defining a JDBC Connection
Configuring File Data Location
Defining a JNDI Connection

Defining a JDBC Connection

1.	To define a JDBC connection click the Administration link found on the right side of the BI Publisher page. This displays the BI Publisher Administration page as shown below (Observe the Data Sources section in the screen below):
2.	Click the JDBC Connection link found under the Data Sources section in the Administration page.
3.	This displays the Data Sources page. In the JDBC section, click Add Data Source to create a JDBC connection to your database.
4.	In the Add Data Source page that is displayed, enter the details as given below - Data Source Name - OE Driver Type - Select a driver type to suit your Database (for example, you can select Oracle 10g or Oracle 11g to suit your Database). Database Driver Class - oracle.jdbc.driver.OracleDriver (Define a driver class to suit your Database) Connection String - Provide the database connection details. For example hostname:port:sid. User name - OE (Database user name) Password - OE (Database user password) Do not click Apply or Cancel after defining the above details. Click Test Connection. Note: To continue with this tutorial exercises, the sample schemas OE, and HR should be installed in your Oracle Database as mentioned in the Prerequisites. Else, you will not be able to define this JDBC connection.
5 .	If the connection to the database is established, a confirmation message is displayed indicating the success. ( As shown in the screen below). Then click Apply.
6 .	You can see this newly defined connection (OE) in the list of JDBC Data Sources.
	Note: You can also set up the the default connection demo to point to OE schema in your database, as the samples use this connection. Then the demo connection can be used as the default data source for this tutorial too.

Configuring File Data Location

1.	BI Publisher ships with a repository of various sample files and reports. These are available in BI Publisher home. To define a File data source, click the File tab in the BI Publisher Administration page. Click the demo files link to update the file location. ( If you want to create a File data source with a different name, you can click Add Data Source. )
2.	Define the top level directory which has all the sample files (repository). ( Using this demo files data source, users can access all the files in the top level directory and also it's subdirectories).

Defining a JNDI (Java Naming and Directory Interface) Connection

BI Publisher supports connecting to a JDBC data source via a connection pool. Using a connection pool increases efficiency by maintaining a cache of physical connections that can be reused. When a client closes a connection, the connection gets placed back into the pool so that another client can use it.
Note: JNDI Connection concept is briefly introduced here, as you need to first set up the connection pool in your application server and access it via Java Naming and Directory Interface (JNDI). Then, you can define the JNDI connection in BI Publisher by entering the required fields in the JNDI connection definition page, so that BI Publisher can utilize the pool to establish connections.

Creating a Data Model

In release 11g, Oracle BI Publisher introduces the Data Model Editor, a graphical user interface for building data models within the BI Publisher interface. It enables you to perform the following tasks:

Create Data Sets - Access data from a wide range of sources: RDBMS, OLAP, WebServices, BI Analyses, XML files, Excel and others.
Query data – Build SQL or MDX queries to extract data from relational or multidimensional (OLAP) data sources.
Structure data – Define master-detail relationships between data sets to group data at multiple levels to optimize document generation.
Aggregate data – Create group level totals and subtotals.
Customize data – Modify data field names to conform to business terms and reporting requirements.
Create calculations – Compute data values that are not stored in the underlying data sources that are required for reporting.
Advanced tasks – Define parameters and lists of values (LOV), triggers, and other advanced elements as required by reports and report users.

In this topic, you are guided to create Data Model based on SQL query using the JDBC connection you defined in the previous topic. Also, you will learn how to add parameters and LOVs to the data model.

	Defining Default Data Source, and Output Options
	Creating a SQL Query Data Set
	Viewing the XML Output and Saving the Sample Data

Defining Default Data Source, and Output Options

1.	Note: Before starting the creation of Data Model, you will first create a folder to save all your objects such as reports, data models and so on. In the Home page, click Catalog link under the Browse/Manage section. In the Catalog page that is displayed, select the My Folders node, click the New icon on the tool bar. Select Folder from the drop-down menu to create a new folder. Enter Learn as the name of the folder, (optionally enter a description), and click Create. You can see that the folder gets listed in My Folders.
2.	Now, you can invoke the Data Model editor in one of the following ways- From the Home page Create section, select Data Model. From the Catalog page, click New icon and select Data Model from the drop-down menu.
3 .	Observe the Data Model Properties page that is displayed on the right. Select a Default Data Source (that points to the OE schema in your database). Optionally, add a description to the data model. Note: Ensure that in the XML Output Options, the Include Parameter Tags is checked, and the Include Empty Tags for Null Elements, and Include Group List Tag check boxes are not checked. For defining the Default Data Source, you can also edit the default demo data source to point to the OE schema in your Database, and use it. Also, note that on the Properties page you can set many options in addition to specifying a default data source. For example, you can specify DB default package, and DB fetch size (the data engine’s default is 300), XML output options based on what XML tags you require in the resulting XML output.
4 .	To save the data model with a name, click Save As icon found at the top right corner of the page. Navigate to the folder that you want to save the data model object ( Learn in this example), enter an appropriate name for the Data Model , and click Save.

Creating a SQL Query Data Set with Parameters and LOVs

1 .	From the Data Model task pane on the left, select Data Sets.
2 .	From the Data Set drop-down menu, select SQL query as the type of data set.
3 .	In the Create Data set -SQL dialog box that appears, enter an appropriate name for the data set, accept the default data set option (as you have already defined it in the Data Model), and click Query Builder to create a query.
4 .	The Query Builder window appears displaying the OE schema objects on the left side. Click DEPARTMENTS, and EMPLOYEES tables to add them to the Model canvas on the right.
5 .	Now, define a join between these two tables as follows: Click the box beside DEPARTMENT_ID column in the DEPARTMENTS table. Similarly click the box beside DEPARTMENT_ID column in the EMPLOYEES table. (These boxes, when marked for joins turn light gray. ) Also note that a fine line joining the tables appears in the Model canvas.
6 .	Select the following columns from the tables (by selecting the check boxes beside the column names): -Select DEPARTMENT_NAME from the DEPARTMENTS table -Select FIRST_NAME, LAST_NAME, HIRE_DATE, and SALARY columns from the EMPLOYEES table (See the screen below)
7 .	Click the Conditions Tab . Change the column names and remove "_" to have more appropriate aliases. Change the aliases for the columns as below: DEPARTMENT_NAME to DepartmentName FIRST_NAME to FirstName LAST_NAME to LastName HIRE_DATE to HireDate SALARY to Salary
8 .	Also, type "IN (:P_DNAME)" in the Condition field for the department name column, as shown in the screen below . This will define a parameter P_DNAME on the department name column. Note: The IN condition is being used here for the parameter to accept All or Multiple values for the department name column. The aliases and display names can be changed in the Data Model page, either in the Diagram or in the Structure too:
9 .	Click Results to see how the query results appear in Query Builder.This will prompt you to enter a department name for the parameter, enter Sales and click OK. View results. Click Save to save the query.
10 .	This takes you back to the Create Data set -SQL dialog box, and observe that the query you created is reflected in the SQL Query area. Enter an appropriate name such as Emp_DataSet, and click OK to add this data set to your data model.
11 .	A message asks whether you want to create a bind variable. Click OK. to create the parameter Note that the parameter created is listed on the left under Parameters node in Data Model: Every time you make changes to the Data Model (changes to the data set, adding parameters or any other changes), click Save icon (found at the top right corner) to save the Emp_DM data Model .
12 .	In the Data Model pane, click List of Values node. In the List of Values pane click the the + sign to Create new List of Values. Select the following Options: Enter DepName as the Name of the LOV Select SQL Query as the Type from the drop-down list Ensure that OE is selected as the Data Source Click Query Builder to define the query that returns department names for the LOV Save the Data Model.
13 .	In the Query Builder page, select DEPARTMENT_NAME from the DEPARTMENTS table. Click Save. Click Save to save the Data Model. The query for the LOV should look like this:
14 .	Now, set the properties for the P_DNAME parameter to use this LOV: Select P_DNAME parameter listed under the Parameters node. Enter Default value as * (the character star) , and select Menu as the Parameter Type (the default parameter type is Text).
15 .	Ensure that DepName is selected as the List of Values for the parameter. Ensure that Multiple Selection, Can select all, and All Values Passed options are selected. (These options allow you to select all / multiple values for the department.) Also, change the Display Label to an appropriate one like- Department: Click Save to save the Data Model.
16 .	You can edit the query manually further to concatenate Last Name and First Name columns as Employee Name. Edit the query to look like this: Select DEPARTMENTS.DEPARTMENT_NAME as "Department Name", EMPLOYEES.FIRST_NAME \|\| ' '\|\|EMPLOYEES.LAST_NAME as "Employee Name", EMPLOYEES.HIRE_DATE as "Hire Date", EMPLOYEES.SALARY as Salary from OE.DEPARTMENTS DEPARTMENTS, OE.EMPLOYEES EMPLOYEES where DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID and DEPARTMENTS.DEPARTMENT_ID IN (:P_DNAME) Click OK and save the data model. The query should look like this in Query Builder: Note: Once you edited the query manually, you will not be able to edit in the Query Builder.

Viewing the XML Output and Saving the Sample Data

1 .	You may have observed that In the current version of the BI Publisher, you can preview the XML data for the data model. Click the XML icon (found at the right top corner of the page), to see the XML output for the data model you defined in previous topic. Note that All is selected for the Department parameter (as this is the default value ). Select All for the number of rows, and click Run to see the XML data output for all the departments: ( A portion of the XML data is displayed here in the screen)
2 .	To save this as sample data, click the Open Menu drop-down list icon, and select Save as Sample Data. You can see that the sample.xml is listed in the Sample Data section of the Data Model ( as shown below): Note: It is very important to save sample data for a data model, else when creating Layouts, the previews do not appear correctly. You can select various values (multiple/ single) for Department to see the data. You can also restrict the number of rows that you want to see:

1 .

You may have observed that In the current version of the BI Publisher, you can preview the XML data for the data model.
Click the XML icon (found at the right top corner of the page), to see the XML output for the data model you defined in previous topic.

Note that All is selected for the Department parameter (as this is the default value ).
Select All for the number of rows, and click Run to see the XML data output for all the departments:
( A portion of the XML data is displayed here in the screen)

2 .

To save this as sample data, click the Open Menu drop-down list icon, and select Save as Sample Data.

You can see that the sample.xml is listed in the Sample Data section of the Data Model ( as shown below):

Note: It is very important to save sample data for a data model, else when creating Layouts, the previews do not appear correctly.
You can select various values (multiple/ single) for Department to see the data. You can also restrict the number of rows that you want to see:

Creating a Report

In this topic, you learn to create a report using the Data Model that you defined in the previous topic. You also create a simple layout using Layout Editor, and edit parameters.

Note: The steps in this topic are all continuous, so do not close any window or logout from BI Publisher unless you are asked to do so.

1 .	If not logged in, login into BI Publisher, and go to My Folders> Learn folder in Catalog pane. Click New (found at the top left corner of page) to see the drop-down menu and click Report to create a new report.
2 .	Navigate to My Folders> Learn. Select Emp_DM data model and click Open. It displays the options to create, use a shared template, or upload a report layout. Select Blank (Portrait) under the Basic Templates section to create a simple report layout. Note: You can define a Layout for the report at the time of creating report, or create it at a later point of time. Creating report layouts using the Layout Editor is covered in detail in the next topic.
3.	This opens Layout Editor, with a blank page:
4.	Click Insert > Data Table from the menu to insert a data table in the layout page.
5.	Select and drag the elements in the following order from the Data Source pane to the [Drop a Data Item Here] area of the table: DEPARTMENT_NAME EMP_NAME HIRE DATE SALARY As you drag an element, position each successive item directly to the right of the previous element and release to create each column, so that the data table, after adding all the columns it looks like this:
6.	Click the Save icon found on the top right corner to save this layout. Enter Default Layout as the name of the layout, and click Save.
7.	This brings you back to the Layout Editor. Click the Preview icon drop-down list (highlighted in the screen) to preview the data in Interactive format (this is also the default format). Note: You can perform sorts and selection interactively here. Close the Interactive Viewer.
8.	.Click Return in Layout Editor to return to the Edit mode of the report. Observe the various links on this page that will help you edit the data model, parameters, properties, and layouts for the report. You can also display the layouts as Thumbnails or as a List. Note: Parameters link is displayed, only when the data model you selected for a report has parameters defined.
9.	Click Save icon (found at the top right corner of the page) to save the report. (You can also click Save As icon ). Navigate to My Folders> Learn. Enter Employee Salaries By Department as the report name and click Save.
10 .	Click the Parameters link and it shows the details for the parameters. You can make changes to the parameter values. Also, you can opt to show or not to show the parameter in the report. Clear the Show check box not to show the parameter. Note: The report parameters are edited here not to show them in layouts. (This is not mandatory, but this is being done here to depict all the data in the layout graphs that you will create in the next topic.)

Building a Layout Using Layout Editor

Release 11g of Oracle BI Publisher introduces an online Layout Editor, a design tool to create and publish report layouts from within BI Publisher interface. The Layout Editor provides an intuitive drag-and-drop interface for adding common components to your report layout from within your web browser. The Layout Editor uses the sample data in the data model to immediately populate your layout components with report data during design-time. It also provides many advanced features to fully customize your reports.
The Layout Editor provides a new Interactive output type in addition to output types like PDF, RTF, Excel, PowerPoint, and HTML. Interactive output enables lightweight interactions with the report data from within the browser such as Excel-like filtering and sorting of tabular data with fixed headers and footers
In this topic, you will design a layout with graphs and using the Layout Editor, for the Employee Salaries by Department report that you created in the previous topic.

	Launching the Layout Editor and Adding a Grid Layout
	Working with Charts
	Adding a Data Table to the Layout
	Formatting the Data, and Working with the Page Layout Features

Launching the Layout Editor and Adding a Grid Layout

1 .	Click Return to return to the Edit mode of the report. (If the report Now, click Add New Layout link to create a new layout for the report.
2 .	The Layout Editor is invoked, and displays the available Basic and Shared templates (templates for report layouts) to choose from. It displays the options to create, use a shared template, or upload a report layout. Click Blank (Portrait) from the Basic Templates section to manually design the layout. Note: It is a good practice to use Pre-built templates for reports because: They encourage consistent look and feel for layouts They make report authors much more productive by taking care comment of elements like headers and footers. However, here a blank portrait is being used to show how to create a template from scratch.
3 .	This shows blank page (portrait) in Layout Editor . Click Layout Grid to insert a grid. In the Insert a Layout Grid screen, enter 2 as the number of rows and 2 as the number of columns for the grid. Click OK. (You will add various report components such as charts, tables, and pivot tables in the the grid rows and columns in the next subtopics.) Note: Using Layout Grids to control pixel perfect placement of objects in the layout is a best practice.
4.	The layout grid with 2 rows and 2 columns is added: Now, merge the cells in the second row. Use key to select both the cells in the second row of the grid, (when the cells are selected, the color turns yellow) click Join Selected Cells option available in the Layout Grid menu.

Working with Charts

5.	Select the first cell in the first row of the grid layout, and click Chart from the Insert menu in the ribbon. This action inserts a chart template with prompts as shown below:
6 .	From the Data Source pane on the left- Select and drag the SALARY element to the Drop Value Here prompt in the empty chart Next, select and drag the DEPARTMENT_NAME element to the Drop Series Here prompt. This would populate the chart with some sample data.
7 .	Next, change the Properties of the SALARY element in the chart to average the value. To do this, first select the SALARY item in the chart, then click the drop-down menu next to Summation under Formula, and select Average from the drop-down menu. The chart changes to reflect the average salaries by department. You can leave the default style for the chart or select from the supported chart styles. To add a title for the chart, first, Add a row above this chart in the grid layout. Select any of the the grid cells in the first row, and click the Add a Row Above icon. Now select the cell right above the chart, and click Insert tab , and then click Text Item.
8 .	Next, add a title for the chart. Double click the text item to select it, then click again to edit it. Delete the default text and type Average Salary by Department as the Title. Select the text and choose Tahoma as font, 12 as the font size, and Bold as the style. Center the title by clicking the Center Alignment icon.
9 .	Click Save As icon in the toolbar, and save the Layout as Departmental Salaries.
10 .	Return to complete the layout in the Layout Editor. Now, select the cell beside the bar chart that you have inserted. Follow the above steps to insert a Pie chart. The steps are briefly listed here: Select the cell beside the chart that you have inserted, and then click the Insert tab. Click Chart. To change the chart type to a pie chart, expand the Chart Types menu. Select the Pie chart to insert an empty pie chart with prompts in the grid cell. To populate your pie chart: From the Data Source pane, select and drag SALARY to Drop Value Here, and select and drag DEPARTMENT_NAME to Drop Series Here Add Percentage Salary by Department as the Title. Use Tahoma as the font, 12 as the size, and Bold as the style. Align the title in the center. Click Preview icon,and select PDF from the drop down list to preview your chart : The preview should now look like this: Click Save icon in the toolbar to save the changes to the Departmental Salaries layout.

Adding a Data Table to the Layout

1.	Select the lower row (which has the cells merged). Click Insert tab. Click Data Table to insert the data table component into the layout.
2.	Select and drag the elements in the following order from the Data Source pane to the [Drop a Data Item Here] area of the table: DEPARTMENT_NAME EMP_NAME HIRE DATE SALARY As you drag an element, position each successive item directly to the right of the previous element and release to create each column. After you insert all the columns notice that a Grand Total row is automatically inserted .
3 .	Click Preview in PDF icon in the toolbar to preview the layout - Click Save to to save the Departmental Salaries layout. Note: You can edit the layout that is saved in one of the following ways: Click Actions and select Edit Layout in the view mode of the report or Open the report in Edit mode (click Edit link below the report in catalog page) and select the layout to be edited in the Layout section, and click Edit (pencil icon)

Formatting the Data, and Working with the Page Layout Features

Layout Editor enables you perform many formatting tasks such as -

Change the back ground, text colors, and other text attributes
Apply number formats
Define groups/totals/ subtotals, and sorts
Apply conditional formats
Define page layout using headers, footers and page breaks.

Note: Most of the above formatting tasks can be done using the options in the menu ribbons. The most commonly used formatting properties are found in the context sensitive menu ribbons on the tool bar. For more fine grained properties, you can use the property pane on the left.

1 .	Changing Background, Text colors, and other Attributes : In the table select a column header, for example DEPARTMENT NAME. From the menu ribbon, change the properties as below: Font to Arial, Font Size to 10, and Font Weight to Bold.
2 .	Click Background Color icon from the properties listed in the Appearance section. Select a color (light blue is selected in the example here) from the Color Picker screen and click OK. Similarly, click the Font color icon (highlighted in the screen) to change the text color for this column header. Select a color (like Dark blue) from the Color Picker. Change the background and text colors for all the column headers in-line with the DEPARTMENT_NAME. Hint: Use the and keys to select the remaining columns headers, and change the background and text colors to the same colors you used for the DEPARTMENT NAME column header. Similarly, select the column footers (totals ) and change the colors . Note that the Properties pane (on the left) changes accordingly: Similarly, apply same formats as above to the footer row (Totals row).
3 .	Change the all the column (data cells) fonts to Arial with size 10. Click Save to to save the Departmental Salaries layout. The table should appear like this after the above formatting changes: You can also modify the other properties such as Text Alignment. Ensure that the data in the numeric columns - such as SALARY is justified to the right .
4 .	Adding Number Formats: You can add appropriate formats to the numeric or date columns such as SALARY, YEAR, HIRE DATE etc. To change the format for SALARY, click anywhere below the column header in the data area to select SALARY column. From the Data Formatting drop down list, that is displayed in the ribbon, select $1,234.57 (Currency) as the format. Similarly, select the same format for the Salary Total (last row). Click Save icon to save the changes to the Layout. Now, adjust the decimal places to the right. Click Move Right twice to remove the decimals in Salary column and the Totals. The table should now look like this (observe the formats for Salary column):
5 .	Similarly, click HIRE DATE column and apply the appropriate date formats: The table looks like this:
6 .	Defining Groups/Totals, and Sorts: In the table you can add groups, totals, and sorts. Click the data in the DEPARTMENT NAME column to select the same, and select Group Above from the Grouping drop-down list that appears in the Column menu. Note: Instead of using menu options, you can also drag DEPARTMENT NAME out of the table and drop it just above the table to add a repeating group. A total for the grouped column is automatically added at the end of the group in the table: Change the font, style, background and text colors as appropriate for DEPARTMENT NAME from the menu options in the ribbon. The table should appear like this after you defined a group on DEPARTMENT NAME column, and applied appropriate formats: Note: Every time you make changes, do not forget to click Save and save the changes to the template.
7 .	Click Preview in HTML icon to see the data in HTML. Scroll down to see the table:
8 .	You can also define sorts on the columns in the table. You can define an ascending or descending sorts on columns. Select the data in EMPLOYEE NAME column and click Ascending Order (A-Z) icon in the Sort menu. The products are arranged in alphabetical order. A portion of the data table, the data for the Purchasing department is shown below, note that the data is sorted by Employee Name: (You can also sort by salary, in which case a numeric sort is applied as appropriate.)
9 .	Applying Conditional Formats: Conditional formats help you to highlight the data in fields based on a condition. For example, you may want to do a pay revision to reduce attrition rates, and may want to see which employees are earning very good salaries, and which employees are not earning so well (say less than 3000 USD). To apply simple conditional formats on SALARY select the data in the column and from the Conditional Formatting menu, click Highlight.
10 .	In the Highlight screen that appears, select is less than or equal to from the Operator drop-down list. Enter 3000 as the Value. Set Bold as the Text style. Click the box beside the Background Color field. In the Color Picker, select Red as the background color. Click OK . This takes you back to the Highlight definition dialog, preview how conditional format looks like and click OK.
11 .	Define two more conditional formats as given below: 1. One with the Operator - is greater than, and enter the Value as 9000. Apply Green as the back ground color, and set Bold as font style. (See the screens below). Click OK. 2. Also define a conditional format with the Operator - is between , and enter the Values as 3000 and 5000 . Apply yellow as the background color, and Bold as font style. (See the screen below). Click OK. Click Save icon to save the Departmental Salaries template.
12 .	Click Preview in HTML icon to see the data in HTML ( a portion of the table data is shown below, note that all the three conditional formats that you defined are applied on the SALARY column) . Note: Some of the salient features of Layout Editor that are helpful in creating report templates are briefly covered here. If you have time, try experimenting with other components , and other features such as section, pivot table, gauges, images, and Group Left with subtotals.
13 .	Working with Page Layout Features: In the layout that you have created with the report components such as charts and table, you may want to add page breaks after each component, and in the table after each year. The next few steps guide you to add page breaks, headers , and/or footers to the report layout. In the grid layout , select the second row which has charts. Click Insert, and select Page Break from the Page Elements section in the menu. Similarly, add a page break in the next row before the end of the section for DEPARTMENT NAME (group). Click inside the table just before the end of the section. Select Insert> Page break from the Page Elements section in the menu. (This inserts a page break after each department data in the table.)
14 .	To see if the page breaks are inserted at right places, preview the layout in PDF. Click Preview in PDF icon. Notice the page breaks in the PDF file: Scroll down to see the second page. Note that a page break is inserted after each department data:
15 .	To insert page/report header and footers, click Page Layout. This displays all the menu options for Page Layout in the ribbon. Click Report Header. A report header (place holder) is inserted into the layout. Insert a Text Item in the header, and double click the text item to edit it. Enter Employee Salary Analysis by Department as the report header. Apply appropriate formats for the report header. Preview the output in Interactive viewer. Observe that you can interactively select a department to view the data. Also, when you click the interactive ' v ' shaped icon for each column you can see the MS Excel like, interactive filtering and sorting features.
16 .	Similarly click Page Footer to insert a footer in the layout. An icon for the page footer at the end of the grid. Insert elements such as page numbers, simple text, time and date into these columns. An example is given here: First, select the page footer, and insert a Grid of a single row with 3 columns. Insert text items into each of these columns. Edit the first column in the Grid in the page footer item so that the text is in the format - " of >. Double click this text item in the first column, and click Page Number icon from the Text menu in the ribbon Beside the page number icon that is inserted into the Page Header, type "of " Now, click Page Total icon from the Text menu. Similarly, insert simple Text such as "Confidential" in the second Grid column, and Date and Time in the third column of the footer. The footer should look like this: The report with the headers and footers defined should look like this in PDF: Note: If you have time, you can experiment with various elements such as gauges, images etc.

Scheduling a Report

This topic guides you through the following subtopics:

Confirming that the scheduler is configured
Defining Delivery Destinations
Creating Scheduled Report Jobs
Managing Job History and Jobs

	Confirming that the Scheduler is Configured
	Defining Delivery Destinations
	Creating Scheduled Report Jobs
	Managing Job History and Jobs

Confirming that the Scheduler is Configured

Oracle BI Publisher Enterprise enables you to schedule reports, and deliver the executed output to various destinations. BI Publisher Scheduler is configured as a part of Oracle BI Enterprise Edition installation process. However, ensure that the scheduler is configured properly, before you start scheduling the reports.

1 .	Log in (if not logged in) to BI Publisher (Web) as a user with BI Administrator privileges. Click Administration link (found at the right top corner of the BI Publisher page): In the Administration page, click the Scheduler Configuration link in the System Maintenance section
2 .	In the Scheduler Configuration page that appears, click Test Connection in the Database Connection section to check if the scheduler is configured properly. This should display the following message indicating the connection was successful: Now, you are ready to schedule your reports.

Defining Delivery Destinations

Oracle BI Publisher enables you to setup different delivery destinations such as - Email, Printer, Fax, WebDAV, HTTP, and other destinations, so that you can deliver your reports to these destinations. This topic guides you how to set up your Email and WebDAV destinations using BI Publisher Administration page.

1 .	In the BI Publisher Administration page, ( you need to login to BI Publisher with BI Administrator privileges to access this page) you see a list of destinations in the Delivery section. Click Email link in the Delivery section.
2 .	In the Email configuration page that appears, Click Add Server.
3.	In the Add Server page that appears, Enter an appropriate name for the mail server in the Server Name field Enter the Email server in the Host field Enter the values in the following fields if applicable— General field: Port Security fields: User Name and Password Click Apply. Important Note: You must enter a unique name for each server regardless of the type (printer, fax, email, WebDAV, or FTP).
4.	Similarly, you can add a WebDAV server as one of the delivery destinations. Click WebDAV link in the Administration page, and click Add Server. Enter An Appropriate name in the Server Name field, and Host for the WebDAV server Enter the following fields if applicable— General fields: Port Security fields: Authentication Type (None, Basic, Digest) and Encryption Type (None, SSL). Proxy Server fields: Host, Port, User Name, Password, Authentication Type (None, Basic, Digest)

Creating Scheduled Report Jobs

1 .	Login (If not logged in) to BI Publisher and go to My Folders> Learn folder in Catalog. Click Schedule link found under the Employee Salaries by Department report. Note: If you do not see the Schedule link it is likely that you do not have Scheduler privileges or that the Scheduler is not configured properly.
2 .	This displays the Schedule Report Job page. In the General tabbed page, you can select report name and parameter values (select All as the value for the Department report parameter).
3 .	Click Output tab to define the output options. Ensure that "Use Bursting Definition to Determine Output& Delivery Destination" check box is not selected. Select the following options: Enter DeptSal as the Name, Select Departmental Salaries from the Layout drop-down list, and select an appropriate output format as PDF Accept default values for locale, calendar, and select an Appropriate time zone from the drop-down list Ensure that the Save Output check box is selected
4 .	Also, ensure that the Make Output Public option is not selected (if you select this option, all users can see the output of your scheduled report) , and Save Data for Republishing option is selected ( so that you can later republish the report with a different layout and output types). You can also add a delivery destination for the scheduled report. In the Destination section, select Email as the Destination Type and click Add Destination. This shows an Email section below the Destination section. Add the mail ids in the To, CC, and Reply To fields as required, add a Subject to the mail, and type the message. Note: For adding destinations for scheduled reports, you should have first configured them in the Administration page. You can define multiple outputs for a single scheduled job and deliver each to separate destinations. For example, you might want to deliver a PDF output as an attachment to an email to a list of managers. You might also want to deliver the Excel output of the same report to a content management server (via WebDAV) at the same time.
5 .	Click Schedule tab to define the scheduling options. Select Run Now option to run the job immediately. Select the Frequency as Once from the drop-down list. You can also add Email Notification options, (as you have already defined Email delivery destination). Click Submit to submit the schedule.
6 .	Enter Employee Salaries by Department-Sched as the Job Name and click Submit again. A message is displayed indicating that the job is submitted successfully. Click OK.
7 .	Scheduling a Report Job to run Weekly: Follow the steps 1 – 3 listed above to create this job. In step 4, click Schedule tab, and select the following options— Select Weekly from the Frequency drop-down list Ensure that you have entered option to run the report Every week Select Monday to run the report on every Monday In the Start and End fields enter the start and end dates for the report, and make sure that the time is 8 am Click Submit to submit the job, and after specifying a name for the job click Submit again. Select some of the different Frequency patterns available for scheduling the jobs. Note that the Specific Dates option allows you to create a completely custom pattern to meet whatever calendar requirements you have.

Managing Job History and Jobs

1 .	Click Open (found at the top right corner of the page) and select Report Job History. The Report Job History page displays information about running and completed report jobs. You can see the report job that you created in the list as shown in the screen below. ( Note that the job status indicates success). Click the job name link ( Employee Salaries by Department -Sched) . You can also enter appropriate Search criteria to search for reports or search for the job history for a specific report. Observe the various search criteria that you can use.
2 .	General job information and other job execution details are deplayed for the specific report. Click DeptSal (in the Output Name column) , to see the scheduled report output using the Departmental Salaries layout that you created. Click Return in BI Publisher. Note: You can also click the Republish option to republish the scheduled job with a different layout and output options.
3 .	Similarly, click Report Jobs to see the Manage Report Jobs page. This page page displays information about jobs that are scheduled for future dates, and also recurring report jobs. You can pause, resume, edit, or delete report jobs from this page.
4 .	Click in the column beside a job name to select that job .Click Pause to suspend the job. (Observe that the job status changes to Paused.) If you want to resume the same, click Resume. Similarly, click Delete if you would like to delete the job.

Summary

In this tutorial, you have learned how to:

Configure Data Sources
Create Data Model based on SQL query
Create and view simple reports
Build layouts using Layout Editor
Schedule Report Jobs
Manage Job History and Jobs

Outlook Email Extractor

2011-08-23T12:01:00.003+06:00

Outlook Addons Programming - Part I

Adding CommandBars to Outlook 2003 using C#

Outlook Email Extractor

Outlook Mail Content Reading and Exporting it in to Text File

Outlook Automation

MiniCRM

Introduction

Sooner or later, we all have to face the problem of an overloaded number of emails and mail attachments to our Outlook account. Some of them are important to our business, as well, so we really need to save these email messages and attachments. Manually saving messages and attachments is a time-consuming process. So, we need to export these emails and other Outlook items to PST (Personal Storage File) files. The PST file is an archive for all items in an Outlook account. So, after exporting this file to storage media, we can process the PST file to get loose data and messages out of it. In this article, I am going to show you how we can extract this PST file through a .NET application.

Background

Basically, to deal with Outlook in a .NET application, we need an interface named MAPI. MAPI stands for Messaging Application Programming Interface and is provided by Microsoft for Outlook programming. Using MAPI, we can access folders like Inbox, Drafts, Sent Items and so on that reside in a PST file. We can even create sessions and namespace to fetch items from Outlook or, say, from a PST file. Currently, MAPI is the only namespace provided by Microsoft to extract PST files. For further information on MAPI, I suggest newbies visit the relevant MSDN article.

.....................
http://www.addtoany.com/buttons/

Synchronizing a Local Data Store with Microsoft Outlook

2011-08-20T10:50:00.001+06:00

Synchronizing a Local Data Store with Microsoft Outlook

Applies to:
   Microsoft Office Outlook 2003
   Microsoft Visual Studio 2005
   Microsoft Visual Studio 2005 Tools for the Microsoft Office System
   Microsoft SQL Server 2005, Express Edition
Summary: Utilize a SQL Express database as a local data cache and the programmability of Microsoft Outlook to integrate enterprise CRM data within the Outlook user interface. (25 printed pages)
Click here to download The CRM Integration for Outlook sample.

Executive Summary
Introduction
Local Data Store
Data Synchronization Requirements
Implementation Commentary
Conclusion
Companion Resources

Executive Summary

Many organizations have made multi-million dollar investments in customer relationship management (CRM) systems and line of business (LOB) applications over the last decade. Although many of these applications have delivered significant business benefits, some organizations have found that these bespoke systems have not provided the expected returns on investment. One of the argued reasons for this is that specialized enterprise systems, databases, and applications do not integrate well with information worker workflows.
This paper is the second in a series of papers that presents an architectural design guide and sample application that demonstrates an approach for integrating enterprise CRM and other LOB applications with Microsoft Outlook. The guidance in this paper originated from Microsoft's Project Elixir, an internal Microsoft IT initiative to integrate critical customer data with Outlook. The design uses Microsoft Visual Studio 2005 Tools for Office (VSTO), Microsoft SQL Server 2005 Express Edition (SQL Server Express), and Web services.
To support disconnected operation, and to improve performance and keep the size of the user's mailbox to a minimum, the sample solution uses a local data cache in a SQL Server Express database. This paper discusses the structure of the SQL Server Express database and examines how data is synchronized between the data files in Outlook and the local database. For more information about the overall solution architecture, see Extending Enterprise Applications with Microsoft Outlook: Architectural Design Guide. For more information about the other papers in the series and links to the sample code, see the Companion Resources section at the end of this paper.

Introduction

In most industries in which people work with information, Microsoft Office is an integral part of daily work. Over four hundred million people rely on Microsoft Office applications to capture, publish, and process critical business information. It is an essential tool for creating content, making decisions, and communicating with one another. In large organizations, this critical business information often resides in large databases. For example, customer contact information is often maintained in dedicated, centralized CRM applications.
Unfortunately, many of these LOB applications provide a poor user experience, with cumbersome and unintuitive client interfaces. Often the applications do not integrate well with the desktop productivity applications that information workers use constantly, such as Microsoft Office, and Outlook in particular. Users increasingly require application interfaces that are:

Easy to learn and use.
Tailored to specific job functions and roles.
Compatible with daily workflows.
Integrated with their other applications.

Information workers need to be able to analyze, manipulate, and act on the information in business systems. Information restricted to corporate databases with limited and awkward access is of little benefit. The challenge is to provide a coherent and aggregated view of business data for information workers to enable them to collaborate more effectively with coworkers; to exercise oversight; and to make quicker, more localized decisions to help drive the business.
This paper describes a sample Outlook add-in solution that Microsoft developed to provide access to back-office data held in a fictitious CRM system through the Outlook user interface. The sample add-in provides custom forms and custom processing inside the familiar Outlook user interface (UI).
The solution architecture adopted by the sample supports disconnected operations to enable information workers to continue using the system even without network connectivity. To support this, data from the fictitious CRM system is cached in a local SQL Server Express database. Some of the CRM data in the local cache corresponds to information already cached in the local data file in Outlook. Other cached CRM data, although related to the Outlook data, is not normally kept in this file. This paper describes the operation of the local synchronization of data between Outlook and the SQL Server Express cache.
The sample CRM add-in solution code discussed in this paper is available for download at the Microsoft CRM Integration Sample for Outlook Source Setup download page.

Architecture

The solution architecture consists of a custom Outlook add-in built with Visual Studio Tools for Office, a local SQL Server Express database that serves as a local data cache, and a remote synchronization agent (CRM Sync Agent). The add-in contains a local synchronization engine (Sync Engine) that synchronizes data between Outlook and the local SQL Server Express cache, while the CRM synchronization agent synchronizes data between the local cache and the back-office CRM system. Figure 1 shows the high-level solution architecture for the sample CRM add-in.

Figure 1. Overview of the sample CRM add-in system components (Click on the image for a larger picture)

The sample CRM add-in that accompanies this whitepaper provides source code for all aspects of the described architecture except for the CRM Sync Agent. Due to the complexity and diversity of enterprise back-office CRM systems, it was not possible to provide a simple, generic CRM Sync Agent.

Outlook Add-in

A core component of the sample solution architecture is the Outlook VSTO add-in, which extends the Outlook UI by displaying a custom toolbar and custom forms to display CRM data. The add-in is registered with Outlook when the solution is deployed.
The add-in integrates with Outlook four types of CRM data retrieved from a Web service that provides an interface to the remote CRM System. The data includes CRM accounts, CRM activities, CRM contacts, and CRM opportunities. The data is exposed as a variety of Outlook objects within the folder hierarchy of the Outlook folder list. Each CRM account is associated with a custom folder in the Outlook folder list window. Each account folder contains a set of CRM activities, CRM contacts, and CRM opportunities related to it. At the root of the CRM folder hierarchy is the CRM Today folder, which provides an overall summary of all the CRM information.
When the user starts Outlook, the registered sample add-in is loaded and the initialization code performs the following tasks:

Determines whether the CRM Today folder exists. If it does not, the code creates the folder.
Folders and items in Outlook are synchronized with data that has been loaded into the local SQL Server Express database from the remote CRM system.
Account folders in the local database are synchronized with Outlook data, and for each account folder, items and their properties in the local database are synchronized with the Outlook data.
A custom CRM toolbar and buttons are added to the standard Outlook UI.
Additional custom forms display within Outlook to show the CRM data.

The add-in itself handles the data synchronization between the Outlook objects and the local database file. To do this, the internal architecture of the add-in separates the code into the following three main areas, as shown previously in Figure 1.

Managed Utility Classes. These classes simplify common Outlook development tasks and enhance the ease with which a managed code Outlook add-in can be built. The classes were designed to complement and extend the baseline Outlook extensibility provided by the native Outlook object model.
Sync Engine. The engine performs synchronization between data rows in the SQL Server Express database and Outlook objects in the Outlook data store.
Data Access Code. This code handles data access to the local SQL Server Express database.

The AddInLoader component, also shown in figure 1, uses the Visual Studio 2005 Tools for Office (VSTO 2005) run-time engine to provide a standard mechanism for loading both document-level customizations and application-level add-ins into Office applications. For more information about the AddInLoader component, see the paper, Architecture of the Outlook Add-in Support in Visual Studio 2005 Tools for Office.
The Outlook add-in template in VSTO 2005 creates a project that references the VSTO runtime and Office interop assemblies to enable the add-in code to work with the COM Outlook object model. However, the organization of the COM Outlook object model is generally not familiar to .NET programmers. So the sample CRM add-in solution architecture adds a further layer of managed utility classes. This managed utility classes layer was developed with reuse in mind to simplify common Outlook development tasks, and could be useful in other Outlook add-in solutions.
For more information about how the Outlook UI was customized and extended to display CRM data and custom forms for this sample add-in see "Outlook Customization for Integrating with Enterprise Applications" at http://msdn.microsoft.com/architecture/default.aspx?pull=/library/en-us/dnbda/html/OtlkCustInEntApp.asp.

Local Data Store

Many add-in users, such as an organization's mobile sales force, need to be able to use the add-in while not connected to the corporate network. To support disconnected operations, the sample CRM add-in uses a local data store to enable Outlook and the add-in to operate even when the remote CRM system is not available. When operating offline, the add-in saves changes to the local data store. Then, when the network is detected, the CRM synchronization agent synchronizes changes with the back-end CRM system.
Depending on the amount of data changes, the synchronization activity can be time consuming and resource intensive. Using a local data store enables the add-in and synchronization agent for the CRM system to operate independently. This configuration can provide enhanced performance, because the CRM synchronization agent can run when spare local processing and network capacity are available.
You have a number of possible options for local data storage. The following section examines the rational for the sample implementation using a SQL Server Express database, as well as other alternatives available to you.

Choosing a Local Data Store

The following choices represent the main options for a local data store:

Outlook data file
Local data file
Local database

The pros and cons of each approach are discussed below.

Outlook Data File

Using the Outlook data file might be an instinctive choice. For example, if you wanted to synchronize a limited number of contacts from a CRM system with the standard Outlook contacts, using this approach might be appropriate because the Outlook object model gives you the ability to create, modify, and delete Outlook objects.
In many CRM synchronization scenarios, the data fields in the CRM objects to be synchronized (for example, contacts, activities, and so on) have corresponding fields in the native Outlook objects. However, there are some additional fields associated with a CRM object that must be integrated with Outlook, but do not exist in a standard Outlook data file. Fortunately, you can extend Outlook data by using custom properties. For example, you could create an additional property, such as Sales Pipeline Status, to a standard Outlook Task object to accommodate an additional data field from a CRM system. You can access and display these new properties from the add-in custom forms. Similarly, you can program the add-in to connect to the remote CRM system by using Outlook custom fields to track the synchronization of data.
Unfortunately, using the Outlook data file is not always an efficient option. As the quantity of data increases, performance can be impacted. In addition, the Outlook object model is not relational. This results in slow iterations of collections and requires you to develop and maintain large amounts of code.
Another potential issue with using the Outlook file occurs when you use Outlook in conjunction with a Microsoft Exchange server. As the Outlook file grows, the performance of the Exchange replication process decreases. You might, for example, need to change the Exchange setup to increase mailbox size to accommodate the extra data. Such a change might be unpopular within some organizations that want to maintain the stability of their Exchange installation by minimizing adjustments to it.

Local Data File

A local data file external to Outlook can provide advantages over using the Outlook data file itself. The Outlook add-in and agents of the remote CRM system can access an external file without having to run inside the Outlook add-in. This means that the synchronization can take place at different times, resulting in improved performance for users.
The local data file could be a simple Extensible Markup Language (XML) file, or a customized binary format. XML has the advantage of interoperability with other systems, but has the disadvantages of size and inefficient parsing and processing. Also, the .NET Framework provides rich support for XML processing. Although a custom binary file could offer performance advantages over XML, the development complexity increases.

Local Database

Using a local database as a local data store provides an "open" data structure and relational processing capability. Using a local database also means that you can use SQL for data operations. In addition, Visual Studio supports the development of data access code for many database systems that you can use locally.

Choosing to Use SQL Server Express

The sample CRM add-in uses SQL Server Express to implement the data store because SQL Server Express provides several key benefits:

Extensive support in Visual Studio. This provides a seamless development experience to create and structure the database, and to implement and debug the data access code that the add-in requires.
Ease of deployment. You can prepare a SQL Server Express data file with the required schema and then deploy it with the add-in using a simple file copy.
Performance. SQL Server Express runs SQL and performs fast relational processing that is automatically tuned.
Synchronization support. SQL Server Express can take part in SQL Server database replication, which provides one possible synchronization option for the back-end CRM data.

The openness of SQL Server Express also makes it easier to develop additional agents in the future to synchronize data with additional LOB applications. The database can be extended as required for each remote system.

SQL Server Express Database Schema

You can prepare a SQL Server Express data file with the schema for a custom CRM add-in to use, and then deploy it by using the add-in. This section shows you the structure of the CRM database from the sample solution that you can use as a model. You can use Visual Studio both to create a SQL Server Express database and to add tables and data columns.
For more information about how to use and administer SQL Server Express, see SQL Server Express Books Online.
Figure 2 shows the schema of the sample SQL Server Express database. This is an example of how the client database could be structured for a CRM system.

Figure 2. The local SQL Server Express database schema for the sample CRM add-in (Click on the image for a larger picture)

The sample local database holds the CRM data in a normalized schema with the following tables:

Accounts. Stores customer account data.
Activities. Stores CRM activity data; relates to accounts.
Contacts. Stores customer contact information; relates to accounts.
Opportunities. Stores CRM opportunity data; relates to accounts.
ActivityContact. Stores the relationship between a CRM activity and its contacts (for the same account).
OpportunityActivity. Stores the relationship between a CRM activity and its opportunities (for the same account).
OpportunityContacts. Stores the relationship between a CRM opportunity and its contacts (for the same account).

Primary and Foreign Keys

Each table in the local database has three keys.

LocalID. The table primary key. LocalID has been designed as a SQL type unique identifier. This means that each row can have its own globally unique identifier (GUID) generated.
OutlookID. Holds copies of the keys that Outlook uses. Outlook maintains a key on all its folders and items, and this is represented in the local database in the column OutlookID of type varchar.
RemoteID. Indicates the position of a key for a remote CRM system.

Details about the keys and synchronization data held in the local database from the remote CRM system are beyond the scope of this paper. The columns RemoteID, RemoteVersion, and SyncStatus indicate the kind of columns that might be used during synchronization with a remote CRM system.

Additional Columns

Some of the other table columns are present in the schema to provide a local store for data that is also kept in the Outlook data file, but this needs to be synchronized with the remote CRM system so that they become available to other users. For example, in the Activities table column, Name, DueDate, and Comments correspond to the Outlook fields Subject, DueDate, and Body.
The sample add-in solution implements a generic local synchronization engine that has been designed to work in a broad array of Outlook data synchronization scenarios. The mapping between data columns and Outlook item properties works declaratively with custom C# attributes that are used to indicate the relationship between data columns and their corresponding Outlook fields. The synchronization engine reads the custom attributes at runtime.
Other columns are present in the schema, but have no equivalent in Outlook. These columns provide local storage for data used in the custom forms that the add-in provides, and they are also synchronized with the remote CRM system. For example, in the Activities table, the columns Type, Category, and Purpose exist in the CRM system, but have no corresponding fields in a standard Outlook installation.

Data Synchronization Requirements

In the sample CRM application, changes made to the data in the Outlook add-in are saved in the Outlook data file and the local database. To keep the enterprise data systems and other CRM users up-to-date, it is assumed that the local changes are then written to the back-end CRM system, but an implementation of this remote synch code is not included in the sample.
Similarly, changes that other user make are loaded into the local database by the agent and are synchronized with the Outlook data when the add-in starts. Through this mechanism, changes to the CRM data are synchronized between users of the add-in and users of the back-end CRM system. The route that the data takes through the local database and the CRM synchronization agent operates independently of any synchronization of data between Outlook and Exchange.
This section describes the general data synchronization patterns for an Outlook add-in using a local SQL Server Express database as a local cache of CRM data, and includes:

Start-up synchronization for Outlook and local database.
Standard form data synchronization.
Custom form data synchronization for the data that the add-in custom forms use.
The generalized synchronization engine.

You can use the results of the analysis presented in the subsequent sections to help guide your implementation of local data synchronization.

Start-up Synchronization

When planning data flows, there are three primary considerations:

Data location. You need to determine if the data will be stored in both the Outlook file and the local database, or only stored in the local database.
Data operations. You need to establish the operations that will be performed on the data (read, write, delete).
Data modifiers. You need to determine which components will accomplish the data operations (the add-in, Outlook, or both).

Figure 3 shows part of the start-up synchronization process in which the rows of the Activities data table are being synchronized into Task objects in Outlook.

Figure 3. Start-up synchronization of Activities (Click on the image for a larger picture)

In this scenario, the data rows are taken to contain the master copy of the data. The database may contain some rows with an existing OutlookID key; for these rows, the fields in the corresponding Outlook items can be updated to match any new values. The dotted arrows in Figure 3 show the mapping between data columns and Outlook field names.
The database may also contain new rows without OutlookID key values. These need to be added as new Outlook items. When adding a new item to Outlook, a new value of the Outlook key (EntryID) will be generated, and you must store this in the database. Finally, items in Outlook without matching data rows need to be removed.

Standard Form Data Synchronization

The sample add-in uses the standard Outlook contact form to edit and add contacts data, and this keeps the Outlook data file up-to-date. Figure 4 shows that synchronization in the direction from Outlook to the local database is required whenever the standard Contact form is saved.

Figure 4. Standard Outlook Contact form data is synchronized into the local database (Click on the image for a larger picture)

Custom Form Data Synchronization

The sample add-in provides custom forms to allow the user to add and edit CRM data. So in addition to the start-up synchronization, the add-in needs to synchronize data from custom forms with the local database and Outlook.
Figure 5 shows a custom form using data from the local database. Three different uses of data are identified:

Read-only data.
Data for editing with no corresponding field in Outlook.
Data for editing with a corresponding field in Outlook.

Figure 5. Custom form using data from the local database (Click on the image for a larger picture)

Where CRM data is required for display only (A), the add-in requires read-only access to the data in the database. When a custom form enables the user to edit some CRM data, but the data column has no corresponding data field in Outlook (B), then the add-in requires read-write access to the database.
If a custom form enables the user to edit some CRM data that has a corresponding data field in Outlook (C), updates are required both into the database data and the Outlook item.

Generalized Synchronization Engine

Although the proceeding scenarios appear to be different at the outset, a common synchronization pattern does emerge. The custom form synchronization turns out to be a single example of synchronization for a specific type of Outlook item, whereas the start-up synchronizations are performed for every item in a folder, and there are several different Outlook item types. By designing a generalized way to handle an Outlook item, the same synchronization engine can be used for both synchronization scenarios.
In addition, the internal architecture of the sample add-in (see Figure 1) uses a separate utility layer to manage the Outlook objects. This enables the generalized synchronization engine to be separated from the detail of handling different Outlook item types, and allows the creation of a generalized component that can process any of the synchronizations required by the add-in and can be reused in future developments.

Implementation Commentary

In order to create the local database access required by the CRM add-in as quickly as possible, the productivity tools available in Visual Studio were utilized. This section shows how the data access and synchronization requirements identified in the previous section have been implemented in the sample CRM add-in.

Data Access

The synchronization requirements identify data that you can update to enable a filtered selection (in some cases) and to add and delete rows. The System.Data.DataSet type was selected as the basis for the data manipulation performed by the add-in. The .NET Framework data provider for SQL Server (the types in the System.Data.SqlClient namespace) was selected to move data in and out of the database.
An alternative approach for data access would be to use custom business objects instead of data sets. This approach is particularly attractive if custom business objects are already available.

Typed Data Sets

To provide type safety and ease of maintenance for the data manipulation code, the team chose to use typed dataset objects, and chose Visual Studio to generate the code for the properties of the DataTable and DataRow classes. The typed dataset is created in the Visual Studio dataset designer, which makes the data access implementation as easy to write and maintain as possible.

Data for Folder Synchronization

The first task for the add-in when it starts is to synchronize the data for customer account folders in the local database with the folders in Outlook.
To get the data needed for folder synchronization, the sample solution has added data tables to a typed dataset designer for all of the tables in the SQL Server Express database. The data are selected by using SQL, and commands for update, insert, and delete are generated by the designer. Note that the generated classes are split between two source files by using the partial keyword. One file contains the generated code, and the other contains space for you to adapt and extend the generated classes.
For example, Figure 6 shows the data table for the Accounts table and its adapter. The designer has generated the Fill and GetData methods for the table adapter.

Figure 6. The Accounts table adapter

Although all of the Accounts table's records are needed for the folder synchronization with Outlook, when the data for synchronizing activities, contacts, and opportunities is fetched, it must be selected for each of the accounts in turn.
To provide the data for synchronizing activities, contacts, and opportunities, the solution adds additional SQL queries to the table adapters. For example, Figure 7 shows the Activities table adapter under development.

Figure 7. The Activities table adapter

The FillByAccountID and GetDataByAccountID methods use a SQL query with a parameter (in this sample, the parameter is @accountID).

SELECT AccountID, Category, Comments, Description, 

DueDate, LocalID, Name, OutlookID, Purpose, RemoteID, 

RemoteVersion, Status, SyncStatus, Type

FROM Activities

WHERE (AccountID = @accountID)

The method generated from this SQL query can then be called by using a single parameter. This sample code illustrates the ease of coding and maintenance that results from using the dataset designer and its code generation.

CrmDataSetTableAdapters.ActivitiesTableAdapter activitiesAdapter

        = new CrmDataSetTableAdapters.ActivitiesTableAdapter();

CrmDataSet.ActivitiesDataTable activitiesTable

        = activitiesAdapter.GetDataByAccountID(account.LocalID);

Managed Utility Classes

The sample add-in solution includes a set of managed utility classes to help handle the COM interop code that the VSTO 2005 Outlook template adds. These classes help handle the folders and items that are required by the synchronization in a generalized way, simplifying common Outlook development tasks. Table 1 lists the classes that are used in the examples in this paper.

Table 1. Outlook Utility Classes

Class	Description
Folder	Helps return and create Outlook folders. Also helps to navigate the folder tree, for example by returning a root folder.
ItemAdapter	Provide a generic Outlook item type to enable polymorphic access to the baseline functionality provided by all Outlook item types (for example, e-mail, tasks, contacts, and so on). Implements a small subset of methods shared by all Outlook items: Save, Class, and EntryID. Refer to the ItemAdapter section in this paper for further information."

Generalized Synchronization Engine

The sample CRM add-in implementation also includes classes that form a generalized synchronization engine shown in the solution architecture in Figure 1 as the Sync Engine component.
This section describes how the sample CRM add-in code uses the generalized synchronization engine. Table 2 lists the synchronization engine classes.

Table 2. Synchronization Engine Classes

Class	Description
SyncDirection (enum)	Contains ItemToOutlook and OutlookToItem values.
SyncEngine	Contains methods to perform synchronization between folders and row data, and between items and row data.
SyncInfo	Helps by collecting information about an item from the item's attributes.

The SyncEngine class contains methods to perform synchronization between managed utility classes and data rows. Table 3 shows some of the methods used by the sample CRM add-in.

Table 3. Synchronization Engine Methods

Method	Description
SyncFolders	This method ensures that the folders underneath the specified parent folder match the list of folder items in the specified collection. Folders underneath the parent that are not in the collection are deleted. Folders that are in the collection are synchronized by calling the SyncOutlookFolder delegate.
SyncItems	This method ensures that the items in the folder specified and the items in the collection specified are in sync. Items missing from the folder are added. Items existing in the folder are updated as needed. Items in the folder that are not in the database are processed by the calling code through the ProcessOutlookItem delegate. If no delegate is provided, the extra items are deleted.
SyncItem	This method iterates through the properties marked with the OutlookFieldSync attribute and updates the version of each property in Outlook if it differs from the data row. Returns true if changes are made to the Outlook item, false if no changes were made.

The synchronization engine defines two delegates for running functions in the calling program during the synchronization process: ProcessOutlookItem and SyncOutlookFolder.

Interface for Data Synchronization

The synchronization engine defines the interface IOutlookSyncItem to implement on the generated typed data row classes. The interface has a single property of type string to return the Outlook key value. You can implement the interface for each of the data row classes and set and return the OutlookID property.
For example, the implementation for a row in the Activities table is shown here.

partial class ActivitiesRow : Utility.IOutlookSyncItem

{

   string Utility.IOutlookSyncItem.EntryID

   {

      get { return this.OutlookID; }

      set { this.OutlookID = value; }

   }

}

Attributes for Data Mapping

The synchronization engine uses custom attributes, attached to the data rows that it has passed, to retrieve information that maps the row properties onto corresponding properties of Outlook objects. Table 4 table shows the custom attributes.

Table 4. Synchronization Engine Custom Attribute Classes

Attribute	Targets	Properties
OutlookCustomSyncInfo	Class	MethodName. Returns the name of the data row method to call to get the mapping data.
OutlookItemSync	Class	ItemType. Returns the Outlook item type.
OutlookFieldSync	Property	FieldName. Returns the Outlook field name IsUserProp. Returns whether the property is user defined.
OutlookFolderSync	Class	FolderType. Returns the Outlook folder type. This property is defaulted to the OlFolderInbox type. FolderNameProperty. Returns the name of the folder.

The original design used the OutlookItemSync attribute on the class and the OutlookFieldSync attribute on the properties. However, when working with strongly typed datasets, the properties are automatically generated, so you cannot add the OutlookFieldSync attribute. Therefore, the OutlookCustomSyncInfo approach was developed and now each data row class has one OutlookItemSync or OutlookFolderSync attribute and one OutlookCustomSyncInfo attribute. For example, the accounts folder simply states the name of the property used for synchronization: Name. The folder type remains in its default OlFolderInbox type. The following attribute has been added to the accounts data row class.

[Utility.OutlookFolderSync("Name")]

partial class AccountsRow : Utility.IOutlookSyncItem

The activities, contacts, and opportunities data rows have OutlookItemSync and OutlookCustomSyncInfo attributes. For example, the following attributes have been added to the ActivitiesRow class.

[Utility.OutlookItemSync(Outlook.OlItemType.olTaskItem)]

[Utility.OutlookCustomSyncInfo("GetSyncInfo")]

partial class ActivitiesRow : Utility.IOutlookSyncItem

The OutlookCustomSyncInfo attribute names the method GetSyncInfo. This is defined as a static member of the data row class. For example, the following definition of GetSyncInfo has been added to the ActivitiesRow class to describe the mapping "data row to Outlook field."

public static IDictionary GetSyncInfo()

{

   Dictionary syncInfo 

      = new Dictionary();

   syncInfo.Add("Name", 

                 new Utility.OutlookFieldSyncAttribute("Subject"));

   syncInfo.Add("Comments", 

                 new Utility.OutlookFieldSyncAttribute("Body"));

   syncInfo.Add("DateNullCheck", 

                 new Utility.OutlookFieldSyncAttribute("DueDate"));

   return syncInfo;

}

Table 5 shows the mapping table provided by these attributes.

Table 5. Activities Data Mapping

String (key)	OutlookFieldSyncAttribute object
Data row property name	Outlook Field Name
Name	Subject
Comments	Body
DateNullCheck	DueDate

Note that if the alternative business objects design is used instead of datasets, then the business objects' classes need to be modified to implement the IOutlookSyncItem interface and the custom attributes added for data mapping. An advantage of a business objects design is that the OutlookFieldSync attribute can be added to the properties, and there is no need for the OutlookCustomSyncInfo approach. For example, code in a business object representing an activity might look similar to the code shown here.

[Utility.OutlookItemSync(Outlook.OlItemType.olTaskItem)]

public class CRMActivity

{

  [Utility.OutlookFieldSync("Subject")]

  public string Name

  {

    get { 

       ... 

    }

    ...

  }

  ...

}

Running the Synchronization Engine

With the data row class implementing the IOutlookSyncItem interface, and with the data table and data row class and property attributes added, the solution now passes the data table's array of data rows to the synchronization engine for synchronization. The call also passes the Outlook folder containing the items for synchronization and a direction.
You can use the managed utility classes to help retrieve folders and items from Outlook for passing into the synchronization engine. For example, to obtain a reference to the Accounts folder that is under the CRM Today folder, you need to navigate from the root, creating the folders if required, as shown here.

Outlook.MAPIFolder root = Utility.Folder.GetRootFolder(this.Session);

Outlook.MAPIFolder crmFolder = Utility.Folder.CreateFolder(

               root, "CRM Today");

Outlook.MAPIFolder accountsFolder = Utility.Folder.CreateFolder(

                                 crmFolder, "Accounts");

Now you can synchronize the account folders by passing the accounts folder and the rows of the Accounts table into the SyncFolders method, as shown here.

CrmDataSetTableAdapters.AccountsTableAdapter adapter 

   = new CrmDataSetTableAdapters.AccountsTableAdapter();

CrmDataSet.AccountsDataTable table = adapter.GetData();

Utility.SyncEngine.SyncFolders(

                                 accountsFolder, 

                                 table.Rows, SyncAccountFolder);

adapter.Update(table);

To synchronize the contents of a folder with, for example, activities, the activity folder and data rows are passed into the SyncItems method shown here.

Utility.SyncEngine.SyncItems(

                                  activityFolder, 

                                  activitiesTable.Rows, 

                                  Utility.SyncDirection.ItemToOutlook);

activitiesAdapter.Update(activitiesTable);

The SyncDirection.ItemToOutlook enumerated value tells the synchronization engine to move changes from the data rows into the Outlook items before updating the data rows with any missing Outlook keys. The updated data table is then written back to the SQL Server Express Database by using the designer-generated data adapter's Update method.

Read-only Summary Table

The sample add-in includes a CRM Today custom form that displays information in summary tables, as shown in Figure 8. This section shows how the solution binds the custom form to the data that it requires.
The data requirement for a summary table is that the data is retrieved by using SQL, bound to a grid, and then becomes read-only.

Figure 8. The CRM Today summary tables (Click on the image for a larger picture)

Figure 9. The Accounts Summary data table and table adapter

To retrieve the summary data, the solution adds a data table and adapter to the dataset designer. The selection of the data is made by SQL and includes calculated columns for the counts. Because the data only needs to be read, only the select command is needed; commands for update, insert, and delete are omitted.

Figure 10. Data access objects added to Accounts Summary form designer

To complete the data access implementation for the summary table, table adapter objects and a dataset have been added to the Accounts Summary form designer. To enable grid data binding, System.Data.BindingSource objects have been added and connected to the data tables.
To display the data from the local SQL Server Express database when the custom form loads, the data tables are simply filled.

accountsSummaryTableAdapter.Fill(crmDataSet.AccountsSummary);

activitiesSummaryTableAdapter.Fill(crmDataSet.ActivitiesSummary);

opportunitiesSummaryTableAdapter.Fill(crmDataSet.OpportunitiesSummary);

Read-only Bound Controls

The Account Profile custom form shown in Figure 11 uses data-bound label and link label controls to display information about a single selected account.

Figure 11. The Account Profile form designer

The label and link label controls are bound to a binding source that binds the Accounts data table from the dataset. The solution fills the data table when the form loads by using the Accounts table adapter object.

CRM Contacts Form

The sample CRM add-in uses the standard Outlook Contact form to edit and add contacts data. This keeps the Outlook data file up-to-date, but synchronization in one direction into the local database is required. The sample add-in sets up an event handler for the contact write event, as shown here.

Outlook.MAPIFolder contactFolder = Utility.Folder.CreateFolder(

                      accountFolder,

                      Properties.Resources.ContactsFolderName,

                      Outlook.OlDefaultFolders.olFolderContacts);

_itemManager.RegisterWriteHandler(contactFolder, 

                         new CancelEventHandler(OnContactWrite));

The new event handler calls the SyncItem method by using the SyncDirection.OutlookToItem enumerated value to indicate that the synchronization engine should move changes from the Outlook item into the data row, as shown here.

Utility.SyncEngine.SyncItem(

                 (CrmDataSet.ContactsRow)contactsTable.Rows[0],

                  item,

                  Utility.SyncDirection.OutlookToItem);

CRM Item Custom Forms

The sample CRM add-in uses two custom forms created by using Windows Forms. These have been designed for adding new and editing existing CRM items. There are forms for both CRM activities and CRM opportunities. The custom forms are coded to handle both new scenarios, edits to them, and also the creation of a new CRM item from an existing Outlook item. For example, the new activity form is run from the Create CRM Activity toolbar button.
The same form is used both to add new CRM items and to edit existing items. Figure 12 shows the custom CRM Activity form displaying data from an existing CRM activity item, ready for the user to make changes.

Figure 12. Custom CRM activity form (Click on the image for a larger picture)

Edit Existing Item

When an existing item is edited, some of the data exists in Outlook, and there is an existing Outlook key. The form needs to retrieve any additional data from the local database that belongs to the item that is being edited.
ItemAdapter
To assist handling Outlook item synchronization, the managed utility classes provide a generic Outlook item type to enable polymorphic access to the baseline functionality provided by all Outlook item types in a class called ItemAdapter. ItemAdapter implements a small subset of methods shared by all Outlook items: Save, Class, and EntryID, where EntryID is the Outlook item key.
When a custom form is editing an existing Outlook item, the solution passes an ItemAdapter object to the form constructor. This is stored as a private field called _activity. When the remainder of the data is required, the Activities table adapter's FillByOutlookID method is called and passed the EntryID property. (This gets the Outlook item key.) For example, to fill the Activities table for a certain record.

this.activitiesTableAdapter.FillByOutlookID(

                            this.crmDataSet.Activities, _activity.EntryID);

The form controls are bound to the Activities data table, so when you click the Save and Close button, the solution code saves the changed item to both to the local database and to the Outlook item by using the synchronization engine's SyncItem method. The SyncItem method returns true if the Outlook item was updated, so you can tell whether the Outlook item needs to be saved, as shown here.

if (Utility.SyncEngine.SyncItem(

     row, _activity, Utility.SyncDirection.ItemToOutlook))

{

    _activity.Save();

}

New CRM Item

When a new CRM item is added, some of the data is new in both Outlook and the local database. A new GUID is generated and a new Outlook key created when the item is added to Outlook. As for edited data, a portion of the data is kept in the local database but not in the Outlook data file.
When the form is loaded there is no existing data row, so you need to add a new row and generate a new GUID. For example:

activitiesBindingSource.AddNew();

CurrentRow.LocalID = Guid.NewGuid();

After a new data row has been added, the form code can continue in the same way as the preceding "Edit Existing Item" example. This includes using the SyncItem method that will create a new Outlook item when one does not exist.

New CRM Item from Outlook Item

If a new CRM item is to be created from an existing Outlook item, then you need to create a new data row (as show previously). As before, the form can then continue the same way as the preceding "Edit Existing Item," and call the SyncItem method when you click Save and Close. In this scenario, SyncItem will copy data from the new data row to the existing Outlook item and return true.

CRM Item Associations

CRM custom forms contain a custom control to display related items called associations to an activity. All the data for this control are kept in the local database, so you do not need to make calls to the synchronization engine. When the control is loaded, existing relationship records are loaded into a data table and then displayed in a list.
When the data in the control are ready to be saved, the solution makes a new copy of the data table. The data table rows that correspond to unselected items in the list are deleted, and data rows are created for selected list items without an existing corresponding data row. Finally, the local database is updated by using a data adapter.

Conclusion

This paper has discussed the local synchronization approach utilized in the sample CRM add-in solution to access a CRM system. By examining the sample CRM add-in, the paper has illustrated how and why a local SQL Server Express database is an optimal choice to store additional field data for a custom Outlook add-in, and how synchronization between Outlook data and local database data can be performed.
The internal architecture of the solution contains a layer that helps manipulate Outlook folders and items, and a generalized synchronization engine with methods that perform data synchronization between Outlook folders and items and data rows. Visual Studio provides graphical designers for data objects and high performance database access, and code generated classes that provide type safety and low maintenance code.
These technologies, when used in combination, minimize implementation and code maintenance costs, and deliver a good return on investment.

Companion Resources

Microsoft has developed a sample Outlook add-in solution that demonstrates how the Outlook UI can be extended and customized to create a front-end for a CRM system. For more information about the "CRM Integration Add-in Sample for Microsoft Outlook" see http://download.microsoft.com/download/9/9/C/99CD8598-2A46-48A8-9A5B-7A30D46C0856/CRM Integration Sample for Outlook Source Setup.msi.
For more information about the overall integration scenario and the technical architecture used in the sample, see the Extending Enterprise Applications with Microsoft Outlook: Architectural Design Guide whitepaper.
For a more detailed discussion on how Outlook can be customized to integrate with data from an enterprise application, see the Outlook Customization for Integrating with Enterprise Applications whitepaper.
For more information about the internal Microsoft IT project (code-named Project Elixir) to create an Outlook front-end to Microsoft's internal CRM systems, see https://members.microsoft.com/customerevidence/search/EvidenceDetails.aspx?EvidenceID=13848&LanguageID=1.

Oracle 11g Security - Getting started with standard auditing

2011-06-02T09:16:00.000+06:00

Auditing of an Oracle database has became big business. So much so that entire companies have dedicated complete solutions to the process. Let’s first get a level-set of what auditing is, at least from Oracle’s perspective. Within the 2-Day+ Security Guide, Oracle defines auditing as the monitoring and recording of selected user database actions. Within Oracle you can:

Use standard auditing (using initialization parameters and the AUDIT and NOAUDIT) to audit SQL statement, privileges, schemas, objects, and network and multitier activity.
Look at activities within the Oracle database that are always audited regardless of whether you want to mess with turning on auditing such as administrator level privileged connections, database startups & shutdowns.
Implement what is known as fine-grained auditing that enables you to create policies that define specific conditions that must be meat for auditing to occur—enabling the security administrator to audit access based on the content and triggering events.

It would seem logical, in this day and age, why we need to audit databases. But it does stand to reason that many of us still need to be convinced that malicious intent runs ramped throughout the enterprise as well as from external threats. But probably the biggest reason to understand and implement auditing is to satisfy an audit. Let’s face the facts, regulatory requirements (Sarbanes-Oxely, HIPAA, Basel II, etc.) are all very real and we must be able to prove compliancy in order to avoid hefty fines and extended litigation. In addition to regulatory requirements there is a strong need for accountability within the enterprise. I don’t know how many times I’ve implemented my own specific fine-grain auditing to prove my own actions and point, yes point the finger, towards someone else. This just doesn’t go for DBA practices, procedures, and tasks. Auditing of user activity, either through SQL*Plus, a programming tools, or end-user applications does wonders for pinpointing where problem users are and the data they’ve either deleted, inserted, or selected or tables they’ve dropped, created, or altered. And let’s not forget that usage patterns can tell a lot as well. How many employees do you think you have that ONLY work between the hours of 10am to 2pm? Auditing can help prove use, misuse, or non-use of company resources. Auditing is a powerful tool.

When thinking about auditing an Oracle database, it’s easiest to first take a look at standard auditing and see what it has to offer. Setup is easy and you can at most requires just a reboot of the database. Standard auditing will provide information about the operation being audited, the user performing the operation, and a data and time associated with the operation. Placement of the auditing records can either be placed within the database (called a database audit trail and uses the DBA_AUDIT_TRAIL view/sys.aud$ table), on the operating system in a set of files (called an operating system audit trail), or in the DBA_COMMON_AUDIT_TRAIL view which is a combination of standard and fine-grained audit log records. Also included for standard auditing is a set of views that can be used to track suspicious activities.

Setting up or enabling the standard audit trail quite easy and is outlined in the 2-Day+ Security Guide as follows:

Start Database Control
Log in as SYS with SYSDBA privileges
Click Server to display the Server subpage
Click Initialization Parameters in the Database Configuration section
Click SPFile to display the SPFile subpage. If you don’t use an SPFile just continue to the next step
Enter audit_trail in the Name field to find the AUDIT_TRAIL parameter and then click Go
Enter the type of auditing you want in the Value field where value can be:

DB – Enables Database Auditing where records will primarily be written to the sys.aud$

OS – Enables auditing records to be written to the operating system where you must also specify AUDIT_FILE_DEST which tells Oracle where to write the audit record.

NONE – Will disable standard auditing

DB, EXTENDED – does all that the type of DB does plus populating the SQL bind and SQL text CLOB-type columns of the sys.aud$ table when available

XML – will write to the operating system audit records in XML format

EXTENDED – will write to the operating system audit records in XML format but also populates the SQL bind and SQL text CLOB-types columns of the sys.aud$ table when available

Click Apply
Restart the Oracle instance

While setting up standard auditing from the control panel is quite acceptable, this is one of tasks that can also easily be accomplished through the standard PL*SQL interface.

Sqlplus / as sysdba

Check the current settings

SQL> show parameter audit

NAME TYPE VALUE

---------------------- ----------- ------------------------------

audit_file_dest string /opt/app/oracle/admin/db11/adump

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

Set the type of auditing you want by setting the audit_trail parameter

SQL> alter system set audit_trail=OS scope=spfile;

System altered.

Set the auditing destination

SQL> alter system set audit_file_dest='/opt/app/oracle/admin/db11/adump' scope=spfile;

System altered.

Restart the Oracle instance

SQL> SHUTDOWN

SQL> STARTUP

Also note that there are two additional parameters audit_sys_operations and audit_syslog_level that you should consider setting if you are concerned about the SYS account activity.

audit_sys_operations - this initialization parameter tells Oracle to turn on auditing of the SYS connections, and users connecting with the SYSDBA or SYSOPER privilege. It has either a TRUE or FALSE value
audit_syslog_level – this initialization parameter enables SYS and standard OS auditing records to be written to the system using the SYSLOG utility

Now that we’ve enables operating system auditing it is always nice to see exactly what we’ve accomplished. While this is not an exhaustive example it does touch the surface of what we are trying to accomplish here. Suppose now that we have an un-authorized access attempt (failed login) to our database through the use of the SCOTT/TIGER account.

Being good DBAs we’ve locked this account and any attempts will be now be met with ‘the account is locked’ message:

[oracle@ludwig adump]$ sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Dec 10 06:51:43 2009

ERROR:

ORA-28000: the account is locked

And now, because of auditing, this attempt will not go unnoticed and will be reported in the audit_file_dest location:

[oracle@ludwig ~]$ cd $ORACLE_HOME/adump

[oracle@ludwig adump]$ more ora_3817.aud

Audit file /opt/app/oracle/admin/db11/adump/ora_3817.aud

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /opt/app/oracle/product/11.1.0/db_1

System name: Linux

Node name: ludwig

Release: 2.6.18-8.el5

Version: #1 SMP Thu Mar 15 19:57:35 EDT 2007

Machine: i686

Instance name: db11

Redo thread mounted by this instance: 1

Oracle process number: 18

Unix process pid: 3817, image: oracle@ludwig (TNS V1-V3)

Thu Dec 10 06:48:46 2009

SESSIONID: "280057" ENTRYID: "1" STATEMENT: "1" USERID: "SCOTT" USERHOST: "ludwig" TERMINAL: "pts/1" ACTION: "100" RETURNCODE: "2800

0" COMMENT$TEXT: "Authenticated by: DATABASE" OS$USERID: "oracle"

Now that you have the settings changed you can start monitoring the activity on the SYS account. Remember standard auditing can monitor and record various user database actions such as SQL statements, privileges, schemas, objects, and network and multitier activity. Now is the time to dig in, see how far standard auditing can take you to gain compliancy and then fill in the gaps. The issue becomes whether you can provide auditors everything they need to pass your audit. If you can monitor all database traffic and take appropriate action then you will pass, otherwise you are in for a lot of work. But more on that latter.

Iron Speed Designer>Working with Microsoft Visual Studio .NET

2011-05-26T13:10:00.000+06:00

Opening a Generated Application in Visual Studio .NET

Microsoft Visual Studio 2003

Follow these steps to open an Iron Speed Designer-generated application in Microsoft Visual Studio .NET 2003:

Step 1: In Visual Studio .NET 2003, select File, Open, Project to display the Open Project dialog.
Step 2: Select an application folder from the Open Project dialog, and click the Open button.
Step 3: Select the *.CSPROJ or *.VBPROJ file, and click the Open button.

Microsoft Visual Studio 2005

Follow these steps to open an Iron Speed Designer-generated application in Microsoft Visual Studio .NET 2005:

Step 1: In Microsoft Visual Studio .NET, select File, Open, Web Site...
Step 2: Select an application folder, and click the Open button.

Visual Studio Project Files

Iron Speed Designer automatically generates Visual Studio project files for Visual Studio 2003 applications (.NET Framework 1.1). These CSPROJ and VBPROJ files are created when Iron Speed Designer initializes your application, and they can be opened in Visual Studio 2003. Iron Speed Designer automatically updates your application’s project file every time the application is built in Iron Speed Designer.
Iron Speed Designer does not create a SLN file (“solution” file), because Visual Studio .NET automatically creates one if you open any Visual Studio project file.
Note: Visual Studio 2005 does not use project files, so Iron Speed Designer does not generate them for Visual Studio 2005 (.NET Framework 2.0) applications. To open a generated application in Visual Studio 2005, open it as a "web project."

Custom Controls Generated by Iron Speed Designer

The custom controls in the application classes are designed to integrate with Visual Studio .NET's Visual Designer, and are used in the user controls (the ASCX files) generated by Iron Speed Designer. This means that if you open one of the generated ASCX files using the Visual Studio .NET Visual Designer, you will be able to see the Iron Speed Designer custom controls, edit their custom properties using their property sheets, add them to your Visual Studio .NET Toolbox, etc.
The web controls in the application classes contain special .NET meta-tags in their code to enable this integration with Visual Studio .NET. You need take no special action to use these controls. These controls are already used in the web pages generated by Iron Speed Designer, so the web pages may be opened directly in Visual Studio .NET.

Enabling and Disabling Strict Compilation Mode

You can enable or disable "strict mode" compilation in Visual Studio .NET. Locate the CompileApplication.rsp file in your application folder:

...\\CompileApplication.rsp

CompileApplication.rsp may contain the optionstrict tag. To enable strict mode, change this setting to:

/optionstrict+

To disable strict mode, change the setting to:

/optionstrict-

Compiling Applications in Debug Mode

Iron Speed Designer creates a standard .NET application so you can use the debugging features of Microsoft Visual Studio .NET to debug your applications. When an application is created, a VBPROJ or a CSPROJ file is created along with the source files. You can open this project file in Visual Studio .NET, compile your application with debugging information, and set breakpoints to stop at any point in your application.
Note: Visual Studio .NET requires an application resident on the local machine for debugging purposes.

Step 1: Open your application in Visual Studio .NET.
Step 2: In Microsoft Visual Studio, select Build, Configuration Manager. This displays the Configuration Manager dialog.
Step 3: Select a Active Solution configuration.
Step 4: Give it a name such as "Debug", and click "OK".
Visual Basic .NET applications:
In the Solution Explorer, right-click the solution’s name and select Properties. Then, select the Configuration Properties and the Build option in this group. You will notice a check box titled "Generate Debugging Information" in the panel to the right. Make sure you check this control.

C# applications:
In the Solution Explorer, right-click the solution’s name and select Properties. Select the Configuration Properties and the Build option in this group. You will notice a property titled "Generate Debugging Information" in the panel to the right. Make sure the value for this property is set to True.
Step 5: Click the "Close" button in the Configuration Manager. Notice that the project can now be compiled in "Debug" mode via Visual Studio .NET.
Step 6: Change your application’s Web.config file for debugging mode:
```
<compilation defaultLanguage="c#" debug="true" />
```
or
```
<compilation defaultLanguage="vb" debug="true" />
```
Set compilation debug="true" to insert debugging symbols (*.pdb information) into the compiled page. Because this creates a larger file that executes more slowly, you should set this value to true only when debugging, and to false at all other times. For more information, refer to the documentation about debugging ASP.NET files.
Note: For faster performance, we recommend your application be compiled in Release mode.

way to get connection for oracle developer 6i with oracle 10g +

2011-05-26T11:09:00.002+06:00

way to get connection for oracle developer 6i with oracle 10g +

>sys as sysdba
SQL> startup restrict
>alter database character set internal_use UTF8;
SQL> shutdown immediate
SQL> startup

C:\Documents and Settings\admin>sqlplus "/ as sysdba"
C:\Documents and Settings\admin>lsnrctl

What's blocking my lock?

2011-05-24T14:00:00.000+06:00

What's blocking my lock?

If you've ever gotten a phone call from an annoyed user whose transaction just won't go through, or from a developer who can't understand why her application sessions are blocking each other, you know how useful it can be to identify not just whose lock is doing the blocking, but what object is locked. Even better, you can identify the exact row that a session is waiting to lock.

Create a blocking lock

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a'); 

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR
--- ---
1   a
2   b

2 rows selected.

SQL> commit ;

Commit complete.

Now grab a lock on the whole table, still in Session 1:
SQL> select * from tstlock for update ;
And in Session 2, try to update a row:

SQL> update tstlock set bar=
  2  'a' where bar='a' ;

This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1
....     ....            ... ...      ....       ....       ....       ....        ....      ....

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.
In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.
To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from v$lock l1, v$lock l2
  3  where l1.block =1 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2
SQL> /

       SID 'ISBLOCKING'         SID
---------- ------------- ----------
       422  IS BLOCKING         479

1 row selected.

Even better, if we throw a little v$session into the mix, the results are highly readable:

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;


BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.

There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2 columns

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.
The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)
There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

Lock Modes

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.
You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.
TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

Identifying the locked object

Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session by looking at the v$session entry for the blocked session:

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
  2* from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        88519             16          171309             0

This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:

SQL> select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
  4  from v$session s, dba_objects do
  5  where sid=543
  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

And, of course, this lets us inspect the row directly.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1   a

Conclusion

We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.

Oracle DBMS_LOCK Locks Locking

2011-05-22T19:42:00.000+06:00

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmslock.sql

First Available

7.3.4

Constants

Name	Description	Data Type	Value
nl_mode	NuLl	INTEGER	1
ss_mode	Sub Shared: used on an aggregate object to indicate that share locks are being acquired on subparts of the object	INTEGER	2
sx_mode	Sub eXclusive: used on an aggregate object to indicate that exclusive locks are being acquired on sub-parts of the object	INTEGER	3
s_mode	Shared: indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks	INTEGER	4
ssx_mod	Shared SubeXclusive	INTEGER	5
x_mode	eXclusive	INTEGER	6

Dependencies

SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_LOCK'
UNION
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_LOCK';

Exceptions

Error Number	Description
ORA-20000	Unable to find or insert lock into catalog dbms_lock_allocated.
ORU-10003	Unable to find or insert lock into catalog dbms_lock_allocated.

Object Privileges

GRANT execute ON dbms_lock TO

GRANT execute ON dbms_lock TO uwclass;

ALLOCATE_UNIQUE

Allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name. Lock identifiers are used to enable applications to coordinate their use of locks

dbms_lock.allocate_unique(
lockname IN VARCHAR2,
lockhandle OUT VARCHAR2,
expiration_secs IN INTEGER DEFAULT 864000);

See dbms_lock demo

CONVERT

Converts a lock from one mode to another

Overload 1

dbms_lock.convert(
id IN INTEGER,
lockmode IN INTEGER,
timeout IN NUMBER DEFAULT maxwait)
RETURN INTEGER;

Return Values
0	Success
1	Timeout
2	Deadlock
3	Parameter error
4	Don't own lock specified by id or lockhandle
5	Illegal lock handle

See dbms_lock demo

Overload 2

dbms_lock.convert(
lockhandle IN VARCHAR2,
lockmode IN INTEGER,
timeout IN NUMBER DEFAULT maxwait)
RETURN INTEGER;

See dbms_lock demo

RELEASE

Explicitly releases a lock previously acquired using the REQUEST function

Overload 1

dbms_lock.release(id IN INTEGER) RETURN INTEGER;

Return Values
0	Success
3	Parameter error
4	Don't own lock specified by id or lockhandle
5	Illegal lock handle

See dbms_lock demo

Overload 2

dbms_lock.release(lockhandle IN VARCHAR2) RETURN INTEGER;

See dbms_lock demo

REQUEST

Requests a lock with a given mode

Overload 1

dbms_lock.request(
id IN INTEGER,
lockmode IN INTEGER DEFAULT x_mode,
timeout IN INTEGER DEFAULT maxwait,
release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;

Return Values
0	Success
1	Timeout
2	Deadlock
3	Parameter error
4	Don't own lock specified by id or lockhandle
5	Illegal lock handle

See dbms_lock demo

Overload 2

dbms_lock.request(
lockhandle IN VARCHAR2,
lockmode IN INTEGER DEFAULT x_mode,
timeout IN INTEGER DEFAULT maxwait,
release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;

See dbms_lock demo

SLEEP

Suspends the session for a given period of time

dbms_lock.sleep(seconds IN NUMBER);

exec dbms_lock.sleep(1.00);

Demo

-- create demo table

CREATE TABLE lock_test (
action VARCHAR2(10),
when TIMESTAMP(9));

GRANT insert ON lock_test TO public;

CREATE OR REPLACE PACKAGE lock_demo IS
v_lockname VARCHAR2(12) := 'control_lock';
v_lockhandle VARCHAR2(200);
v_result PLS_INTEGER;

-- obtain a lock
PROCEDURE request_lock(p_ltype INTEGER, p_retval OUT INTEGER);
-- release an existing lock
PROCEDURE release_lock(p_retval OUT INTEGER);
-- view the stored handle
FUNCTION see_handle RETURN VARCHAR2;
-- decode lock request
FUNCTION decode_req(p_result PLS_INTEGER) RETURN VARCHAR2;
-- decode lock release
FUNCTION decode_rel(p_result PLS_INTEGER) RETURN VARCHAR2;

END lock_demo;
/

CREATE OR REPLACE PACKAGE BODY lock_demo IS

PROCEDURE request_lock(p_ltype IN INTEGER, p_retval OUT INTEGER) IS
BEGIN
IF v_lockhandle IS NULL THEN
dbms_lock.allocate_unique(v_lockname, v_lockhandle);
p_retval := dbms_lock.request(v_lockhandle, p_ltype);
END IF;
END request_lock;
------------------------------------------------------------
PROCEDURE release_lock(p_retval OUT INTEGER) IS
BEGIN
IF v_lockhandle IS NOT NULL THEN
p_retval := dbms_lock.release(v_lockhandle);
END IF;
END release_lock;
------------------------------------------------------------
FUNCTION see_handle RETURN VARCHAR2 IS
BEGIN
IF v_lockhandle IS NOT NULL THEN
RETURN v_lockhandle;
ELSE
RETURN 'Not Allocated';
END IF;
END see_handle;
------------------------------------------------------------
FUNCTION decode_req(p_result PLS_INTEGER) RETURN VARCHAR2 IS
retval VARCHAR2(20);
BEGIN
SELECT DECODE(p_result,0,'Success',1,'Timeout',2,'Deadlock',
3,'Parameter Error',4,'Already owned',5,'Illegal Lock Handle')
INTO retval
FROM dual;

RETURN retval;
END decode_req;
------------------------------------------------------------
FUNCTION decode_rel(p_result PLS_INTEGER) RETURN VARCHAR2 IS
retval VARCHAR2(20);
BEGIN
SELECT DECODE(p_result,0,3, 'Parameter Error',4, 'Already owned',
5, 'Illegal Lock Handle')
INTO retval
FROM dual;

RETURN retval;
END decode_rel;
------------------------------------------------------------
END lock_demo;
/

GRANT execute ON lock_demo TO public;

set serveroutput on

-- get an exclusive lock in the current session (Session 1)
DECLARE
s VARCHAR2(200);
BEGIN
lock_demo.request_lock(6, s);
dbms_output.put_line(s);
END;
/

/* Two session request a shared lock (ss_mode). The shared lock cannot be acquired because session 1 holds an exclusive lock. Execution will stop on the request until the the exclusive lock is released. */

Session 2	Session 3
set serveroutput on DECLARE s VARCHAR2(200); BEGIN uwclass.lock_demo.request_lock( dbms_lock.ss_mode, s); dbms_output.put_line(s); INSERT INTO uwclass.lock_test (action, when) VALUES ('started', SYSTIMESTAMP); dbms_lock.sleep(5); INSERT INTO uwclass.lock_test (action, when) VALUES ('ended', SYSTIMESTAMP); COMMIT; END; /	set serveroutput on DECLARE s VARCHAR2(200); BEGIN uwclass.lock_demo.request_lock( dbms_lock.ss_mode, s); dbms_output.put_line(s); INSERT INTO uwclass.lock_test (action, when) VALUES ('started', SYSTIMESTAMP); dbms_lock.sleep(5); INSERT INTO uwclass.lock_test (action, when) VALUES ('ended' , SYSTIMESTAMP); COMMIT; END; /

-- Session 1 releases its lock
DECLARE
s VARCHAR2(200);
BEGIN
lock_demo.release_lock(s);
dbms_output.put_line(s);
END;
/

-- Execution resumes when the exclusive lock is released
SELECT TO_CHAR(when,'dd.mm.yyyy hh24:mi:ss'), action
FROM lock_test
ORDER BY when;

Reference Guide to Supply Chain Management (SCM) Features and Functions

2011-05-21T16:40:00.000+06:00

Reference Guide to Supply Chain Management (SCM) Features and Functions

Anne Le Bris and Melissa Vaes, with contributions from TEC analyst data - 5/4/2010 1:24:00 PM

This SCM reference guide provides insight into the supply chain management (SCM) features and functions currently accessible on today’s market. This guide will help you understand which features are essential for your organization and which are not.

You can also download a comprehensive guide in Excel format, with all of the nearly 2,600 SCM functions and features, at TEC’s Supply Chain Management (SCM) RFP Template page.

Before we get started, here is a short definition of the role of SCM software in supply chain activities:

What Is SCM Software?

SCM software manages product flows from supplier, to manufacturer, to customer. In brief, it manages supply and demand for an organization. According to APICS, SCM software refers to

the design, planning, execution, control, and monitoring of supply chain activities with the objective of creating net value, building a competitive infrastructure, leveraging worldwide logistics, synchronizing supply with demand, and measuring performance globally.

SCM systems integrate all features of procurement processes, warehouse management system (WMS) features and functions, transportation, and logistics, as well as other product planning modules.

To learn more about the distinction between advance planning and scheduling (APS), SCM, and enterprise resource planning (ERP), read TEC’s article Comparative Analysis: Are You Still Confused About APS, SCM, and ERP?

About This SCM Guide

Our SCM request for information (SCM RFI) template is composed of almost 2,600 criteria; consequently, we’ll focus here on the “big picture” features only.

We’ve brought SCM features together by broad category:

Warehouse management system (WMS)
Transportation management system (TMS)
International trade logistics (ITL)
Supplier relationship management (SRM)
Demand management
Supply chain analytics
Order management
Service parts planning
Product technology

These categories correspond to a high-level functional breakdown of software features. In this reference guide, we give a short explanation of how each category impacts your supply chain management processes.

If you would like more information about full listings of enterprise software functions and features (SCM, ERP, and other enterprise software categories), please see TEC’s RFP Templates.

Supply Chain Management (SCM) Software Functions and Features

SCM functions and features, submodule #1: warehouse management system (WMS)

Functionality
This category includes the general warehouse management functionalities such as warehouse configuration, bin location and product setup, inventory control, license plate tracking, quality control, picking, packing and shipping, etc.

Adaptability
The adaptability module in SCM software covers business processes, decision support, and reporting.

Warehouse management system (WMS) technology configuration
The technology configuration addresses WMS-specific functionality such as radio-frequency identification (RFID), security, and internationalization supported by each vendor. It also touches on programming and other technologies used in the development of the WMS application.

SCM functions and features, submodule #2: transportation management system (TMS)

System definition and implementation
The TMS should include tools and applications to enable to create profiles for all your contracts, associated carriers, and trade lanes for inter-modal and multi-leg moves. This should support regional as well as international transportation movements. The key to the successful operation of the TMS is a robust foundation created during the system implementation.

Transportation management operation functionality
Transportation management operation functionality allows distributors to align their transportation operations with their supply chain strategies and overall business objectives. This includes areas that take into consideration the entire shipment life cycle, from planning, network optimization, and execution, to shipment tracking and analysis.

SCM functions and features, submodule #3: international trade logistics (ITL)

Collaboration
True collaboration across a global and disparate set of entities and information systems requires a neutral and secure environment. This type of environment enables all players to engage in an electronic dialogue to collaborate in acquiring, transferring, transporting, and settling with regional and international trading partners. In addition, the data model should take into account the different roles of all participants and manage these through a set of rule-based processes. As such, the system implementation should include clear definitions in terms of buy-sell relationships, financial terms, and service level agreements, as well as related contact details and user profiles for suppliers, customers, and related service providers.

Content
The system should support all the data and information required in order to establish a true total cost of goods sold at the time of the initial buy-sell transaction. The SCM software should also enable users to track incremental costs as the shipment is processed from point of origin to final point of receipt. All costs and activities should be available at the transaction initiation point and can be classified by the primary components, which are product costs, compliance costs, and logistics costs.

Commerce
Starting with the initial request for goods—whether this is an RFI, a request for quotation (RFQ), or a formal purchase order, all information that is exchanged during the shipment life cycle should be facilitated by the international trade logistics (ITL) system. This requires a synchronized system and process, where the product and shipment requirements are integrated into an automated order fulfillment environment. The product item master and associated tables will determine the cost of the product as well as the relationship between the product and any duties or tariffs that are applicable. Tables related to customs duties and tariffs as well as associated rates of exchange and transportation costs should be available as part of the system functions. This will enable the user to obtain an estimated total cost of goods sold as well as a final cost of goods sold, to highlight any variances or discrepancies. This implies a data model that includes an understanding of all the data exchanged and processed at the product and item level, between order management systems, as well as the data exchanged with warehouse management and transportation systems.

SCM functions and features, submodule #4: supplier relationship management (SRM)

Design
Best practice dictates that strategic suppliers be involved in the new product process from the very beginning—i.e., the concept, requirements, and design definition phase. Supplier relationship management (SRM) suites support this with functionality for requirements collaboration tools, component selection tools, and bills of materials (BOMs) grading.

Sourcing
Many consider sourcing to be the heart of SRM and commodity management to be the heart of sourcing. Advanced sourcing suites are rich in analysis and decision support technology to absorb huge amounts of data quickly and make intelligent sourcing decisions. They also lay the foundation of execution through RFI, RFQ, and RFP processes, and manage performance against contracts. Risk management is an area which spans the full life cycle, in particular association with the sourcing function.

Procurement
Procurement is generally divided into material requirements planning (MRP)-driven procurement (sometimes referred to as direct materials procurement) and requisition-driven procurement (sometimes referred to as indirect or maintenance, repair, and operations [MRO] procurement), although some long-lead-time, first-run direct materials are ordered via requisitions. This division reflects substantial differences in the two methods of procurement. Management of catalogs supports requisition-driven procurement and the sourcing processes that precede MRP-driven procurement.

Fulfillment
The bulk of fulfillment functionality is traditionally performed by ERP and related systems such as order management, warehouse management, and distribution management systems. As companies virtualize and suppliers become increasingly involved in the fulfillment process, some functionality is appropriate within SRM. SRM systems should support a range of modern inbound inventory management practices, such as kanban and vendor-managed inventory (VMI) and provide visibility into the inbound pipeline. Returns management becomes important in SRM for situations where components are being returned to and repaired or replaced by suppliers.

Manufacturing
As with fulfillment, the bulk of manufacturing functionality is traditionally performed by ERP systems. However, the important supplier-facing processes of quality and engineer change order (ECO) management may be done outside the ERP system as part of an SRM suite.

Settlement
The primary SRM-related function for settlements is in reconciliation between the original order, actual received goods, and the invoice. Advanced settlement processes may also be supported, such as evaluated receipts or electronic invoice presentation and payment.

Utilities
There are several areas that span across the lifecycle categories. Specifically, you need project management utilities during design, sourcing, and manufacturing. The same is true for managing BOMs and managing cost.

Infrastructure
SRM is by nature an integrative function and requires the infrastructure to support that integration, as well as to manage the massive volume of related content, alerts, and data.

SCM functions and features, submodule #5: demand management

Promotion planning
Promotion management systems allow your organization to plan promotions with your trading partners, including simulating, executing, and evaluating the promotion performance. Some performance planning issues to be aware include the following:

Promotion plans are frequently not integrated into the demand stream.
Promotions are launched without the requisite tracking of real-time events to monitor and modify the promotions in action, during the promotion cycle.

Pricing and profit optimization
The pricing and profit module manages profitable and sellable prices for products by dimensions such as markets, demographics, and channel partners. The module also enables future evaluation by maintaining pricing logic and results. The challenge in pricing is that the source and adjustments to price come from various organizations within the enterprise and the channel partners, which impacts actual pricing and profitability. Thus, the ability to track and report history is equally important for managing pricing activities.

Forecasting
Reliable forecasts are based not only on algorithms that are appropriate to the business setting, but also on an inclusive, highly integrative process that gathers all data that can impact the ultimate demand placed upon the supply chain. Data granularity is critical to ensuring that the right product at the item level is produced or distributed. Superior forecast processes require the evaluation of historical data as well as the current demand activity, and the ability to adjust forecasts on the most current data and assumptions.

Merchandise planning
Merchandise planning analyzes demand at the item level. It allows organizations like merchants (retailers) to understand demand based on issues ranging from demographics, store locations, shelves, and support, to purchasing as well as the positioning of merchandise in the retail channel.

Life cycle planning
Life cycle planning is becoming a more popular capability. Demand characteristics change over the life of a product and require close attention to demand patterns to ensure that markets are not starved during ramp-up, or supplied with excess in later stages. In addition, firms are frequently left with excess inventory thanks to ECOs or other product changes, as well as at end-of-life, due to poor planning and visibility into demand cycles and communication of product phase-outs. Life cycle planning provides the ability to view sell-in and sell-through point of sale (POS) data and will recommend alternate curves based on early actual sales information.

Consensus planning
Consensus planning is a method to create a "one number" forecast for the enterprise. Within complex organizational structures, many professionals are responsible for planning in different areas, such as product marketing for product and product families; sales for territory sales plans; channel and alliance management for channel forecasts; finance for revenue and corporate strategic plans; and manufacturing for shipment or off-the-dock plans. The wide range of professionals involved often creates confusion, poor coordination, and missed business opportunities when sales are missed or excess inventories mount. Ultimately, a process must produce a forecast—one number—upon which the supply chain will act.

Collaborative planning
Collaboration among trading partners has become standard practice in many industries as more supply chain activities are being outsourced. Within the demand management module, collaborative forecasting must comply with process and data standards that have been validated by the Voluntary Interindustry Commerce Solutions (VICS) Council, RosettaNet, and other industry bodies that have modeled these processes for their industries. In addition, a collaborative software system must allow the ability for joint sharing and modeling of demand supply gaps between trading partners. It must allow trading partners to view, drive alternative solutions and simulations, and resolve issues around price and unit availability, which include flexibility and target replenishment levels (re-order points).

Sales and operation planning (S&OP)
S&OP is a process that employs enabling technology to balance demand and supply to create a feasible forecast that meets an enterprise's global organizational needs. Cross-functional organizations from marketing to manufacturing require visibility, simulation, and consensus building for meeting revenue, cost, and delivery needs. The ability to reallocate and reprioritize based on customer, profit, and other factors are important elements of S&OP today. It is important to determine whether the vendor supports the S&OP process with information from multiple systems. S&OP processes also ask questions around investment to improve responsiveness, customer service, reduce risk, and increase market share. Integration is important because the data to answer these questions may reside in other modules such as APS or inventory planning.

Vendor-managed inventory (VMI) replenishment
VMI replenishment allows the co-management of inventory by both customers and suppliers. Best practices allow for joint creation and analysis of the current level of inventory to support demand and keep cost down. In addition, VMI dynamically detects when inventory levels fall bellow required (agreed-to) levels and place a refill (replenishment) order.

Event planning
Event planning for various marketing events has become more complex as more firms use a rich set of trading partners, which include media, channel partners, and retailers. Product launches, special media and advertising, promotional events, or new store openings need finely tuned planning to be successful. Today, systems must move from PowerPoint-level tools to strong profit analytics for ensuring market success and return on investment (ROI).

Metrics and reporting
Metrics and reporting capabilities today must be forward-looking to report (but more importantly, prevent) negative business performance. Beyond excellent modeling and simulation capabilities, metrics systems must be real-time and predictive. They must not only record real-time events and their impacts, but also use techniques such as pattern recognition to determine processes out of tolerance and provide early detection. Preventing late orders is better than reporting late orders, naturally.

Demand management systems architecture
Demand management tools have unique and high levels of integration requirements. These requirements should be factored into your evaluation of SCM functions and features if your organization places a high priority on demand management functionality.

SCM functions and features, submodule #6: supply chain analytics

Supply chain optimization
Supply chain optimization modules allow you to design the best-fit (optimal) supply chain by time, cost, and other factors, in order to create responsive and lean supply chains. Each module has a unique specialty such as logistics or inventory. In addition, the optimized network blends and trades off all these factors.

Supply chain event management
Supply chain event management (SCEM) or supply chain network systems are a new class of solutions designed to monitor, notify, analyze, measure, and control business process and execution types of activities. These systems take advantage of new architectural principles brought about by several forces: high-availability, publish-and-subscribe architectures; tools like Java; the maturity of artificial intelligence (AI) rule-based programming capabilities; emerging agent technologies; and Web architectures and standards such as simple object access protocol (SOAP) and extensible markup language (XML). These solutions allow open, real-time views into global information, as well as the ability to pinpoint and drill into key information, sensing deviations in business plans versus execution expectations (unplanned events).

Production and supply planning
Today's supply chain planning systems have significant advantages over the manufacturing resource planning (MRP II) systems of the past. These systems incorporate up-to-date algorithms and philosophies on how supply chains work. In addition, they have a technology advantage over MRP II, in that they are memory-resident, which allows the solutions to solve simulation issues extremely quickly, with very large arrays (models). These large models solve simultaneous, multi-level, and multi-node problems that MRP II systems cannot.

SCM functions and features, submodule #7: order management

Order promising
The order promising submodule includes criteria for available-to-promise (ATP) and configuration management across multiple databases, integration of configuration management with multiple unique BOMs, and integration with demand and replenishment orders.

Inventory management and visibility
Many organizations want to provide real-time promising, so inventory visibility from multiple sources is critical.

Sourcing
A primary attribute of a distributed order fulfillment system is the ability to perform multi-stage sourcing and assembly. This requires a very open architecture to integrate seamlessly with various systems and supply chain nodes, in a real-time fashion.

Execution visibility
Once an order has been promised, keeping track while the order is built and shipped has become a critical function in the supply chain.

Inbound and assembly coordination or multi-site staging
Many orders are sourced and built by a network of partners. Frequently, notification of cancellations does not occur. Keeping these orders synchronized is critical toward meeting schedules as well as avoiding over-building or building ahead of demand.

Shipping and outbound management
Once an order has been created and built, it must be shipped. Notification of advance shipments to customers is key to seamless transportation, as well as tracing, tracking, and receiving the orders.

Order management-specific technology
A distributed architecture is key to a successful order management system, whether supply chain nodes are internal or external to the enterprise.

SCM functions and features, submodule #8: service parts planning

Planning
Service parts planning deals with the creation and replenishment of a supply network for service operations. This can include original equipment manufacturers (OEMs) and service partners, as well as service logistics providers.

Service delivery and execution
The service delivery and execution submodule includes functionality for integration with call center systems (for dispatching requirements), service response prioritization and optimization, allocation of scarce parts, and dispatch.

Workforce optimization
Workforce optimization is used for determining the right skill mix and location of personnel to support service demands. This can include on-site, co-managed personnel at the customer site.

Logistics transportation and reverse logistics
Transportation planning and execution has a significant role to play in the service supply chain. As in traditional models, there is always a trade-off in time and cost between fixed assets and delivery responsiveness in serving customers. Dynamic repair needs such as emergency breakdowns in remote settings can exacerbate delivery problems. In addition, reverse logistics issues, such as pickup of parts for delivery to third-party locations for repair, are addressed in this submodule.

Analytics and reporting
The service planning system is a wealth of information for many organizations which are accountable for product planning and design, asset and capital management, and general customer relationship management. Therefore analytics and reporting features are not just designed to enable functional excellence, but also to feed these other key areas of the value chain.

SCM functions and features, submodule #9: product technology

Architecture
Architecture refers to the framework for organizing the planning and implementation of data resources. It also refers to the way the system is designed and the manner in which all components are connected to one another.

User interface
User interface refers to the manner in which people access and interact with the software. The user interface should facilitate the user's easy operation of the software.

Platforms
The platform refers to the framework, both the hardware (e.g., type of processor) and the operating system that allows a computer or set of computers to function.

Application tools
Application tools are the components that provide the ability for an application or program to work.

Reporting
Functionality for reporting refers to technical options for generating and delivering reports.

SaaS and hosting options
This category refers to features for software-as-a-service (SaaS) or hosted solutions

Final Thoughts
A comprehensive SCM RFP will also include other general considerations, including reseller and value-added reseller (VAR) channels, along with vendor viability considerations.

We hope you’ve found this reference guide helpful. For more information about SCM features and functions, as well as functionality for other classes of enterprise software, please visit TEC’s RFP Templates page.

For more information and to start your own custom solution evaluation of SCM software, including the ability to assess and prioritize the SCM functions and features that are important to your organization, please visit TEC’s SCM Software Evaluation Center.

RetrieveTreeHierarchy / SQL server / SQL/T-SQL

2011-05-11T14:15:00.002+06:00

General way to handle trees. Fails when the sequence varchar length is exceeded - in this case 100 levels.
Author Nigel Rivett

-- create test data
create table #z (id int, name varchar(20), Parent int null)
insert #z select   1,  'foo', null
insert #z select     2,  'foo2',   null
insert #z select     3,  'foo2a',      2
insert #z select    11,  'foo2aa',     3
insert #z select    12,  'foo2aaa',   11
insert #z select    13,  'foo2ab',     3
insert #z select    14,  'foo2ac',     3
insert #z select     4,  'foo2b',      2
insert #z select     5,  'foo3',   null
insert #z select     6,  'foo3b',      5
insert #z select     7,  'foo3ba',     6
insert #z select     8,  'foo4',   null
insert #z select     9,  'fooa',       1
insert #z select    10,  'foo2ba',     4

-- Get the hierarchy
create table #tree (id int, sequence varchar(1000), levelNo int)
-- insert top level (to get sub tree just insert relevent id here)
insert #tree select id, right(space(10) + convert(varchar(10),id),10), 1 from #z where Parent is null
declare @i int
select @i = 0
-- keep going until no more rows added
while @@rowcount > 0
begin
     select @i = @i + 1
     insert #tree
     -- Get all children of previous level
     select #z.id, sequence + right(space(10) + convert(varchar(10),#z.id),10), @i + 1
     from #z, #tree 
     where #tree.levelNo = @i
     and #z.Parent = #tree.id
end

-- output with hierarchy formatted
select space((levelNo-1)*4) + #z.name 
from #tree, #z 
where #tree.id = #z.id
order by sequence

drop table #tree
drop table #z

/*  OUTPUT
foo
    fooa
foo2
    foo2a
        foo2aa
            foo2aaa
        foo2ab
        foo2ac
    foo2b
        foo2ba
foo3
    foo3b
        foo3ba
foo4
*/

Using a Tree View/Asp.net/C#/IronSpeed

2011-05-11T14:09:00.001+06:00

Using a Tree View

A Tree View can be used in an Iron Speed Designer application to display hierarchical data from the database, and when the user selects one of the nodes, the relevant information can be displayed to the user on the right.

In the example below, a tree view is used to display a list of products organized hierarchically within each category. The user can expand any Category and then select a Product. The details of the selected Product will be displayed on the right.

Iron Speed Designer allows a tree view control to be easily integrated into applications, and populate it with data from the database. The Selected Node Changed event handler is implemented to display the selected product to the right of the tree view.

In this example, an ASP:TreeView control is added to the spreadsheet grid within the Design Mode for a page. The tree control is populated dynamically when a user expands or collapses a particular section of the tree. A Populate function is called to fill the sub-tree whenever the user expands a section of the tree. The Populate function will populate the tree with either Categories or with the list of Products within a Category. An event handler is implemented to handle the selection of a specific node to display information about the product on the right.

This example extends a Show Product record page built using Iron Speed Designer. The Southwind database included with Iron Speed Designer is used for this application. Normally, the Show Product record page displays the Product based on the URL parameter. This page will be extended to display the Product selected in the Tree View. The initial display of the product specified by the URL will continue to work as-is.

The first few steps insert the tree view into the page layout, and the following steps implement the callback functions to populate the tree, and handle the selection of a node.

Layout Changes

Step 1: Create space for a tree view: Go to the Design mode on the Show Product page. Zoom out to the ShowProduct.aspx.

Step 2: Move the ProductsRecordControl two columns to the right to allocate space on the page for the Tree View control in the Quick Layout. The second column will be used to add a HTML space character ( ) to separate the tree view from the product record control.

Step 3. (Optional) Delete all the controls in the Cell with the ProductsTabContainer and replace it with a HTML space ( ) in the cell editor.

Step 4: Insert Tree View: Select the top-left cell A1. Copy and paste the following asp:TreeView Control into the Cell Editor.

Code:

TreeView Properties: The inserted tree view specifies a number properties as described below:

Property	Description
ID	Id of the tree view control (tvCategories).
runat	Indicates the ability to access this control in the code-behind.
Font-Names	Font used to display entries in the tree view.
ForeColor	The foreground text color.
ExpandDepth	The initial expansion shown to the user.
ExpandImageUrl	The image shown to indicate a node can be expanded.
CollapseImageUrl	The image shown to indicate a node can be collapsed.
OnTreeNodePopulate	The code-behind function to be called when a user clicks the node for expansion. This function will populate the products within a category.
OnSelectedNodeChanged	The code-behind function to be called when a user selects a leaf node. This function updates the product record on the right.
SelectedNodeStyle-BackColor	The background color to use to display a selected node.
SelectedNodeStyle-ForeColor	The foreground color to use to display a selected node.
Nodes	A substructure that specifies the initial list of nodes in the tree view. Only the top-level node is included at this point, and the sub-nodes will be added by NodePopulate function when it is called when the user expands a node.
LevelStyles	The styles for each of the levels. There will be three levels, so the styles for each of the three levels are defined. Currently they are the same styles, but could be different.

Step 5: Align Tree View to the top of the cell: By default, all content in the cell is shown center-aligned vertically. To display the tree view at the top, right-click and select Align, Top.

Code Changes

The following steps populate the tree view nodes, and handle the selection of a product and display the updated record control on the right.

Step 6: Implement Node_Populate: The Node_Populate method is called to populate any of the nodes in the tree control. Node_Populate will be called either for the second-level (Category) or the third-level (Products within a Category). Based on the level, either the FillCategories or the FillProductsForCategories is called to populate the tree view appropriately.

The name of the populate method (Node_Populate) is specified on the OnTreeNodePopulate property set on the Tree View control on the layout changes.

This code is inserted into the Section 1 of the code-behind file for the page (not the Controls code-behind file). Click the ShowProducts.aspx.vb tab, and expand the region “Section 1”.

Copy and paste the code below:

[C#]

Code:

public void Node_Populate(object sender, System.Web.UI.WebControls.TreeNodeEventArgs e) 
{ 
if (e.Node.ChildNodes.Count == 0) { 
switch (e.Node.Depth) { 
case 0: 
// If the parent is the top-level node, then fill with Categories 
FillCategories(e.Node); 
break; // TODO: might not be correct. Was : Exit Select 

case 1: 
// If the parent is a category (depth=1), then fill with Products for the selected Catetory 
FillProductsForCategories(e.Node); 
break; // TODO: might not be correct. Was : Exit Select 

} 
} 
} 

private void FillCategories(TreeNode parent) 
{ 
// Get all records - whereClause is Nothing 
foreach (CategoriesRecord rec in CategoriesTable.GetRecords(null)) { 
// Make sure to populate the Text as the Name, and the Value as the ID so we can 
// use it later when reading the Products for the selected Category ID. 
TreeNode node = new TreeNode(rec.CategoryName, rec.CategoryID.ToString()); 
// Populate when needed - this will ensure that only the first level is populated 
// when tree is first displayed. 
node.PopulateOnDemand = true; 
// Expand when selected. 
node.SelectAction = TreeNodeSelectAction.Expand; 

parent.ChildNodes.Add(node); 
} 
} 

private void FillProductsForCategories(TreeNode parent) 
{ 
// parent.Value will be CategoryId 
// So create a WhereClause that looks like: 
// CategoryId = 34 
// If CategoryId is 34. 
string whereStr = ProductsTable.CategoryID.UniqueName + " = " + parent.Value;
foreach (ProductsRecord rec in ProductsTable.GetRecords(whereStr))
{ 
// Populate the tree node with the name of the product and Product ID. 
// ProductID will be used in the CreateWhereClause to display the correct product. 
TreeNode node = new TreeNode(rec.ProductName, rec.ProductID.ToString()); 
// No sub nodes to populate, so the PopulateOnDemand is False 
node.PopulateOnDemand = false; 
// Display as Selected 
node.SelectAction = TreeNodeSelectAction.Select; 
parent.ChildNodes.Add(node); 
} 
} 

public void Node_Changed(object sender, EventArgs e) 
{ 
// We need to refresh the Products Record panel on the right - so 
// first null out the current record by setting the RecordUniqueId = Nothing 
// Then load data on the page again. 
this.ProductsRecordControl.RecordUniqueId = null; 
LoadData(); 
}

[VB]

Code:

Public Sub Node_Populate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.TreeNodeEventArgs)
If e.Node.ChildNodes.Count = 0 Then
Select Case e.Node.Depth
Case 0
' If the parent is the top-level node, then fill with Categories
FillCategories(e.Node)
Exit Select
Case 1
' If the parent is a category (depth=1), then fill with Products for the selected Catetory
FillProductsForCategories(e.Node)
Exit Select
End Select
End If
End Sub

Private Sub FillCategories(ByVal parent As TreeNode)
Dim rec As CategoriesRecord
' Get all records - whereClause is Nothing
For Each rec In CategoriesTable.GetRecords(Nothing)
' Make sure to populate the Text as the Name, and the Value as the ID so we can
' use it later when reading the Products for the selected Category ID.
Dim node As TreeNode = New TreeNode(rec.CategoryName, rec.CategoryID.ToString)
' Populate when needed - this will ensure that only the first level is populated
' when tree is first displayed.
node.PopulateOnDemand = True
' Expand when selected.
node.SelectAction = TreeNodeSelectAction.Expand

parent.ChildNodes.Add(node)
Next
End Sub

Private Sub FillProductsForCategories(ByVal parent As TreeNode)
Dim rec As ProductsRecord
' parent.Value will be CategoryId
' So create a WhereClause that looks like:
' CategoryId = 34
' If CategoryId is 34.
Dim whereStr As String = ProductsTable.CategoryID.UniqueName & " = " & parent.Value
For Each rec In ProductsTable.GetRecords(whereStr)
' Populate the tree node with the name of the product and Product ID.
' ProductID will be used in the CreateWhereClause to display the correct product.
Dim node As TreeNode = New TreeNode(rec.ProductName, rec.ProductID.ToString)
' No sub nodes to populate, so the PopulateOnDemand is False
node.PopulateOnDemand = False
' Display as Selected
node.SelectAction = TreeNodeSelectAction.Select
parent.ChildNodes.Add(node)
Next
End Sub

Public Sub Node_Changed(ByVal sender As Object, ByVal e As EventArgs)
' We need to refresh the Products Record panel on the right - so
' first null out the current record by setting the RecordUniqueId = Nothing
' Then load data on the page again.
Me.ProductsRecordControl.RecordUniqueId = Nothing
LoadData()
End Sub

Step 7: Implement Node_Changed: The Node_Changed method is called to whenever a user selects a leaf-node. The intermediate nodes such as the selection of a category will not raise the Node_Changed event.

The name of the selected index changed method (Node_Changed) is specified on the OnSelectedNodeChanged property set on the Tree View control on the layout changes.

This code is inserted into the Section 1 of the code-behind file for the page (not the Controls code-behind file). This code can be inserted right below the Node_Populate method described above.

The Node_Changed event simply sets the UniqueId of the Product Record Control to Nothing or NULL, and calls LoadData. The page-level LoadData function will call the LoadData for the Product Record Control and load the data for it once again.

A function defined in the next step will ensure that the currently selected product’s data will be displayed in the product record control.

[C#]

Code:

public void Node_Changed(object sender, EventArgs e) 
{ 
// We need to refresh the Products Record panel on the right - so 
// first null out the current record by setting the RecordUniqueId = Nothing 
// Then load data on the page again. 
this.ProductsRecordControl.RecordUniqueId = null; 
LoadData(); 
}

[VB]

Code:

Public Sub Node_Changed(ByVal sender As Object, ByVal e As EventArgs)
' We need to refresh the Products Record panel on the right - so
' first null out the current record by setting the RecordUniqueId = Nothing
' Then load data on the page again.
Me.ProductsRecordControl.RecordUniqueId = Nothing
LoadData()
End Sub

Step 8: Modify CreateWhereClause: When LoadData for the ProductsRecordControl is called, it in turns calls CreateWhereClause function to create a where clause to load the data from the products table. Normally the generated CreateWhereClause simply uses the Product Id specified by the “Products” URL parameter.

We will insert a few lines of code at the top of the CreateWhereClause to check if a product node is selected in the tree control. If a product node is selected, we will instead use the product ID of this selected node to form the where clause. Otherwise we will let the remainder of the generated code of CreateWhereClause handle the creation of the clause by calling the base CreateWherClause.

To override the CreateWhereClause, go to the ShowProducts.aspx page in the Application Explorer and zoom to the ShowProducts.aspx in the bread crumbs. Next, select the ProductsRecordControl in the Quick Layout. A series of code tabs are displayed at the bottom next to the Cell Editor. Select the CreateWhereClause() tab. The default generated CreateWhereClause is displayed as shown below:

Copy and paste the entire CreateWhereClause function below to replace the existing CreateWhereClause.

[C#]

Code:

public override WhereClause CreateWhereClause() 
{ 
WhereClause wc = new WhereClause(); 
ProductsTable.Instance.InnerFilter = null; 
wc = new WhereClause(); 

// Find the tree view and see if a node is selected.
// If a node is selected, formulate a where clause and return it.
TreeView tv = (TreeView) this.Page.FindControlRecursively("tvCategories"); 
if (!((tv == null)) && !((tv.SelectedNode == null)) && !((tv.SelectedNode.Value == null))) { 
wc.iAND(ProductsTable.ProductID, 
BaseFilter.ComparisonOperator.EqualsTo, 
tv.SelectedNode.Value); 
return wc; 
} 

// Otherwise return the where clause specified by the generated function.
return base.CreateWhereClause(); 
}

[VB]

Code:

Public Overrides Function CreateWhereClause() As WhereClause

Dim wc As WhereClause
ProductsTable.Instance.InnerFilter = Nothing
wc = New WhereClause()

' Find the tree view and see if a node is selected.
' If a node is selected, formulate a where clause and return it.
Dim tv As TreeView = CType(Me.Page.FindControlRecursively("tvCategories"), TreeView)
If Not(IsNothing(tv)) AndAlso Not(IsNothing(tv.SelectedNode)) AndAlso _
Not(IsNothing(tv.SelectedNode.Value)) Then
wc.iAND(ProductsTable.ProductID, _
BaseFilter.ComparisonOperator.EqualsTo, _
tv.SelectedNode.Value)
Return wc
End If

' Otherwise return the where clause specified by the generated function.
Return MyBase.CreateWhereClause()
End Function

Build and run the application to see tree view and the selected product on the right.

Step 9: (Optional) To reformat the Product Panel to align with the bottom of the TreeView simply cut and paste the two columns containg the SupplierIDLabel and field value to ReorderLevelLabel and field value as shown below.

And paste the controls under the Discontinued Label as shown below.

The final result will be a nicely formatted page with the TreeView and Products Record Panel.

Attached Images:

Attached Files:
Using_Tree_View.doc (379.00 KB, 109 views)

How do you open a Popup from Oracle Forms

2011-04-21T17:03:00.000+06:00

Using web.javascript_eval_expr Statement

Example

web.javascript_eval_expr('window.showModelessDialog("help/SomeFile.html", "''", "dialogLeft:120px; dialogTop:260px; dialogWidth:800px; dialogHeight:455px; scroll-y:on; resizable:yes; status:no; help:no;");');

web.javascript_eval_expr('window.showModalDialog("help/SomeFile.html", "''", "dialogLeft:120px; dialogTop:260px; dialogWidth:800px; dialogHeight:455px; scroll-y:on; resizable:yes; status:no; help:no;");');

web.javascript_eval_expr('window.open("help/SomeFile.html", "WinHelp", "location=no,menubar=no,left=220,top=260,width=800,height=455,toolbar=no,resizable=yes,scrollbars=yes");');

Integrating Oracle Forms 11g with JavaScript

2011-04-21T15:54:00.000+06:00

Integrating Oracle Forms 11g with JavaScript

<Do not delete this text because it is a placeholder for the generated list of "main" topics when run in a browser>

Purpose

In this tutorial you set up and run an application that demonstrates Oracle Forms and JavaScript integration. The application uses Cascading Style Sheets (CSS) and a popular third party JavaScript library called jQuery.

Time to Complete

Approximately 30 minutes

Overview

The demonstration application integrates simple forms with a dHTML menu system that controls both the form and other dHTML objects appearing in the same browser window, such as a date picker and an image viewer. With this application you can demonstrate how Oracle Forms can call out to these browser widgets and call into the Oracle Forms Runtime via the Forms client applet.
The resulting application, with the menu and the date picker visible, looks like this:

Prerequisites

Before starting this tutorial, you should:

1.	Have access to or have installed Oracle Forms version 11.1.1. You may use either Release 1 (R1) or Patch Set 1 (PS1), although the directory structures of these releases are somewhat different.
2.	Have access to an Oracle database with the scott schema ( EMP and DEPT ) installed. Warning: For security reasons, it may not be advisable to install the sample schemas into a production database. If you do install them, you should use passwords other than default passwords, although default passwords are used in the examples shown in tutorials provided by Oracle. When you are finished using the sample schemas for tutorial and demo purposes, you may drop them by issuing the following SQLPlus command for each installed sample schema: DROP USER CASCADE; If you are using the sample schemas for the first time, you may find that you must unlock the schema user, and then grant CONNECT and RESOURCE roles to it. You can do this by using Oracle Enterprise Manager, which is part of Oracle. Alternatively, you can issue the following SQLPlus commands: ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK; GRANT CONNECT, RESOURCE to scott;
3.	Use Internet Explorer 7 or later. Version 6 of IE and any version of Firefox does not allow the application's JavaScript menu (a Milonic menu) to show in front of the Forms applet. However, the Milonic menu can be used with frames; see Resources.

Definitions

This demonstration uses a few Web standards that may be unfamiliar to Forms developers, such as:

dHTML: This acronym stands for dynamic HTML, an umbrella term for using client side languages like JavaScript, or server side languages like PHP and Java Server Pages, to create dynamic (as opposed to static) Web pages.
JavaScript: JavaScript has emerged as the dominant client side scripting language. Like PL/SQL, it is pointerless, but while PL/SQL is compiled to byte code, JavaScript is interpreted. It is imperative, weakly typed, and object oriented (but prototype based rather than class based), and its functions are first-class entities.
CSS: CSS stands for Cascading Style Sheets, which you can use to define the style of a Web site. You use it in the demonstration application to make the applet appear to be a part of the larger browser page.
DOM: An understanding of the DOM, or Document Object Model, is crucial in order to be effective when programming in JavaScript. The DOM is the model by which HTML objects are represented and manipulated.

See Resources for more information on these topics.

Setting Up the Application

Use following steps to setup the application:

1.	From the expanded zip file, copy the .fmb files ( found in the JSInteg\Solutions\jsdemo `folder)` to a directory of your choice.
2 .	Open the modules in Forms Builder, connect as scott, and compile the forms to the same directory as the .fmb files.
3.	Copy the js.html file to the Forms configuration directory. The path for this directory is defferent depending on the release of JDeveloper that you are using: For Release 1 (R1): \user_projects\domains\\servers\ WLS_FORMS\stage\formsapp\11.1.1\formsapp\config For Patch Set 1 (PS1): \config\FormsComponent\forms\server\
4.	Copy these directories: img, JSCal2, lightbox05, menu , and transEffects and these files: jquery.js, jsdemo.css, jsdemo.html, and jsdemo.js to the following directory: For Release 1 (R1): \user_projects\domains\\servers\ WLS_FORMS\stage\formsapp\11.1.1\formsapp\formsweb.war\ For Patch Set 1 (PS1): \user_projects\domains\\servers\ WLS_FORMS\tmp\_WL_user\formsapp_11.1.1\e18uoi\war\
5.	Edit jsdemo.html and modify the hostname and port values according to your installation. These values are located toward the end of the file.
6.	Create a new configuration section called [javascript] in the formsweb.cfg file. In 11g, you could do this by using Enterprise Manager, but you can also edit the file manually. For the sake of simplicity, this tutorial shows manually editing the file. Add some parameters to the configuration section. The defaults for height and width are a little too big for the application. The new color scheme called swan looks good for this application, so set colorscheme and lookAndFeel to accomplish that. Make sure that JavaScript support is turned on, and also name the applet. Use the following settings: [javascript] width=440 height=300 splashScreen=no background=no colorScheme=swan lookAndFeel=oracle logo=no applet_name=forms_applet enableJavascriptEvent=true baseHTMLjpi=js.html
7 .	Edit the default.env file: Add the directory where you saved the forms to the environment variable FORMS_PATH. For example, append to FORMS_PATH: ;D:\Data\MyForms\JSIntegDemo

Running the Application

To demonstrate the features of the application, perform the following steps:

1.	Run the application by issuing the following URL in your browser, substituting the value for : http://:9001/forms/jsdemo.html You do not need to invoke the servlet URL (frmservlet) because the iframe in the application calls the Forms servlet.
2.	Use the self-explanatory JavaScript menu to navigate to submenus and perform different operations. For example, invoke the Query menu, enter an employee name, such as WARD, in the search box, and click Go. The form displays the record for the specified employee.
3.	To redisplay all the records, select Query > Execute from the menu. Navigate to the hiredate field in Employees form. Use the up- and down- arrow keys to scroll between records, noticing the corresponding change in the date picker calendar.
4.	Select a date from the calendar, which updates the hiredate of the employee record.
5 .	Select View > Departments to navigate to the Department form.
6 .	Scroll up and down to navigate between different departments, noticing the changes in various images.

Summary

In this tutorial, you learned to set up and run the sample application that demonstrates JavaScript integration in Oracle Forms 11g.
The application hides the Forms standard menu and replaces its functionality with a JavaScript-based menu system. This menu is connected to the Forms applet. The application implements Javascript-based UI widgets as extensions to the Forms widget set, seamlessly making use of these widgets.

Resources

JavaScript Integration topic in Forms Builder online help
Related information on external Web sites:
To learn more about Oracle Forms, refer to the OTN Web site

ZAKIR's Blog

WebUtil: How to Read an Excel file into an Oracle Form

Oracle BI Tutorial - Getting Started with Oracle BI Publisher 11g

Getting Started with Oracle BI Publisher 11g

Creating a SQL Query Data Set with Parameters and LOVs

Outlook Email Extractor

Introduction

Background

Synchronizing a Local Data Store with Microsoft Outlook

Synchronizing a Local Data Store with Microsoft Outlook

Contents

Executive Summary

Introduction

Architecture

Outlook Add-in

Local Data Store

Choosing a Local Data Store

Outlook Data File

Local Data File

Local Database

Choosing to Use SQL Server Express

SQL Server Express Database Schema

Primary and Foreign Keys

Additional Columns

Data Synchronization Requirements

Start-up Synchronization

Standard Form Data Synchronization

Custom Form Data Synchronization

Generalized Synchronization Engine

Implementation Commentary

Data Access

Typed Data Sets

Data for Folder Synchronization

Managed Utility Classes

Generalized Synchronization Engine

Interface for Data Synchronization

Attributes for Data Mapping

Running the Synchronization Engine

Read-only Summary Table

Read-only Bound Controls

CRM Contacts Form

CRM Item Custom Forms

Edit Existing Item

New CRM Item

New CRM Item from Outlook Item

CRM Item Associations

Conclusion

Companion Resources

Oracle 11g Security - Getting started with standard auditing

Iron Speed Designer>Working with Microsoft Visual Studio .NET

Opening a Generated Application in Visual Studio .NET

Microsoft Visual Studio 2003

Microsoft Visual Studio 2005

Visual Studio Project Files

Custom Controls Generated by Iron Speed Designer

Enabling and Disabling Strict Compilation Mode

Compiling Applications in Debug Mode

way to get connection for oracle developer 6i with oracle 10g +

What's blocking my lock?

Oracle DBMS_LOCK Locks Locking

Reference Guide to Supply Chain Management (SCM) Features and Functions

Reference Guide to Supply Chain Management (SCM) Features and Functions

RetrieveTreeHierarchy / SQL server / SQL/T-SQL

Using a Tree View/Asp.net/C#/IronSpeed

Layout Changes

Code Changes

How do you open a Popup from Oracle Forms

Integrating Oracle Forms 11g with JavaScript

Purpose