-- Group: apOpenItems
-- Name:  detail
-- Notes:
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.

SELECT apopen_id, apopen_ponumber, apopen_docnumber,
                    CASE WHEN (apopen_doctype='C') THEN <? value("creditMemo") ?>
                         WHEN (apopen_doctype='D') THEN <? value("debitMemo") ?>
                         WHEN (apopen_doctype='V') THEN <? value("voucher") ?>
                         ELSE <? value("other") ?>
                    END AS f_doctype,
                    CASE WHEN (apopen_doctype='C') THEN 'emphasis' END AS f_doctype_qtforegroundrole,
                    vend_number, vend_name,
                    apopen_invcnumber AS invoicenumber,
                    apopen_docdate, apopen_duedate, apopen_amount, apopen_status,
<? if exists("isReport") ?>
                    formatDate(apopen_docdate) AS f_docdate,
                    formatDate(apopen_duedate) AS f_duedate,
                    formatMoney(apopen_amount) AS f_amount,
                    formatMoney(apopen_paid - COALESCE(SUM(apapply_target_paid),0)) AS f_paid,
                    formatMoney((apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0)) *
                    CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
                    END) AS f_balance,
                    formatMoney((apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0))
                    / apopen_curr_rate * (CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
                         END)) AS f_base_balance,
<? endif ?>
                    apopen_paid - COALESCE(SUM(apapply_target_paid),0) AS paid,
                    (apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0)) *
                    CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
                    END AS balance,
                    currConcat(apopen_curr_id) AS currAbbr,
                    (apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0))
                    / apopen_curr_rate * (CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
                         END) AS base_balance,
                    'curr' AS apopen_amount_xtnumericrole,
                    'curr' AS paid_xtnumericrole,
                    'curr' AS balance_xtnumericrole,
                    'curr' AS base_balance_xtnumericrole,
                    0 AS base_balance_xttotalrole
             FROM apopen
               JOIN vendinfo ON (vend_id=apopen_vend_id)
               JOIN vendtype ON (vendtype_id=vend_vendtype_id)
               LEFT OUTER JOIN apapply ON (((apopen_id=apapply_target_apopen_id)
                                       OR (apopen_id=apapply_source_apopen_id))
                                       AND (apapply_postdate > <? value("asofDate") ?>))
              WHERE ( (COALESCE(apopen_closedate,date <? value("asofDate") ?> + integer '1')><? value("asofDate") ?>)
                AND   (CASE WHEN (<? value("useDocDate") ?>) THEN apopen_docdate ELSE apopen_distdate END<=<? value("asofDate") ?>)
<? if exists("vend_id") ?>
                AND   (apopen_vend_id=<? value("vend_id") ?>)
<? endif ?>
<? if exists("vendtype_id") ?>
                AND   (vendtype_id=<? value("vendtype_id") ?>)
<? endif ?>
<? if exists("vendtype_pattern") ?>
                AND   (vendtype_code~<? value("vendtype_pattern") ?>)
<? endif ?>
                AND   (apopen_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) )
              GROUP BY apopen_id, apopen_ponumber, apopen_docnumber,apopen_doctype, apopen_invcnumber, apopen_docdate,
                apopen_duedate, apopen_docdate, apopen_amount, apopen_paid, apopen_curr_id, apopen_curr_rate,
                apopen.apopen_status, vend_number, vend_name
              ORDER BY apopen_docdate;