--001 COMPLETELY LOST POSTINGS
SELECT 
  NVL(COUNT(*),0) NUMBEROFLOSTPOSTINGS,
  NVL(SUM(DECODE(ZPOS_PAIDOUT,1,0,DECODE(ZPOS_CDT,5,-ZPOS_UNITPRICE*ZPOS_QUANTITY,0)))+SUM(DECODE(ZPOS_PAIDOUT,1,-ZPOS_UNITPRICE*ZPOS_QUANTITY,DECODE(ZPOS_CDT,5,0,ZPOS_UNITPRICE*ZPOS_QUANTITY))),0) AMOUNT  
FROM 
  V8_CHECK_COMPLETELYLOSTPOSTING;
  

--002 WRONG INTERFACE POSTINGS
SELECT  
  NVL(SUM(DECODE(ZPOS_PAIDOUT,1,0,DECODE(ZPOS_CDT,5,-ZPOS_UNITPRICE*ZPOS_QUANTITY,0)))+SUM(DECODE(ZPOS_PAIDOUT,1,-ZPOS_UNITPRICE*ZPOS_QUANTITY,DECODE(ZPOS_CDT,5,0,ZPOS_UNITPRICE*ZPOS_QUANTITY))),0) AMOUNT
FROM 
  V8_CHECK_WRONGINTERFACEPOSTING;  

  
--003 ALLOWANCES WHICH SHOULD BE CLOSED ALREADY  
  
SELECT 
  NVL(COUNT(*),0) NUMBEROFALLOWANCES
FROM
  V8_CHECK_ALLOWANCESNOTCLOSED;
  

--004 LOST POSTINGS WHICH WERE PRINTED ON INVOICE BUT NO LONGER FOUND IN ZPOS
  
SELECT
  NVL(COUNT(*),0) NUMBEROFLOSTPOSTINGS
FROM
  V8_CHECK_POSTFROMINVOICELOST;

  
--005 LONELY TAX RECORDS WHITHOUT REVENUE RECORD

SELECT 
  NVL(COUNT(*),0) NUMBEROFLONELYTAXRECORDS
FROM
  V8_CHECK_LONELYTAXRECORDS;
  

--006 LONELY TAX RECORDS WHITHOUT MAIN INTERNALSPLIT POSTING (almost the same as 005)  
  
SELECT   
  NVL(COUNT(*),0) NUMBEROFLONELYTAXRECORDS
from  
  V8_CHECK_MISSINGINTERNALSPLIT;
  
  
--007 closed allowances with not enough postings   
  
select  
  NVL(COUNT(*),0) INCORRECTCLOSEALLOWANCE
from
  V8_CHECK_ALLOWANCES_INBALANCE;  

  
--008 not closed allowances for checked out guests   
  
select  
  NVL(COUNT(*),0) INCORRECTOPENALLOWANCE
from
  V8_CHECK_ALLOWANCES_NOTCLOSED;  
  
  
  
--009 Posting on no window

select  
	  NVL(COUNT(*),0) PostingWhithoutWindow
from
  V8_CHECK_NOWINDOWPOSTING;  
  

--010 Reservations not in Balance  
select 
  NVL(COUNT(*),0) reservationsoutofbalance 
from 
  v8_check_outofbalanceguests3 v 
where 
  v.AMOUNT<>0 
order by v.ZPOS_YRES_ID;   
  
  
--011 Financial Accounts not in Balance
  
select 
  NVL(COUNT(*),0) zfacsoutofbalance 
from 
  v8_check_outofbalancezfac3 v 
where 
  v.AMOUNT<>0 order by v.zpos_zfac_id;


--012 NET POSTINGS HAVING SAME ZPOS_TAXLINK_ID

select NVL(COUNT(*),0) zposnetduplicatetaxid from zpos where exists (select 1 from zpos tax where ZPOS.ZPOS_TAXLINK_ID=TAX.ZPOS_TAXLINK_ID and ZPOS.ZPOS_POSTDATE<>TAX.ZPOS_POSTDATE)
and zpos.ZPOS_CDT=1;



--013
SELECT v8_sys_check.*
      ,LTRIM(TRIM(name)) NameLeft
	  ,DECODE(MOD(rownum,2), 0, 'WhiteLine', 'GreyLine') cssclass
	  ,rownum Position
FROM   (SELECT V8_SYS_CHECK.*
              ,MOD(rownum,2) LINENUM
	    FROM   v8_sys_check
	   ) v8_sys_check;
	   
--014
SELECT ROUND(sqlinfo.elapsed_time/1000000,2) elapsedtime
      ,sqlinfo.executions
      ,sqlinfo.averageexectime
      ,TO_CHAR(sqlinfo.last_load_time,'DD-MM-YYYY HH24:MI:SS') lastloadtime 
      ,sqlinfo.sql_text sqltext
      ,sql_id
      ,DECODE(MOD(rownum,2), 0, 'WhiteLine', 'GreyLine') cssclass
FROM   (SELECT username
              ,buffer_gets
              ,disk_reads
              ,executions
              ,ROUND(((elapsed_time/1000000)/NVL(REPLACE(executions,0,1),1)),2) averageexectime
              ,buffer_get_per_exec
              ,parse_calls
              ,sorts
              ,rows_processed
              ,hit_ratio
              ,module
              ,elapsed_time
              ,sql_text
              ,sql_id
           -- ,cpu_time
           -- ,user_io_wait_time
              ,last_load_time
FROM   (SELECT
              a.sql_fulltext sql_text
           -- t.sql_text sql_text
              ,a.sql_id
              ,b.username
              ,a.disk_reads
              ,a.buffer_gets
              ,TRUNC (a.buffer_gets / a.executions) buffer_get_per_exec
              ,a.parse_calls
              ,a.sorts
              ,a.executions
              ,a.rows_processed
              ,100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio
              ,a.module
              --,a.cpu_time
              ,a.elapsed_time
              --,a.user_io_wait_time
              ,a.last_active_time last_load_time
        FROM   v$sql a, all_users b --, v$sqltext t
        WHERE  a.parsing_user_id = b.user_id
     -- AND    a.address=t.address
     -- AND    b.username NOT IN ('SYS', 'SYSTEM', 'RMAN', 'SYSMAN','DBSNMP')
     -- AND    a.buffer_gets > 10000
        ORDER BY buffer_get_per_exec DESC
       )
) sqlinfo        
WHERE ROWNUM < 21
AND sqlinfo.last_load_time>SYSDATE-1
--AND   UPPER(sqlinfo.username)='V8LIVE'
AND   UPPER(sqlinfo.username)=SYS_CONTEXT('USERENV','SESSION_USER')
AND averageexectime >= 1 
ORDER BY 3 DESC
;
