Issue #24224:nomalize sales reporting

[xtuple] / foundation-database / public / tables / metasql / summarizedSalesHistory-detail.mql

diff --git a/foundation-database/public/tables/metasql/summarizedSalesHistory-detail.mql b/foundation-database/public/tables/metasql/summarizedSalesHistory-detail.mql
index 4fc3201..6386fd8 100644 (file)
--- a/foundation-database/public/tables/metasql/summarizedSalesHistory-detail.mql
+++ b/foundation-database/public/tables/metasql/summarizedSalesHistory-detail.mql
@@ -29,19 +29,19 @@ SELECT 1 AS dummy,
          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,
@@ -76,9 +76,8 @@ SELECT 1 AS dummy,
          '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 ?>
@@ -96,11 +95,11 @@ WHERE ((COALESCE(cohist_misc_type, '') <> 'F')
 <? 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") ?>
@@ -112,11 +111,11 @@ WHERE ((COALESCE(cohist_misc_type, '') <> 'F')
 <? 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") ?>
@@ -124,7 +123,7 @@ WHERE ((COALESCE(cohist_misc_type, '') <> 'F')
 <? endif ?>
 
 <? if exists("custtype_id") ?>
- AND (cust_custtype_id=<? value("custtype_id") ?>)
+ AND (custtype_id=<? value("custtype_id") ?>)
 <? endif ?>
 
 <? if exists("custtype_pattern") ?>
@@ -158,7 +157,7 @@ WHERE ((COALESCE(cohist_misc_type, '') <> 'F')
 )
 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
@@ -167,13 +166,13 @@ GROUP BY dummy
  , 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