Fixes BUG-0044310: Avoid hard code value for Doc Type ID
authorAtul Gaware <atul.gaware@openbravo.com>
Thu, 18 Jun 2020 11:01:21 +0530
changeset 37457 cc1e88215ec8
parent 37456 cfa2470b6b63
child 37458 e30010b81e7d
Fixes BUG 0044310: Avoid hard code value for Doc Type ID
parameter when calling DB function m_get_default_aum_for_document

**Fetch Document Type ID from C_Order that is to be used as a parameter
for DB function M_GET_DEFAULT_AUM_FOR_DOCUMENT instead of a fixed ID.
src-db/database/model/functions/C_INVOICE_CREATE.xml
src-db/database/model/views/M_CREATEPOLINES_PE_V.xml
src/org/openbravo/erpCommon/ad_actionButton/CopyFromOrder_data.xsql
--- a/src-db/database/model/functions/C_INVOICE_CREATE.xml	Wed Jun 24 13:29:44 2020 +0000
+++ b/src-db/database/model/functions/C_INVOICE_CREATE.xml	Thu Jun 18 11:01:21 2020 +0530
@@ -537,6 +537,10 @@
 											END IF;
 	                  END IF;
 
+                SELECT C_DOCTYPE_ID
+                INTO c_soo_doctype
+                FROM C_ORDER
+                WHERE C_ORDER_ID = ptr_ol.C_ORDER_ID;
 	            v_default_aum := CASE WHEN v_uom_preference = 'Y' AND ptr_ol.AUMQTY IS NULL AND ptr_ol.M_Product_Uom_ID IS NULL 
 					 THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(ptr_ol.M_Product_ID, c_soo_doctype) 
 					 ELSE ptr_ol.C_AUM 
@@ -650,6 +654,13 @@
 											END IF;
 	                  END IF;
 		    
+		    SELECT C_DOCTYPE_ID
+		    INTO c_soo_doctype
+		    FROM C_ORDER
+		    WHERE EXISTS (SELECT 1
+		                  FROM C_ORDERLINE
+		                  WHERE C_ORDER.C_ORDER_ID = C_ORDERLINE.C_ORDER_ID
+		                  AND C_ORDERLINE.C_ORDERLINE_ID =  ptr_sl.C_ORDERLINE_ID);
 		    v_default_aum := CASE WHEN v_uom_preference = 'Y' AND ptr_sl.AUMQTY IS NULL AND ptr_sl.M_Product_Uom_ID IS NULL 
 					 THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(ptr_sl.M_Product_ID, c_soo_doctype) 
 					 ELSE ptr_sl.C_AUM 
@@ -1224,6 +1235,12 @@
 									END IF;
                 END IF;
 
+                IF(Cur_InvoiceSchedule.C_Order_ID IS NOT NULL) THEN
+                  SELECT C_DOCTYPE_ID
+                  INTO c_soo_doctype
+                  FROM C_ORDER
+                  WHERE C_ORDER_ID = Cur_InvoiceSchedule.C_Order_ID;
+                END IF;
                 v_default_aum := CASE WHEN v_uom_preference = 'Y' AND Cur_InvoiceSchedule.AUMQTY IS NULL AND Cur_InvoiceSchedule.M_Product_Uom_ID IS NULL 
 					 THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(Cur_InvoiceSchedule.M_Product_ID, c_soo_doctype) 
 					 ELSE Cur_InvoiceSchedule.C_AUM 
--- a/src-db/database/model/views/M_CREATEPOLINES_PE_V.xml	Wed Jun 24 13:29:44 2020 +0000
+++ b/src-db/database/model/views/M_CREATEPOLINES_PE_V.xml	Thu Jun 18 11:01:21 2020 +0530
@@ -1,4 +1,4 @@
 <?xml version="1.0"?>
   <database name="VIEW M_CREATEPOLINES_PE_V">
-    <view name="M_CREATEPOLINES_PE_V"><![CDATA[SELECT pp.m_productprice_id AS m_createpolines_pe_v_id, pp.ad_client_id, pp.ad_org_id, pp.isactive, pp.created, pp.createdby, pp.updated, pp.updatedby, 'N' AS ob_selected, pp.m_product_id, plv.m_pricelist_id, pp.pricestd, NULL AS qtyordered, NULL AS qtyaum, m_get_default_aum_for_document(p.m_product_id, '24C649C1402B4ABDA4C59046BB45B9FF') AS aum FROM m_productprice pp JOIN m_product p ON pp.m_product_id = p.m_product_id AND COALESCE(p.isgeneric, 'N') = 'N' JOIN m_pricelist_version plv ON pp.m_pricelist_version_id = plv.m_pricelist_version_id JOIN m_pricelist pl ON pl.m_pricelist_id = plv.m_pricelist_id WHERE p.isactive = 'Y' AND pl.issopricelist = 'N' AND plv.validfrom = ((SELECT max(plv2.validfrom) AS max FROM m_pricelist_version plv2 WHERE plv2.m_pricelist_id = pl.m_pricelist_id AND plv2.validfrom <= now()))]]></view>
+    <view name="M_CREATEPOLINES_PE_V"><![CDATA[SELECT pp.m_productprice_id AS m_createpolines_pe_v_id, pp.ad_client_id, pp.ad_org_id, pp.isactive, pp.created, pp.createdby, pp.updated, pp.updatedby, 'N' AS ob_selected, pp.m_product_id, plv.m_pricelist_id, pp.pricestd, NULL AS qtyordered, NULL AS qtyaum, m_get_default_aum_for_document(p.m_product_id, ad_get_doctype(p.ad_client_id, p.ad_org_id, 'POO', NULL)) AS aum FROM m_productprice pp JOIN m_product p ON pp.m_product_id = p.m_product_id AND COALESCE(p.isgeneric, 'N') = 'N' JOIN m_pricelist_version plv ON pp.m_pricelist_version_id = plv.m_pricelist_version_id JOIN m_pricelist pl ON pl.m_pricelist_id = plv.m_pricelist_id WHERE p.isactive = 'Y' AND pl.issopricelist = 'N' AND plv.validfrom = ((SELECT max(plv2.validfrom) AS max FROM m_pricelist_version plv2 WHERE plv2.m_pricelist_id = pl.m_pricelist_id AND plv2.validfrom <= now()))]]></view>
   </database>
--- a/src/org/openbravo/erpCommon/ad_actionButton/CopyFromOrder_data.xsql	Wed Jun 24 13:29:44 2020 +0000
+++ b/src/org/openbravo/erpCommon/ad_actionButton/CopyFromOrder_data.xsql	Thu Jun 18 11:01:21 2020 +0530
@@ -12,7 +12,7 @@
  * under the License. 
  * The Original Code is Openbravo ERP. 
  * The Initial Developer of the Original Code is Openbravo SLU 
- * All portions are Copyright (C) 2001-2019 Openbravo SLU 
+ * All portions are Copyright (C) 2001-2020 Openbravo SLU 
  * All Rights Reserved. 
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -58,8 +58,8 @@
                                                                ELSE M_BOM_PRICESTD(CL.M_PRODUCT_ID, M_GET_PRICELIST_VERSION(?, TO_DATE(?))) 
                                                                 END) AS LASTPRICESO,
         C_TAX.C_TAX_ID, C_TAX.NAME AS NAMETAX, C_UOM.C_UOM_ID, C_UOM.UOMSYMBOL,
-        (CASE WHEN CL.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(CL.M_PRODUCT_ID, '01360D369B5F473686354A701A6EA559') ELSE CL.C_AUM END) AS C_AUM,
-        (CASE WHEN CL.C_AUM IS NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(CL.M_PRODUCT_ID, '01360D369B5F473686354A701A6EA559')) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = CL.C_AUM) END) AS C_AUMSYMBOL,
+        (CASE WHEN CL.C_AUM IS NULL THEN M_GET_DEFAULT_AUM_FOR_DOCUMENT(CL.M_PRODUCT_ID, C.C_DOCTYPE_ID) ELSE CL.C_AUM END) AS C_AUM,
+        (CASE WHEN CL.C_AUM IS NULL THEN (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = M_GET_DEFAULT_AUM_FOR_DOCUMENT(CL.M_PRODUCT_ID, C.C_DOCTYPE_ID)) ELSE (SELECT UOMSYMBOL FROM C_UOM WHERE C_UOM_ID = CL.C_AUM) END) AS C_AUMSYMBOL,
         C.C_DOCTYPE_ID
         FROM C_ORDER C JOIN m_pricelist pl ON c.m_pricelist_id = pl.m_pricelist_id,
             C_ORDERLINE CL LEFT JOIN (SELECT M_ATTRIBUTESETINSTANCE_ID , DESCRIPTION FROM M_ATTRIBUTESETINSTANCE) MA ON (CL.M_ATTRIBUTESETINSTANCE_ID = MA.M_ATTRIBUTESETINSTANCE_ID), M_PRODUCT M, C_UOM, C_TAX