        -- Group: vendorAPHistory
        -- Name: detail
        -- Notes: used by dspVendorAPHistory
        -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
        -- See www.xtuple.com/CPAL for the full text of the software license.
        
        --http://www.bloomandgrowdirect.com/sandbox/hk.php/Roo/Metasql?_group=apopen&_name=bydate&csvTitles=*&csvCols=*&limit=100

SELECT
    docdate,
    ap_total,
    gl_total,
    gl_count,
    ap_total - gl_total as ap_gl_diff,
    gl_jetotal,
    gl_cmtotal
    
    FROM 
    (SELECT
        docdate,
        sum(base_amount) as ap_total,
        
        -- 194 is the AP in HK..
        
        (SELECT COALESCE(sum(gltrans_amount),0)  FROM
                    gltrans
                    INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                    where
                    gltrans_source != 'G/L'
                AND
                gltrans_date =  docdate
                AND NOT gltrans_deleted
                ) as gl_total,
                
        (SELECT COALESCE(sum(gltrans_amount),0)  FROM gltrans
            INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                where
                    gltrans_source != 'G/L'
                    AND gltrans_date =  docdate
                    AND NOT gltrans_deleted 
                    AND gltrans_notes = 'CM Application'
                ) as gl_cmtotal,
         
         (SELECT COALESCE(sum(gltrans_amount),0)  FROM gltrans
                   INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                    where
                    gltrans_source = 'G/L'
                    AND gltrans_date =  docdate
                    
                    AND NOT gltrans_deleted 
                
                ) as gl_jetotal,
                 
                
        (SELECT count(gltrans_id)
                FROM gltrans
                   INNER JOIN apaccnt ON apaccnt_ap_accnt_id = gltrans_accnt_id AND apaccnt_vendtype ='.*'
                where
                    gltrans_source != 'G/L'
                    AND gltrans_date =  docdate
                    
                    AND NOT gltrans_deleted
        ) as gl_count
      
        FROM
        (
          
          
          --------FROM - ALLL
           
        
            
            SELECT apopen_id,   0 AS type,
                   apopen_distdate AS sortdate, apopen_docnumber AS sortnumber,
                   apopen_docnumber AS docnumber,
                   formatBoolYN(apopen_open) AS f_open,
                   CASE WHEN (apopen_doctype='V') THEN 'voucher'
                        WHEN (apopen_doctype='C') THEN 'creditMemo'
                        WHEN (apopen_doctype='D') THEN 'debitMemo'
                     ELSE 'other'
                   END AS documenttype,
                   apopen_invcnumber AS invoicenumber,
                   apopen_ponumber AS ponumber,
                   apopen_distdate AS docdate, apopen_duedate AS duedate, apopen_amount AS amount,
                   (apopen_amount - apopen_paid) AS balance,
                   currconcat(apopen_curr_id) AS currAbbr,
                   
                   
                  
       CASE WHEN (apopen_doctype='C') THEN ((CASE WHEN apopen_amount  = 0.0 THEN apopen_discountable_amount ELSE apopen_amount  END)) / apopen_curr_rate * -1.0
         ELSE (CASE WHEN apopen_amount  = 0.0 THEN apopen_discountable_amount ELSE apopen_amount  END)/ apopen_curr_rate
       END AS base_amount,
                   
                   
                   0 AS base_applied,
                   CASE WHEN (apopen_doctype='C') THEN (apopen_amount - apopen_paid) / apopen_curr_rate * -1.0
                     ELSE (apopen_amount - apopen_paid) / apopen_curr_rate
                   END AS base_balance
                   
            FROM apopen 
            WHERE (
                (true)
            <? if exists("vend_id") ?>
                 AND (apopen_vend_id=<? value("vend_id") ?>)
            <? endif ?>
            <? if exists("startDate") ?>     
                AND (apopen_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
            <? endif ?>
            -- if we try and use the date to determine reversed it does not help.
            
            
             AND NOT apopen_isreversed(apopen_id, apopen_docnumber, apopen_doctype, apopen_status)
    
            
             )
            
                          
             UNION
                SELECT
                checkitem_id as  apopen_id,
                
                1 AS type,
                checkhead_checkdate AS sortdate,
                checkhead_number::text AS sortnumber,
                
                checkhead_number::text AS docnumber,
                '' AS f_open,
                 'check' AS documenttype,
                ' ' AS invoicenumber,
                 '' AS ponumber,
                   checkhead_checkdate  AS docdate,
                   CAST(NULL AS DATE) AS duedate,
                   
                   checkitem_amount AS amount,
                   0 AS balance,
                   currconcat(checkitem_curr_id) AS currAbbr,
                    --- changed so base_ammount is *-1 (was 0)
                    currtobase( checkitem_curr_id, checkitem_amount,apapply_postdate ) * -1 as base_amount,
                 --  (checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
                   
                   (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
                   
                   
                   
                   0 AS base_balance 
                  
            FROM
                    checkitem
                LEFT JOIN
                    checkhead
                ON
                    checkitem_checkhead_id = checkhead_id
                 LEFT JOIN
                    apapply
                ON
                    apapply_journalnumber = checkhead_journalnumber
                    
                     
            WHERE (
                checkhead_recip_type = 'V'
             --   AND NOT checkhead_deleted  
                AND NOT checkhead_void
                
                AND checkitem_apopen_id IS NOT NULL
                
            <? if exists("vend_id") ?>    
                AND (checkhead_recip_id=<? value("vend_id") ?>)
                AND (checkhead_recip_id=<? value("vend_id") ?>)
            <? endif ?>
            <? if exists("startDate") ?>     
                AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
            <? endif ?>
            )
 
             
             -- finally union with deleted entries..
             
             UNION
             
             -- code seems to indicate teh apopen close date is when it get's applied to gl.
             -- however tests are indicating that it's the check date..
             
             SELECT
                 checkhead_id as  apopen_id,
                  
                 2 AS type,
                 checkhead_checkdate AS sortdate,
                 checkhead_number::text AS sortnumber,
                 
                 checkhead_number::text AS docnumber,
                 '' AS f_open,
                  'checkrev' AS documenttype,
                 ' ' AS invoicenumber,
                  '' AS ponumber,
                    checkhead_checkdate  AS docdate,
                    CAST(NULL AS DATE) AS duedate,
                    
                    checkitem_amount AS amount,
                    0 AS balance,
                    currconcat(checkitem_curr_id) AS currAbbr,
                     --- changed so base_ammount is *-1 (was 0)
                    currtobase( checkitem_curr_id, checkitem_amount,apopen_closedate )   as base_amount,
                    --(checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
                    
                    (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
                    
                      0 AS base_balance 
                   
             FROM
                     checkitem
                 LEFT JOIN
                     checkhead
                 ON
                     checkitem_checkhead_id = checkhead_id
                 LEFT JOIN
                     apopen
                 ON
                     apopen_id = checkitem_apopen_id 
                     
                     
             WHERE (
                 checkhead_recip_type = 'V'
                 
                 --AND NOT checkhead_deleted
                 AND checkitem_apopen_id IS NOT NULL
                 
                 AND checkhead_deleted
                AND NOT checkhead_void
                 
             <? if exists("vend_id") ?>    
                 AND (checkhead_recip_id=<? value("vend_id") ?>)
                 AND (checkhead_recip_id=<? value("vend_id") ?>)
             <? endif ?>
             <? if exists("startDate") ?>     
                 AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
             <? endif ?>
             )
             



--- for voided checks, 
UNION
      SELECT
                checkitem_id as  apopen_id,
                
                3 AS type,
                checkhead_checkdate AS sortdate,
                checkhead_number::text AS sortnumber,
                
                checkhead_number::text AS docnumber,
                '' AS f_open,
                 'checkvoided' AS documenttype,
                ' ' AS invoicenumber,
                 '' AS ponumber,
                   checkhead_checkdate  AS docdate,
                   CAST(NULL AS DATE) AS duedate,
                   
                   checkitem_amount AS amount,
                   0 AS balance,
                   currconcat(checkitem_curr_id) AS currAbbr,
                    --- changed so base_ammount is *-1 (was 0)
                    currtobase( checkitem_curr_id, checkitem_amount,apapply_postdate ) * -1  as base_amount,
                 --  (checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
                   
                   (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
                   
                   
                   
                   0 AS base_balance 
                  
            FROM
                    checkitem
                LEFT JOIN
                    checkhead
                ON
                    checkitem_checkhead_id = checkhead_id
                 LEFT JOIN
                    apapply
                ON
                    apapply_journalnumber = checkhead_journalnumber
                     
            WHERE (
            
            
            
            
                checkhead_recip_type = 'V'
            
               AND checkhead_void
                AND   checkhead_voided IS NOT NULL   
                AND checkitem_apopen_id IS NOT NULL
                
            <? if exists("vend_id") ?>    
                AND (checkhead_recip_id=<? value("vend_id") ?>)
                AND (checkhead_recip_id=<? value("vend_id") ?>)
            <? endif ?>
            <? if exists("startDate") ?>     
                AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
            <? endif ?>
            )
 




UNION
    -- the check void..
  -- the void date is based on the journal number...
             
             SELECT
                 checkhead_id as  apopen_id,
               
                 4 AS type,
                 checkhead_voided AS sortdate,
                 checkhead_number::text AS sortnumber,
                 
                 checkhead_number::text AS docnumber,
                 '' AS f_open,
                  'checkvoid' AS documenttype,
                 ' ' AS invoicenumber,
                  '' AS ponumber,
                    checkhead_voided  AS docdate,
                    CAST(NULL AS DATE) AS duedate,
                    
                    checkitem_amount AS amount,
                    0 AS balance,
                    currconcat(checkitem_curr_id) AS currAbbr,
                     --- changed so base_ammount is *-1 (was 0)
                    currtobase( checkitem_curr_id, checkitem_amount,checkhead_voided )   as base_amount,
                    --(checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
                    
                    (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
                    
                      0 AS base_balance 
                   
             FROM
                     checkitem
                 LEFT JOIN
                     checkhead
                 ON
                     checkitem_checkhead_id = checkhead_id
                 
                     
                     
             WHERE (
                 checkhead_recip_type = 'V'
                  
                 AND checkitem_apopen_id IS NOT NULL
                  
                 AND checkhead_void
                AND   checkhead_voided IS NOT NULL
                 
             <? if exists("vend_id") ?>    
                 AND (checkhead_recip_id=<? value("vend_id") ?>)
                 AND (checkhead_recip_id=<? value("vend_id") ?>)
             <? endif ?>
             <? if exists("startDate") ?>     
                 AND (checkhead_voided BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
             <? endif ?>
             )
             

 

        ) x
        GROUP by docdate
    ) x
    WHERE
        ABS(ap_total - gl_total) > 1.0
    ORDER BY docdate;
    
        



-------- TESTING --------------------------
-- 
-- SELECT
--         *
--       
--         FROM
--         (
--           
--           
--           --------FROM - ALLL
--            
--         
--             
--             SELECT apopen_id,   0 AS type,
--                    apopen_distdate AS sortdate, apopen_docnumber AS sortnumber,
--                    apopen_docnumber AS docnumber,
--                    formatBoolYN(apopen_open) AS f_open,
--                    CASE WHEN (apopen_doctype='V') THEN 'voucher'
--                         WHEN (apopen_doctype='C') THEN 'creditMemo'
--                         WHEN (apopen_doctype='D') THEN 'debitMemo'
--                      ELSE 'other'
--                    END AS documenttype,
--                    apopen_invcnumber AS invoicenumber,
--                    apopen_ponumber AS ponumber,
--                    apopen_distdate AS docdate, apopen_duedate AS duedate, apopen_amount AS amount,
--                    (apopen_amount - apopen_paid) AS balance,
--                    currconcat(apopen_curr_id) AS currAbbr,
--                    
--                    
--                   
--        CASE WHEN (apopen_doctype='C') THEN ((CASE WHEN apopen_amount  = 0.0 THEN apopen_discountable_amount ELSE apopen_amount  END)) / apopen_curr_rate * -1.0
--          ELSE (CASE WHEN apopen_amount  = 0.0 THEN apopen_discountable_amount ELSE apopen_amount  END)/ apopen_curr_rate
--        END AS base_amount,
--        
--                     
--                    
--                    
--                    
--                    0 AS base_applied,
--                    CASE WHEN (apopen_doctype='C') THEN (apopen_amount - apopen_paid) / apopen_curr_rate * -1.0
--                      ELSE (apopen_amount - apopen_paid) / apopen_curr_rate
--                    END AS base_balance
--                    
--             FROM apopen 
--             WHERE (
--                 (true)
--             <? if exists("vend_id") ?>
--                  AND (apopen_vend_id=<? value("vend_id") ?>)
--             <? endif ?>
--             <? if exists("startDate") ?>     
--                 AND (apopen_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
--             <? endif ?>
--             -- if we try and use the date to determine reversed it does not help.
--             
--             
--              AND NOT apopen_isreversed(apopen_id, apopen_docnumber, apopen_doctype, apopen_status)
--     
--             
--              )
--             
--                           
--              UNION
--                 SELECT
--                 checkitem_id as  apopen_id,
--                 
--                 1 AS type,
--                 checkhead_checkdate AS sortdate,
--                 checkhead_number::text AS sortnumber,
--                 
--                 checkhead_number::text AS docnumber,
--                 '' AS f_open,
--                  'check' AS documenttype,
--                 ' ' AS invoicenumber,
--                  '' AS ponumber,
--                    checkhead_checkdate  AS docdate,
--                    CAST(NULL AS DATE) AS duedate,
--                    
--                    checkitem_amount AS amount,
--                    0 AS balance,
--                    currconcat(checkitem_curr_id) AS currAbbr,
--                     --- changed so base_ammount is *-1 (was 0)
--                     currtobase( checkitem_curr_id, checkitem_amount,apapply_postdate ) * -1 as base_amount,
--                  --  (checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
--                    
--                    (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
--                    
--                    
--                    
--                    0 AS base_balance 
--                   
--             FROM
--                     checkitem
--                 LEFT JOIN
--                     checkhead
--                 ON
--                     checkitem_checkhead_id = checkhead_id
--                  LEFT JOIN
--                     apapply
--                 ON
--                     apapply_journalnumber = checkhead_journalnumber
--                      
--             WHERE (
--                 checkhead_recip_type = 'V'
--              --   AND NOT checkhead_deleted  
--                 AND NOT checkhead_void
--                 
--                 AND checkitem_apopen_id IS NOT NULL
--                 
--             <? if exists("vend_id") ?>    
--                 AND (checkhead_recip_id=<? value("vend_id") ?>)
--                 AND (checkhead_recip_id=<? value("vend_id") ?>)
--             <? endif ?>
--             <? if exists("startDate") ?>     
--                 AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
--             <? endif ?>
--             )
--  
--              
--              -- finally union with deleted entries..
--              
--              UNION
--              
--              -- code seems to indicate teh apopen close date is when it get's applied to gl.
--              -- however tests are indicating that it's the check date..
--              
--              SELECT
--                  checkhead_id as  apopen_id,
--                   
--                  2 AS type,
--                  checkhead_checkdate AS sortdate,
--                  checkhead_number::text AS sortnumber,
--                  
--                  checkhead_number::text AS docnumber,
--                  '' AS f_open,
--                   'checkrev' AS documenttype,
--                  ' ' AS invoicenumber,
--                   '' AS ponumber,
--                     checkhead_checkdate  AS docdate,
--                     CAST(NULL AS DATE) AS duedate,
--                     
--                     checkitem_amount AS amount,
--                     0 AS balance,
--                     currconcat(checkitem_curr_id) AS currAbbr,
--                      --- changed so base_ammount is *-1 (was 0)
--                     currtobase( checkitem_curr_id, checkitem_amount,apopen_closedate )   as base_amount,
--                     --(checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
--                     
--                     (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
--                     
--                       0 AS base_balance 
--                    
--              FROM
--                      checkitem
--                  LEFT JOIN
--                      checkhead
--                  ON
--                      checkitem_checkhead_id = checkhead_id
--                  LEFT JOIN
--                      apopen
--                  ON
--                      apopen_id = checkitem_apopen_id 
--                      
--                      
--              WHERE (
--                  checkhead_recip_type = 'V'
--                  
--                  --AND NOT checkhead_deleted
--                  AND checkitem_apopen_id IS NOT NULL
--                  
--                  AND checkhead_deleted
--                 AND NOT checkhead_void
--                  
--              <? if exists("vend_id") ?>    
--                  AND (checkhead_recip_id=<? value("vend_id") ?>)
--                  AND (checkhead_recip_id=<? value("vend_id") ?>)
--              <? endif ?>
--              <? if exists("startDate") ?>     
--                  AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
--              <? endif ?>
--              )
--              
-- 
-- 
-- 
-- --- for voided checks, 
-- UNION
--       SELECT
--                 checkitem_id as  apopen_id,
--                 
--                 3 AS type,
--                 checkhead_checkdate AS sortdate,
--                 checkhead_number::text AS sortnumber,
--                 
--                 checkhead_number::text AS docnumber,
--                 '' AS f_open,
--                  'checkvoided' AS documenttype,
--                 ' ' AS invoicenumber,
--                  '' AS ponumber,
--                    checkhead_checkdate  AS docdate,
--                    CAST(NULL AS DATE) AS duedate,
--                    
--                    checkitem_amount AS amount,
--                    0 AS balance,
--                    currconcat(checkitem_curr_id) AS currAbbr,
--                     --- changed so base_ammount is *-1 (was 0)
--                     currtobase( checkitem_curr_id, checkitem_amount,apapply_postdate ) * -1  as base_amount,
--                  --  (checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
--                    
--                    (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
--                    
--                    
--                    
--                    0 AS base_balance 
--                   
--             FROM
--                     checkitem
--                 LEFT JOIN
--                     checkhead
--                 ON
--                     checkitem_checkhead_id = checkhead_id
--                  LEFT JOIN
--                     apapply
--                 ON
--                     apapply_journalnumber = checkhead_journalnumber
--                      
--             WHERE (
--             
--             
--             
--             
--                 checkhead_recip_type = 'V'
--             
--                AND checkhead_void
--                 AND   checkhead_voided IS NOT NULL   
--                 AND checkitem_apopen_id IS NOT NULL
--                 
--             <? if exists("vend_id") ?>    
--                 AND (checkhead_recip_id=<? value("vend_id") ?>)
--                 AND (checkhead_recip_id=<? value("vend_id") ?>)
--             <? endif ?>
--             <? if exists("startDate") ?>     
--                 AND (checkhead_checkdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
--             <? endif ?>
--             )
--  
-- 
-- 
-- 
-- 
-- UNION
--     -- the check void..
--   -- the void date is based on the journal number...
--              
--              SELECT
--                  checkhead_id as  apopen_id,
--                
--                  4 AS type,
--                  checkhead_voided AS sortdate,
--                  checkhead_number::text AS sortnumber,
--                  
--                  checkhead_number::text AS docnumber,
--                  '' AS f_open,
--                   'checkvoid' AS documenttype,
--                  ' ' AS invoicenumber,
--                   '' AS ponumber,
--                     checkhead_voided  AS docdate,
--                     CAST(NULL AS DATE) AS duedate,
--                     
--                     checkitem_amount AS amount,
--                     0 AS balance,
--                     currconcat(checkitem_curr_id) AS currAbbr,
--                      --- changed so base_ammount is *-1 (was 0)
--                     currtobase( checkitem_curr_id, checkitem_amount,checkhead_voided )   as base_amount,
--                     --(checkitem_amount  / checkhead_curr_rate)  * -1 AS base_amount,
--                     
--                     (checkitem_amount  / checkhead_curr_rate) AS base_applied, 
--                     
--                       0 AS base_balance 
--                    
--              FROM
--                      checkitem
--                  LEFT JOIN
--                      checkhead
--                  ON
--                      checkitem_checkhead_id = checkhead_id
--                  
--                      
--                      
--              WHERE (
--                  checkhead_recip_type = 'V'
--                   
--                  AND checkitem_apopen_id IS NOT NULL
--                   
--                  AND checkhead_void
--                 AND   checkhead_voided IS NOT NULL
--                  
--              <? if exists("vend_id") ?>    
--                  AND (checkhead_recip_id=<? value("vend_id") ?>)
--                  AND (checkhead_recip_id=<? value("vend_id") ?>)
--              <? endif ?>
--              <? if exists("startDate") ?>     
--                  AND (checkhead_voided BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) 
--              <? endif ?>
--              )
--              
-- 
--  
-- 
--         ) x
-- 
-- WHERE docdate = '2013-01-25'