src/org/openbravo/erpCommon/ad_forms/ProductInfo_data.xsql
author Iván Perdomo <ivan.perdomo@openbravo.com>
Thu, 24 Apr 2008 17:12:02 +0000
changeset 770 8850d5956eba
parent 423 ecf368072c48
child 799 fef2c5e2feb7
permissions -rw-r--r--
Bug fix [0000111]. Modified mapping name used on manual code.
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   Compiere License  Version 1.1
carlos@0
     5
 * ("License"); You may not use this file except in compliance with the License
carlos@0
     6
 * You may obtain a copy of the License at http://www.compiere.org/license.html
carlos@0
     7
 * Software distributed under the License is distributed on an  "AS IS"  basis,
carlos@0
     8
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
carlos@0
     9
 * the specific language governing rights and limitations under the License.
carlos@0
    10
 * The Original Code is                  Compiere  ERP & CRM  Business Solution
carlos@0
    11
 * The Initial Developer of the Original Code is Jorg Janke  and ComPiere, Inc.
carlos@0
    12
 * Portions created by Jorg Janke are Copyright (C) 1999-2001 Jorg Janke, parts
carlos@0
    13
 * created by ComPiere are Copyright (C) ComPiere, Inc.;   All Rights Reserved.
carlos@0
    14
 * Contributor(s): Openbravo SL
carlos@0
    15
 * Contributions are Copyright (C) 2001-2006 Openbravo S.L.
carlos@0
    16
 ******************************************************************************
carlos@0
    17
-->
carlos@0
    18
carlos@0
    19
carlos@0
    20
carlos@0
    21
carlos@0
    22
carlos@0
    23
<SqlClass name="ProductInfoData" package="org.openbravo.erpCommon.ad_forms">
carlos@0
    24
  <SqlClassComment></SqlClassComment>
carlos@0
    25
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    26
    <SqlMethodComment></SqlMethodComment>
carlos@0
    27
    <Sql>
carlos@0
    28
      <![CDATA[
carlos@0
    29
  SELECT P.PRODUCTTYPE, PC.VALUE,P.C_REVENUERECOGNITION_ID,P.C_UOM_ID,P.AD_CLIENT_ID,P.AD_ORG_ID,
carlos@0
    30
  '' AS REVENUE, '' AS EXPENSE, '' AS ASSET, '' AS COGS, '' AS PURCHASEPRICEVARIANCE, '' AS INVOICEPRICEVARIANCE,
carlos@0
    31
  '' AS DISCOUNTREC, '' AS DISCOUNTGRANT, '' AS CONVERTED, '' AS CURRENTCOSTPRICE, '' AS COSTAVERAGE, '' AS PRICELASTPO,
carlos@0
    32
  '' AS COSTSTANDARD, '' AS C_CURRENCY_ID, '' AS PRICELIST, '' AS PRICESTD, '' AS PRICELIMIT, '' AS PRICEPO
carlos@0
    33
  FROM M_PRODUCT_CATEGORY PC, M_PRODUCT P 
carlos@0
    34
  WHERE PC.M_PRODUCT_CATEGORY_ID=P.M_PRODUCT_CATEGORY_ID
carlos@423
    35
  AND P.M_PRODUCT_ID = TO_NUMBER(?)
carlos@0
    36
     ]]>
carlos@0
    37
     </Sql>
carlos@0
    38
     <Parameter name="Product"/>
carlos@0
    39
   </SqlMethod>
carlos@0
    40
  <SqlMethod name="selectProductAcct" type="preparedStatement" return="multiple">
carlos@0
    41
    <SqlMethodComment></SqlMethodComment>
carlos@0
    42
    <Sql>
carlos@0
    43
      <![CDATA[
carlos@0
    44
  SELECT P_Revenue_Acct as revenue, P_Expense_Acct as expense, P_Asset_Acct as asset, P_Cogs_Acct as cogs, 
carlos@0
    45
  P_PurchasePriceVariance_Acct as purchasepricevariance, P_InvoicePriceVariance_Acct as invoicepricevariance, 
carlos@0
    46
  P_TradeDiscountRec_Acct as discountrec, P_TradeDiscountGrant_Acct as discountgrant
carlos@0
    47
  FROM M_Product_Acct
carlos@423
    48
  WHERE M_Product_ID = TO_NUMBER(?) 
carlos@423
    49
  AND C_AcctSchema_ID = TO_NUMBER(?)
carlos@0
    50
     ]]>
carlos@0
    51
     </Sql>
carlos@0
    52
     <Parameter name="Product"/>
carlos@0
    53
     <Parameter name="AcctSchema"/>
carlos@0
    54
   </SqlMethod>
carlos@0
    55
  <SqlMethod name="selectDefaultAcct" type="preparedStatement" return="multiple">
carlos@0
    56
    <SqlMethodComment></SqlMethodComment>
carlos@0
    57
    <Sql>
carlos@0
    58
      <![CDATA[
carlos@0
    59
  SELECT P_Revenue_Acct as revenue, P_Expense_Acct as expense, P_Asset_Acct as asset, P_Cogs_Acct as cogs, 
carlos@0
    60
  P_PurchasePriceVariance_Acct as purchasepricevariance, P_InvoicePriceVariance_Acct as invoicepricevariance, 
carlos@0
    61
  P_TradeDiscountRec_Acct as discountrec, P_TradeDiscountGrant_Acct as discountgrant
carlos@0
    62
  FROM M_Product_Category pc, M_Product_Category_Acct pca 
carlos@0
    63
  WHERE pc.M_Product_Category_ID=pca.M_Product_Category_ID
carlos@423
    64
  AND pca.C_AcctSchema_ID = TO_NUMBER(?) 
carlos@0
    65
  ORDER BY pc.IsDefault DESC, pc.Created
carlos@0
    66
     ]]>
carlos@0
    67
     </Sql>
carlos@0
    68
     <Parameter name="AcctSchema"/>
carlos@0
    69
   </SqlMethod>
carlos@0
    70
   <SqlMethod name="UOMConvert" type="preparedStatement" return="multiple">
carlos@0
    71
      <SqlMethodComment></SqlMethodComment>
carlos@0
    72
      <Sql>
carlos@0
    73
        select C_UOM_Convert (TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),?) as converted from dual
carlos@0
    74
      </Sql>
carlos@0
    75
      <Parameter name="qty"/>
carlos@0
    76
      <Parameter name="C_UOM_From_ID"/>
carlos@0
    77
      <Parameter name="C_UOM_To_ID"/>
carlos@0
    78
      <Parameter name="StdPrecision"/>
carlos@0
    79
   </SqlMethod>
carlos@0
    80
   <SqlMethod name="selectProductCost" type="preparedStatement" return="multiple">
carlos@0
    81
      <SqlMethodComment></SqlMethodComment>
carlos@0
    82
      <Sql>
carlos@0
    83
  SELECT CURRENTCOSTPRICE,COSTAVERAGE,PRICELASTPO,COSTSTANDARD
carlos@0
    84
  FROM M_PRODUCT_COSTING 
carlos@423
    85
  WHERE M_PRODUCT_ID = TO_NUMBER(?) 
carlos@423
    86
  AND C_ACCTSCHEMA_ID = TO_NUMBER(?)
carlos@0
    87
      </Sql>
carlos@0
    88
      <Parameter name="M_Product_ID"/>
carlos@0
    89
      <Parameter name="C_AcctSchema_ID"/>
carlos@0
    90
   </SqlMethod>
carlos@0
    91
   <SqlMethod name="selectProductAverageCost" type="preparedStatement" return="string">
carlos@0
    92
      <SqlMethodComment></SqlMethodComment>
carlos@0
    93
      <Sql>
carlos@0
    94
        SELECT COALESCE(GET_PRODUCT_COST(TO_NUMBER(?), TO_DATE(?), TO_CHAR(NULL)),0) FROM DUAL
carlos@0
    95
      </Sql>
carlos@0
    96
      <Parameter name="M_Product_ID"/>
carlos@0
    97
      <Parameter name="date"/>
carlos@0
    98
   </SqlMethod>
carlos@0
    99
  <SqlMethod name="insertProductCosting" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   100
    <SqlMethodComment></SqlMethodComment>
carlos@0
   101
    <Sql>
carlos@0
   102
      <![CDATA[
carlos@0
   103
  INSERT INTO M_Product_Costing (M_Product_ID,C_AcctSchema_ID,AD_Client_ID,AD_Org_ID,IsActive,
carlos@0
   104
  Created,CreatedBy,Updated,UpdatedBy,CurrentCostPrice,CostStandard,FutureCostPrice,CostStandardPOQty,
carlos@0
   105
  CostStandardPOAmt,CostStandardCumQty,CostStandardCumAmt,CostAverage,CostAverageCumQty,
carlos@0
   106
  CostAverageCumAmt,PriceLastPO,PriceLastInv, TotalInvQty,TotalInvAmt) 
carlos@0
   107
  VALUES (TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?),'Y',now(),0,
carlos@0
   108
  now(),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
carlos@0
   109
     ]]>
carlos@0
   110
     </Sql>
carlos@0
   111
     <Parameter name="M_Product_ID"/>
carlos@0
   112
     <Parameter name="C_AcctSchema_ID"/>
carlos@0
   113
     <Parameter name="AD_Client_ID"/>
carlos@0
   114
     <Parameter name="AD_Org_ID"/>
carlos@0
   115
   </SqlMethod>
carlos@0
   116
   <SqlMethod name="selectPriceList" type="preparedStatement" return="multiple">
carlos@0
   117
      <SqlMethodComment></SqlMethodComment>
carlos@0
   118
      <Sql>
carlos@0
   119
  SELECT pl.C_Currency_ID, pp.PriceList, pp.PriceStd, pp.PriceLimit 
carlos@0
   120
  FROM M_PriceList pl, M_PriceList_Version plv, M_ProductPrice pp 
carlos@0
   121
  WHERE pl.M_PriceList_ID = plv.M_PriceList_ID
carlos@0
   122
  AND plv.M_PriceList_Version_ID = pp.M_PriceList_Version_ID
carlos@423
   123
  AND pp.M_Product_ID = TO_NUMBER(?)
carlos@0
   124
  AND 1=1
carlos@0
   125
  ORDER BY pl.IsSOPriceList ASC, plv.ValidFrom DESC
carlos@0
   126
      </Sql>
carlos@0
   127
      <Parameter name="M_Product_ID"/>
carlos@0
   128
      <Parameter name="onlyPOPriceList" optional="true" type="none" after="AND 1=1" text=" AND pl.IsSOPriceList='N'"/>
carlos@0
   129
   </SqlMethod>
carlos@0
   130
  <SqlMethod name="updateProductCosting" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   131
    <SqlMethodComment></SqlMethodComment>
carlos@0
   132
    <Sql>
carlos@0
   133
      <![CDATA[
carlos@0
   134
  UPDATE M_Product_Costing SET CurrentCostPrice = TO_NUMBER(?)
carlos@423
   135
  WHERE M_Product_ID = TO_NUMBER(?)
carlos@423
   136
  AND C_AcctSchema_ID = TO_NUMBER(?)
carlos@0
   137
     ]]>
carlos@0
   138
     </Sql>
carlos@0
   139
     <Parameter name="cost"/>
carlos@0
   140
     <Parameter name="M_Product_ID"/>
carlos@0
   141
     <Parameter name="C_AcctSchema_ID"/>
carlos@0
   142
   </SqlMethod>
carlos@0
   143
   <SqlMethod name="selectPOCost" type="preparedStatement" return="multiple">
carlos@0
   144
      <SqlMethodComment></SqlMethodComment>
carlos@0
   145
      <Sql>
carlos@0
   146
  SELECT C_Currency_ID, PriceList,PricePO,PriceLastPO 
carlos@0
   147
  FROM M_Product_PO 
carlos@423
   148
  WHERE M_Product_ID = TO_NUMBER(?) 
carlos@0
   149
  ORDER BY IsCurrentVendor DESC
carlos@0
   150
      </Sql>
carlos@0
   151
      <Parameter name="M_Product_ID"/>
carlos@0
   152
   </SqlMethod>
carlos@0
   153
   </SqlClass>