When to use InlineValue attribute in Insert in Oracle EBS adapter?


In typical Oracle EBS scenarios, it is a common requirement to insert computed values into a table. For example, one might want to populate the key column using a sequence, or insert something like SYSDATE into a date column. However, if the adapter makes it mandatory to provide constant values to be inserted, this becomes impossible to achieve in a single operation.


We came up with a simple solution. Every element in an InsertRecord takes an optional InlineValue attribute, which if populated, is used as-is in the insert statement. Let me illustrate with an example.


Consider the following insert operation XML snippet:


<InsertRecord xmlns=”http://schemas.microsoft.com/OracleEBS/2008/05/TableViewRecord/SCOTT/EMP“>


  <EMPNO>1024</EMPNO>


  <ENAME>SCOTT</ENAME>


  <MGR>512</MGR>


  <HIREDATE>2006-05-31T00:00:00</HIREDATE>


  <SAL>30000</SAL>


  <COMM>33</COMM>


  <DEPTNO>101</DEPTNO>


  </InsertRecord>



This results in an insert statement that is equivalent to:


INSERT INTO SCOTT.EMP VALUES (:P0, :P1, :P2, );


Where P0, P1, P2 etc are OracleParameter instances bound to the OracleCommand. Now consider the following XML snippet:


<InsertRecord xmlns=”http://schemas.microsoft.com/OracleEBS/2008/05/TableViewRecord/SCOTT/EMP“>


  <EMPNO InlineValue=”SCOTT.EMP_SEQ.NEXTVAL“/>


  <ENAME>SCOTT</ENAME>


  <MGR>512</MGR>


  <HIREDATE InlineValue=”SYSDATE“/>


  <SAL InlineValue=”SOME_API_TO_GET_SAL()“/>


  <COMM>33</COMM>


  <DEPTNO>101</DEPTNO>


  </InsertRecord>



This results in an insert statement that looks like this:


INSERT INTO SCOTT.EMP VALUES(SCOTT.EMP_SEQ.NEXTVAL, :P1, :P2, SYSDATE, SOME_API_TO_GET_SAL(), :P3, );


Now, it is tempting to provide data values in the InlineValue attribute, but we recommend against that. <ENAME InlineValue=”SCOTT“/> would result in an error as SCOTT is not a valid identifier. You’d have to use single quotes around the name as adapter merely puts this string in the insert statement without any sanity check on the value. Therefore, as a thumb rule, avoid using InlineValue attribute for constant values.

SHAREPOINT WEBPART development

Download Visual Studio 2008 Extensions for Windows SharePoint Services (requires Windows SharePoint Services 3.0 to be installed first)

You might want to check out this post if you want to install WSS on Vista.

Sample Code
   1: using System;
   2: using System.Runtime.InteropServices;
   3: using System.Web.UI;
   4: using System.Web.UI.WebControls;
   5: using System.Web.UI.WebControls.WebParts;
   6: using System.Xml.Serialization;
   7:  
   8: using Microsoft.SharePoint;
   9: using Microsoft.SharePoint.WebControls;
  10: using Microsoft.SharePoint.WebPartPages;
  11:  
  12: namespace WebPart1
  13: {
  14:     [Guid("f40e66da-5c46-4f9e-b146-3437bacf10a1")]
  15:     [ToolboxData("<{0}:CustomPropertyWebPart runat=server></{0}:CustomPropertyWebPart>"),
  16:         XmlRoot(Namespace = "WebPart1")]
  17:     public class WebPart1 : System.Web.UI.WebControls.WebParts.WebPart
  18:     {
  19:         const string c_MyStringDefault = "Sample String";
  20:         const bool c_MyBoolDefault = false;
  21:         const int c_MyIntDefault = 20;
  22:         const float c_MyFloatDefault = 33.33f;
  23:         public enum myFarmEnum
  24:         {
  25:             barn=0,
  26:             tractor,
  27:             hay,
  28:             pitchfork
  29:         };
  30:         protected myFarmEnum _myEnum;
  31:  
  32:         // Private variables
  33:         private string _myString;
  34:         private bool _myBool;
  35:         private int _myInt;
  36:         private float _myFloat;
  37:         private System.DateTime _myDateTime;
  38:         private System.Drawing.KnownColor _myColor =
  39:             System.Drawing.KnownColor.Red;
  40:  
  41:         // Constructor
  42:         public WebPart1()
  43:         {
  44:             // Initialize private variables.
  45:             _myString = c_MyStringDefault;
  46:             _myBool = c_MyBoolDefault;
  47:             _myInt = c_MyIntDefault;
  48:             _myFloat = c_MyFloatDefault;
  49:             _myEnum = myFarmEnum.hay;
  50:             _myDateTime = System.DateTime.Now;
  51:         }
  52:  
  53:         // Creates a custom property that is a string.
  54:         // This property will be displayed as a text box in the
  55:         // property pane.
  56:  
  57:         // Create a custom category in the property sheet.
  58:         [SPWebCategoryName("Custom Properties")]
  59:         // Assign the default value.
  60:         //[DefaultValue(c_MyStringDefault)]
  61:         // Property is available in both Personalization
  62:         // and Customization mode.
  63:         [Personalizable(PersonalizationScope.User)]
  64:         [WebPartStorage(Storage.Personal)]
  65:         // The caption that appears in the property sheet.
  66:         [FriendlyNameAttribute("Custom String")]
  67:         // The tool tip that appears when pausing the mouse pointer over
  68:         // the friendly name in the property pane.
  69:         [WebDescription("Type a string value.")]
  70:         // Display the property in the property pane.
  71:         [WebBrowsable(true)]
  72:         [XmlElement(ElementName="MyString")]
  73:         // The accessor for this property.
  74:         public string MyString
  75:         {
  76:             get
  77:             {
  78:                 return _myString;
  79:             }
  80:             set
  81:             {
  82:                 _myString = value;
  83:             }
  84:         }
  85:  
  86:         // Creates a custom property that is a Boolean value.
  87:         // This property will display as a check box in the
  88:         // property pane.
  89:  
  90:         [SPWebCategoryName("Custom Properties")]
  91:         //[DefaultValue(c_MyBoolDefault)]
  92:         [WebPartStorage(Storage.Personal)]
  93:         [Personalizable(PersonalizationScope.User)]
  94:         [FriendlyNameAttribute("Custom Boolean")]
  95:         [WebDescription("Select to set value to True.")]
  96:         [WebBrowsable(true)]
  97:         [XmlElement(ElementName="MyBoolean")]
  98:         // The accessor for this property.
  99:         public bool MyBool
 100:         {
 101:             get
 102:             {
 103:                 return _myBool;
 104:             }
 105:             set
 106:             {
 107:                 _myBool = value;
 108:             }
 109:         }
 110:  
 111:         // Creates a custom property that is an integer.
 112:         // This property will display as a text box in the
 113:         // property pane.
 114:         [SPWebCategoryName("Custom Properties")]
 115:         //[DefaultValue(c_MyIntDefault)]
 116:         [WebPartStorage(Storage.Personal)]
 117:         [Personalizable(PersonalizationScope.User)]
 118:         [FriendlyNameAttribute("Custom Integer")]
 119:         [WebDescription("Type an integer value.") ]
 120:         [WebBrowsable(true)]
 121:         [XmlElement(ElementName="MyInt")]
 122:         public int MyInt
 123:         {
 124:             get
 125:             {
 126:                 return _myInt;
 127:             }
 128:             set
 129:             {
 130:                 _myInt = value;
 131:             }
 132:         }
 133:  
 134:         // Creates a custom property that is a float.
 135:         // This property will display as a text box in the
 136:         // property pane.
 137:         [SPWebCategoryName("Custom Properties")]
 138:         //[DefaultValue(c_MyFloatDefault)]
 139:         [Personalizable(PersonalizationScope.User)]
 140:         [WebPartStorage(Storage.Personal)]
 141:         [FriendlyNameAttribute("Custom Float")]
 142:         [WebDescription("Type a floating point value.") ]
 143:         [WebBrowsable(true)]
 144:         [XmlElement(ElementName="MyFloat")]
 145:         public float MyFloat
 146:         {
 147:             get
 148:             {
 149:                 return _myFloat;
 150:             }
 151:             set
 152:             {
 153:                 _myFloat = value;
 154:             }
 155:         }
 156:  
 157:         // Creates a custom property that is a System.DateTime.
 158:         // This property will display as a text box in the
 159:         // property pane.
 160:         [SPWebCategoryName("Custom Properties")]
 161:         [Personalizable(PersonalizationScope.User)]
 162:         [WebPartStorage(Storage.Personal)]
 163:         [FriendlyNameAttribute("Custom Date Time")]
 164:         [WebDescription("Type a DateTime value.")]
 165:         [WebBrowsable(true)]
 166:         [XmlElement(typeof(System.DateTime))]
 167:         public System.DateTime MyDateTime
 168:         {
 169:             get
 170:             {
 171:                 return _myDateTime;
 172:             }
 173:             set
 174:             {
 175:                 _myDateTime = value;
 176:             }
 177:         }
 178:  
 179:         public bool ShouldSerializeMyDateTime()
 180:         {
 181:             return true;
 182:         }
 183:  
 184:         // Creates a custom property that is an enum.
 185:         // This property will be displayed as a drop-down list in the
 186:         // property pane.
 187:         [SPWebCategoryName("Custom Properties")]
 188:         //[DefaultValue(myFarmEnum.hay)]
 189:         [Personalizable(PersonalizationScope.User)]
 190:         [WebPartStorage(Storage.Personal)]
 191:         [FriendlyName("Custom Enum")]
 192:         [WebDescription("Select a value from the dropdown list.")]
 193:         [WebBrowsable(true)]
 194:         public myFarmEnum MyEnum
 195:         {
 196:             get
 197:             {
 198:                 return _myEnum;
 199:             }
 200:             set
 201:             {
 202:                 _myEnum = value;
 203:             }
 204:         }
 205:  
 206:         // Creates a property that is a known system color.
 207:         // This property will be displayed as a drop-down list in the
 208:         // property pane.
 209:         [SPWebCategoryName("Custom Properties")]
 210:         [Personalizable(PersonalizationScope.User)]
 211:         [WebPartStorage(Storage.Personal)]
 212:         [FriendlyNameAttribute("Custom Color")]
 213:         [WebDescription("Select a color from the dropdown list.")]
 214:         [WebBrowsable(true)]
 215:         [XmlElement(typeof(System.Drawing.KnownColor))]
 216:         public System.Drawing.KnownColor MyColor
 217:         {
 218:             get
 219:             {
 220:                 return _myColor;
 221:             }
 222:             set
 223:             {
 224:                 _myColor = value;
 225:             }
 226:         }
 227:  
 228:         public bool ShouldSerializeMyColor()
 229:         {
 230:             return true;
 231:         }
 232:          /// <summary>
 233:         /// Gets the custom tool parts for this Web Part by
 234:         /// overriding the GetToolParts method of the WebPart
 235:         /// base class. You must implement custom tool parts in a
 236:         /// separate class that derives from the
 237:         /// Microsoft.SharePoint.WebPartPages.ToolPart.
 238:         /// </summary>
 239:         /// <returns>
 240:         /// An array of references to ToolPart objects.
 241:         /// </returns>
 242:         // public override ToolPart[] GetToolParts()
 243:         // {
 244:         //    ToolPart[] toolparts = new ToolPart[2];
 245:         //    WebPartToolPart wptp = new WebPartToolPart();
 246:         //    CustomPropertyToolPart custom = new CustomPropertyToolPart();
 247:         //    toolparts[0] = custom;
 248:         //    toolparts[1] = wptp;
 249:         //    return toolparts;
 250:         // }
 251:         /// <summary>
 252:         /// Render this Web Part to the output parameter specified.
 253:         /// </summary>
 254:         /// <param name="output">
 255:         /// The HTML writer to write out to
 256:         /// </param>
 257:         protected override void Render(HtmlTextWriter output)
 258:         {
 259:             // Write stored property values to the Web Part.
 260:             output.Write("<b>Stored Property Values</b>");
 261:             output.Write("<br><b>String: </b>" +
 262:                 this.MyString);
 263:             output.Write("<br><b>Boolean: </b>" +
 264:                 this.MyBool.ToString());
 265:             output.Write("<br><b>Int: </b>" +
 266:                 this.MyInt.ToString());
 267:             output.Write("<br><b>Float: </b>" +
 268:                 this.MyFloat.ToString());
 269:             output.Write("<br><b>DateTime: </b>" +
 270:                 this.MyDateTime.ToString());
 271:             output.Write("<br><b>Enum: </b>" +
 272:                 this.MyEnum.ToString());
 273:             output.Write("<br><b>Color Enum: </b>" +
 274:                 this.MyColor.ToString());
 275:  
 276:         }
 277:     }
 278: }

Sample Project : WebPart1.zip

For additional SharePoint development resources, navigate to http://www.mssharepointdeveloper.com/

Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

BTSUG: Oct 29th – Building InfoPath, SharePoint and BizTalk Solutions

InfoPath 2007, SharePoint 2007 + BizTalk 2006 R2!!

Aloha!!! With PDC currently on over in the US we’re going to be sinking our teeth
into some great material this month.

We’ve had some great discussions this month on our BizTalk Discussions email list
(email me if you want to get on it) – we’ve got around 50 BizTalk User group members
extending out to the US (New York) as well. Email me if you want to get on – it’s
currently invite only.

What’s cooking this month for the group?
I will focus on 3 main key players this month – InfoPath 2007, SharePoint
2007 + BizTalk 2006 R2!!
I’ve had a lot of requests on how to do this and the best way to marry all these
players up at the poker table over the years. Together you can build out some very
powerful forms based solutions (or even some part of exception handling that needs
user input).

Other questions like “Why wouldn’t I use SharePoint Workflow only?”; “Where can I
keep my InfoPath Forms?”; “Where does BizTalk fit in and do I need to install BizTalk
on my SharePoint machine?” …plus a whole bunch of others.
I’ll answer those and more.
If you have SharePoint, and want the full potential of BizTalk…….see you
there.

I’ll give you some specifics…..

Main Event on the night:
– Using InfoPath forms to drive SharePoint and BizTalk solutions.

This session will cover:

1.      What InfoPath forms comprise of.

2.      InfoPath forms communicating to SharePoint and BizTalk
via WCF.

3.      Using SharePoint and BizTalk in Harmony for serious
processing.

4.      Gotchas (no there wouldn’t be these…it all just
’works’ 😉

Meeting details:

When: Oct 29, Food at 6pm, kick off 6.30pm. Finish up around 8.30pm.
Where: Microsoft
1 Epping Road
Riverside Corporate Park
North Ryde NSW 2113 Australia.
(parking available)
Speaker: Mick Badran (your trusty User Group Host)

What’s happening in the BizTalk Community:
– PDC is currently going on in the US with new ’CTP’ bits of BizTalk vNext (Oslo),
.NET Framework v4.0 (CTP) and Visual Studio 2010 (ctp) being sampled by the audience.
I’ll share what I can with you guys so bring a HDD just in case – 40GB should cover
it J
Share the User Group Soap Box:
I always welcome a new voice and ideas at our group – if you want to share
your experiences, thoughts, “I wish I can do….. for my solution…”. Then contact
me and I’ll be more than happy to slot you in.
Q. Do you need to have presentation skills: No (just look at me) – can you
tell a story in the office or at the pub? Or at a 3 yr old b.day party? – then I want
you.
Q. Do I need a PowerPoint Slide Deck? – no!!! *death by powerpoint*
is a painful way to go……
Q. Can you capture my ’best’ side? We take you whichever way you are. J

We’re up for a great night – come along and learn how to make your BizTalk
solutions go a long way.

See you there and let me know your coming

Mick (mb: 0404 842 833)
http://sydbiz.org

Precedence while setting Application Context in Oracle EBS Adapter

The messages sent to Oracle EBS Adapter can take three message context properties (starting CTP4):

1. Application Short Name
2. Responsibility Name
3. Organization Id

We introduced these properties so that one can invoke operations on a single port with different Application Contexts for each message being passed to the port, enabling the same port to be reused.

Here is the precedence order in which the adapter reads values for properies when it sets the application context before invoking an operation:



Also note that the precedence order for application short name we’re talking about is used only to set the application context – it does not change the application to which the interface table, interface view, concurrent program, or request set belongs to.

Why have these settings in Message Context as well as the Binding Properties? Well, we don’t want you to have to set message context properties on a message every time you have to set Application Context. You can put your typically used settings in the Binding Properties, and messages will be processed with Application Context as per those settings. When you want to process a message with a different Application Context, just set the Message Context Properties on it and you can send it to the same port – Message Context Properties override settings in the Binding!

Models as Text…

I’m not able to attend the PDC this year, but its been interesting watch the
definition of Oslo take better shape after watching it for a long while.

I was reading John
Flander’s post
on the topic, where he reiterated the “Oslo as language,
model repository, and visual editor aka Quadrant” theme.  The language,
known as “M” – draws this quote from John: “visual models
are useful for a certain portion of the developer population, but for the most part
developers like to write code, which means text.”

Text is good.  I can store text in a version control system, and branch it when
needed.  I can merge it
with well-known tools later on, and compare the differences between
versions easily.  I can have multiple developers work on it at once with a sane
reconciliation process upon check-in. 

Even visual models have a textual representation behind
the scenes
struggle with these basics…because the text is not first class
– it is (often) just a convenient serialization format that is sufficiently
opaque for the benefits of text to be lost.

So I’m all for M.  It will be fun to watch the vision unfold further.