src/org/openbravo/erpCommon/ad_reports/ReportProductionCost_data.xsql
author Carlos Romero <carlos.romero@openbravo.com>
Tue, 06 Nov 2007 08:33:31 +0000
changeset 0 0247c26f10c6
child 423 ecf368072c48
permissions -rw-r--r--
Modified PostgreSQL post script in order to include C_DEBT_PAYMENT_GENERATE modification tules
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 
carlos@0
    15
 * All portions are Copyright (C) 2001-2006 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="ReportProductionCostData" package="org.openbravo.erpCommon.ad_reports">
carlos@0
    26
  <SqlClassComment></SqlClassComment>
carlos@0
    27
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    28
    <SqlMethodComment></SqlMethodComment>
carlos@0
    29
    <Sql>
carlos@0
    30
      <![CDATA[
carlos@0
    31
      SELECT rp.M_Product_ID AS ID, M_Product.Name, SUM(rp.RealTotalCost)/SUM(rp.ProducedQty) AS RealUnitCost, SUM(rp.ProducedQty*sp.Cost)/SUM(rp.ProducedQty) AS StandarUnitCost, rp.M_Product_ID as identifier, ? as levelid, 'datawarehouseclose' as button_class, '' AS ConsumedPerUnit, '' AS CostPerUnit, '' AS ConsumedPerUnitStd, '' AS CostPerUnitStd, to_number(?)*5 as level_blanck, 40-(to_number(?)*5) as level_name, '' as class_name
carlos@0
    32
      FROM
carlos@0
    33
          (SELECT pl.M_Product_ID, wrp.MA_Sequence_ID, 
carlos@0
    34
          SUM(pl.MovementQty*pl.CalcCost) AS RealTotalCost, SUM(pl.MovementQty) AS 
carlos@0
    35
          ProducedQty
carlos@0
    36
          FROM M_Production p INNER JOIN M_ProductionPlan pp ON (p.M_Production_ID = pp.M_Production_ID)
carlos@0
    37
                              INNER JOIN M_ProductionLine pl ON (pp.M_ProductionPlan_ID = pl.M_ProductionPlan_ID)
carlos@0
    38
                              INNER JOIN MA_WRPhase wrp ON (pp.MA_WRPhase_ID = wrp.MA_WRPhase_ID)
carlos@0
    39
          WHERE p.IsSOTrx = 'N'
carlos@0
    40
          AND p.Processed = 'Y'
carlos@0
    41
          AND pl.ProductionType = '+'
carlos@0
    42
          AND p.MovementDate >= ?
carlos@0
    43
          AND p.MovementDate < ?
carlos@0
    44
          AND 1=1
carlos@0
    45
          GROUP BY pl.M_Product_ID, wrp.MA_Sequence_ID) rp
carlos@0
    46
          LEFT OUTER JOIN MA_SequenceProduct sp ON (rp.M_Product_ID = sp.M_Product_ID AND rp.MA_Sequence_ID = sp.MA_Sequence_ID), M_Product
carlos@0
    47
        WHERE rp.M_Product_ID = M_Product.M_Product_ID
carlos@0
    48
        AND M_Product.IsSold = 'Y'
carlos@0
    49
        GROUP BY rp.M_Product_ID, M_Product.Name
carlos@0
    50
        ]]></Sql>
carlos@0
    51
      <Parameter name="level"/>
carlos@0
    52
      <Parameter name="level"/>
carlos@0
    53
      <Parameter name="level"/>
carlos@0
    54
    <Parameter name="dateFrom"/>
carlos@0
    55
    <Parameter name="dateTo"/>
carlos@0
    56
    <Parameter name="mProductId" optional="true" after="1=1"> AND pl.M_PRODUCT_ID = ?</Parameter>
carlos@0
    57
  </SqlMethod>
carlos@0
    58
  <SqlMethod name="set" type="constant" return="multiple">
carlos@0
    59
      <SqlMethodComment></SqlMethodComment>
carlos@0
    60
      <Sql></Sql>
carlos@0
    61
    </SqlMethod>
carlos@0
    62
  <SqlMethod name="selectMaterial" type="preparedStatement" return="multiple">
carlos@0
    63
     <SqlMethodComment></SqlMethodComment>
carlos@0
    64
     <Sql>
carlos@0
    65
       <![CDATA[ 
carlos@0
    66
       SELECT pr.ID, pr.Name, pr.ConsumedPerUnit, pr.CostPerUnit, pr.ConsumedPerUnitStd, pr.CostPerUnitStd, pr.ConsumedPerUnit*pr.CostPerUnit as RealUnitCost, pr.ConsumedPerUnitStd*pr.CostPerUnitStd as StandarUnitCost, pr.identifier, pr.level_blanck, pr.level_name, pr.button_class, pr.levelid, AD_Ref_List.name as class_name
carlos@0
    67
       FROM (
carlos@0
    68
       SELECT auxSeq.M_Product_ID AS ID, M_Product.Name, 
carlos@0
    69
        SUM(auxSeq.ComponentCost*auxSeq.ConsumedQty)/SUM(auxSeq.ProducedQty) AS ConsumedPerUnit, 
carlos@0
    70
        SUM(auxSeq.TotalRealCost)/SUM(auxSeq.ConsumedQty) AS CostPerUnit,
carlos@0
    71
        SUM(auxSeq.ComponentCost*sp.Quantity)/SUM(sp.Quantity) AS ConsumedPerUnitStd, 
carlos@0
    72
        MAX(sp.Cost) AS CostPerUnitStd, to_char(?)||to_char(auxSeq.M_Product_ID) as identifier, to_char((to_number(?)+1)*5) as level_blanck, to_char(40-((to_number(?)+1)*5)) as level_name, case when M_Product.MA_ProcessPlan_ID is null then '' else 'datawarehouseclose' end as button_class, to_char(to_number(?)+1) as levelid
carlos@0
    73
        FROM 
carlos@0
    74
          (SELECT rpn.M_Product_ID, rpp.ComponentCost, SUM(rpn.ConsumedQty) AS ConsumedQty, 
carlos@0
    75
           SUM(rpp.ProducedQty) AS ProducedQty, SUM(rpn.ConsumedQty*rpn.CalcCost) AS TotalRealCost, rpp.MA_Sequence_ID
carlos@0
    76
           FROM
carlos@0
    77
              (SELECT SUM(pl.MovementQty) AS ProducedQty, MAX(pl.ComponentCost) AS ComponentCost, 
carlos@0
    78
              pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
    79
              FROM M_Production p
carlos@0
    80
                INNER JOIN M_ProductionPlan pp ON (p.M_Production_ID = pp.M_Production_ID)
carlos@0
    81
                INNER JOIN M_ProductionLine pl ON (pp.M_ProductionPlan_ID = pl.M_ProductionPlan_ID)
carlos@0
    82
                INNER JOIN MA_WRPhase wrp ON (pp.MA_WRPhase_ID = wrp.MA_WRPhase_ID)
carlos@0
    83
              WHERE p.IsSOTrx = 'N'
carlos@0
    84
              AND pl.ProductionType = '+'
carlos@0
    85
              AND p.Processed = 'Y'
carlos@0
    86
              AND p.MovementDate >= ?
carlos@0
    87
              AND p.MovementDate < ?
carlos@0
    88
              AND pl.M_Product_ID = ?
carlos@0
    89
              GROUP BY pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
    90
              HAVING SUM(pl.MovementQty) <> 0) rpp 
carlos@0
    91
           LEFT OUTER JOIN
carlos@0
    92
              (SELECT pl.M_Product_ID, SUM(pl.MovementQty) AS ConsumedQty, 
carlos@0
    93
              SUM(pl.MovementQty*pl.CalcCost)/SUM(pl.MovementQty) AS CalcCost, pl.M_ProductionPlan_ID
carlos@0
    94
              FROM M_ProductionLine pl
carlos@0
    95
              WHERE pl.ProductionType = '-'
carlos@0
    96
              GROUP BY pl.M_Product_ID, pl.M_ProductionPlan_ID
carlos@0
    97
              HAVING SUM(pl.MovementQty) <> 0) rpn 
carlos@0
    98
           ON (rpp.M_ProductionPlan_ID = rpn.M_ProductionPlan_ID) 
carlos@0
    99
           GROUP BY rpn.M_Product_ID, rpp.ComponentCost, rpp.MA_Sequence_ID) auxSeq
carlos@0
   100
        LEFT OUTER JOIN MA_SequenceProduct sp 
carlos@0
   101
        ON (auxSeq.M_Product_ID = sp.M_Product_ID AND auxSeq.MA_Sequence_ID = sp.MA_Sequence_ID), M_Product
carlos@0
   102
        WHERE auxSeq.M_Product_ID = M_Product.M_Product_ID
carlos@0
   103
        GROUP BY auxSeq.M_Product_ID, M_Product.Name, M_Product.MA_ProcessPlan_ID) pr, AD_Ref_List
carlos@0
   104
        WHERE pr.levelid = AD_Ref_List.value
carlos@0
   105
        AND Ad_Ref_List.AD_Reference_ID = 800097
carlos@0
   106
        ]]></Sql>
carlos@0
   107
      <Parameter name="id"/>
carlos@0
   108
      <Parameter name="level"/>
carlos@0
   109
     <Parameter name="level"/>
carlos@0
   110
     <Parameter name="level"/>
carlos@0
   111
     <Parameter name="dateFrom"/>
carlos@0
   112
     <Parameter name="dateTo"/>
carlos@0
   113
     <Parameter name="mProductId"/>
carlos@0
   114
   </SqlMethod>
carlos@0
   115
carlos@0
   116
   <SqlMethod name="selectMachine" type="preparedStatement" return="multiple">
carlos@0
   117
     <SqlMethodComment></SqlMethodComment>
carlos@0
   118
     <Sql>
carlos@0
   119
       <![CDATA[ 
carlos@0
   120
       SELECT pr.ID, pr.name, pr.CostPerUnit, pr.CostPerUnitStd, pr.level_blanck, pr.level_name, AD_Ref_List.Name as class_name, pr.levelid
carlos@0
   121
       FROM (
carlos@0
   122
       SELECT auxSeq.MA_Machine_ID AS ID, MA_Machine.name,  
carlos@0
   123
        SUM(auxSeq.TotalRealCost*auxSeq.ComponentCost)/SUM(auxSeq.ProducedQty) AS CostPerUnit,
carlos@0
   124
        SUM(sm.CalcCost*auxSeq.ComponentCost*auxSeq.ProducedQty)/SUM(auxSeq.ProducedQty) AS CostPerUnitStd, to_char((to_number(?)+1)*5) as level_blanck, to_char(40-((to_number(?)+1)*5)) as level_name, to_char(to_number(?)+1) as levelid
carlos@0
   125
        FROM 
carlos@0
   126
          (SELECT rmc.MA_Machine_ID, rpp.ComponentCost, SUM(rpp.ProducedQty) AS ProducedQty,
carlos@0
   127
           SUM(rmc.CalcCost) AS TotalRealCost, rpp.MA_Sequence_ID
carlos@0
   128
           FROM
carlos@0
   129
              (SELECT SUM(pl.MovementQty) AS ProducedQty, MAX(pl.ComponentCost) AS ComponentCost, 
carlos@0
   130
              pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
   131
              FROM M_Production p
carlos@0
   132
                INNER JOIN M_ProductionPlan pp ON (p.M_Production_ID = pp.M_Production_ID)
carlos@0
   133
                INNER JOIN M_ProductionLine pl ON (pp.M_ProductionPlan_ID = pl.M_ProductionPlan_ID)
carlos@0
   134
                INNER JOIN MA_WRPhase wrp ON (pp.MA_WRPhase_ID = wrp.MA_WRPhase_ID)
carlos@0
   135
              WHERE p.IsSOTrx = 'N'
carlos@0
   136
              AND pl.ProductionType = '+'
carlos@0
   137
              AND p.Processed = 'Y'
carlos@0
   138
              AND p.MovementDate >= ?
carlos@0
   139
              AND p.MovementDate < ?
carlos@0
   140
              AND pl.M_Product_ID = ?
carlos@0
   141
              GROUP BY pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
   142
              HAVING SUM(pl.MovementQty) <> 0) rpp 
carlos@0
   143
           LEFT OUTER JOIN
carlos@0
   144
              (SELECT plm.MA_Machine_ID, plm.CalcCost, plm.M_ProductionPlan_ID
carlos@0
   145
              FROM MA_Pl_Machine plm) rmc
carlos@0
   146
           ON (rpp.M_ProductionPlan_ID = rmc.M_ProductionPlan_ID) 
carlos@0
   147
           GROUP BY rmc.MA_Machine_ID, rpp.ComponentCost, rpp.MA_Sequence_ID
carlos@0
   148
           HAVING rmc.MA_Machine_ID IS NOT NULL) auxSeq
carlos@0
   149
        LEFT OUTER JOIN MA_Sequence_Machine sm 
carlos@0
   150
        ON (auxSeq.MA_Machine_ID = sm.MA_Machine_ID AND auxSeq.MA_Sequence_ID = sm.MA_Sequence_ID), MA_Machine
carlos@0
   151
        WHERE auxSeq.MA_Machine_ID = MA_Machine.MA_Machine_ID
carlos@0
   152
        GROUP BY auxSeq.MA_Machine_ID, MA_Machine.name) pr, AD_Ref_List
carlos@0
   153
        WHERE pr.levelid = AD_Ref_List.value
carlos@0
   154
        AND AD_Ref_List.AD_Reference_ID = 800097
carlos@0
   155
        ]]></Sql>
carlos@0
   156
     <Parameter name="level"/>
carlos@0
   157
     <Parameter name="level"/>
carlos@0
   158
     <Parameter name="level"/>
carlos@0
   159
     <Parameter name="dateFrom"/>
carlos@0
   160
     <Parameter name="dateTo"/>
carlos@0
   161
     <Parameter name="mProductId"/>
carlos@0
   162
   </SqlMethod>
carlos@0
   163
carlos@0
   164
  <SqlMethod name="selectIndirect" type="preparedStatement" return="multiple">
carlos@0
   165
     <SqlMethodComment></SqlMethodComment>
carlos@0
   166
     <Sql>
carlos@0
   167
       <![CDATA[ 
carlos@0
   168
       SELECT pr.ID, pr.name, pr.CostPerUnit, pr.CostPerUnitStd, pr.level_blanck, pr.level_name, AD_Ref_List.Name as class_name, pr.levelid
carlos@0
   169
       FROM (
carlos@0
   170
       SELECT auxSeq.MA_Indirect_Cost_ID AS ID, MA_Indirect_Cost.name,  
carlos@0
   171
        SUM(auxSeq.TotalRealCost*auxSeq.ComponentCost)/SUM(auxSeq.ProducedQty) AS CostPerUnit,
carlos@0
   172
        SUM(si.CalcCost*auxSeq.ComponentCost*auxSeq.ProducedQty)/SUM(auxSeq.ProducedQty) AS CostPerUnitStd, to_char((to_number(?)+1)*5) as level_blanck, to_char(40-((to_number(?)+1)*5)) as level_name, to_char(to_number(?)+1) as levelid
carlos@0
   173
        FROM 
carlos@0
   174
          (SELECT rmi.MA_Indirect_Cost_ID, rpp.ComponentCost, SUM(rpp.ProducedQty) AS ProducedQty,
carlos@0
   175
           SUM(rmi.CalcCost) AS TotalRealCost, rpp.MA_Sequence_ID
carlos@0
   176
           FROM
carlos@0
   177
              (SELECT SUM(pl.MovementQty) AS ProducedQty, MAX(pl.ComponentCost) AS ComponentCost, 
carlos@0
   178
              pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
   179
              FROM M_Production p
carlos@0
   180
                INNER JOIN M_ProductionPlan pp ON (p.M_Production_ID = pp.M_Production_ID)
carlos@0
   181
                INNER JOIN M_ProductionLine pl ON (pp.M_ProductionPlan_ID = pl.M_ProductionPlan_ID)
carlos@0
   182
                INNER JOIN MA_WRPhase wrp ON (pp.MA_WRPhase_ID = wrp.MA_WRPhase_ID)
carlos@0
   183
              WHERE p.IsSOTrx = 'N'
carlos@0
   184
              AND pl.ProductionType = '+'
carlos@0
   185
              AND p.Processed = 'Y'
carlos@0
   186
              AND p.MovementDate >= ?
carlos@0
   187
              AND p.MovementDate < ?
carlos@0
   188
              AND pl.M_Product_ID = ?
carlos@0
   189
              GROUP BY pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
   190
              HAVING SUM(pl.MovementQty) <> 0) rpp 
carlos@0
   191
           LEFT OUTER JOIN
carlos@0
   192
              (SELECT pli.MA_Indirect_Cost_ID, pli.CalcCost, pli.M_ProductionPlan_ID
carlos@0
   193
              FROM MA_Pl_Ic pli) rmi
carlos@0
   194
           ON (rpp.M_ProductionPlan_ID = rmi.M_ProductionPlan_ID) 
carlos@0
   195
           GROUP BY rmi.MA_Indirect_Cost_ID, rpp.ComponentCost, rpp.MA_Sequence_ID
carlos@0
   196
           HAVING rmi.MA_Indirect_Cost_ID IS NOT NULL) auxSeq
carlos@0
   197
        LEFT OUTER JOIN MA_Sequence_IC si
carlos@0
   198
        ON (auxSeq.MA_Indirect_Cost_ID = si.MA_Indirect_Cost_ID AND auxSeq.MA_Sequence_ID = si.MA_Sequence_ID), MA_Indirect_Cost
carlos@0
   199
        WHERE auxSeq.MA_Indirect_Cost_ID = MA_Indirect_Cost.MA_Indirect_Cost_ID
carlos@0
   200
        GROUP BY auxSeq.MA_Indirect_Cost_ID, MA_Indirect_Cost.name) pr, AD_Ref_List
carlos@0
   201
        WHERE pr.levelid = AD_Ref_List.value
carlos@0
   202
        AND AD_Ref_List.AD_Reference_ID = 800097
carlos@0
   203
        ]]></Sql>
carlos@0
   204
     <Parameter name="level"/>
carlos@0
   205
     <Parameter name="level"/>
carlos@0
   206
     <Parameter name="level"/>
carlos@0
   207
     <Parameter name="dateFrom"/>
carlos@0
   208
     <Parameter name="dateTo"/>
carlos@0
   209
     <Parameter name="mProductId"/>
carlos@0
   210
   </SqlMethod>
carlos@0
   211
carlos@0
   212
   <SqlMethod name="selectEmployee" type="preparedStatement" return="multiple">
carlos@0
   213
     <SqlMethodComment></SqlMethodComment>
carlos@0
   214
     <Sql>
carlos@0
   215
       <![CDATA[ 
carlos@0
   216
       SELECT pr.ID, pr.name, pr.CostPerUnit, pr.CostPerUnitStd, pr.level_blanck, pr.level_name, AD_Ref_List.Name as class_name, pr.levelid
carlos@0
   217
       FROM (
carlos@0
   218
       SELECT auxSeq.C_Salary_Category_ID AS ID, C_Salary_Category.name,
carlos@0
   219
        SUM(auxSeq.TotalRealCost*auxSeq.ComponentCost)/SUM(auxSeq.ProducedQty) AS CostPerUnit,
carlos@0
   220
        SUM(se.CalcCost*auxSeq.ComponentCost*auxSeq.ProducedQty)/SUM(auxSeq.ProducedQty) AS CostPerUnitStd, to_char((to_number(?)+1)*5) as level_blanck, to_char(40-((to_number(?)+1)*5)) as level_name, to_char(to_number(?)+1) as levelid
carlos@0
   221
        FROM 
carlos@0
   222
          (SELECT rme.C_Salary_Category_ID, rpp.ComponentCost, SUM(rpp.ProducedQty) AS ProducedQty,
carlos@0
   223
           SUM(rme.CalcCost) AS TotalRealCost, rpp.MA_Sequence_ID
carlos@0
   224
           FROM
carlos@0
   225
              (SELECT SUM(pl.MovementQty) AS ProducedQty, MAX(pl.ComponentCost) AS ComponentCost, 
carlos@0
   226
              pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
   227
              FROM M_Production p
carlos@0
   228
                INNER JOIN M_ProductionPlan pp ON (p.M_Production_ID = pp.M_Production_ID)
carlos@0
   229
                INNER JOIN M_ProductionLine pl ON (pp.M_ProductionPlan_ID = pl.M_ProductionPlan_ID)
carlos@0
   230
                INNER JOIN MA_WRPhase wrp ON (pp.MA_WRPhase_ID = wrp.MA_WRPhase_ID)
carlos@0
   231
              WHERE p.IsSOTrx = 'N'
carlos@0
   232
              AND pl.ProductionType = '+'
carlos@0
   233
              AND p.Processed = 'Y'
carlos@0
   234
              AND p.MovementDate >= ?
carlos@0
   235
              AND p.MovementDate < ?
carlos@0
   236
              AND pl.M_Product_ID = ?
carlos@0
   237
              GROUP BY pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID
carlos@0
   238
              HAVING SUM(pl.MovementQty) <> 0) rpp 
carlos@0
   239
           LEFT OUTER JOIN
carlos@0
   240
              (SELECT ple.C_Salary_Category_ID, ple.CalcCost, ple.M_ProductionPlan_ID
carlos@0
   241
              FROM MA_Pl_Employee ple) rme
carlos@0
   242
           ON (rpp.M_ProductionPlan_ID = rme.M_ProductionPlan_ID) 
carlos@0
   243
           GROUP BY rme.C_Salary_Category_ID, rpp.ComponentCost, rpp.MA_Sequence_ID
carlos@0
   244
           HAVING rme.C_Salary_Category_ID IS NOT NULL) auxSeq
carlos@0
   245
        LEFT OUTER JOIN MA_Sequence_Employee se
carlos@0
   246
        ON (auxSeq.C_Salary_Category_ID = se.C_Salary_Category_ID AND auxSeq.MA_Sequence_ID = se.MA_Sequence_ID), C_Salary_Category
carlos@0
   247
        WHERE auxSeq.C_Salary_Category_ID = C_Salary_Category.C_Salary_Category_ID
carlos@0
   248
        GROUP BY auxSeq.C_Salary_Category_ID, C_Salary_Category.name) pr, AD_Ref_List
carlos@0
   249
        WHERE pr.levelid =AD_Ref_List.value
carlos@0
   250
        AND AD_Ref_List.AD_Reference_ID = 800097
carlos@0
   251
        ]]></Sql>
carlos@0
   252
     <Parameter name="level"/>
carlos@0
   253
     <Parameter name="level"/>
carlos@0
   254
     <Parameter name="level"/>
carlos@0
   255
     <Parameter name="dateFrom"/>
carlos@0
   256
     <Parameter name="dateTo"/>
carlos@0
   257
     <Parameter name="mProductId"/>
carlos@0
   258
   </SqlMethod>
carlos@0
   259
carlos@0
   260
   <SqlMethod name="selectCostCenter" type="preparedStatement" return="multiple">
carlos@0
   261
     <SqlMethodComment></SqlMethodComment>
carlos@0
   262
     <Sql>
carlos@0
   263
       <![CDATA[ 
carlos@0
   264
       SELECT pr.ID, pr.name, pr.CostPerUnit, pr.CostPerUnitStd, pr.level_blanck, pr.level_name, AD_Ref_List.Name as class_name, pr.levelid
carlos@0
   265
       FROM (
carlos@0
   266
       SELECT auxSeq.MA_CostCenter_Version_ID AS ID, MA_CostCenter.name,
carlos@0
   267
        SUM(auxSeq.TotalRealCost*auxSeq.ComponentCost)/SUM(auxSeq.ProducedQty) AS CostPerUnit,
carlos@0
   268
        SUM(se.CostCenterCost*auxSeq.ComponentCost*auxSeq.ProducedQty)/SUM(auxSeq.ProducedQty) AS CostPerUnitStd, to_char((to_number(?)+1)*5) as level_blanck, to_char(40-((to_number(?)+1)*5)) as level_name, to_char(to_number(?)+1) as levelid
carlos@0
   269
        FROM 
carlos@0
   270
          (SELECT rpp.MA_CostCenter_Version_ID, rpp.ComponentCost, SUM(rpp.ProducedQty) AS ProducedQty,
carlos@0
   271
           SUM(rpp.CalcCost) AS TotalRealCost, rpp.MA_Sequence_ID
carlos@0
   272
           FROM
carlos@0
   273
              (SELECT SUM(pl.MovementQty) AS ProducedQty, MAX(pl.ComponentCost) AS ComponentCost, 
carlos@0
   274
              pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID, pp.MA_CostCenter_Version_ID, pp.CalcCost
carlos@0
   275
              FROM M_Production p
carlos@0
   276
                INNER JOIN M_ProductionPlan pp ON (p.M_Production_ID = pp.M_Production_ID)
carlos@0
   277
                INNER JOIN M_ProductionLine pl ON (pp.M_ProductionPlan_ID = pl.M_ProductionPlan_ID)
carlos@0
   278
                INNER JOIN MA_WRPhase wrp ON (pp.MA_WRPhase_ID = wrp.MA_WRPhase_ID)
carlos@0
   279
              WHERE p.IsSOTrx = 'N'
carlos@0
   280
              AND pl.ProductionType = '+'
carlos@0
   281
              AND p.Processed = 'Y'
carlos@0
   282
              AND p.MovementDate >= ?
carlos@0
   283
              AND p.MovementDate < ?
carlos@0
   284
              AND pl.M_Product_ID = ?
carlos@0
   285
              GROUP BY pp.M_ProductionPlan_ID, wrp.MA_Sequence_ID, pp.MA_CostCenter_Version_ID, pp.CalcCost
carlos@0
   286
              HAVING SUM(pl.MovementQty) <> 0) rpp 
carlos@0
   287
           GROUP BY rpp.MA_CostCenter_Version_ID, rpp.ComponentCost, rpp.MA_Sequence_ID
carlos@0
   288
           HAVING rpp.MA_CostCenter_Version_ID IS NOT NULL) auxSeq
carlos@0
   289
        LEFT OUTER JOIN MA_Sequence se
carlos@0
   290
        ON (auxSeq.MA_Sequence_ID = se.MA_Sequence_ID), MA_CostCenter_Version, MA_CostCenter
carlos@0
   291
        WHERE auxSeq.MA_CostCenter_version_ID = MA_CostCenter_version.MA_CostCenter_Version_ID
carlos@0
   292
          AND MA_CostCenter_Version.MA_CostCenter_ID = MA_CostCenter.MA_CostCenter_ID
carlos@0
   293
        GROUP BY auxSeq.MA_CostCenter_Version_ID, MA_CostCenter.name) pr, AD_Ref_List
carlos@0
   294
        WHERE pr.levelid =AD_Ref_List.value
carlos@0
   295
        AND AD_Ref_List.AD_Reference_ID = 800097
carlos@0
   296
        ]]></Sql>
carlos@0
   297
     <Parameter name="level"/>
carlos@0
   298
     <Parameter name="level"/>
carlos@0
   299
     <Parameter name="level"/>
carlos@0
   300
     <Parameter name="dateFrom"/>
carlos@0
   301
     <Parameter name="dateTo"/>
carlos@0
   302
     <Parameter name="mProductId"/>
carlos@0
   303
   </SqlMethod>
carlos@0
   304
  <SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
carlos@0
   305
    <SqlMethodComment></SqlMethodComment>
carlos@0
   306
    <Sql>
carlos@0
   307
    SELECT max(NAME) as name FROM C_BPARTNER 
carlos@0
   308
    WHERE C_BPARTNER_ID = ?
carlos@0
   309
    </Sql>
carlos@0
   310
    <Parameter name="cBpartnerId"/>
carlos@0
   311
  </SqlMethod>
carlos@0
   312
carlos@0
   313
  <SqlMethod name="mProductDescription" type="preparedStatement" return="String" default="">
carlos@0
   314
     <SqlMethodComment></SqlMethodComment>
carlos@0
   315
     <Sql>
carlos@0
   316
     SELECT MAX(NAME) AS NAME FROM M_PRODUCT WHERE M_PRODUCT_ID = ?
carlos@0
   317
     </Sql>
carlos@0
   318
     <Parameter name="cBpartnerId"/>
carlos@0
   319
   </SqlMethod>
carlos@0
   320
</SqlClass>