Tuesday, 15 September 2015

Query Data between two Dates using Siebel Workflow

Hi All,
recently i had a requirement to fetch data between two dates and send this data to other application in XML format.
The exact requirement was someting like,

1. Allow user to Give two dates and it should return the Records within the given date range.
2. Implement this in a workflow and expose it as a webservice.

I used the below apprach to implement this functionality.

1. Created a workflow and Used EAI Siebel Adapter Busines Service, QueryPage or Query method
2. In Search Spec i gave the below expression

[LOY Transaction.Transaction Date] >= '" + [&StartDate]+ "' AND [LOY Transaction.Transaction Date] =< '" + [&EndDate]+ "'"

this queries the data withing the given date range, the expression can be extended to support other fields as well and Checks for NULL values.

eg. IIf (
  [&TxnNumber] IS NULL ,
  "[LOY Transaction.Transaction Date] >= '" + [&StartDate]+ "' AND [LOY Transaction.Transaction Date] <= '" + [&EndDate]+ "'",
  IIf ([&StartDate] IS NULL OR [&EndDate] IS NULL ,
  "[LOY Transaction.Transaction Number] = '" + [&TxnNumber]+ "'",
  "[LOY Transaction.Transaction Number] = '" + [&TxnNumber]+ "' AND [LOY Transaction.Transaction Date] >= '" + [&StartDate]+ "' AND [LOY Transaction.Transaction Date] <= '" + [&EndDate]+ "'")

 this expession takes in 3rd variable transaction Number in Input as well and allows to query with the following options which was my actual requirement.

1. Only Transaction Number
2. Only Start and End Dates
3. Both Transaction Number and Date Range

Paging can be used if the Number of records is huge.. hope this was helpful.,..

No comments:

Post a Comment

Siebel GoTo View - Handling Realtime cases

 Hello All,  We all must have used GoTo view functionality of siebel to navigate to a particular view from current view. What if the require...