WHERE (invcitem_invchead_id=pInvcheadid);
IF (pType IN ('T', 'X')) THEN
- SELECT SUM(tax) INTO _tax
+ SELECT COALESCE(SUM(tax), 0.0) INTO _tax
FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
FROM tax
JOIN calculateTaxDetailSummary('I', pInvcheadid, 'T')ON (taxdetail_tax_id=tax_id)
AND (coitem_status != 'X');
IF (pType IN ('T', 'B', 'X')) THEN
- SELECT SUM(tax) INTO _tax
+ SELECT COALESCE(SUM(tax), 0.0) INTO _tax
FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
FROM tax
JOIN calculateTaxDetailSummary('S', pCoheadid, 'T')ON (taxdetail_tax_id=tax_id)
(coitem_price / coitem_price_invuomratio), 2) AS extpricebalance,
round((noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio) *
(currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) AS baseextpricebalance,
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (coitem_unitcost / coitem_price_invuomratio), 2) AS extcost,
+ (round((coitem_qtyord * coitem_qty_invuomratio) *
+ (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (coitem_unitcost / coitem_price_invuomratio), 2)) AS margin,
+ CASE WHEN (coitem_price > 0.0) THEN
+ (round((coitem_qtyord * coitem_qty_invuomratio) *
+ (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2) -
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (coitem_unitcost / coitem_price_invuomratio), 2)) /
+ round((coitem_qtyord * coitem_qty_invuomratio) *
+ (currtobase(cohead_curr_id, coitem_price, cohead_orderdate) / coitem_price_invuomratio), 2)
+ ELSE 0.0
+ END AS marginpercent,
curr_abbr AS currAbbr,
-- TODO - not needed, remove? (very slow)
-- CASE WHEN (qtyAvailable(itemsite_id, coitem_scheddate) < 0.0) THEN 'error'
'qty' AS qtyavailable_xtnumericrole,
'salesprice' AS coitem_price_xtnumericrole,
'salesprice' AS baseunitprice_xtnumericrole,
+ 'cost' AS coitem_unitcost_xtnumericrole,
'curr' AS extprice_xtnumericrole,
+ 'curr' AS extcost_xtnumericrole,
+ 'curr' AS margin_xtnumericrole,
+ 'percent' AS marginpercent_xtnumericrole,
'curr' AS baseextprice_xtnumericrole,
'curr' AS extpricebalance_xtnumericrole,
'curr' AS baseextpricebalance_xtnumericrole,
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
- cohist_ordernumber, cohist_invcnumber, cohist_invcdate, currAbbr,
+ cohist_ordernumber, cohist_orderdate, cohist_invcnumber, cohist_invcdate, currAbbr,
SUM(extprice) AS sumextprice,
SUM(cohist_commission) AS sumcommission,
SUM(baseextprice) AS sumbaseextprice,
SUM(basecommission) AS sumbasecommission,
+<? if exists("isReport") ?>
+ formatDate(cohist_orderdate) AS f_orderdate,
+ formatDate(cohist_invcdate) AS f_invcdate,
+ formatMoney(SUM(extprice)) AS f_sumextprice,
+ formatMoney(SUM(baseextprice)) AS f_sumbaseextprice,
+ formatMoney(SUM(cohist_commission)) AS f_sumcommission,
+ formatMoney(SUM(basecommission)) AS f_sumbasecommission,
+<? endif ?>
+ <? value("return") ?> AS cohist_invcdate_xtnullrole,
'curr' AS sumextprice_xtnumericrole,
'curr' AS sumcommission_xtnumericrole,
'curr' AS sumbaseextprice_xtnumericrole,
<? endif ?>
WHERE ((cohist_commission <> 0)
AND(cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
- <? if exists("includeMisc") ?>
- AND (COALESCE(cohist_misc_type, '') <> 'T')
- AND (COALESCE(cohist_misc_type, '') <> 'F')
- <? endif ?>
<? if exists("salesrep_id") ?>
AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
<? endif ?>
)
GROUP BY cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
- cohist_ordernumber, cohist_invcnumber, cohist_invcdate, currAbbr
+ cohist_ordernumber, cohist_orderdate, cohist_invcnumber, cohist_invcdate, currAbbr
ORDER BY salesrep_number, cust_number, cohist_invcdate;
-- 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
- COALESCE(cohead_id,-1) AS cohead_id, cust_id, cohist_ponumber,
- cust_custtype_id, custtype_code,
- cohist_cust_id, SUM(extcost) AS extcost,
+SELECT cohead_id, cust_id, cohist_ponumber,
+ custtype_id, custtype_code,
+ cohist_cust_id, cohist_ordernumber,
+ cust_number, cust_name, currAbbr,
+ cohist_orderdate, cohist_invcdate,
+ CASE WHEN (COALESCE(cohist_invcnumber, '-1') IN ('', '-1')) THEN <? value("credit") ?>
+ ELSE cohist_invcnumber
+ END AS invoicenumber,
+ SUM(extprice) AS extprice,
+ SUM(baseextprice) AS baseextprice,
+ SUM(extcost) AS extcost,
+ SUM(margin) AS margin,
+ CASE WHEN (SUM(baseextprice) > 0.0) THEN
+ ROUND(SUM(margin) / SUM(baseextprice), 4)
+ ELSE 0.0
+ END AS marginpercent,
+<? if exists("isReport") ?>
+ formatDate(cohist_orderdate) AS f_orderdate,
+ formatDate(cohist_invcdate) AS f_invcdate,
+ formatMoney(SUM(extprice)) AS f_extprice,
+ formatMoney(SUM(baseextprice)) AS f_baseextprice,
+ formatMoney(SUM(extcost)) AS f_extcost,
+ formatMoney(SUM(margin)) AS f_margin,
+<? endif ?>
+ <? value("return") ?> AS cohist_invcdate_xtnullrole,
+ 'curr' AS extprice_xtnumericrole,
+ 'curr' AS baseextprice_xtnumericrole,
'curr' AS extcost_xtnumericrole,
+ 'curr' AS margin_xtnumericrole,
+ 'percent' AS marginpercent_xtnumericrole,
+ 0 AS baseextprice_xttotalrole,
0 AS extcost_xttotalrole,
- cohist_ordernumber,
- cust_number, cust_name, invoicenumber,
- cohist_orderdate, cohist_invcdate,
- SUM(baseextprice) AS extended,
- 'curr' AS extended_xtnumericrole,
- 0 AS extended_xttotalrole
-FROM saleshistory
- LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
-WHERE ((cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
+ 0 AS margin_xttotalrole
+<? if exists("includeMisc") ?>
+ FROM saleshistorymisc
+<? else ?>
+ FROM saleshistory
+<? endif ?>
+WHERE ( (true)
+<? if exists("startDate") ?>
+ AND (cohist_invcdate >= <? value("startDate") ?>)
+<? endif ?>
+<? if exists("endDate") ?>
+ AND (cohist_invcdate <= <? value("endDate") ?>)
+<? endif ?>
+<? if exists("shipStartDate") ?>
+ AND (cohist_shipdate >= <? value("shipStartDate") ?>)
+<? endif ?>
+<? if exists("shipEndDate") ?>
+ AND (cohist_shipdate <= <? value("shipEndDate") ?>)
+<? endif ?>
<? if exists("cust_id") ?>
AND (cohist_cust_id=<? value("cust_id") ?>)
<? endif ?>
AND (cohist_shipto_id=<? value("shipto_id") ?>)
<? endif ?>
<? if exists("custtype_id") ?>
- AND (cust_custtype_id=<? value("custtype_id") ?>)
+ AND (custtype_id=<? value("custtype_id") ?>)
<? endif ?>
<? if exists("custtype_pattern") ?>
AND (custtype_code ~ <? value("custtype_pattern") ?>)
AND (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
<? endif ?>
<? if exists("warehous_id") ?>
- AND (itemsite_warehous_id=<? value("warehous_id") ?>)
+ AND (warehous_id=<? value("warehous_id") ?>)
<? endif ?>
<? if exists("item_id") ?>
- AND (itemsite_item_id=<? value("item_id") ?>)
+ AND (item_id=<? value("item_id") ?>)
<? endif ?>
<? if exists("prodcat_id") ?>
- AND (item_prodcat_id=<? value("prodcat_id") ?>)
+ AND (prodcat_id=<? value("prodcat_id") ?>)
<? endif ?>
<? if exists("prodcat_pattern") ?>
- AND (item_prodcat_id IN (SELECT prodcat_id
- FROM prodcat
- WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
+ AND (prodcat_id IN (SELECT prodcat_id
+ FROM prodcat
+ WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
<? endif ?>
<? if exists("salesrep_id") ?>
AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
+<? endif ?>
+<? if exists("shipzone_id") ?>
+ AND (cohist_shipzone_id=<? value("shipzone_id") ?>)
+<? endif ?>
+<? if exists("saletype_id") ?>
+ AND (cohist_saletype_id=<? value("saletype_id") ?>)
<? endif ?>
)
-GROUP BY cohead_id, cust_id,cust_number,cust_custtype_id,cohist_cust_id,custtype_code,
- cust_name,cohist_ordernumber, cohist_ponumber,
- invoicenumber,cohist_orderdate, cohist_invcdate
+GROUP BY cohead_id, cust_id, cust_number, custtype_id,
+ cohist_cust_id, custtype_code, currAbbr,
+ cust_name, cohist_ordernumber, cohist_ponumber,
+ cohist_invcnumber, cohist_orderdate, cohist_invcdate
ORDER BY cohist_invcdate, cohist_orderdate;
invchead_invcnumber AS docnumber,
findCustomerForm(cust_id, 'I') AS reportname,
<? endif ?>
+ CASE WHEN (calcInvoiceAmt(invchead_id,'S') != 0.0)
+ THEN calcInvoiceAmt(invchead_id,'M')
+ ELSE 0.0
+ END AS margin,
CASE WHEN (calcInvoiceAmt(invchead_id,'S') != 0.0)
THEN (calcInvoiceAmt(invchead_id,'M') / calcInvoiceAmt(invchead_id,'S'))
ELSE 1.0
END AS marginpercent,
'percent' AS marginpercent_xtnumericrole,
+ 'curr' AS margin_xtnumericrole,
'curr' AS extprice_xtnumericrole,
'curr' AS balance_xtnumericrole,
CASE WHEN (aropen_id IS NULL) THEN 'Unposted' END AS balance_qtdisplayrole
<? endforeach ?>
firstline(cohead_ordercomments) AS notes,
calcSalesOrderAmt(cohead_id) AS ordertotal,
+ CASE WHEN (calcSalesOrderAmt(cohead_id,'S') != 0.0)
+ THEN calcSalesOrderAmt(cohead_id,'M')
+ ELSE 0.0
+ END AS ordermargin,
CASE WHEN (calcSalesOrderAmt(cohead_id,'S') != 0.0)
THEN (calcSalesOrderAmt(cohead_id,'M') / calcSalesOrderAmt(cohead_id,'S'))
ELSE 1.0
END AS ordermarginpercent,
'percent' AS ordermarginpercent_xtnumericrole,
- 'extprice' AS ordertotal_xtnumericrole
+ 'extprice' AS ordertotal_xtnumericrole,
+ 'extprice' AS ordermargin_xtnumericrole
FROM cohead
JOIN custinfo ON (cohead_cust_id=cust_id)
JOIN custtype ON (cust_custtype_id=custtype_id)
-- 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 cohist.*,
- CASE WHEN (cohist_invcnumber='-1') THEN <? value("credit") ?>
+SELECT *,
+ CASE WHEN (COALESCE(cohist_invcnumber, '-1') IN ('', '-1')) THEN <? value("credit") ?>
ELSE cohist_invcnumber
END AS invoicenumber,
- cust_number, cust_name, salesrep_name,
- saletype_code, shipzone_name,
- item_id, COALESCE(item_number, cohist_misc_descrip) AS item_number,
- item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
- warehous_code,
- currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
- round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
- round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS baseextprice,
- round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
- currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
<? if exists("isReport") ?>
+ formatDate(cohist_orderdate) AS f_orderdate,
formatDate(cohist_invcdate) AS f_invcdate,
formatQty(cohist_qtyshipped) AS f_qtyshipped,
formatBoolYN(cohist_commissionpaid) AS f_commissionpaid,
- formatSalesPrice(currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)) AS f_baseunitprice,
- formatMoney(round((cohist_qtyshipped * cohist_unitprice), 2)) AS f_extprice,
- formatMoney(round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2)) AS f_baseextprice,
- formatMoney(round((cohist_qtyshipped * cohist_unitcost), 4)) AS f_extcost,
- formatMoney(currtobase(cohist_curr_id, cohist_commission, cohist_invcdate)) AS f_basecommission,
+ formatSalesPrice(baseunitprice) AS f_baseunitprice,
+ formatMoney(extprice) AS f_extprice,
+ formatMoney(baseextprice) AS f_baseextprice,
+ formatMoney(extcost) AS f_extcost,
+ formatMoney(margin) AS f_margin,
+ formatPrcnt(marginpercent) AS f_marginpercent,
+ formatMoney(basecommission) AS f_basecommission,
<? endif ?>
- currConcat(cohist_curr_id) AS currAbbr,
<? value("return") ?> AS cohist_invcdate_xtnullrole,
- 'qty' AS cohist_qtyshipped_xtnumericrole,
- 'salesprice' AS cohist_unitprice_xtnumericrole,
- 'salesprice' AS baseunitprice_xtnumericrole,
- 'curr' AS extprice_xtnumericrole,
- 'curr' AS baseextprice_xtnumericrole,
- 'cost' AS cohist_unitcost_xtnumericrole,
- 'curr' AS extcost_xtnumericrole,
- 'curr' AS cohist_commission_xtnumericrole,
- 'curr' AS basecommission_xtnumericrole,
0 AS cohist_qtyshipped_xttotalrole,
0 AS baseextprice_xttotalrole,
0 AS extcost_xttotalrole,
+ 0 AS margin_xttotalrole,
0 AS basecommission_xttotalrole
- FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
- JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
- LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
- LEFT OUTER JOIN shipzone ON (shipzone_id=cohist_shipzone_id)
<? if exists("includeMisc") ?>
- LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
- LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
+ FROM saleshistorymisc
<? else ?>
- JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- JOIN site() ON (warehous_id=itemsite_warehous_id)
- JOIN item ON (item_id=itemsite_item_id)
-<? endif ?>
-<? if exists("cohead_id") ?>
- JOIN cohead ON (cohead_number=cohist_ordernumber)
+ FROM saleshistory
<? endif ?>
WHERE ( (true)
-<? if exists("includeMisc") ?>
- AND (COALESCE(cohist_misc_type, '') <> 'F')
- AND (COALESCE(cohist_misc_type, '') <> 'T')
-<? endif ?>
<? if exists("startDate") ?>
AND (cohist_invcdate >= <? value("startDate") ?>)
<? endif ?>
AND (cohist_cust_id=<? value("cust_id") ?>)
<? endif ?>
<? if exists("custtype_id") ?>
- AND (cust_custtype_id=<? value("custtype_id") ?>)
+ AND (custtype_id=<? value("custtype_id") ?>)
<? elseif exists("custtype_pattern") ?>
- AND (cust_custtype_id IN (SELECT DISTINCT custtype_id
- FROM custtype
- WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
+ AND (custtype_id IN (SELECT DISTINCT custtype_id
+ FROM custtype
+ WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
<? endif ?>
<? if exists("by_custgrp") ?>
AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
<? endif ?>
<? if exists("item_id") ?>
- AND (itemsite_item_id=<? value("item_id") ?>)
+ AND (item_id=<? value("item_id") ?>)
<? endif ?>
<? if exists("prodcat_id") ?>
- AND (item_prodcat_id=<? value("prodcat_id") ?>)
+ AND (prodcat_id=<? value("prodcat_id") ?>)
<? endif ?>
<? if exists("prodcat_pattern") ?>
- AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
- FROM prodcat
- WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
+ AND (prodcat_id IN (SELECT DISTINCT prodcat_id
+ FROM prodcat
+ WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
<? endif ?>
<? if exists("warehous_id") ?>
- AND (itemsite_warehous_id=<? value("warehous_id") ?>)
+ AND (warehous_id=<? value("warehous_id") ?>)
<? endif ?>
<? if exists("shipzone_id") ?>
AND (cohist_shipzone_id=<? value("shipzone_id") ?>)
AND (cohead_id=<? value("cohead_id") ?>)
<? endif ?>
)
-ORDER BY cohist_invcdate, item_number;
+ORDER BY cohist_invcdate, itemnumber;
cust_number, cohist_cust_id AS cust_number_xtidrole, cust_name,
<? endif ?>
<? if exists("byCustomerType") ?>
- custtype_code, cust_custtype_id AS custtype_code_xtidrole,
+ custtype_code, custtype_id AS custtype_code_xtidrole,
<? endif ?>
<? if exists("byItem") ?>
item_number, item_id AS item_number_xtidrole, itemdescription,
<? endif ?>
<? if exists("bySalesRep") ?>
- salesrep_number, cohist_salesrep_id AS salesrep_number_xtidrole, salesrep_name,
+ salesrep_number, salesrep_id AS salesrep_number_xtidrole, salesrep_name,
<? endif ?>
<? if exists("byShippingZone") ?>
shipzone_name, shipzone_id AS shipzone_name_xtidrole,
<? endif ?>
<? if exists("bySite") ?>
- warehous_code, itemsite_warehous_id AS warehous_code_xtidrole,
+ warehous_code, warehous_id AS warehous_code_xtidrole,
<? endif ?>
<? if exists("byCurrency") ?>
currAbbr,
'curr' AS totalsales_xtnumericrole,
0 AS totalunits_xttotalrole,
0 AS totalsales_xttotalrole
-FROM saleshistory
-WHERE ((COALESCE(cohist_misc_type, '') <> 'F')
- AND (COALESCE(cohist_misc_type, '') <> 'T')
+FROM saleshistorymisc
+WHERE ( (TRUE)
<? if exists("startDate") ?>
AND (cohist_invcdate >= <? value("startDate") ?>)
<? endif ?>
<? endif ?>
<? if exists("warehous_id") ?>
- AND (itemsite_warehous_id=<? value("warehous_id") ?>)
+ AND (warehous_id=<? value("warehous_id") ?>)
<? endif ?>
<? if exists("item_id") ?>
- AND (itemsite_item_id=<? value("item_id") ?>)
+ AND (item_id=<? value("item_id") ?>)
<? endif ?>
<? if exists("cust_id") ?>
<? endif ?>
<? if exists("salesrep_id") ?>
- AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
+ AND (salesrep_id=<? value("salesrep_id") ?>)
<? endif ?>
<? if exists("prodcat_id") ?>
- AND (item_prodcat_id=<? value("prodcat_id") ?>)
+ AND (prodcat_id=<? value("prodcat_id") ?>)
<? endif ?>
<? if exists("prodcat_pattern") ?>
<? endif ?>
<? if exists("custtype_id") ?>
- AND (cust_custtype_id=<? value("custtype_id") ?>)
+ AND (custtype_id=<? value("custtype_id") ?>)
<? endif ?>
<? if exists("custtype_pattern") ?>
)
GROUP BY dummy
<? if exists("bySalesRep") ?>
- , cohist_salesrep_id, salesrep_number, salesrep_name
+ , salesrep_id, salesrep_number, salesrep_name
<? endif ?>
<? if exists("byShippingZone") ?>
, shipzone_id, shipzone_name, shipzone_descrip
, cohist_cust_id, cust_number, cust_name
<? endif ?>
<? if exists("byCustomerType") ?>
- , cust_custtype_id, custtype_code, custtype_descrip
+ , custtype_id, custtype_code, custtype_descrip
<? endif ?>
<? if exists("byItem") ?>
, item_id, item_number, itemdescription
<? endif ?>
<? if exists("bySite") ?>
- , itemsite_warehous_id, warehous_code, warehous_descrip
+ , warehous_id, warehous_code, warehous_descrip
<? endif ?>
<? if exists("byCurrency") ?>
, cust_curr_id, currAbbr
<title>Brief Earned Commissions</title>
<name>BriefEarnedCommissions</name>
<description></description>
+ <grid>
+ <snap/>
+ <show/>
+ <x>0.05</x>
+ <y>0.05</y>
+ </grid>
<size>Letter</size>
<portrait/>
<topmargin>50</topmargin>
formatDate(<? value("startDate") ?>) AS startdate,
formatDate(<? value("endDate") ?>) AS enddate;</sql>
</querysource>
- <querysource>
+ <querysource loadFromDb="true">
<name>detail</name>
- <sql>SELECT cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
- cohist_ordernumber, cohist_invcnumber, formatDate(cohist_invcdate) AS f_invcdate, currAbbr,
- formatMoney(SUM(baseextprice)) AS f_extprice,
- formatMoney(SUM(basecommission)) AS f_commission,
- SUM(baseextprice) AS extprice,
- SUM(basecommission) AS commission
-<? if exists("includeMisc") ?>
-FROM saleshistorymisc
-<? else ?>
-FROM saleshistory
-<? endif ?>
-WHERE ( (cohist_commission <> 0)
- AND (cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
-
-<? if exists("includeMisc") ?>
- AND (COALESCE(cohist_misc_type, '') <> 'T')
- AND (COALESCE(cohist_misc_type, '') <> 'F')
-<? endif ?>
-
-<? if exists("salesrep_id") ?>
- AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
-<? endif ?>
-)
-GROUP BY cohist_salesrep_id, salesrep_number, salesrep_name, cust_number, cust_name,
- cohist_ordernumber, cohist_invcnumber, cohist_invcdate, currAbbr
-ORDER BY salesrep_number, cust_number, cohist_invcdate
-</sql>
+ <mqlgroup>briefEarnedCommission</mqlgroup>
+ <mqlname>detail</mqlname>
</querysource>
<rpthead>
<height>221</height>
<vcenter/>
<data>
<query>detail</query>
- <column>f_extprice</column>
+ <column>f_sumbaseextprice</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>f_commission</column>
+ <column>f_sumbasecommission</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>extprice</column>
+ <column>sumbaseextprice</column>
</data>
<format builtin="true">money</format>
<tracktotal/>
<vcenter/>
<data>
<query>detail</query>
- <column>commission</column>
+ <column>sumbasecommission</column>
</data>
<format builtin="true">money</format>
<tracktotal/>
<? endif ?>
AS docnumber; </sql>
</querysource>
- <querysource>
+ <querysource loadFromDb="true">
<name>detail</name>
- <sql>SELECT cohist_ordernumber AS sonumber,
- cohist_invcnumber AS invnumber,
- formatDate(cohist_orderdate) AS orddate,
- formatDate(cohist_invcdate, 'Return') AS shipdate,
- SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) as extended,
- formatMoney(SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2))) as f_total,
- SUM(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) as total
-FROM saleshistory
- LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
-WHERE ((cohist_invcdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
-<? if exists("cust_id") ?>
- AND (cohist_cust_id=<? value("cust_id") ?>)
-<? endif ?>
-<? if exists("shipto_id") ?>
- AND (cohist_shipto_id=<? value("shipto_id") ?>)
-<? endif ?>
-<? if exists("custtype_id") ?>
- AND (cust_custtype_id=<? value("custtype_id") ?>)
-<? endif ?>
-<? if exists("custtype_pattern") ?>
- AND (custtype_code ~ <? value("custtype_pattern") ?>)
-<? endif ?>
-<? if exists("custgrp_id") ?>
- AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
- FROM custgrpitem
- WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
-<? endif ?>
-<? if exists("custgrp_pattern") ?>
- AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
- FROM custgrp, custgrpitem
- WHERE ( (custgrpitem_custgrp_id=custgrp_id)
- AND (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
-<? endif ?>
-<? if exists("warehous_id") ?>
- AND (itemsite_warehous_id=<? value("warehous_id") ?>)
-<? endif ?>
-<? if exists("item_id") ?>
- AND (itemsite_item_id=<? value("item_id") ?>)
-<? endif ?>
-<? if exists("prodcat_id") ?>
- AND (item_prodcat_id=<? value("prodcat_id") ?>)
-<? endif ?>
-<? if exists("prodcat_pattern") ?>
- AND (item_prodcat_id IN (SELECT prodcat_id
- FROM prodcat
- WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
-<? endif ?>
-<? if exists("salesrep_id") ?>
- AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
-<? endif ?>
- )
-GROUP BY cohead_id, cust_id,cust_number,cust_custtype_id,cohist_cust_id,custtype_code,
- cust_name,cohist_ordernumber, cohist_ponumber,
- cohist_invcnumber,cohist_orderdate, cohist_invcdate
-ORDER BY cohist_invcdate, cohist_orderdate;</sql>
+ <mqlgroup>briefSalesHistory</mqlgroup>
+ <mqlname>detail</mqlname>
</querysource>
<rpthead>
<height>221</height>
</font>
<hcenter/>
<vcenter/>
- <string>Ship Date</string>
+ <string>Invc. Date</string>
</label>
<label>
<rect>
<vcenter/>
<data>
<query>detail</query>
- <column>sonumber</column>
+ <column>cohist_ordernumber</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>invnumber</column>
+ <column>invoicenumber</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>f_total</column>
+ <column>f_baseextprice</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>shipdate</column>
+ <column>f_invcdate</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>orddate</column>
+ <column>f_orderdate</column>
</data>
</field>
</detail>
<vcenter/>
<data>
<query>detail</query>
- <column>total</column>
+ <column>baseextprice</column>
</data>
<format builtin="true">money</format>
<tracktotal/>
<title>Earned Commissions</title>
<name>EarnedCommissions</name>
<description></description>
+ <grid>
+ <snap/>
+ <show/>
+ <x>0.05</x>
+ <y>0.05</y>
+ </grid>
<size>Letter</size>
<portrait/>
<topmargin>50</topmargin>
formatDate(<? value("startDate") ?>) AS startdate,
formatDate(<? value("endDate") ?>) AS enddate;</sql>
</querysource>
- <querysource loadFromDb="true" >
+ <querysource loadFromDb="true">
<name>detail</name>
<mqlgroup>salesHistory</mqlgroup>
<mqlname>detail</mqlname>
<vcenter/>
<data>
<query>detail</query>
- <column>item_number</column>
+ <column>itemnumber</column>
</data>
</field>
<field>
<query>detail</query>
<column>baseextprice</column>
</data>
- <tracktotal builtin="true" >qty</tracktotal>
+ <format builtin="true">qty</format>
+ <tracktotal/>
</field>
<field>
<rect>
<query>detail</query>
<column>basecommission</column>
</data>
- <tracktotal builtin="true" >qty</tracktotal>
+ <format builtin="true">qty</format>
+ <tracktotal/>
</field>
</rptfoot>
</report>
<bottommargin>50</bottommargin>
<rightmargin>50</rightmargin>
<leftmargin>50</leftmargin>
- <querysource>
+ <querysource loadFromDb="true">
<name>detail</name>
- <sql>SELECT cohist_ordernumber AS sonumber,
- cohist_invcnumber AS invnumber,
- formatDate(cohist_orderdate) AS orddate,
- formatDate(cohist_invcdate, 'Return') AS invcdate,
- item_number, item_descrip1, item_descrip2,
- formatQty(cohist_qtyshipped) AS shipped,
- <? if exists("showPrices") ?>
- formatPrice(currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate)) AS unitprice,
- formatMoney(round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2)) AS f_total,
- <? else ?>
- '' AS unitprice,
- '' AS f_total,
- <? endif ?>
- round(cohist_qtyshipped * currtobase(cohist_curr_id,cohist_unitprice,cohist_invcdate),2) AS total
- FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
- JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
-<? if exists("includeMisc") ?>
- LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
- LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
-<? else ?>
- JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
- JOIN site() ON (warehous_id=itemsite_warehous_id)
- JOIN item ON (item_id=itemsite_item_id)
-<? endif ?>
-<? if exists("cohead_id") ?>
- JOIN cohead ON (cohead_number=cohist_ordernumber)
-<? endif ?>
-WHERE ( (true)
-<? if exists("includeMisc") ?>
- AND (COALESCE(cohist_misc_type, '') <> 'F')
- AND (COALESCE(cohist_misc_type, '') <> 'T')
-<? endif ?>
-<? if exists("startDate") ?>
- AND (cohist_invcdate >= <? value("startDate") ?>)
-<? endif ?>
-<? if exists("endDate") ?>
- AND (cohist_invcdate <= <? value("endDate") ?>)
-<? endif ?>
-<? if exists("shipStartDate") ?>
- AND (cohist_shipdate >= <? value("shipStartDate") ?>)
-<? endif ?>
-<? if exists("shipEndDate") ?>
- AND (cohist_shipdate <= <? value("shipEndDate") ?>)
-<? endif ?>
-<? if exists("salesrep_id") ?>
- AND (cohist_salesrep_id=<? value("salesrep_id") ?>)
-<? endif ?>
-<? if exists("shipto_id") ?>
- AND (cohist_shipto_id=<? value("shipto_id") ?>)
-<? endif ?>
-<? if exists("billToName") ?>
- AND (UPPER(cohist_billtoname) ~ UPPER(<? value("billToName") ?>))
-<? endif ?>
-<? if exists("cust_id") ?>
- AND (cohist_cust_id=<? value("cust_id") ?>)
-<? endif ?>
-<? if exists("custtype_id") ?>
- AND (cust_custtype_id=<? value("custtype_id") ?>)
-<? elseif exists("custtype_pattern") ?>
- AND (cust_custtype_id IN (SELECT DISTINCT custtype_id
- FROM custtype
- WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
-<? endif ?>
-<? if exists("by_custgrp" ?>
- AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
- FROM custgrpitem))
-<? endif ?>
-<? if exists("custgrp_id") ?>
- AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
- FROM custgrpitem
- WHERE (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)))
-<? endif ?>
-<? if exists("custgrp_pattern") ?>
- AND (cust_id IN (SELECT DISTINCT custgrpitem_cust_id
- FROM custgrp, custgrpitem
- WHERE ( (custgrpitem_custgrp_id=custgrp_id)
- AND (custgrp_name ~ <? value("custgrp_pattern") ?>) )) )
-<? endif ?>
-
-<? if exists("item_id") ?>
- AND (itemsite_item_id=<? value("item_id") ?>)
-<? endif ?>
-<? if exists("prodcat_id") ?>
- AND (item_prodcat_id=<? value("prodcat_id") ?>)
-<? endif ?>
-<? if exists("prodcat_pattern") ?>
- AND (item_prodcat_id IN (SELECT DISTINCT prodcat_id
- FROM prodcat
- WHERE (prodcat_code ~ <? value("prodcat_pattern") ?>)))
-<? endif ?>
-
-<? if exists("warehous_id") ?>
- AND (itemsite_warehous_id=<? value("warehous_id") ?>)
-<? endif ?>
-<? if exists("shipzone_id") ?>
- AND (cohist_shipzone_id=<? value("shipzone_id") ?>)
-<? endif ?>
-<? if exists("saletype_id") ?>
- AND (cohist_saletype_id=<? value("saletype_id") ?>)
-<? endif ?>
-<? if exists("cohead_id") ?>
- AND (cohead_id=<? value("cohead_id") ?>)
-<? endif ?>
- )
-ORDER BY cohist_invcdate, item_number</sql>
+ <mqlgroup>salesHistory</mqlgroup>
+ <mqlname>detail</mqlname>
</querysource>
<querysource>
<name>head</name>
</pghead>
<section>
<name>detail</name>
+ <group>
+ <name>total</name>
+ <column></column>
+ <foot>
+ <height>39</height>
+ <field>
+ <rect>
+ <x>660</x>
+ <y>9</y>
+ <width>80</width>
+ <height>15</height>
+ </rect>
+ <font>
+ <face>Arial</face>
+ <size>8</size>
+ <weight>bold</weight>
+ </font>
+ <right/>
+ <vcenter/>
+ <data>
+ <query>detail</query>
+ <column>baseextprice</column>
+ </data>
+ <format builtin="true">money</format>
+ <tracktotal/>
+ </field>
+ <label>
+ <rect>
+ <x>570</x>
+ <y>9</y>
+ <width>80</width>
+ <height>15</height>
+ </rect>
+ <font>
+ <face>Arial</face>
+ <size>8</size>
+ <weight>normal</weight>
+ </font>
+ <right/>
+ <vcenter/>
+ <string>Total Sales:</string>
+ </label>
+ </foot>
+ </group>
<detail>
<key>
<query>detail</query>
<vcenter/>
<data>
<query>detail</query>
- <column>sonumber</column>
+ <column>cohist_ordernumber</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>unitprice</column>
+ <column>f_baseunitprice</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>orddate</column>
+ <column>f_orderdate</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>shipped</column>
+ <column>f_qtyshipped</column>
</data>
</field>
<line>
<vcenter/>
<data>
<query>detail</query>
- <column>invcdate</column>
+ <column>f_invcdate</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>item_number</column>
+ <column>itemnumber</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>f_total</column>
+ <column>f_baseextprice</column>
</data>
</field>
<field>
<vcenter/>
<data>
<query>detail</query>
- <column>invnumber</column>
+ <column>invoicenumber</column>
</data>
</field>
</detail>
</section>
- <section>
- <name>total</name>
- <detail>
- <key>
- <query>showPrices</query>
- </key>
- <height>16</height>
- <field>
- <rect>
- <x>665</x>
- <y>0</y>
- <width>80</width>
- <height>15</height>
- </rect>
- <font>
- <face>Arial</face>
- <size>8</size>
- <weight>bold</weight>
- </font>
- <right/>
- <vcenter/>
- <data>
- <query>detail</query>
- <column>total</column>
- </data>
- <format builtin="true">money</format>
- <tracktotal/>
- </field>
- <label>
- <rect>
- <x>575</x>
- <y>0</y>
- <width>80</width>
- <height>15</height>
- </rect>
- <font>
- <face>Arial</face>
- <size>8</size>
- <weight>normal</weight>
- </font>
- <right/>
- <vcenter/>
- <string>Total Sales:</string>
- </label>
- </detail>
- </section>
<pgfoot>
<height>16</height>
<label>
SELECT dropIfExists('view', 'saleshistory');
CREATE VIEW saleshistory AS
-SELECT cohist.*,
- CASE WHEN (cohist_invcnumber='-1') THEN 'Credit'
- ELSE cohist_invcnumber
- END AS invoicenumber,
- cust_id, cust_number, cust_name, cust_curr_id, cust_custtype_id, custtype_code, custtype_descrip,
- salesrep_number, salesrep_name, shipzone_id, shipzone_name, shipzone_descrip,
- itemsite_warehous_id, itemsite_item_id,
- item_id, item_number, item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
- item_prodcat_id, warehous_code, warehous_descrip, prodcat_code,
- currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
- currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
- currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice,
+SELECT *,
round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
- round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS baseextprice,
- round((cohist_qtyshipped * currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS custextprice,
+ round((cohist_qtyshipped * baseunitprice), 2) AS baseextprice,
+ round((cohist_qtyshipped * custunitprice), 2) AS custextprice,
round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
+ round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) AS margin,
+ CASE WHEN (cohist_qtyshipped * baseunitprice > 0.0) THEN
+ (round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) /
+ round((cohist_qtyshipped * baseunitprice), 2))
+ ELSE 0.0
+ END AS marginpercent,
currConcat(cohist_curr_id) AS currAbbr,
- 'Return'::TEXT AS cohist_invcdate_xtnullrole,
'qty'::TEXT AS cohist_qtyshipped_xtnumericrole,
'salesprice'::TEXT AS cohist_unitprice_xtnumericrole,
'salesprice'::TEXT AS baseunitprice_xtnumericrole,
'curr'::TEXT AS baseextprice_xtnumericrole,
'cost'::TEXT AS cohist_unitcost_xtnumericrole,
'curr'::TEXT AS extcost_xtnumericrole,
+ 'curr'::TEXT AS margin_xtnumericrole,
+ 'percent'::TEXT AS marginpercent_xtnumericrole,
'curr'::TEXT AS cohist_commission_xtnumericrole,
- 'curr'::TEXT AS basecommission_xtnumericrole,
- 0 AS cohist_qtyshipped_xttotalrole,
- 0 AS custextprice_xttotalrole,
- 0 AS baseextprice_xttotalrole,
- 0 AS extcost_xttotalrole,
- 0 AS basecommission_xttotalrole
+ 'curr'::TEXT AS basecommission_xtnumericrole
+FROM (
+SELECT cohist.*,
+ COALESCE(cohead_id,-1) AS cohead_id,
+ cust_id, cust_number, cust_name, cust_curr_id,
+ custtype_id, custtype_code, custtype_descrip,
+ salesrep_id, salesrep_number, salesrep_name,
+ shipzone_id, shipzone_name, shipzone_descrip,
+ saletype_code, saletype_descr,
+ itemsite_id,
+ warehous_id, warehous_code, warehous_descrip,
+ item_id, item_number, item_descrip1, item_descrip2,
+ (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
+ COALESCE(item_number, cohist_misc_descrip) AS itemnumber,
+ prodcat_id, prodcat_code,
+ currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
+ currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
+ currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice
FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
- JOIN custtype ON (custtype_id=cust_custtype_id)
- JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
+ LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
+ LEFT OUTER JOIN custtype ON (custtype_id=cust_custtype_id)
+ LEFT OUTER JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
+ LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
+ LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id)
+ LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
JOIN site() ON (warehous_id=itemsite_warehous_id)
JOIN item ON (item_id=itemsite_item_id)
JOIN prodcat ON (prodcat_id=item_prodcat_id)
- LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
- LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id);
+ ) AS data;
REVOKE ALL ON TABLE saleshistory FROM PUBLIC;
GRANT ALL ON TABLE saleshistory TO GROUP xtrole;
SELECT dropIfExists('view', 'saleshistorymisc');
CREATE VIEW saleshistorymisc AS
-SELECT cohist.*,
- CASE WHEN (cohist_invcnumber='-1') THEN 'Credit'
- ELSE cohist_invcnumber
- END AS invoicenumber,
- cust_id, cust_number, cust_name, cust_curr_id, cust_custtype_id, custtype_code,
- salesrep_number, salesrep_name, shipzone_id, shipzone_name,
- itemsite_warehous_id, itemsite_item_id,
- item_number, item_descrip1, (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
- item_prodcat_id, warehous_code, prodcat_code,
- currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
- currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
- currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice,
+SELECT *,
round((cohist_qtyshipped * cohist_unitprice), 2) AS extprice,
- round((cohist_qtyshipped * currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS baseextprice,
- round((cohist_qtyshipped * currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate)), 2) AS custextprice,
+ round((cohist_qtyshipped * baseunitprice), 2) AS baseextprice,
+ round((cohist_qtyshipped * custunitprice), 2) AS custextprice,
round((cohist_qtyshipped * cohist_unitcost), 4) AS extcost,
+ round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) AS margin,
+ CASE WHEN (cohist_qtyshipped * baseunitprice > 0.0) THEN
+ (round((cohist_qtyshipped * baseunitprice) - (cohist_qtyshipped * cohist_unitcost), 2) /
+ round((cohist_qtyshipped * baseunitprice), 2))
+ ELSE 0.0
+ END AS marginpercent,
currConcat(cohist_curr_id) AS currAbbr,
- 'Return'::TEXT AS cohist_invcdate_xtnullrole,
'qty'::TEXT AS cohist_qtyshipped_xtnumericrole,
'salesprice'::TEXT AS cohist_unitprice_xtnumericrole,
'salesprice'::TEXT AS baseunitprice_xtnumericrole,
'curr'::TEXT AS baseextprice_xtnumericrole,
'cost'::TEXT AS cohist_unitcost_xtnumericrole,
'curr'::TEXT AS extcost_xtnumericrole,
+ 'curr'::TEXT AS margin_xtnumericrole,
+ 'percent'::TEXT AS marginpercent_xtnumericrole,
'curr'::TEXT AS cohist_commission_xtnumericrole,
- 'curr'::TEXT AS basecommission_xtnumericrole,
- 0 AS cohist_qtyshipped_xttotalrole,
- 0 AS custextprice_xttotalrole,
- 0 AS baseextprice_xttotalrole,
- 0 AS extcost_xttotalrole,
- 0 AS basecommission_xttotalrole
+ 'curr'::TEXT AS basecommission_xtnumericrole
+FROM (
+SELECT cohist.*,
+ COALESCE(cohead_id,-1) AS cohead_id,
+ cust_id, cust_number, cust_name, cust_curr_id,
+ custtype_id, custtype_code, custtype_descrip,
+ salesrep_id, salesrep_number, salesrep_name,
+ shipzone_id, shipzone_name, shipzone_descrip,
+ saletype_code, saletype_descr,
+ itemsite_id,
+ warehous_id, warehous_code, warehous_descrip,
+ item_id, item_number, item_descrip1, item_descrip2,
+ (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
+ COALESCE(item_number, cohist_misc_descrip) AS itemnumber,
+ prodcat_id, prodcat_code,
+ currtobase(cohist_curr_id, cohist_commission, cohist_invcdate) AS basecommission,
+ currtobase(cohist_curr_id, cohist_unitprice, cohist_invcdate) AS baseunitprice,
+ currtocurr(cohist_curr_id, cust_curr_id, cohist_unitprice, cohist_invcdate) AS custunitprice
FROM cohist JOIN custinfo ON (cust_id=cohist_cust_id)
- JOIN custtype ON (custtype_id=cust_custtype_id)
- JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
+ LEFT OUTER JOIN cohead ON (cohead_number=cohist_ordernumber)
+ LEFT OUTER JOIN custtype ON (custtype_id=cust_custtype_id)
+ LEFT OUTER JOIN salesrep ON (salesrep_id=cohist_salesrep_id)
+ LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
+ LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id)
+ LEFT OUTER JOIN saletype ON (saletype_id=cohist_saletype_id)
LEFT OUTER JOIN itemsite ON (itemsite_id=cohist_itemsite_id)
LEFT OUTER JOIN site() ON (warehous_id=itemsite_warehous_id)
LEFT OUTER JOIN item ON (item_id=itemsite_item_id)
LEFT OUTER JOIN prodcat ON (prodcat_id=item_prodcat_id)
- LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohist_shipto_id)
- LEFT OUTER JOIN shipzone ON (shipzone_id=shipto_shipzone_id);
+WHERE (COALESCE(cohist_misc_type, '') <> 'F')
+ AND (COALESCE(cohist_misc_type, '') <> 'T')
+ ) AS data;
REVOKE ALL ON TABLE saleshistorymisc FROM PUBLIC;
GRANT ALL ON TABLE saleshistorymisc TO GROUP xtrole;
projects / xtuple / commitdiff