modules/org.openbravo.advpaymentmngt/src-util/buildvalidation/src/org/openbravo/advpaymentmngt/buildvalidation/UnpostRefundPayments_data.xsql
author Harpreet Singh <harpreet@openbravo.com>
Fri, 10 Dec 2010 19:47:45 +0100
changeset 9076 c4f1a5c7d7c2
child 11339 c0468108be7a
permissions -rw-r--r--
Added 3.0 modules to modules dir and changed the VERSION number
<?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>