src/org/openbravo/erpCommon/ad_forms/DocLineLandedCost_data.xsql
author Alvaro Ferraz <alvaro.ferraz@openbravo.com>
Mon, 01 Dec 2014 16:50:51 +0100
changeset 25494 4ae6c365fd3e
parent 24890 5f3d0f2a445a
permissions -rw-r--r--
Fixes bug 28308 LandedCost Accounting should always be created in the same order

In order to help JUnit tests assert landed cost accounting, an orderBy clause has been added when creating Landed Cost Accounting lines, to be created always in the same order
miguel@24845
     1
<?xml version="1.0" encoding="UTF-8" ?>
miguel@24845
     2
<!--
miguel@24890
     3
 *************************************************************************
miguel@24890
     4
 * The contents of this file are subject to the Openbravo  Public  License
miguel@24890
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
miguel@24890
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
miguel@24890
     7
 * file except in compliance with the License. You  may  obtain  a copy of
miguel@24890
     8
 * the License at http://www.openbravo.com/legal/license.html
miguel@24890
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
miguel@24890
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
miguel@24890
    11
 * License for the specific  language  governing  rights  and  limitations
miguel@24890
    12
 * under the License.
miguel@24890
    13
 * The Original Code is Openbravo ERP.
miguel@24890
    14
 * The Initial Developer of the Original Code is Openbravo SLU
miguel@24890
    15
 * All portions are Copyright (C) 2014 Openbravo SLU
miguel@24890
    16
 * All Rights Reserved.
miguel@24890
    17
 * Contributor(s):  ______________________________________.
miguel@24890
    18
 *************************************************************************
miguel@24845
    19
-->
miguel@24845
    20
miguel@24845
    21
miguel@24845
    22
miguel@24845
    23
miguel@24845
    24
miguel@24845
    25
<SqlClass name="DocLineLandedCostData" package="org.openbravo.erpCommon.ad_forms">
miguel@24845
    26
  <SqlClassComment></SqlClassComment>
miguel@24845
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
miguel@24845
    28
    <SqlMethodComment></SqlMethodComment>
miguel@24845
    29
    <Sql>
miguel@24845
    30
      <![CDATA[
miguel@24845
    31
        SELECT LCR.M_LC_RECEIPTLINE_AMT_ID, LCR.AD_CLIENT_ID, LCR.AD_ORG_ID, 
miguel@24879
    32
        LCR.M_LC_COST_ID, LCR.AMOUNT  * COALESCE(A.QUANTITY, IOL.MOVEMENTQTY)/IOL.MOVEMENTQTY AS AMOUNT, 
miguel@24879
    33
        LCC.M_LC_TYPE_ID, L.M_WAREHOUSE_ID, IOL.M_PRODUCT_ID,
miguel@24861
    34
        LCC.C_CURRENCY_ID, '' AS ACCOUNT_ID, '' AS NAME, '' AS GLITEM_DEBIT_ACCT, '' AS GLITEM_CREDIT_ACCT, 
miguel@24879
    35
        A.C_Campaign_ID, COALESCE(A.C_Project_Id, IOL.C_Project_Id) AS C_Project_Id, 
miguel@24879
    36
        COALESCE(A.User1_ID, IOL.User1_ID) AS user1Id, COALESCE(A.User2_ID, IOL.User2_ID) AS user2Id, 
miguel@24879
    37
        COALESCE(A.C_Costcenter_ID, IOL.C_Costcenter_ID) AS C_Costcenter_ID,COALESCE(A.A_Asset_ID,IOL.A_Asset_ID) AS A_Asset_ID, 
miguel@24879
    38
        COALESCE(A.C_BPartner_ID, IOL.C_BPartner_ID) AS C_BPartner_ID, A.C_Activity_ID
miguel@24879
    39
        FROM M_LC_RECEIPTLINE_AMT  LCR, M_INOUT IO, M_LC_COST LCC, M_LOCATOR L, M_INOUTLINE IOL
miguel@24879
    40
           left join M_InOutLine_AcctDimension A ON IOl.M_InOutLine_ID = A.M_InOutLine_ID
miguel@24845
    41
        WHERE LCR.ISACTIVE='Y'
miguel@24845
    42
        AND LCR.M_INOUTLINE_ID = IOL.M_INOUTLINE_ID
miguel@24845
    43
        AND IOL.M_INOUT_ID = IO.M_INOUT_ID
miguel@24845
    44
        AND LCR.M_LC_COST_ID = LCC.M_LC_COST_ID
miguel@24845
    45
        AND IOL.M_LOCATOR_ID = L.M_LOCATOR_ID
miguel@24861
    46
        AND LCR.ISMATCHADJUSTMENT = 'N'
miguel@24846
    47
        AND LCC.M_LANDEDCOST_ID = ?
alvaro@25494
    48
        ORDER BY LCC.LINE, IO.DOCUMENTNO, IO.M_INOUT_ID, IOL.LINE
miguel@24845
    49
     ]]>
miguel@24845
    50
     </Sql>
miguel@24845
    51
     <Parameter name="LC_Receiptline_Amt"/>
miguel@24845
    52
   </SqlMethod>
miguel@24845
    53
   <SqlMethod name="selectLCAccount" type="preparedStatement" return="multiple">
miguel@24845
    54
    <SqlMethodComment></SqlMethodComment>
miguel@24845
    55
    <Sql>
miguel@24845
    56
      <![CDATA[
miguel@24845
    57
        SELECT COALESCE(LCT.ACCOUNT_ID, '') AS ACCOUNT_ID, COALESCE(LCT.M_PRODUCT_ID, '') AS M_PRODUCT_ID, NAME
miguel@24845
    58
        FROM M_LC_TYPE LCT 
miguel@24845
    59
        WHERE LCT.M_LC_TYPE_ID = ?
miguel@24845
    60
     ]]>
miguel@24845
    61
     </Sql>
miguel@24845
    62
     <Parameter name="Lc_Type_Id"/>
miguel@24845
    63
   </SqlMethod>
miguel@24845
    64
   <SqlMethod name="selectGlitem" type="preparedStatement" return="multiple">
miguel@24845
    65
    <SqlMethodComment></SqlMethodComment>
miguel@24845
    66
    <Sql>
miguel@24845
    67
      <![CDATA[
miguel@24845
    68
        SELECT GLITEM_DEBIT_ACCT, GLITEM_CREDIT_ACCT
miguel@24845
    69
        FROM C_GLITEM_ACCT 
miguel@24845
    70
        WHERE C_GLITEM_id = ?
miguel@24845
    71
        AND C_ACCTSCHEMA_id = ?
miguel@24845
    72
     ]]>
miguel@24845
    73
     </Sql>
miguel@24845
    74
     <Parameter name="C_Glitem_ID"/>
miguel@24845
    75
     <Parameter name="C_AcctSchema_ID"/>
miguel@24845
    76
   </SqlMethod>
miguel@24845
    77
   <SqlMethod name="selectLCProduct" type="preparedStatement" return="multiple">
miguel@24845
    78
    <SqlMethodComment></SqlMethodComment>
miguel@24845
    79
    <Sql>
miguel@24845
    80
      <![CDATA[
miguel@24847
    81
        SELECT P_EXPENSE_ACCT AS ACCOUNT_ID
miguel@24845
    82
        FROM M_PRODUCT_ACCT 
miguel@24845
    83
        WHERE M_PRODUCT_ID = ?
miguel@24845
    84
        AND C_ACCTSCHEMA_id = ?
miguel@24845
    85
     ]]>
miguel@24845
    86
     </Sql>
miguel@24845
    87
     <Parameter name="M_Product_ID"/>
miguel@24845
    88
     <Parameter name="C_AcctSchema_ID"/>
miguel@24845
    89
   </SqlMethod>
miguel@24845
    90
 </SqlClass>