Outbound Data Audit Reporting

Hi Chris,

send_status  in not a column in the DB, the send_status is create by a CASE function in the DAL query.

      (case when isnull(ack_dt,0) = 0 and isnull(delivery_dt,0)=0    then 0
      when isnull(ack_dt,0) <> 0 and isnull(delivery_dt,0) <> 0 and  (row_status is null OR row_status <> 1) then 1
	  when isnull(ack_dt,0) <> 0 and isnull(delivery_dt,0) <> 0 and  (row_status is not null and  row_status = 1) then 2
      else -1
      end
      )
      as send_status,

But i think you should use row_status, where row_status is a exciting column in DB state_from_events,

Also row_status in re-created by a CASE function in the DAL query used to get outbound messages.

	  (case when isnull(ack_dt,0) = 0 and isnull(delivery_dt,0)=0    then 0
      when isnull(ack_dt,0) <> 0 and isnull(delivery_dt,0) <> 0 and  (row_status is null OR row_status <> 1) then 1
	  when isnull(ack_dt,0) <> 0 and isnull(delivery_dt,0) <> 0 and  (row_status is not null and  row_status = 1) then 2
      else 3
      end
      )
      as row_status

You can find the full query, that is used for getting the outbound messages, in this DAL file: 

C:\Program Files\Astea Alliance 15.0\Bin\Application\DAL\state_from_events\state_from_event_scroller.query

I think you can work from here, to create you dasboard.

Note: The row_status values are integer, you should map the status to correct Row Status:

0 = Waiting Delivery

1 = Succeeded

2 = Skipped

3 = Failed

Good Luck,

Robin

HI Chris,

The row status value is a computed column thus not stored in the database.  If I look at the query executed, Row Status is calculated with a case statement:

	  (case when isnull(ack_dt,0) = 0 and isnull(delivery_dt,0)=0    then 0
      when isnull(ack_dt,0) <> 0 and isnull(delivery_dt,0) <> 0 and  (row_status is null OR row_status <> 1) then 1
	  when isnull(ack_dt,0) <> 0 and isnull(delivery_dt,0) <> 0 and  (row_status is not null and  row_status = 1) then 2
      else 3
      end
      )
      as row_status,

Please keep in mind there are two tables: state_from_events and c_state_from_events.  The latter serves as an archive for whatever the value * 10  is in the global.xml for the clear state from events:

So in the above settings, data is retained in state_from_events for 14 days then is moved to c_state_from_events and retained 140 days.  After that, it is deleted so this way the tables do not become too large.