src/org/openbravo/erpCommon/ad_callouts/SL_Order_Product_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-2008 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
<SqlClass name="SLOrderProductData" package="org.openbravo.erpCommon.ad_callouts">
carlos@0
    23
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    24
    <SqlMethodComment></SqlMethodComment>
carlos@0
    25
    <Sql>
carlos@0
    26
      <![CDATA[
carlos@0
    27
      SELECT C_ORDER_ID AS ID, DATEORDERED, C_BPARTNER_ID, M_PRICELIST_ID, '' AS DATEINVOICED 
juanpablo@1605
    28
        FROM C_ORDER WHERE C_ORDER_ID = ?
carlos@0
    29
      ]]>
carlos@0
    30
    </Sql>
carlos@0
    31
    <Parameter name="cOrderId"/>
carlos@0
    32
  </SqlMethod>
carlos@0
    33
  <SqlMethod name="selectInvoice" type="preparedStatement" return="multiple">
carlos@0
    34
    <SqlMethodComment></SqlMethodComment>
carlos@0
    35
    <Sql>
carlos@0
    36
      <![CDATA[
rafael@418
    37
      SELECT C_INVOICE_ID AS ID, DATEINVOICED, C_BPARTNER_ID, M_PRICELIST_ID
juanpablo@1605
    38
      	FROM C_INVOICE WHERE C_INVOICE_ID = ?
carlos@0
    39
      ]]>
carlos@0
    40
    </Sql>
carlos@0
    41
    <Parameter name="cInvoiceId"/>
carlos@0
    42
  </SqlMethod>
carlos@0
    43
  <!--SqlMethod name="strMProductUOMID" type="preparedStatement" return="String" default="">
carlos@0
    44
    <SqlMethodComment></SqlMethodComment>
carlos@0
    45
    <Sql>
juanpablo@1605
    46
      SELECT M_PRODUCT_UOM_ID FROM M_PRODUCT_UOM WHERE M_PRODUCT_ID = ? AND C_UOM_ID = ?
carlos@0
    47
    </Sql>
carlos@0
    48
    <Parameter name="mProductId"/>
carlos@0
    49
    <Parameter name="cUOMId"/>
carlos@0
    50
  </SqlMethod-->
carlos@0
    51
  <SqlMethod name="hasSecondaryUOM" type="preparedStatement" return="String" default="0">
carlos@0
    52
    <SqlMethodComment></SqlMethodComment>
carlos@0
    53
    <Sql>
carlos@0
    54
      SELECT (CASE COUNT(*) WHEN 0 THEN 0 ELSE 1 END) AS TOTAL 
carlos@0
    55
      FROM M_PRODUCT_UOM 
juanpablo@1605
    56
      WHERE M_PRODUCT_ID = ?
carlos@0
    57
    </Sql>
carlos@0
    58
    <Parameter name="mProductId"/>
carlos@0
    59
  </SqlMethod>
carlos@0
    60
carlos@0
    61
  <SqlMethod name="getOffersPrice" type="preparedStatement" return="String" default="0">
carlos@0
    62
    <SqlMethodComment></SqlMethodComment>
carlos@0
    63
    <Sql>
juanpablo@1605
    64
      SELECT ROUND(M_GET_OFFERS_PRICE(TO_DATE(?),?,?,TO_NUMBER(?), TO_NUMBER(?), ?),
carlos@0
    65
                   (SELECT PRICEPRECISION 
carlos@0
    66
                       FROM C_CURRENCY C,
carlos@0
    67
                            C_ORDER    O 
carlos@0
    68
                      WHERE C.C_CURRENCY_ID = O.C_CURRENCY_ID
juanpablo@1605
    69
                        AND O.C_ORDER_ID    = ?)) AS TOTAL FROM DUAL
carlos@0
    70
    </Sql>
carlos@0
    71
    <Parameter name="dateordered"/>
carlos@0
    72
    <Parameter name="cBpartnerId"/>
carlos@0
    73
    <Parameter name="mProductId"/>
carlos@0
    74
    <Parameter name="pricestd"/>
carlos@0
    75
    <Parameter name="qty"/>
carlos@0
    76
    <Parameter name="pricelist"/>
carlos@0
    77
    <Parameter name="orderid"/>
carlos@0
    78
    
carlos@0
    79
  </SqlMethod>
carlos@0
    80
carlos@0
    81
  <SqlMethod name="getOffersPriceInvoice" type="preparedStatement" return="String" default="0">
carlos@0
    82
    <SqlMethodComment></SqlMethodComment>
carlos@0
    83
    <Sql>
juanpablo@1605
    84
      SELECT ROUND(M_GET_OFFERS_PRICE(TO_DATE(?),?,?,TO_NUMBER(?), TO_NUMBER(?), ?),
carlos@0
    85
                    (SELECT PRICEPRECISION 
carlos@0
    86
                       FROM C_CURRENCY C,
carlos@0
    87
                            C_INVOICE  I 
carlos@0
    88
                      WHERE C.C_CURRENCY_ID = I.C_CURRENCY_ID
juanpablo@1605
    89
                        AND I.C_INVOICE_ID  = ?)) AS TOTAL FROM DUAL
carlos@0
    90
    </Sql>
carlos@0
    91
    <Parameter name="dateordered"/>
carlos@0
    92
    <Parameter name="cBpartnerId"/>
carlos@0
    93
    <Parameter name="mProductId"/>
carlos@0
    94
    <Parameter name="pricestd"/>
carlos@0
    95
    <Parameter name="qty"/>
carlos@0
    96
    <Parameter name="pricelist"/>
carlos@0
    97
    <Parameter name="invoiceid"/>
carlos@0
    98
    
carlos@0
    99
  </SqlMethod>
carlos@0
   100
carlos@0
   101
   <SqlMethod name="getOffersStdPrice" type="preparedStatement" return="String" default="0">
carlos@0
   102
    <SqlMethodComment></SqlMethodComment>
carlos@0
   103
    <Sql>
juanpablo@1605
   104
	  SELECT ROUND(M_GET_OFFERS_STD_PRICE(?,TO_NUMBER(?),?,TO_DATE(?), TO_NUMBER(?), ?),
carlos@0
   105
                    (SELECT PRICEPRECISION 
carlos@0
   106
                       FROM C_CURRENCY C,
carlos@0
   107
                            C_ORDER    O 
carlos@0
   108
                      WHERE C.C_CURRENCY_ID = O.C_CURRENCY_ID
juanpablo@1605
   109
                        AND O.C_ORDER_ID    = ?)) as TOTAL FROM DUAL
carlos@0
   110
	 </Sql>
carlos@0
   111
    <Parameter name="cBpartnerId"/>
rafael@418
   112
	<Parameter name="pricestd"/>
carlos@0
   113
    <Parameter name="mProductId"/>
carlos@0
   114
    <Parameter name="dateordered"/>
carlos@0
   115
    <Parameter name="qty"/>
carlos@0
   116
    <Parameter name="priclist"/>
carlos@0
   117
    <Parameter name="orderid"/>
carlos@0
   118
  </SqlMethod>
rafael@418
   119
  
rafael@418
   120
  <SqlMethod name="getOffersStdPriceInvoice" type="preparedStatement" return="String" default="0">
rafael@418
   121
    <SqlMethodComment></SqlMethodComment>
rafael@418
   122
    <Sql>
juanpablo@1605
   123
	  SELECT ROUND(M_GET_OFFERS_STD_PRICE(?,TO_NUMBER(?),?,TO_DATE(?), TO_NUMBER(?), ?),
rafael@418
   124
                    (SELECT PRICEPRECISION 
rafael@418
   125
                       FROM C_CURRENCY C,
rafael@418
   126
                            C_INVOICE  I 
rafael@418
   127
                      WHERE C.C_CURRENCY_ID = I.C_CURRENCY_ID
juanpablo@1605
   128
                        AND I.C_INVOICE_ID    = ?)) as TOTAL FROM DUAL
rafael@418
   129
	 </Sql>
rafael@418
   130
    <Parameter name="cBpartnerId"/>
rafael@418
   131
	<Parameter name="pricestd"/>
rafael@418
   132
    <Parameter name="mProductId"/>
rafael@418
   133
    <Parameter name="dateordered"/>
rafael@418
   134
    <Parameter name="qty"/>
rafael@418
   135
    <Parameter name="priclist"/>
rafael@418
   136
    <Parameter name="invoiceid"/>
rafael@418
   137
  </SqlMethod>
gorka@1275
   138
  
gorka@1275
   139
  <SqlMethod name="getOrgLocationId" type="preparedStatement" return="String" default="0">
gorka@1275
   140
    <SqlMethodComment></SqlMethodComment>
gorka@1275
   141
    <Sql>
gorka@1275
   142
	  	SELECT C_LOCATION_ID 
gorka@1275
   143
	  		FROM AD_ORGINFO 
gorka@1275
   144
	  		WHERE AD_Client_ID IN ('1') 
gorka@1275
   145
	  		AND AD_Org_ID IN ('1')
gorka@1275
   146
	 </Sql>
gorka@1275
   147
     <Parameter name="adUserClient" type="replace" optional="true" after="AD_Client_ID IN (" text="'1'"/>
gorka@1275
   148
     <Parameter name="adUserOrg" type="replace" optional="true" after="AD_Org_ID IN (" text="'1'"/>
gorka@1275
   149
  </SqlMethod>
david@7921
   150
david@7921
   151
  <SqlMethod name="getWarehouseOrg" type="preparedStatement" return="String">
david@7921
   152
    <SqlMethodComment></SqlMethodComment>
david@7921
   153
    <Sql>
david@7921
   154
	  SELECT AD_ORG_ID
david@7921
   155
      FROM M_WAREHOUSE
david@7921
   156
      WHERE M_WAREHOUSE_ID = ?
david@7921
   157
	 </Sql>
david@7921
   158
    <Parameter name="cWarehouseID"/>
david@7921
   159
  </SqlMethod>
david@7921
   160
david@7921
   161
  <SqlMethod name="getWarehouseOfOrg" type="preparedStatement" return="String">
david@7921
   162
    <SqlMethodComment></SqlMethodComment>
david@7921
   163
    <Sql>
david@7921
   164
      <![CDATA[
david@7921
   165
        select min(w.m_warehouse_id)
david@7921
   166
        from m_warehouse w
david@7921
   167
        where w.ad_client_id=?
david@7921
   168
          and (ad_isorgincluded(?,w.ad_org_id,?)<>-1
david@7921
   169
            or ad_isorgincluded(w.ad_org_id,?,?)<>-1)
david@7921
   170
      ]]>
david@7921
   171
    </Sql>
david@7921
   172
    <Parameter name="adClientId"/>
david@7921
   173
    <Parameter name="adOrgId"/>
david@7921
   174
    <Parameter name="adClientId"/>
david@7921
   175
    <Parameter name="adOrgId"/>
david@7921
   176
    <Parameter name="adClientId"/>
david@7921
   177
  </SqlMethod>
david@7921
   178
carlos@0
   179
</SqlClass>