1 -- Group: opensalesorders
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.
9 COALESCE(cust_number, :error) AS cust_number,
10 getSoSchedDate(cohead_id) AS scheddate,
11 getSoStatus(cohead_id) AS status,
12 <? foreach("char_id_text_list") ?>
13 charass_alias<? literal("char_id_text_list") ?>.charass_value AS char<? literal("char_id_text_list") ?>,
15 <? foreach("char_id_list_list") ?>
16 charass_alias<? literal("char_id_list_list") ?>.charass_value AS char<? literal("char_id_list_list") ?>,
18 <? foreach("char_id_date_list") ?>
19 charass_alias<? literal("char_id_date_list") ?>.charass_value::date AS char<? literal("char_id_date_list") ?>,
21 firstline(cohead_ordercomments) AS notes,
22 calcSalesOrderAmt(cohead_id) AS ordertotal,
23 CASE WHEN (calcSalesOrderAmt(cohead_id,'S') != 0.0)
24 THEN calcSalesOrderAmt(cohead_id,'M')
27 CASE WHEN (calcSalesOrderAmt(cohead_id,'S') != 0.0)
28 THEN (calcSalesOrderAmt(cohead_id,'M') / calcSalesOrderAmt(cohead_id,'S'))
30 END AS ordermarginpercent,
31 'percent' AS ordermarginpercent_xtnumericrole,
32 'extprice' AS ordertotal_xtnumericrole,
33 'extprice' AS ordermargin_xtnumericrole
35 JOIN custinfo ON (cohead_cust_id=cust_id)
36 JOIN custtype ON (cust_custtype_id=custtype_id)
37 <? if exists("selectedSites") ?>
38 JOIN coitem ON (coitem_cohead_id=cohead_id)
39 JOIN itemsite ON (coitem_itemsite_id=itemsite_id)
40 JOIN site() ON (itemsite_warehous_id=warehous_id)
41 <? elseif exists("warehous_id") ?>
42 LEFT OUTER JOIN coitem ON (coitem_cohead_id=cohead_id)
43 LEFT OUTER JOIN itemsite ON (coitem_itemsite_id=itemsite_id)
44 LEFT OUTER JOIN whsinfo ON (itemsite_warehous_id=warehous_id)
46 <? foreach("char_id_text_list") ?>
47 LEFT OUTER JOIN charass charass_alias<? literal("char_id_text_list") ?>
48 ON ((charass_alias<? literal("char_id_text_list") ?>.charass_target_type='SO')
49 AND (charass_alias<? literal("char_id_text_list") ?>.charass_target_id=cohead_id)
50 AND (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=<? value("char_id_text_list") ?>))
51 LEFT OUTER JOIN char char_alias<? literal("char_id_text_list") ?>
52 ON (charass_alias<? literal("char_id_text_list") ?>.charass_char_id=char_alias<? literal("char_id_text_list") ?>.char_id)
54 <? foreach("char_id_list_list") ?>
55 LEFT OUTER JOIN charass charass_alias<? literal("char_id_list_list") ?>
56 ON ((charass_alias<? literal("char_id_list_list") ?>.charass_target_type='SO')
57 AND (charass_alias<? literal("char_id_list_list") ?>.charass_target_id=cohead_id)
58 AND (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=<? value("char_id_list_list") ?>))
59 LEFT OUTER JOIN char char_alias<? literal("char_id_list_list") ?>
60 ON (charass_alias<? literal("char_id_list_list") ?>.charass_char_id=char_alias<? literal("char_id_list_list") ?>.char_id)
62 <? foreach("char_id_date_list") ?>
63 LEFT OUTER JOIN charass charass_alias<? literal("char_id_date_list") ?>
64 ON ((charass_alias<? literal("char_id_date_list") ?>.charass_target_type='SO')
65 AND (charass_alias<? literal("char_id_date_list") ?>.charass_target_id=cohead_id)
66 AND (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=<? value("char_id_date_list") ?>))
67 LEFT OUTER JOIN char char_alias<? literal("char_id_date_list") ?>
68 ON (charass_alias<? literal("char_id_date_list") ?>.charass_char_id=char_alias<? literal("char_id_date_list") ?>.char_id)
71 <? if exists("cust_id") ?>
72 AND (cust_id=<? value("cust_id") ?> )
74 <? if exists("custtype_id") ?>
75 AND (cust_custtype_id=<? value("custtype_id") ?>)
77 <? if exists("custtype_pattern") ?>
78 AND (custtype_code ~* <? value("custtype_pattern") ?>)
80 <? if exists("poNumber") ?>
81 AND (cohead_custponumber~*<? value("poNumber") ?>)
83 <? if exists("startDate") ?>
84 AND (cohead_orderdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
86 <? if not exists("showClosed") ?>
87 AND (cohead_status='O')
89 <? if exists("warehous_id") ?>
90 AND (warehous_id=<? value("warehous_id") ?>)
92 <? if exists("salesrep_id") ?>
93 AND (cohead_salesrep_id=<? value("salesrep_id") ?>)
95 <? literal("charClause") ?>
97 ORDER BY cohead_number;