modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/UnpostRefundPayments_data.xsql
changeset 9076 c4f1a5c7d7c2
child 11339 c0468108be7a
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/UnpostRefundPayments_data.xsql	Fri Dec 10 19:47:45 2010 +0100
@@ -0,0 +1,146 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!--
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo  Public  License
+ * Version  1.0  (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) 2010 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s):  ______________________________________.
+ ************************************************************************
+-->
+
+<SqlClass name="UnpostRefundPaymentsData" package="org.openbravo.advpaymentmngt.buildvalidation">
+  <SqlMethod name="select" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT  AD_CLIENT_ID, AD_ORG_ID,
+         CREATED, CREATEDBY, UPDATED, UPDATEDBY, ISACTIVE,
+         '' as RECORD_ID, '' as DESCRIPTION, '' as REFERENCEKEY_ID, '' as AD_ROLE_ID,
+         AD_ALERTRULE_ID, SQL, NAME
+       FROM AD_ALERTRULE
+       WHERE AD_ALERTRULE_ID = ?
+    ]]></Sql>
+    <Parameter name="alertRule"/>
+  </SqlMethod>
+  <SqlMethod name="existsAlertRule" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT COUNT(*) AS EXISTING
+       FROM AD_ALERTRULE
+       WHERE NAME LIKE 'Posted Refund Payments'
+         AND ISACTIVE = 'Y'
+      ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="getAlertRuleId" type="preparedStatement" return="string">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT MAX(ad_alertrule_id) AS name
+       FROM AD_ALERTRULE
+       WHERE NAME LIKE 'Posted Refund Payments'
+         AND ISACTIVE = 'Y'
+      ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="existsReference" type="preparedStatement" return="boolean">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT COUNT(*) AS EXISTING
+       FROM AD_ALERT
+       WHERE AD_ALERTRULE_ID = ?
+         AND REFERENCEKEY_ID = ?
+         AND ISFIXED = 'N'
+    ]]></Sql>
+    <Parameter name="alertRule"/>
+    <Parameter name="ref"/>
+  </SqlMethod>
+  <SqlMethod name="getUUID" type="preparedStatement" return="string">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT get_uuid() as name
+       FROM dual
+    ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="clientsWithPayments" type="preparedStatement" return="multiple">
+    <SqlMethodComment>This query counts the number of rows in c_bp_vendor_acct</SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT DISTINCT AD_COLUMN_IDENTIFIER('AD_Client', p.ad_client_id, 'en_US') AS NAME
+      FROM fin_payment p, fin_payment_detail pd
+      WHERE p.fin_payment_id = pd.fin_payment_id
+        AND p.posted = 'Y'
+        AND pd.refund = 'Y'
+        AND pd.isprepayment = 'N'
+    ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="existsPostedRefundPayments" type="preparedStatement" return="boolean">
+    <SqlMethodComment>This query counts the number of rows in c_bp_vendor_acct</SqlMethodComment>
+    <Sql><![CDATA[
+      SELECT count(*) AS EXISTING
+      FROM fin_payment p, fin_payment_detail pd
+      WHERE p.fin_payment_id = pd.fin_payment_id
+        AND p.posted = 'Y'
+        AND pd.refund = 'Y'
+        AND pd.isprepayment = 'N'
+    ]]></Sql>
+  </SqlMethod>
+  <SqlMethod name="insertAlertRule" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      INSERT INTO AD_ALERTRULE (
+        AD_ALERTRULE_ID, AD_CLIENT_ID, AD_ORG_ID,ISACTIVE,
+        CREATED, CREATEDBY,  UPDATED, UPDATEDBY,
+        NAME, AD_TAB_ID, FILTERCLAUSE, TYPE,
+        SQL
+      ) VALUES (
+        ?, '0', '0', 'Y',
+        now(), '100', now(), '100',
+        'Posted Refund Payments', 'C4B6506838E14A349D6717D6856F1B56', '', 'D',
+        'select fin_payment_id as referencekey_id,
+           ad_column_identifier(''FIN_Payment'', fin_payment_id, ''en_US'') as record_id,
+           0 as ad_role_id, null as ad_user_id,
+           ''Posted refund payment. Please ensure that it is unposted before applying the module version upgrade.'' as description,
+           ''Y'' as isActive,
+           ad_org_id, ad_client_id,
+           now() as created, 0 as createdBy, now() as updated, 0 as updatedBy
+           from fin_payment p
+           where p.posted= ''Y''
+             and exists (select 1 from fin_payment_detail pd where p.fin_payment_id = pd.fin_payment_id and pd.refund=''Y'' and pd.isprepayment=''N'')'
+      )
+    ]]></Sql>
+    <Parameter name="alertRuleId"/>
+  </SqlMethod>
+  <SqlMethod name="insertAlert" type="preparedStatement" return="rowcount">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+      INSERT INTO AD_Alert (
+        AD_Alert_ID, AD_Client_ID, AD_Org_ID, IsActive,
+        Created, CreatedBy, Updated, UpdatedBy,
+        Description, AD_AlertRule_ID, Record_Id, Referencekey_ID
+      ) VALUES (
+        get_uuid(), ?, '0', 'Y',
+        NOW(), '0', NOW(), '0',
+        ?, ?, ?, ?)
+      ]]>
+    </Sql>
+    <Parameter name="client" />
+    <Parameter name="description" />
+    <Parameter name="adAlertRuleId" />
+    <Parameter name="recordId" />
+    <Parameter name="referencekey_id" />
+  </SqlMethod>
+  <SqlMethod name="selectAlert" type="preparedStatement" return="multiple">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql><![CDATA[
+       SELECT AAA.*
+       FROM (SELECT '' AS DESCRIPTION, '' AS RECORD_ID, '' AS REFERENCEKEY_ID, '' AS AD_CLIENT_ID FROM DUAL) AAA 
+    ]]></Sql>
+    <Parameter name="sql" type="replace" optional="true" after="FROM (" text="SELECT '' AS DESCRIPTION, '' AS RECORD_ID, '' AS REFERENCEKEY_ID, '' AS AD_CLIENT_ID FROM DUAL"/>
+  </SqlMethod>
+</SqlClass>