git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/apOpenItems-detail.mql

   1 -- Group: apOpenItems
   2 -- Name:  detail
   3 -- Notes:
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6
   7 SELECT apopen_id, apopen_ponumber, apopen_docnumber,
   8                     CASE WHEN (apopen_doctype='C') THEN <? value("creditMemo") ?>
   9                          WHEN (apopen_doctype='D') THEN <? value("debitMemo") ?>
  10                          WHEN (apopen_doctype='V') THEN <? value("voucher") ?>
  11                          ELSE <? value("other") ?>
  12                     END AS f_doctype,
  13                     CASE WHEN (apopen_doctype='C') THEN 'emphasis' END AS f_doctype_qtforegroundrole,
  14                     vend_number, vend_name,
  15                     apopen_invcnumber AS invoicenumber,
  16                     apopen_docdate, apopen_duedate, apopen_amount, apopen_status,
  17 <? if exists("isReport") ?>
  18                     formatDate(apopen_docdate) AS f_docdate,
  19                     formatDate(apopen_duedate) AS f_duedate,
  20                     formatMoney(apopen_amount) AS f_amount,
  21                     formatMoney(apopen_paid - COALESCE(SUM(apapply_target_paid),0)) AS f_paid,
  22                     formatMoney((apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0)) *
  23                     CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
  24                     END) AS f_balance,
  25                     formatMoney((apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0))
  26                     / apopen_curr_rate * (CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
  27                          END)) AS f_base_balance,
  28 <? endif ?>
  29                     apopen_paid - COALESCE(SUM(apapply_target_paid),0) AS paid,
  30                     (apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0)) *
  31                     CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
  32                     END AS balance,
  33                     currConcat(apopen_curr_id) AS currAbbr,
  34                     (apopen_amount - apopen_paid + COALESCE(SUM(apapply_target_paid),0))
  35                     / apopen_curr_rate * (CASE WHEN apopen_doctype IN ('D', 'V') THEN 1 ELSE -1
  36                          END) AS base_balance,
  37                     'curr' AS apopen_amount_xtnumericrole,
  38                     'curr' AS paid_xtnumericrole,
  39                     'curr' AS balance_xtnumericrole,
  40                     'curr' AS base_balance_xtnumericrole,
  41                     0 AS base_balance_xttotalrole
  42              FROM apopen
  43                JOIN vendinfo ON (vend_id=apopen_vend_id)
  44                JOIN vendtype ON (vendtype_id=vend_vendtype_id)
  45                LEFT OUTER JOIN apapply ON (((apopen_id=apapply_target_apopen_id)
  46                                        OR (apopen_id=apapply_source_apopen_id))
  47                                        AND (apapply_postdate > <? value("asofDate") ?>))
  48               WHERE ( (COALESCE(apopen_closedate,date <? value("asofDate") ?> + integer '1')><? value("asofDate") ?>)
  49                 AND   (CASE WHEN (<? value("useDocDate") ?>) THEN apopen_docdate ELSE apopen_distdate END<=<? value("asofDate") ?>)
  50 <? if exists("vend_id") ?>
  51                 AND   (apopen_vend_id=<? value("vend_id") ?>)
  52 <? endif ?>
  53 <? if exists("vendtype_id") ?>
  54                 AND   (vendtype_id=<? value("vendtype_id") ?>)
  55 <? endif ?>
  56 <? if exists("vendtype_pattern") ?>
  57                 AND   (vendtype_code~<? value("vendtype_pattern") ?>)
  58 <? endif ?>
  59                 AND   (apopen_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>) )
  60               GROUP BY apopen_id, apopen_ponumber, apopen_docnumber,apopen_doctype, apopen_invcnumber, apopen_docdate,
  61                 apopen_duedate, apopen_docdate, apopen_amount, apopen_paid, apopen_curr_id, apopen_curr_rate,
  62                 apopen.apopen_status, vend_number, vend_name
  63               ORDER BY apopen_docdate;