Friday 9 November 2012

Write-Back Template Example in OBIEE

The write back template is an custom messages (XML-formatted) file that contains SQL commands needed to insert and update records in the write back table and columns you have configured. It must be store in the directory
"OracleBI\web\msgdb\customMessages"

The line
<WebMessage name="SetQuotaUseID">
contain the value of the template name. This value must match with the value filled in the table properties.

The line
<writeBack connectionPool="Supplier">
contain the value of the connection pool. You must have the same name in the repository.

The line
<update>UPDATE regiontypequota SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>
contain the SQL statement to update the database.

Values can be referenced either by position (such as @1, @3) or by column ID (@{c0}, @{c2}).

A write-back template might resemble the example given below

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="SetQuotaUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypequota VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>
            <update>UPDATE regiontypequota SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>
         </writeBack>
      </XML>
   </WebMessage>
   <WebMessage name="SetQuota">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypequota VALUES(@1,@2,'@3','@4',@5)</insert>
            <update>UPDATE regiontypequota SET Dollars=@5 WHERE YR=@1 AND Quarter=@2 AND Region='@3' AND ItemType='@4'</update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis