src/org/openbravo/erpCommon/ad_actionButton/ExpenseSOrder_data.xsql
author Asier Lostalé <asier.lostale@openbravo.com>
Mon, 05 May 2008 06:59:24 +0000
changeset 799 fef2c5e2feb7
parent 597 284cd726c37c
child 895 ae9ae8e8c9cc
permissions -rw-r--r--
Merged cleanup branch (r3931) with trunk
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
carlos@0
     5
 * Version  1.0  (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. 
carlos@0
    14
 * The Initial Developer of the Original Code is Openbravo SL 
rafael@597
    15
 * All portions are Copyright (C) 2001-2008 Openbravo SL 
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
carlos@0
    25
<SqlClass name="ExpenseSOrderData" package="org.openbravo.erpCommon.ad_actionButton">
carlos@0
    26
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    27
    <SqlMethodComment></SqlMethodComment>
carlos@0
    28
    <Sql>
carlos@0
    29
      <![CDATA[
carlos@0
    30
        SELECT E.AD_CLIENT_ID, E.AD_ORG_ID,EL.C_BPARTNER_ID, E.M_WAREHOUSE_ID,
carlos@0
    31
        EL.C_PROJECT_ID, EL.C_ACTIVITY_ID, EL.C_CAMPAIGN_ID,
carlos@0
    32
        EL.M_PRODUCT_ID, EL.S_RESOURCEASSIGNMENT_ID, EL.C_UOM_ID,EL.QTY, EL.DESCRIPTION, EL.NOTE, EL.INVOICEPRICE,
carlos@0
    33
        COALESCE(BP.M_PRICELIST_ID, E.M_PRICELIST_ID) AS M_PRICELIST_ID, E.DOCUMENTNO, EL.S_TIMEEXPENSELINE_ID, EL.C_CURRENCY_ID,
rafael@597
    34
        '' AS DATEORDERED, '' AS DATEPROMISED, '' AS C_BPARTNER_LOCATION_ID, '' AS PROCESSING,
rafael@597
    35
        '' AS STDPRECISION, '' AS PRICEPRECISION 
carlos@0
    36
        FROM S_TIMEEXPENSELINE EL left join  S_RESOURCEASSIGNMENT RA on EL.S_RESOURCEASSIGNMENT_ID=RA.S_RESOURCEASSIGNMENT_ID,
carlos@0
    37
        S_TIMEEXPENSE E,  C_BPARTNER BP
carlos@0
    38
        WHERE E.S_TIMEEXPENSE_ID=EL.S_TIMEEXPENSE_ID
carlos@0
    39
        AND EL.C_BPARTNER_ID=BP.C_BPARTNER_ID
carlos@0
    40
        AND EL.C_ORDERLINE_ID IS NULL
carlos@0
    41
        AND EL.ISINVOICED='Y'
carlos@0
    42
        AND E.PROCESSED='Y'
carlos@0
    43
        AND E.AD_CLIENT_ID IN ('1')
carlos@0
    44
        AND E.AD_ORG_ID IN ('1')
carlos@0
    45
        ORDER BY E.AD_ORG_ID, el.C_BPartner_ID, el.C_Project_ID, e.S_TimeExpense_ID, el.Line
carlos@0
    46
      ]]>
carlos@0
    47
    </Sql>
carlos@423
    48
    <Parameter name="bpartner" optional="true" after="WHERE "><![CDATA[ el.C_BPartner_ID = TO_NUMBER(?) AND ]]></Parameter>
carlos@0
    49
    <Parameter name="dateFrom" optional="true" after="WHERE "><![CDATA[ e.DateReport >= TO_DATE(?) AND ]]></Parameter>
carlos@0
    50
    <Parameter name="dateTo" optional="true" after="WHERE "><![CDATA[ e.DateReport  < TO_DATE(?) AND ]]></Parameter>
carlos@0
    51
    <Parameter name="adUserClient" type="replace" optional="true" after="E.AD_CLIENT_ID IN (" text="'1'"/>
carlos@0
    52
    <Parameter name="adUserOrg" type="replace" optional="true" after="E.AD_ORG_ID IN (" text="'1'"/>
carlos@0
    53
  </SqlMethod>
carlos@0
    54
  <SqlMethod name="updateTimeExpenseLine" type="preparedStatement" connection="true" return="rowCount">
carlos@0
    55
    <SqlMethodComment></SqlMethodComment>
carlos@0
    56
    <Sql>
carlos@0
    57
      <![CDATA[
carlos@0
    58
        UPDATE S_TimeExpenseLine SET C_OrderLine_ID= TO_NUMBER(?)
carlos@423
    59
        WHERE S_TimeExpenseLine_ID = TO_NUMBER(?)
carlos@0
    60
      ]]>
carlos@0
    61
    </Sql>
carlos@0
    62
    <Parameter name="COrderlineId"/>
carlos@0
    63
    <Parameter name="STimeExpenseLineId"/>
carlos@0
    64
  </SqlMethod>
carlos@0
    65
  <SqlMethod name="insertCOrder" type="preparedStatement" connection="true" return="rowCount">
carlos@0
    66
    <SqlMethodComment></SqlMethodComment>
carlos@0
    67
    <Sql>
carlos@0
    68
      <![CDATA[
carlos@0
    69
        INSERT INTO C_ORDER (C_ORDER_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY, DOCUMENTNO,
rafael@597
    70
        DOCSTATUS, DOCACTION, PROCESSING, C_DOCTYPE_ID, C_DOCTYPETARGET_ID,
carlos@0
    71
        DATEORDERED, DATEPROMISED, DATEACCT, C_BPARTNER_ID, C_BPARTNER_LOCATION_ID, BILLTO_ID, C_CURRENCY_ID, 
asier@799
    72
        PAYMENTRULE, C_PAYMENTTERM_ID, INVOICERULE, DELIVERYRULE,
carlos@0
    73
        DELIVERYVIARULE, M_WAREHOUSE_ID, M_PRICELIST_ID, 
carlos@0
    74
        C_PROJECT_ID, C_ACTIVITY_ID, C_CAMPAIGN_ID, PRIORITYRULE)
asier@799
    75
        VALUES (TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),now(),TO_NUMBER(?),now(),TO_NUMBER(?),?,?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_DATE(?),TO_DATE(?),TO_DATE(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),?,TO_NUMBER(?),?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),'5')
carlos@0
    76
      ]]>
carlos@0
    77
    </Sql>
carlos@0
    78
    <Parameter name="cOrderId"/>
carlos@0
    79
    <Parameter name="adClientId"/>
carlos@0
    80
    <Parameter name="adOrgId"/>
carlos@0
    81
    <Parameter name="user"/>
carlos@0
    82
    <Parameter name="user"/>
carlos@0
    83
    <Parameter name="documentNo"/>
carlos@0
    84
    <Parameter name="docStatus"/>
carlos@0
    85
    <Parameter name="docAction"/>
rafael@597
    86
    <Parameter name="processing"/>
carlos@0
    87
    <Parameter name="cDoctypeId"/>
carlos@0
    88
    <Parameter name="cDoctypetargetId"/>
carlos@0
    89
    <Parameter name="dateordered"/>
carlos@0
    90
    <Parameter name="datepromised"/>
carlos@0
    91
    <Parameter name="dateacct"/>
carlos@0
    92
    <Parameter name="cBpartnerId"/>
carlos@0
    93
    <Parameter name="cBpartnerLocationId"/>
carlos@0
    94
    <Parameter name="billtoId"/>
carlos@0
    95
    <Parameter name="cCurrencyId"/>
carlos@0
    96
    <Parameter name="paymentrule"/>
carlos@0
    97
    <Parameter name="cPaymenttermId"/>
carlos@0
    98
    <Parameter name="invoicerule"/>
carlos@0
    99
    <Parameter name="deliveryrule"/>
carlos@0
   100
    <Parameter name="deliveryviarule"/>
carlos@0
   101
    <Parameter name="mWarehouseId"/>
carlos@0
   102
    <Parameter name="mPricelistId"/>
carlos@0
   103
    <Parameter name="cProjectId"/>
carlos@0
   104
    <Parameter name="cActivityId"/>
carlos@0
   105
    <Parameter name="cCampaignId"/>
carlos@0
   106
  </SqlMethod>
carlos@0
   107
  <SqlMethod name="insertCOrderline" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   108
    <SqlMethodComment></SqlMethodComment>
carlos@0
   109
    <Sql>
carlos@0
   110
      <![CDATA[
carlos@0
   111
        INSERT INTO C_ORDERLINE (C_ORDERLINE_ID, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY,
carlos@0
   112
        C_ORDER_ID, LINE, C_BPARTNER_ID, C_BPARTNER_LOCATION_ID, DATEORDERED, DATEPROMISED, 
carlos@0
   113
        DESCRIPTION, M_PRODUCT_ID, M_WAREHOUSE_ID, C_UOM_ID, QTYORDERED, C_CURRENCY_ID, 
carlos@0
   114
        PRICELIST, 
carlos@0
   115
        PRICEACTUAL, 
carlos@0
   116
        PRICELIMIT, C_TAX_ID, S_RESOURCEASSIGNMENT_ID, DISCOUNT, 
carlos@0
   117
        PRICESTD) 
carlos@0
   118
        VALUES (TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),now(),TO_NUMBER(?),now(),TO_NUMBER(?),
carlos@0
   119
        TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_DATE(?),TO_DATE(?),
carlos@0
   120
        ?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),
carlos@0
   121
        TO_NUMBER(?), 
carlos@0
   122
        M_GET_OFFERS_PRICE(now(), TO_NUMBER(?), TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?)),
carlos@0
   123
        TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?),TO_NUMBER(?), 
carlos@0
   124
        TO_NUMBER(?))
carlos@0
   125
      ]]>
carlos@0
   126
    </Sql>
carlos@0
   127
    <Parameter name="cOrderlineId"/>
carlos@0
   128
    <Parameter name="adClientId"/>
carlos@0
   129
    <Parameter name="adOrgId"/>
carlos@0
   130
    <Parameter name="user"/>
carlos@0
   131
    <Parameter name="user"/>
carlos@0
   132
    <Parameter name="cOrderId"/>
carlos@0
   133
    <Parameter name="line"/>
carlos@0
   134
    <Parameter name="cBpartnerId"/>
carlos@0
   135
    <Parameter name="cBpartnerLocationId"/>
carlos@0
   136
    <Parameter name="dateordered"/>
carlos@0
   137
    <Parameter name="datepromised"/>
carlos@0
   138
    <Parameter name="description"/>
carlos@0
   139
    <Parameter name="mProductId"/>
carlos@0
   140
    <Parameter name="mWarehouseId"/>
carlos@0
   141
    <Parameter name="cUomId"/>
carlos@0
   142
    <Parameter name="qtyordered"/>
carlos@0
   143
    <Parameter name="cCurrencyId"/>
carlos@0
   144
    <Parameter name="pricelist"/>
carlos@0
   145
    <Parameter name="cBpartnerId"/>
carlos@0
   146
    <Parameter name="mProductId"/>
carlos@0
   147
    <Parameter name="priceactual"/>
carlos@0
   148
    <Parameter name="qtyordered"/>
carlos@0
   149
    <Parameter name="pricelist"/>
carlos@0
   150
    <Parameter name="pricelimit"/>
carlos@0
   151
    <Parameter name="cTaxId"/>
carlos@0
   152
    <Parameter name="sResourceassignmentId"/>
carlos@0
   153
    <Parameter name="discount"/>
carlos@0
   154
    <Parameter name="priceactual"/>
carlos@0
   155
  </SqlMethod>
carlos@0
   156
  <SqlMethod name="cDoctypeTarget" type="preparedStatement" connection="true" return="string" default="">
carlos@0
   157
    <SqlMethodComment></SqlMethodComment>
carlos@0
   158
    <Sql>
carlos@0
   159
      <![CDATA[
carlos@0
   160
	SELECT AD_GET_DOCTYPE(TO_NUMBER(?), TO_NUMBER(?), 'SOO', 'WP') FROM DUAL
carlos@0
   161
      ]]>
carlos@0
   162
    </Sql>
carlos@0
   163
    <Parameter name="adClientId"/>
carlos@0
   164
    <Parameter name="adOrgId"/>
carlos@0
   165
  </SqlMethod>
carlos@0
   166
  <SqlMethod name="cBPartnerLocationId" type="preparedStatement" return="string">
carlos@0
   167
    <SqlMethodComment></SqlMethodComment>
carlos@0
   168
    <Sql>
carlos@0
   169
      <![CDATA[
carlos@0
   170
        SELECT MAX(C_BPARTNER_LOCATION_ID) FROM C_BPARTNER_LOCATION
carlos@423
   171
        WHERE C_BPARTNER_ID = TO_NUMBER(?)
carlos@0
   172
        AND C_BPartner_Location.IsActive='Y'
carlos@0
   173
      ]]>
carlos@0
   174
    </Sql>
carlos@0
   175
    <Parameter name="cBpartnerId"/>
carlos@0
   176
  </SqlMethod>
carlos@0
   177
  <SqlMethod name="billto" type="preparedStatement" return="string">
carlos@0
   178
    <SqlMethodComment></SqlMethodComment>
carlos@0
   179
    <Sql>
carlos@0
   180
      <![CDATA[
carlos@0
   181
        SELECT MAX(C_BPARTNER_LOCATION_ID) FROM C_BPARTNER_LOCATION
carlos@0
   182
        WHERE  C_BPartner_Location.IsBillTo='Y' 
carlos@0
   183
        AND C_BPartner_Location.IsActive='Y'
carlos@423
   184
        AND C_BPARTNER_ID = TO_NUMBER(?)
carlos@0
   185
      ]]>
carlos@0
   186
    </Sql>
carlos@0
   187
    <Parameter name="cBpartnerId"/>
carlos@0
   188
  </SqlMethod>
carlos@0
   189
  <SqlMethod name="selectBpartner" type="preparedStatement" return="String" default="">
carlos@0
   190
    <SqlMethodComment></SqlMethodComment>
carlos@0
   191
    <Sql><![CDATA[
carlos@0
   192
      SELECT Name
carlos@0
   193
      FROM C_BPartner 
carlos@423
   194
      WHERE C_BPartner_ID = TO_NUMBER(?) 
carlos@0
   195
    ]]></Sql>
carlos@0
   196
    <Parameter name="cBpartnerId"/>
carlos@0
   197
  </SqlMethod>
carlos@0
   198
  <SqlMethod name="insertPInstance" connection="true" type="preparedStatement" return="rowCount">
carlos@0
   199
    <SqlMethodComment></SqlMethodComment>
carlos@0
   200
    <Sql>
carlos@0
   201
      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)
carlos@0
   202
      VALUES(TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),'N',now(),TO_NUMBER(?),now(), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?))
carlos@0
   203
    </Sql>
carlos@0
   204
    <Parameter name="adPinstanceId"/>
carlos@0
   205
    <Parameter name="adProcessId"/>
carlos@0
   206
    <Parameter name="recordId"/>
carlos@0
   207
    <Parameter name="adUserId"/>
carlos@0
   208
    <Parameter name="adClientId"/>
carlos@0
   209
    <Parameter name="adOrgId"/>
carlos@0
   210
    <Parameter name="adUserId"/>
carlos@0
   211
    <Parameter name="adUserId"/>
carlos@0
   212
  </SqlMethod>
carlos@0
   213
  <SqlMethod name="processOrder" type="callableStatement" connection="true" return="object" object="ExpenseSOrderData">
carlos@0
   214
      <SqlMethodComment></SqlMethodComment>
carlos@0
   215
      <Sql><![CDATA[
carlos@0
   216
        CALL C_Order_Post(TO_NUMBER(?))
carlos@0
   217
      ]]></Sql>
carlos@0
   218
      <Parameter name="adPinstanceId"/>
carlos@0
   219
   </SqlMethod>
rafael@597
   220
   <SqlMethod name="help" type="preparedStatement" return="string" default="">
rafael@597
   221
     <SqlMethodComment></SqlMethodComment>
rafael@597
   222
     <Sql>
rafael@597
   223
       <![CDATA[
rafael@597
   224
       SELECT HELP
rafael@597
   225
       FROM AD_PROCESS
rafael@597
   226
       WHERE VALUE = TO_CHAR(?)
rafael@597
   227
      ]]>
rafael@597
   228
    </Sql>
rafael@597
   229
    <Parameter name="process"/>
rafael@597
   230
  </SqlMethod>
rafael@597
   231
  <SqlMethod name="description" type="preparedStatement" return="string" default="">
rafael@597
   232
     <SqlMethodComment></SqlMethodComment>
rafael@597
   233
     <Sql>
rafael@597
   234
       <![CDATA[
rafael@597
   235
       SELECT DESCRIPTION
rafael@597
   236
       FROM AD_PROCESS
rafael@597
   237
       WHERE VALUE = TO_CHAR(?)
rafael@597
   238
      ]]>
rafael@597
   239
    </Sql>
rafael@597
   240
    <Parameter name="process"/>
rafael@597
   241
  </SqlMethod>
rafael@597
   242
  <SqlMethod name="selectPrecisions" type="preparedStatement" return="multiple">
rafael@597
   243
      <SqlMethodComment></SqlMethodComment>
rafael@597
   244
      <Sql>
rafael@597
   245
        <![CDATA[
rafael@597
   246
          SELECT STDPRECISION, PRICEPRECISION 
rafael@597
   247
          FROM C_CURRENCY 
rafael@597
   248
          WHERE C_CURRENCY_ID = to_number(?)
rafael@597
   249
        ]]>
rafael@597
   250
      </Sql>
rafael@597
   251
      <Parameter name="cCurrencyId"/>
rafael@597
   252
   </SqlMethod>
carlos@0
   253
</SqlClass>