/*****************************************************************************
*
* Filename         - $Workfile:   bag_trace_v6.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema View
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   bag_trace_v6.sql  $  $Revision:   1.0  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* 
*
****************************************************************************/

PROMPT Creating view   BAG_TRACE_v6   ;

CREATE OR REPLACE FORCE VIEW   BAG_TRACE_v6   (
daydate,
location_code, 
agent_code,
action_count1,
action_count2,
action_count3,
action_count4,
action_count5,
action_count6,
action_count7,
action_count8,
action_count9,
action_count10,
action_count11,
action_count12,
action_count13,
action_count14,
action_count15,
action_count16,
action_count17,
action_count18,
action_count19,
action_count20,
action_count21,
action_count22,
action_count23,
action_count24
)
 as
  select  
	to_char(BAG_TRACE_TIME,'dd MON YYYY'),
	LOCATION_CODE,
	AGENT_CODE,
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'00',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'01',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'02',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'03',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'04',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'05',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'06',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'07',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'08',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'09',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'10',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'11',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'12',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'13',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'14',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'15',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'16',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'17',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'18',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'19',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'20',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'21',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'22',1,0)),
	sum(decode( to_char(BAG_TRACE_TIME,'hh24'),'23',1,0))
  from    bag_trace  b, tagtrak_users u
	where b.user_id = u.user_id
	and error_id = 0
  AND trace_action IN ('LOAD      ','LOAD WHERE','MWS_LOAD  ','LOG       ')
	group by 
	to_char(BAG_TRACE_TIME,'dd MON YYYY'),
	LOCATION_CODE,
	AGENT_CODE;
