src/org/openbravo/erpCommon/ad_callouts/InvoicingSchedule_data.xsql
author RM packaging bot <staff.rm@openbravo.com>
Tue, 28 Jun 2011 10:33:23 +0000
changeset 9542 845811ab032b
parent 9072 79e55dfc8af0
permissions -rw-r--r--
Update AD_MODULE version to 2.50MP31
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
carlos@0
     3
 *************************************************************************
carlos@0
     4
 * The contents of this file are subject to the Openbravo  Public  License
priya@9072
     5
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
carlos@0
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
carlos@0
     7
 * file except in compliance with the License. You  may  obtain  a copy of
carlos@0
     8
 * the License at http://www.openbravo.com/legal/license.html 
carlos@0
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
carlos@0
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
carlos@0
    11
 * License for the specific  language  governing  rights  and  limitations
carlos@0
    12
 * under the License. 
carlos@0
    13
 * The Original Code is Openbravo ERP. 
ggi@6701
    14
 * The Initial Developer of the Original Code is Openbravo SLU 
ggi@6701
    15
 * All portions are Copyright (C) 2001-2006 Openbravo SLU 
carlos@0
    16
 * All Rights Reserved. 
carlos@0
    17
 * Contributor(s):  ______________________________________.
carlos@0
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
carlos@0
    21
carlos@0
    22
carlos@0
    23
carlos@0
    24
stefan@3932
    25
<SqlClass name="InvoicingScheduleData" package="org.openbravo.erpCommon.ad_callouts">
carlos@0
    26
  <SqlMethod name="selectPeriods" type="preparedStatement" connection="true" return="multiple">
carlos@0
    27
    <SqlMethodComment></SqlMethodComment>
carlos@0
    28
    <Sql><![CDATA[
asier@799
    29
      SELECT p.M_WH_PERIOD_ID AS ID, p.STARTDATE, p.ENDDATE, 
asier@799
    30
             '' as name, '' as AD_ORG_ID
asier@683
    31
      FROM M_WH_PERIOD p 
juanpablo@1605
    32
      WHERE p.M_WH_SCHEDULE_ID = ? 
carlos@0
    33
      AND p.STARTDATE >= TO_DATE(?)
carlos@0
    34
      AND p.ENDDATE <= TO_DATE(?)
asier@683
    35
      AND NOT EXISTS (SELECT 1 
asier@683
    36
                       FROM M_WH_PERIOD_INVOICED i 
asier@683
    37
								      WHERE i.M_WH_PERIOD_ID = p.M_WH_PERIOD_ID 
juanpablo@1605
    38
								      AND i.C_BPARTNER_ID = ?
juanpablo@1605
    39
								      AND i.AD_ORG_ID = ? )
asier@683
    40
			ORDER BY p.STARTDATE
carlos@0
    41
    ]]></Sql>
carlos@0
    42
    <Parameter name="mWhScheduleId"/>
carlos@0
    43
    <Parameter name="periodfrom"/>
carlos@0
    44
    <Parameter name="periodto"/>
carlos@0
    45
    <Parameter name="cBpartnerId"/>
carlos@0
    46
    <Parameter name="adOrgId"/>
carlos@0
    47
  </SqlMethod>
carlos@0
    48
  <SqlMethod name="selectM_WH_Period_ID" type="preparedStatement" return="multiple">
carlos@0
    49
    <SqlMethodComment></SqlMethodComment>
carlos@0
    50
    <Sql><![CDATA[
carlos@0
    51
      SELECT M_WH_Period.M_WH_Period_ID as id, (M_WH_Period.StartDate || ' - ' || M_WH_Period.EndDate) as name 
carlos@0
    52
      FROM M_WH_Period
carlos@0
    53
      WHERE M_WH_Period.AD_Org_ID IN('1') 
carlos@0
    54
      AND M_WH_Period.AD_Client_ID IN('1') 
carlos@0
    55
      AND M_WH_Period.isActive = 'Y' 
juanpablo@1605
    56
      AND M_WH_Schedule_ID = ?
carlos@0
    57
      ORDER BY M_WH_Period.StartDate
carlos@0
    58
    ]]></Sql>
carlos@0
    59
    <Parameter name="adOrgClient" type="replace" optional="true" after="M_WH_Period.AD_Org_ID IN(" text="'1'"/>
carlos@0
    60
    <Parameter name="adUserClient" type="replace" optional="true" after="M_WH_Period.AD_Client_ID IN(" text="'1'"/>
carlos@0
    61
    <Parameter name="mWhScheduleId"/>
carlos@0
    62
  </SqlMethod>
carlos@0
    63
  <SqlMethod name="selectOrg" type="preparedStatement" return="multiple">
carlos@0
    64
    <SqlMethodComment></SqlMethodComment>
carlos@0
    65
    <Sql><![CDATA[
carlos@0
    66
      SELECT AD_ORG_ID
carlos@0
    67
      FROM AD_ORG
carlos@0
    68
      WHERE AD_ORG_ID IN ('1')
carlos@0
    69
    ]]></Sql>
carlos@0
    70
    <Parameter name="adOrgClient" type="replace" optional="true" after="WHERE AD_ORG_ID IN (" text="'1'"/>
carlos@0
    71
  </SqlMethod>
carlos@0
    72
  <SqlMethod name="selectPeriodFrom" type="preparedStatement" return="Date">
carlos@0
    73
    <SqlMethodComment></SqlMethodComment>
carlos@0
    74
    <Sql><![CDATA[
carlos@0
    75
      SELECT STARTDATE
carlos@0
    76
      FROM M_WH_PERIOD 
juanpablo@1605
    77
      WHERE M_WH_PERIOD_ID = ? 
carlos@0
    78
    ]]></Sql>
carlos@0
    79
    <Parameter name="mWhPeriodId"/>
carlos@0
    80
  </SqlMethod>
carlos@0
    81
  <SqlMethod name="selectPeriodTo" type="preparedStatement" return="Date">
carlos@0
    82
    <SqlMethodComment></SqlMethodComment>
carlos@0
    83
    <Sql><![CDATA[
carlos@0
    84
      SELECT ENDDATE
carlos@0
    85
      FROM M_WH_PERIOD 
juanpablo@1605
    86
      WHERE M_WH_PERIOD_ID = ? 
carlos@0
    87
    ]]></Sql>
carlos@0
    88
    <Parameter name="mWhPeriodId"/>
carlos@0
    89
  </SqlMethod>
carlos@0
    90
  <SqlMethod name="selectCurrency" type="preparedStatement" connection="true" return="String" default="">
carlos@0
    91
    <SqlMethodComment></SqlMethodComment>
carlos@0
    92
    <Sql><![CDATA[
carlos@0
    93
      SELECT C_Currency_ID
carlos@0
    94
      FROM M_PriceList 
juanpablo@1605
    95
      WHERE M_PriceList_ID = ? 
carlos@0
    96
    ]]></Sql>
carlos@0
    97
    <Parameter name="mPricelistId"/>
carlos@0
    98
  </SqlMethod>
carlos@0
    99
  <SqlMethod name="selectPriceList" type="preparedStatement" connection="true" return="String" default="">
carlos@0
   100
    <SqlMethodComment></SqlMethodComment>
carlos@0
   101
    <Sql><![CDATA[
carlos@0
   102
      SELECT M_PriceList_ID
carlos@0
   103
      FROM C_Bpartner 
juanpablo@1605
   104
      WHERE C_Bpartner_ID = ? 
carlos@0
   105
    ]]></Sql>
carlos@0
   106
    <Parameter name="cBpartnerId"/>
carlos@0
   107
  </SqlMethod>
carlos@0
   108
  <SqlMethod name="selectUOM" type="preparedStatement" connection="true" return="String" default="">
carlos@0
   109
    <SqlMethodComment></SqlMethodComment>
carlos@0
   110
    <Sql><![CDATA[
carlos@0
   111
      SELECT C_UOM_ID
carlos@0
   112
      FROM M_PRODUCT 
juanpablo@1605
   113
      WHERE M_Product_ID = ? 
carlos@0
   114
    ]]></Sql>
carlos@0
   115
    <Parameter name="mProductId"/>
carlos@0
   116
  </SqlMethod>
carlos@0
   117
  <SqlMethod name="selectBpartner" type="preparedStatement" return="String" default="">
carlos@0
   118
    <SqlMethodComment></SqlMethodComment>
carlos@0
   119
    <Sql><![CDATA[
carlos@0
   120
      SELECT Name
carlos@0
   121
      FROM C_BPartner 
juanpablo@1605
   122
      WHERE C_BPartner_ID = ? 
carlos@0
   123
    ]]></Sql>
carlos@0
   124
    <Parameter name="cBpartnerId"/>
carlos@0
   125
  </SqlMethod>
carlos@0
   126
  <SqlMethod name="selectDateordered" type="preparedStatement" return="date">
carlos@0
   127
    <SqlMethodComment></SqlMethodComment>
carlos@0
   128
    <Sql><![CDATA[
carlos@0
   129
      SELECT dateordered
carlos@0
   130
      FROM C_ORDER
juanpablo@1605
   131
      WHERE C_ORDER_ID = ? 
carlos@0
   132
    ]]></Sql>
carlos@0
   133
    <Parameter name="cBpartnerId"/>
carlos@0
   134
  </SqlMethod>
carlos@0
   135
  <SqlMethod name="selectProject" type="preparedStatement" return="String" default="">
carlos@0
   136
    <SqlMethodComment></SqlMethodComment>
carlos@0
   137
    <Sql><![CDATA[
carlos@0
   138
      SELECT C_Project_ID
carlos@0
   139
      FROM C_ORDER
juanpablo@1605
   140
      WHERE C_ORDER_ID = ? 
carlos@0
   141
    ]]></Sql>
carlos@0
   142
    <Parameter name="cOrderId"/>
carlos@0
   143
  </SqlMethod>
carlos@0
   144
  <SqlMethod name="update" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   145
    <SqlMethodComment></SqlMethodComment>
carlos@0
   146
    <Sql><![CDATA[
carlos@0
   147
      UPDATE M_INOUTLINE SET ISINVOICED='Y' WHERE M_INOUT_ID IN (
carlos@0
   148
      SELECT IO.M_INOUT_ID
asier@799
   149
      FROM M_INOUT IO, M_INOUTLINE IOL
carlos@0
   150
      WHERE IO.M_INOUT_ID = IOL.M_INOUT_ID
juanpablo@1605
   151
      AND IO.C_BPARTNER_ID = ?
carlos@0
   152
      AND IO.AD_ORG_ID IN ('1')
carlos@0
   153
      AND IO.AD_CLIENT_ID IN ('1') 
carlos@0
   154
      AND IO.ISLOGISTIC  = 'Y'
carlos@0
   155
      AND IOL.ISINVOICED = 'N'
carlos@0
   156
      AND IO.DOCSTATUS = 'CO'
carlos@0
   157
      AND IO.MOVEMENTDATE >= TO_DATE(?)
carlos@0
   158
      AND IO.MOVEMENTDATE < TO_DATE(?)
carlos@0
   159
      GROUP BY IO.M_INOUT_ID)
carlos@0
   160
    ]]></Sql>
carlos@0
   161
    <Parameter name="cBpartnerId"/>
carlos@0
   162
    <Parameter name="adOrgClient" type="replace" optional="true" after="AND IO.AD_ORG_ID IN (" text="'1'"/>
carlos@0
   163
    <Parameter name="adUserClient" type="replace" optional="true" after="AND IO.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
   164
    <Parameter name="periodfrom"/>
carlos@0
   165
    <Parameter name="periodto"/>
carlos@0
   166
  </SqlMethod>
carlos@0
   167
  <SqlMethod name="createPeriodInvoiced" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   168
    <SqlMethodComment></SqlMethodComment>
carlos@0
   169
    <Sql><![CDATA[
carlos@0
   170
      INSERT INTO M_WH_PERIOD_INVOICED (M_WH_PERIOD_INVOICED_ID, AD_CLIENT_ID, AD_ORG_ID, ISACTIVE, CREATED, CREATEDBY, 
carlos@0
   171
      UPDATED, UPDATEDBY, M_WH_PERIOD_ID, C_BPARTNER_ID, C_ORDER_ID) 
juanpablo@1605
   172
      VALUES (?, ?, ?, 'Y', now(), ?, 
juanpablo@1605
   173
      now(), ?, ?, ?, ?)
carlos@0
   174
    ]]></Sql>
carlos@0
   175
    <Parameter name="mWhPeriodInvoicedId"/>
carlos@0
   176
    <Parameter name="adClientId"/>
carlos@0
   177
    <Parameter name="adOrgId"/>
carlos@0
   178
    <Parameter name="adUserId"/>
carlos@0
   179
    <Parameter name="adUserId"/>
carlos@0
   180
    <Parameter name="mWhPeriodId"/>
carlos@0
   181
    <Parameter name="cBpartnerId"/>
carlos@0
   182
    <Parameter name="cOrderId"/>
carlos@0
   183
  </SqlMethod>
carlos@0
   184
  <SqlMethod name="insertPInstance" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   185
    <SqlMethodComment></SqlMethodComment>
carlos@0
   186
    <Sql>
carlos@0
   187
      INSERT INTO AD_PINSTANCE (AD_PINSTANCE_ID, AD_PROCESS_ID, RECORD_ID, ISPROCESSING, CREATED, AD_USER_ID, UPDATED, AD_CLIENT_ID, AD_ORG_ID, CREATEDBY, UPDATEDBY)
juanpablo@1605
   188
      VALUES(?,?,?,?,now(),?,now(), ?, ?, ?, ?)
carlos@0
   189
    </Sql>
carlos@0
   190
    <Parameter name="adPinstanceId"/>
carlos@0
   191
    <Parameter name="adProcessId"/>
carlos@0
   192
    <Parameter name="recordId"/>
carlos@0
   193
    <Parameter name="isprocessing"/>
carlos@0
   194
    <Parameter name="adUserId"/>
carlos@0
   195
    <Parameter name="adClientId"/>
carlos@0
   196
    <Parameter name="adOrgId"/>
carlos@0
   197
    <Parameter name="adUserId"/>
carlos@0
   198
    <Parameter name="adUserId"/>
carlos@0
   199
  </SqlMethod>
carlos@0
   200
  <SqlMethod name="process104" type="callableStatement" return="object" connection="true" object="InvoicingScheduleData">
carlos@0
   201
      <SqlMethodComment></SqlMethodComment>
carlos@0
   202
      <Sql><![CDATA[
juanpablo@1605
   203
        CALL C_Order_Post(?)
carlos@0
   204
      ]]></Sql>
carlos@0
   205
      <Parameter name="adPinstanceId"></Parameter>
carlos@0
   206
   </SqlMethod>
carlos@0
   207
</SqlClass>