git.roojs.org Git - Pman.Xtuple/blob - metasql/aropen.bydatex.sql

   1 -- Group: arOpenItems
   2 -- Name: detail
   3 -- Notes: used by arWorkBench, dspAROpenItems
   4 -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6
   7 -- Get unposted invoices
   8
   9 -- Get Posted AR Open
  10
  11 SELECT
  12     docdate,
  13     base_amount ,
  14     cr_amount
  15
  16     FROM
  17
  18
  19
  20     ( SELECT
  21         id,
  22         'ar' as type,
  23         docdate,
  24         base_amount,
  25         0 as cr_amount
  26
  27
  28         FROM (
  29
  30             SELECT aropen_id AS id,
  31                    CASE WHEN (aropen_doctype='I') THEN 0
  32                         WHEN (aropen_doctype='C') THEN 1
  33                         WHEN (aropen_doctype='D') THEN 2
  34                         WHEN (aropen_doctype='R') THEN 3
  35                         ELSE -1
  36                    END AS altId,
  37                    aropen_docnumber AS docnumber,
  38                    COALESCE(invchead_id,cmhead_id,-1) AS docnumber_xtidrole,
  39                    true AS posted,
  40                    aropen_docdate AS docdate,
  41                    aropen_duedate,
  42                    aropen_ordernumber AS ordernumber,
  43                    COALESCE(cohead_id,-1) AS ordernumber_xtidrole,
  44                    CASE WHEN (aropen_doctype='I') THEN 'invoice'
  45                         WHEN (aropen_doctype='C') THEN 'creditMemo'
  46                         WHEN (aropen_doctype='D') THEN 'debitMemo'
  47                         WHEN (aropen_doctype='R') THEN 'cashdeposit'
  48                         ELSE 'other'
  49                    END AS doctype,
  50
  51                    CASE WHEN (aropen_doctype='C') THEN 'emphasis'
  52                      ELSE CASE WHEN(aropen_doctype='R')THEN 'altemphasis'
  53                      END
  54                    END AS doctype_qtforegroundrole,
  55
  56                    aropen_amount AS amount,
  57                    (aropen_amount/aropen_curr_rate  *  CASE WHEN (aropen_doctype='I') THEN 1
  58                         WHEN (aropen_doctype='C') THEN  -1
  59                         WHEN (aropen_doctype='D') THEN 1
  60                         WHEN (aropen_doctype='R') THEN -1
  61                         ELSE 1 END
  62                    )
  63
  64                         AS base_amount,
  65
  66                    (aropen_paid - (COALESCE(SUM(arapply_target_paid),0))) AS paid,
  67                    (aropen_paid - (COALESCE(SUM(arapply_target_paid),0))/aropen_curr_rate) AS base_paid,
  68
  69                     (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0))) *
  70                          CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END)
  71                     AS balance,
  72
  73                     currConcat(aropen_curr_id) AS currAbbr,
  74                     (((aropen_amount-aropen_paid+COALESCE(SUM(arapply_target_paid),0)))/aropen_curr_rate *
  75                     CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1 ELSE 1 END)  AS base_balance,
  76
  77                    cust_id, cust_number, cust_name,
  78                    COALESCE(invchead_recurring_invchead_id IS NOT NULL, false) AS recurring,
  79                    aropen_open AS open,
  80                    '' AS ccard_number,
  81                    'curr' AS amount_xtnumericrole,
  82                    'curr' AS base_amount_xtnumericrole,
  83                    0 AS base_amount_xttotalrole,
  84                    'curr' AS paid_xtnumericrole,
  85                    'curr' AS base_paid_xtnumericrole,
  86                    0 AS base_paid_xttotalrole,
  87                    'curr'AS balance_xtnumericrole,
  88                    'curr' AS base_balance_xtnumericrole,
  89                    0 AS base_balance_xttotalrole,
  90                    NULL AS aropen_docnumber_qtdisplayrole,
  91                    NULL AS aropen_docdate_qtdisplayrole,
  92                    0 AS xtindentrole,
  93
  94                    CASE WHEN ((COALESCE(aropen_duedate,current_date) < current_date) AND COALESCE(aropen_open,FALSE)) THEN 'error'
  95                         END AS aropen_duedate_qtforegroundrole,
  96
  97                    ccpay_id AS ccard_number_xtidrole,
  98                    firstLine(aropen_notes) AS notes
  99
 100
 101                     FROM aropen
 102
 103                         JOIN custinfo ON (aropen_cust_id=cust_id)
 104                         JOIN custtype ON (cust_custtype_id=custtype_id)
 105                         LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
 106                         LEFT OUTER JOIN invchead ON ((aropen_docnumber=invchead_invcnumber)
 107                                                AND (aropen_doctype='I'))
 108                         LEFT OUTER JOIN cohead ON (invchead_ordernumber=cohead_number)
 109                         LEFT OUTER JOIN cmhead ON ((aropen_docnumber=cmhead_number)
 110                                                AND (aropen_doctype='C'))
 111                         LEFT OUTER JOIN arapply ON (((aropen_id=arapply_source_aropen_id)
 112                                          OR (aropen_id=arapply_target_aropen_id))
 113                                          AND (arapply_distdate>COALESCE(<?  valueor("asofDate", "NULL") ?>,current_date)))
 114                         LEFT OUTER JOIN payaropen ON (payaropen_aropen_id=aropen_id)
 115                         LEFT OUTER JOIN ccpay     ON (payaropen_ccpay_id=ccpay_id)
 116                         LEFT OUTER JOIN ccard     ON (ccpay_ccard_id=ccard_id)
 117             WHERE ((true)
 118             --<? if not exists("showClosed") ?>
 119             --  AND (aropen_docdate <= COALESCE(<? value("asofDate") ?>, current_date))
 120             --  AND (COALESCE(aropen_closedate, DATE(<? value("asofDate") ?>) + 1, current_date + 1) > COALESCE(<? value("asofDate") ?>, current_date))
 121             --<? endif ?>
 122
 123             --<? if exists("debitsOnly") ?>
 124             --  AND   (aropen_doctype IN ('D','I'))
 125             --<? elseif exists("creditsOnly") ?>
 126             --  AND   (aropen_doctype IN ('C', 'R'))
 127             --<? endif ?>
 128
 129             --<? if ! exists("showPaid") ?>
 130             --    AND ABS(aropen_amount - aropen_paid) != 0.0
 131             --<? endif ?>
 132
 133
 134             <? if exists("cust_id") ?>
 135               AND   (aropen_cust_id=<? value("cust_id") ?>)
 136             <? endif ?>
 137
 138             <? if exists("startDate") ?>
 139               AND   (aropen_docdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
 140             <? endif ?>
 141
 142             <? if exists("startDueDate") ?>
 143               AND   (aropen_duedate >= <? value("startDueDate") ?>)
 144             <? endif ?>
 145
 146             <? if exists("endDueDate") ?>
 147               AND   (aropen_duedate <= <? value("endDueDate") ?>)
 148             <? endif ?>
 149                   )
 150               GROUP BY id,                      altId,              invchead_id,
 151                        aropen_docdate,          aropen_duedate,         aropen_doctype,
 152                        aropen_docnumber,        aropen_amount,
 153                        aropen_notes,            aropen_posted,          aropen_ordernumber,
 154                        aropen_paid,             aropen_open,            aropen_curr_id,
 155                        aropen_closedate,        aropen_curr_rate,       cmhead_id,
 156                        cust_id,                     cust_number,            cust_name,
 157                        cohead_id,               ccpay_id,               ccard_number,
 158                        invchead_recurring_invchead_id
 159
 160
 161             ORDER BY
 162               docdate DESC, docnumber DESC, xtindentrole
 163         ) x
 164
 165     ) y
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178      ORDER BY
 179           docdate DESC
 180     ;
 181
 182