VIEW: APPS.CSM_HTML5_SYNC_HISTORY_V

Source

View Text - Preformatted

SELECT up.user_name, up.start_date start_time, down.end_date end_time,ROUND((down.end_date - down.start_date + up.end_date - up.start_date)*24*60*60) total_time,'UPLOAD_DOWNLOAD',down.session_id download_sync_Id,ROUND((down.end_date - down.start_date)*24*60*60) download_time,down.status download_status,up.session_id upload_sync_Id,ROUND((up.end_date - up.start_date)*24*60*60) upload_time,DECODE(up.status, 'ERROR', 'FAILURE', up.status) upload_status FROM (SELECT rownum up_num, a.* FROM (SELECT * FROM csm_html5_sync_info ORDER BY user_name,session_id) a ) up,(SELECT rownum down_num,a.* FROM (SELECT * FROM csm_html5_sync_info ORDER BY user_name,session_id) a) down WHERE up.sync_type = 'UPLOAD' AND down.sync_type = 'DOWNLOAD' AND up.user_name = down.user_name AND up.up_num= down.down_num-1 UNION ALL SELECT download.user_name, start_date start_time, end_date end_time,ROUND((end_date - start_date)*24*60*60),DECODE(full_sync,'Y','FULL_SYNC','N','DOWNLOAD_ONLY'),session_id,ROUND((end_date - start_date)*24*60*60),DECODE(status, 'ERROR', 'FAILURE', status) status , NULL,NULL,NULL FROM csm_html5_sync_info download WHERE sync_type='DOWNLOAD' AND session_id NOT IN (SELECT down.session_id FROM (SELECT rownum up_num, a.* FROM (SELECT * FROM csm_html5_sync_info ORDER BY user_name,session_id ) a ) up, (SELECT rownum down_num, a.* FROM (SELECT * FROM csm_html5_sync_info ORDER BY user_name,session_id ) a ) down WHERE up.sync_type='UPLOAD' AND down.sync_type='DOWNLOAD' AND up.user_name=down.user_name AND up.up_num=down.down_num-1)

View Text - HTML Formatted

SELECT UP.USER_NAME
, UP.START_DATE START_TIME
, DOWN.END_DATE END_TIME
, ROUND((DOWN.END_DATE - DOWN.START_DATE + UP.END_DATE - UP.START_DATE)*24*60*60) TOTAL_TIME
, 'UPLOAD_DOWNLOAD'
, DOWN.SESSION_ID DOWNLOAD_SYNC_ID
, ROUND((DOWN.END_DATE - DOWN.START_DATE)*24*60*60) DOWNLOAD_TIME
, DOWN.STATUS DOWNLOAD_STATUS
, UP.SESSION_ID UPLOAD_SYNC_ID
, ROUND((UP.END_DATE - UP.START_DATE)*24*60*60) UPLOAD_TIME
, DECODE(UP.STATUS
, 'ERROR'
, 'FAILURE'
, UP.STATUS) UPLOAD_STATUS
FROM (SELECT ROWNUM UP_NUM
, A.*
FROM (SELECT *
FROM CSM_HTML5_SYNC_INFO ORDER BY USER_NAME
, SESSION_ID) A ) UP
, (SELECT ROWNUM DOWN_NUM
, A.*
FROM (SELECT *
FROM CSM_HTML5_SYNC_INFO ORDER BY USER_NAME
, SESSION_ID) A) DOWN
WHERE UP.SYNC_TYPE = 'UPLOAD'
AND DOWN.SYNC_TYPE = 'DOWNLOAD'
AND UP.USER_NAME = DOWN.USER_NAME
AND UP.UP_NUM= DOWN.DOWN_NUM-1 UNION ALL SELECT DOWNLOAD.USER_NAME
, START_DATE START_TIME
, END_DATE END_TIME
, ROUND((END_DATE - START_DATE)*24*60*60)
, DECODE(FULL_SYNC
, 'Y'
, 'FULL_SYNC'
, 'N'
, 'DOWNLOAD_ONLY')
, SESSION_ID
, ROUND((END_DATE - START_DATE)*24*60*60)
, DECODE(STATUS
, 'ERROR'
, 'FAILURE'
, STATUS) STATUS
, NULL
, NULL
, NULL
FROM CSM_HTML5_SYNC_INFO DOWNLOAD
WHERE SYNC_TYPE='DOWNLOAD'
AND SESSION_ID NOT IN (SELECT DOWN.SESSION_ID
FROM (SELECT ROWNUM UP_NUM
, A.*
FROM (SELECT *
FROM CSM_HTML5_SYNC_INFO ORDER BY USER_NAME
, SESSION_ID ) A ) UP
, (SELECT ROWNUM DOWN_NUM
, A.*
FROM (SELECT *
FROM CSM_HTML5_SYNC_INFO ORDER BY USER_NAME
, SESSION_ID ) A ) DOWN
WHERE UP.SYNC_TYPE='UPLOAD'
AND DOWN.SYNC_TYPE='DOWNLOAD'
AND UP.USER_NAME=DOWN.USER_NAME
AND UP.UP_NUM=DOWN.DOWN_NUM-1)