details: https://code.openbravo.com/erp/devel/pi/rev/a0cb0d75ac64 changeset: 17438:a0cb0d75ac64 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Jul 04 11:42:04 2012 +0200 summary: Related to issue 20917.Move ad_get_doc_le_bu out of postcripts.
details: https://code.openbravo.com/erp/devel/pi/rev/6345635cf5ff changeset: 17439:6345635cf5ff user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Jul 04 11:54:41 2012 +0200 summary: Related to issue 20917.Add ad_get_org_le_bu function. details: https://code.openbravo.com/erp/devel/pi/rev/a1c093f9393e changeset: 17440:a1c093f9393e user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Jul 04 15:49:40 2012 +0200 summary: Related to issue 20917.Fix db consistency issues in procedures. details: https://code.openbravo.com/erp/devel/pi/rev/fae24ce2e543 changeset: 17441:fae24ce2e543 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Jul 04 17:06:47 2012 +0200 summary: Related to issue 20917.Add new functions M_GET_NO_TRX_PRODUCT_COST M_GET_TRX_AND_PRODUCT_COST. details: https://code.openbravo.com/erp/devel/pi/rev/043915b30197 changeset: 17442:043915b30197 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Jul 04 19:02:42 2012 +0200 summary: Related to issue 20917.Fixed issue in M_GET_NO_TRX_PRODUCT_COST. details: https://code.openbravo.com/erp/devel/pi/rev/fb1a916d4aa2 changeset: 17443:fb1a916d4aa2 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Wed Jul 04 19:36:03 2012 +0200 summary: Fixed issue 20917.Fixes in ReportInvoiceCustomerDimensionalAnalysesJR report details: https://code.openbravo.com/erp/devel/pi/rev/c250255214e2 changeset: 17444:c250255214e2 user: Gorka Ion Damián <gorkaion.damian <at> openbravo.com> date: Thu Jul 05 10:35:27 2012 +0200 summary: Fixed issue 20917.Null costtype return cost on any new costtype. diffstat: src-db/database/model/excludeFilter.xml | 1 - src-db/database/model/functions/AD_GET_DOC_LE_BU.xml | 56 ++ src-db/database/model/functions/AD_GET_ORG_LE_BU.xml | 94 +++ src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml | 96 ++++ src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml | 53 ++ src-db/database/model/postscript-Oracle.sql | 84 --- src-db/database/model/postscript-PostgreSql.sql | 85 --- src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql | 238 +++++---- 8 files changed, 427 insertions(+), 280 deletions(-) diffs (truncated from 814 to 300 lines): diff -r a8b18247dfc5 -r c250255214e2 src-db/database/model/excludeFilter.xml --- a/src-db/database/model/excludeFilter.xml Thu May 24 16:44:45 2012 +0200 +++ b/src-db/database/model/excludeFilter.xml Thu Jul 05 10:35:27 2012 +0200 @@ -61,7 +61,6 @@ <excludedFunction name="AD_ORG_CHK_DOC_PAYMENTS"/> <excludedFunction name="C_CREATE_TEMPORARY_TABLES"/> <excludedFunction name="AD_DB_MODIFIED"/> - <excludedFunction name="AD_GET_DOC_LE_BU"/> <excludedFunction name="AD_GET_RDBMS"/> <excludedFunction name="AD_CREATE_AUDIT_TRIGGERS"/> <excludedFunction name="TEMP_FINDINARRAY"/> diff -r a8b18247dfc5 -r c250255214e2 src-db/database/model/functions/AD_GET_DOC_LE_BU.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/AD_GET_DOC_LE_BU.xml Thu Jul 05 10:35:27 2012 +0200 @@ -0,0 +1,56 @@ +<?xml version="1.0"?> + <database name="FUNCTION AD_GET_DOC_LE_BU"> + <function name="AD_GET_DOC_LE_BU" type="VARCHAR"> + <parameter name="p_header_table" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_document_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_header_column_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_type" type="VARCHAR" mode="in"> + <default/> + </parameter> + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2009-2012 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ + v_org_header_id ad_org.ad_org_id%TYPE; + v_isbusinessunit ad_orgtype.isbusinessunit%TYPE; + v_islegalentity ad_orgtype.islegalentity%TYPE; + +BEGIN + + -- Gets the organization and the organization type of the document's header + EXECUTE IMMEDIATE + 'SELECT ad_org.ad_org_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity + FROM '||p_header_table||', ad_org, ad_orgtype + WHERE '||p_header_table||'.'||p_header_column_id||' = '''||p_document_id||''' + AND ad_org.ad_orgtype_id = ad_orgtype.ad_orgtype_id + AND '||p_header_table||'.ad_org_id=ad_org.ad_org_id ' + INTO v_org_header_id, v_isbusinessunit, v_islegalentity; + + IF ((v_isbusinessunit = 'Y' AND COALESCE(p_type, 'BU') = 'BU') + OR (v_islegalentity = 'Y' AND COALESCE(p_type, 'LE') = 'LE')) THEN + RETURN v_org_header_id; + END IF; + + RETURN AD_GET_ORG_LE_BU(v_org_header_id, p_type); +END AD_GET_DOC_LE_BU +]]></body> + </function> + </database> diff -r a8b18247dfc5 -r c250255214e2 src-db/database/model/functions/AD_GET_ORG_LE_BU.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/AD_GET_ORG_LE_BU.xml Thu Jul 05 10:35:27 2012 +0200 @@ -0,0 +1,94 @@ +<?xml version="1.0"?> + <database name="FUNCTION AD_GET_ORG_LE_BU"> + <function name="AD_GET_ORG_LE_BU" type="VARCHAR"> + <parameter name="p_org_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_type" type="VARCHAR" mode="in"> + <default/> + </parameter> + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2012 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ + v_org_header_id ad_org.ad_org_id%TYPE; + v_isbusinessunit ad_orgtype.isbusinessunit%TYPE := 'N'; + v_islegalentity ad_orgtype.islegalentity%TYPE := 'N'; + +BEGIN + v_org_header_id := p_org_id; + --Check if given org is legal entity or business unit + SELECT ot.islegalentity, ot.isbusinessunit + INTO v_islegalentity, v_isbusinessunit + FROM ad_org o JOIN ad_orgtype ot ON o.ad_orgtype_id = ot.ad_orgtype_id + WHERE o.ad_org_id = p_org_id; + -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity + IF (p_type IS NULL) THEN + WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP + SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity + INTO v_org_header_id, v_isbusinessunit, v_islegalentity + FROM ad_org + JOIN ad_orgtype ON ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id + JOIN ad_treenode pp ON pp.node_id=ad_org.ad_org_id + JOIN ad_treenode hh ON pp.node_id = hh.parent_id + AND hh.ad_tree_id = pp.ad_tree_id + WHERE hh.node_id=v_org_header_id + AND ad_org.isready='Y' + AND EXISTS (SELECT 1 FROM ad_tree + WHERE ad_tree.treetype='OO' + AND hh.ad_tree_id=ad_tree.ad_tree_id + AND hh.ad_client_id=ad_tree.ad_client_id); + END LOOP; + -- Gets recursively the organization parent until finding a Legal Entity + ELSIF (p_type='LE') THEN + WHILE (v_islegalentity='N') LOOP + SELECT hh.parent_id, ad_orgtype.islegalentity + INTO v_org_header_id, v_islegalentity + FROM ad_org + JOIN ad_orgtype ON ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id + JOIN ad_treenode pp ON pp.node_id=ad_org.ad_org_id + JOIN ad_treenode hh ON pp.node_id = hh.parent_id + AND hh.ad_tree_id = pp.ad_tree_id + WHERE hh.node_id=v_org_header_id + AND ad_org.isready='Y' + AND EXISTS (SELECT 1 FROM ad_tree + WHERE ad_tree.treetype='OO' + AND hh.ad_tree_id=ad_tree.ad_tree_id + AND hh.ad_client_id=ad_tree.ad_client_id); + END LOOP; + -- Gets recursively the organization parent until finding a Business Unit + ELSIF (p_type='BU') THEN + WHILE (v_isbusinessunit='N' AND v_org_header_id<>'0') LOOP + SELECT hh.parent_id, ad_orgtype.isbusinessunit + INTO v_org_header_id, v_isbusinessunit + FROM ad_org + JOIN ad_orgtype ON ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id + JOIN ad_treenode pp ON pp.node_id=ad_org.ad_org_id + JOIN ad_treenode hh ON pp.node_id = hh.parent_id + AND hh.ad_tree_id = pp.ad_tree_id + WHERE hh.node_id=v_org_header_id + AND ad_org.isready='Y' + AND EXISTS (SELECT 1 FROM ad_tree + WHERE ad_tree.treetype='OO' + AND hh.ad_tree_id=ad_tree.ad_tree_id + AND hh.ad_client_id=ad_tree.ad_client_id); + END LOOP; + END IF; + + RETURN v_org_header_id; +END AD_GET_ORG_LE_BU +]]></body> + </function> + </database> diff -r a8b18247dfc5 -r c250255214e2 src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/M_GET_NO_TRX_PRODUCT_COST.xml Thu Jul 05 10:35:27 2012 +0200 @@ -0,0 +1,96 @@ +<?xml version="1.0"?> + <database name="FUNCTION M_GET_NO_TRX_PRODUCT_COST"> + <function name="M_GET_NO_TRX_PRODUCT_COST" type="NUMERIC"> + <parameter name="p_product_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_movementdate" type="TIMESTAMP" mode="in"> + <default/> + </parameter> + <parameter name="p_costtype" type="CHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_org_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_warehouse_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (the "License"), being the Mozilla Public License +* Version 1.1 with a permitted attribution clause; you may not use this +* file except in compliance with the License. You may obtain a copy of +* the License at http://www.openbravo.com/legal/license.html +* Software distributed under the License is distributed on an "AS IS" +* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the +* License for the specific language governing rights and limitations +* under the License. +* The Original Code is Openbravo ERP. +* The Initial Developer of the Original Code is Openbravo SLU +* All portions are Copyright (C) 2012 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ + v_Cost NUMBER; + v_Count NUMBER; + v_CostType VARCHAR2(60) ; + v_islegalentity CHAR(1); + v_legal_entity AD_ORG.AD_ORG_ID%TYPE; + TYPE RECORD IS REF CURSOR; + Cur_Cost RECORD; +BEGIN + v_CostType := p_CostType; + IF(p_Product_ID IS NULL) THEN + RETURN 0; + END IF; + IF(p_MovementDate IS NULL) THEN + RETURN 0; + END IF; + IF(p_org_id IS NULL) THEN + RETURN 0; + ELSE + SELECT ot.islegalentity INTO v_islegalentity + FROM ad_org o JOIN ad_orgtype ot ON o.ad_orgtype_id = ot.ad_orgtype_id + WHERE o.ad_org_id = p_org_id; + IF (v_islegalentity = 'Y') THEN + v_legal_entity := p_org_id; + ELSE + v_legal_entity := AD_GET_ORG_LE_BU(p_org_id, 'LE'); + END IF; + END IF; + + SELECT COUNT(*) INTO v_Count + FROM m_costing + WHERE datefrom <= p_movementdate + AND dateto > p_movementdate + AND m_product_id = p_product_id + AND COALESCE(m_warehouse_id, p_warehouse_id, '-1') = COALESCE(p_warehouse_id, m_warehouse_id, '-1') + AND ad_org_id = v_legal_entity + AND cost IS NOT NULL + AND costtype IN ('STA', 'AVA') + AND costtype = COALESCE(v_CostType, costtype); + IF(v_Count = 0) THEN + RETURN 0; + END IF; + FOR Cur_Cost IN ( + SELECT cost + FROM m_costing + WHERE datefrom <= p_movementdate + AND dateto > p_movementdate + AND m_product_id = p_product_id + AND COALESCE(m_warehouse_id, p_warehouse_id, '-1') = COALESCE(p_warehouse_id, m_warehouse_id, '-1') + AND ad_org_id = v_legal_entity + AND cost IS NOT NULL + AND costtype IN ('STA', 'AVA') + AND costtype = COALESCE(v_CostType, costtype) + ORDER BY datefrom, dateto DESC, CASE WHEN m_warehouse_id IS NULL THEN 1 ELSE 0 END + ) LOOP + v_Cost := Cur_Cost.COST; + EXIT; + END LOOP; + RETURN v_Cost; +END M_GET_NO_TRX_PRODUCT_COST +]]></body> + </function> + </database> diff -r a8b18247dfc5 -r c250255214e2 src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/src-db/database/model/functions/M_GET_TRX_AND_PRODUCT_COST.xml Thu Jul 05 10:35:27 2012 +0200 @@ -0,0 +1,53 @@ +<?xml version="1.0"?> + <database name="FUNCTION M_GET_TRX_AND_PRODUCT_COST"> + <function name="M_GET_TRX_AND_PRODUCT_COST" type="NUMERIC"> + <parameter name="p_transaction_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_product_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_movementdate" type="TIMESTAMP" mode="in"> + <default/> + </parameter> + <parameter name="p_costtype" type="CHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_org_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_warehouse_id" type="VARCHAR" mode="in"> + <default/> + </parameter> + <parameter name="p_qty" type="NUMERIC" mode="in"> + <default/> + </parameter> + <parameter name="p_precission" type="NUMERIC" mode="in"> + <default/> + </parameter> ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
