src/org/openbravo/erpCommon/ad_process/ImportTax_data.xsql
author Asier Lostalé <asier.lostale@openbravo.com>
Mon, 05 May 2008 06:59:24 +0000
changeset 799 fef2c5e2feb7
parent 649 9ad1f1fc6c02
child 886 7e955b2f69df
permissions -rw-r--r--
Merged cleanup branch (r3931) with trunk
carlos@0
     1
<?xml version="1.0" encoding="UTF-8" ?>
carlos@0
     2
<!--
asier@799
     3
 *************************************************************************
asier@799
     4
 * The contents of this file are subject to the Openbravo  Public  License
asier@799
     5
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
asier@799
     6
 * Version 1.1  with a permitted attribution clause; you may not  use this
asier@799
     7
 * file except in compliance with the License. You  may  obtain  a copy of
asier@799
     8
 * the License at http://www.openbravo.com/legal/license.html 
asier@799
     9
 * Software distributed under the License  is  distributed  on  an "AS IS"
asier@799
    10
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
asier@799
    11
 * License for the specific  language  governing  rights  and  limitations
asier@799
    12
 * under the License. 
asier@799
    13
 * The Original Code is Openbravo ERP. 
asier@799
    14
 * The Initial Developer of the Original Code is Openbravo SL 
asier@799
    15
 * All portions are Copyright (C) 2001-2008 Openbravo SL 
asier@799
    16
 * All Rights Reserved. 
asier@799
    17
 * Contributor(s):  ______________________________________.
asier@799
    18
 ************************************************************************
carlos@0
    19
-->
carlos@0
    20
<SqlClass name="ImportTaxData" package="org.openbravo.erpCommon.ad_process">
carlos@0
    21
carlos@0
    22
  <SqlMethod name="select" type="preparedStatement" return="multiple">
carlos@0
    23
    <SqlMethodComment></SqlMethodComment>
carlos@0
    24
    <Sql>
carlos@0
    25
      <![CDATA[
carlos@0
    26
        SELECT I_Tax_ID, C_Tax_ID, T_Name, C_TaxCategory_ID, TC_Name, C_BP_TaxCategory_ID, BPTC_Name,
carlos@0
    27
               parent_tax_id, parent_Name, C_Country_ID, C_Region_ID, TO_Country_Id, TO_Region_ID, 
carlos@0
    28
               '' as n
carlos@0
    29
			  FROM I_Tax
carlos@0
    30
			  WHERE I_IsImported='N'
carlos@423
    31
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
    32
      ]]>
carlos@0
    33
    </Sql>
carlos@0
    34
    <Parameter name="ad_client_id"/>
carlos@0
    35
  </SqlMethod>
carlos@0
    36
  
carlos@0
    37
    <SqlMethod name="selectTaxCategoryId" type="preparedStatement" return="string">
carlos@0
    38
    <SqlMethodComment></SqlMethodComment>
carlos@0
    39
    <Sql>
carlos@0
    40
      <![CDATA[
carlos@0
    41
        SELECT C_TaxCategory_ID
carlos@0
    42
			  FROM C_TaxCategory
carlos@0
    43
			  WHERE Name = ?
carlos@423
    44
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
    45
      ]]>
carlos@0
    46
    </Sql>
carlos@0
    47
    <Parameter name="name"/>
carlos@0
    48
    <Parameter name="ad_client_id"/>
carlos@0
    49
  </SqlMethod>
carlos@0
    50
carlos@0
    51
    <SqlMethod name="selectBPTaxCategoryId" type="preparedStatement" return="string">
carlos@0
    52
    <SqlMethodComment></SqlMethodComment>
carlos@0
    53
    <Sql>
carlos@0
    54
      <![CDATA[
carlos@0
    55
        SELECT C_BP_TaxCategory_ID
carlos@0
    56
			  FROM C_BP_TaxCategory
carlos@0
    57
			  WHERE Name = ?
carlos@423
    58
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
    59
      ]]>
carlos@0
    60
    </Sql>
carlos@0
    61
    <Parameter name="name"/>
carlos@0
    62
    <Parameter name="ad_client_id"/>
carlos@0
    63
  </SqlMethod> 
carlos@0
    64
  
carlos@0
    65
      <SqlMethod name="selectTaxId" type="preparedStatement" return="string">
carlos@0
    66
    <SqlMethodComment></SqlMethodComment>
carlos@0
    67
    <Sql>
carlos@0
    68
      <![CDATA[
carlos@0
    69
        SELECT C_Tax_ID
carlos@0
    70
			  FROM C_Tax
carlos@0
    71
			  WHERE Name = ?
carlos@423
    72
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
    73
      ]]>
carlos@0
    74
    </Sql>
carlos@0
    75
    <Parameter name="name"/>
carlos@0
    76
    <Parameter name="ad_client_id"/>
carlos@0
    77
  </SqlMethod>
carlos@0
    78
  
carlos@0
    79
    
carlos@0
    80
  <SqlMethod name="deleteOld" type="preparedStatement" connection="true" return="rowCount">
carlos@0
    81
    <SqlMethodComment></SqlMethodComment>
carlos@0
    82
    <Sql>
carlos@0
    83
      <![CDATA[
carlos@0
    84
      DELETE FROM I_Tax WHERE I_IsImported='Y'
carlos@423
    85
      AND AD_Client_ID = TO_NUMBER(?)
carlos@0
    86
      ]]>
carlos@0
    87
    </Sql>
carlos@0
    88
    <Parameter name="ad_client_id"/>
carlos@0
    89
  </SqlMethod>
carlos@0
    90
  
carlos@0
    91
  <SqlMethod name="updateRecords" type="preparedStatement" connection="true" return="rowCount">
carlos@0
    92
    <SqlMethodComment></SqlMethodComment>
carlos@0
    93
    <Sql>
carlos@0
    94
      <![CDATA[
carlos@0
    95
        UPDATE I_Tax 
carlos@0
    96
        SET AD_Client_ID = COALESCE(AD_Client_ID, TO_NUMBER(?)),
carlos@0
    97
        AD_Org_ID = COALESCE(AD_Org_ID, 0),
carlos@0
    98
        IsActive = COALESCE(IsActive, 'Y'),
carlos@0
    99
        Created = COALESCE(Created, now()),
carlos@0
   100
        CreatedBy = COALESCE(CreatedBy, 0),
carlos@0
   101
        Updated = COALESCE(Updated, now()),
carlos@0
   102
        UpdatedBy = COALESCE(UpdatedBy, 0),
carlos@0
   103
        I_ErrorMsg = NULL,
carlos@0
   104
        I_IsImported = 'N' 
carlos@0
   105
        WHERE I_IsImported<>'Y' OR I_IsImported IS NULL
carlos@0
   106
      ]]>
carlos@0
   107
    </Sql>
carlos@0
   108
    <Parameter name="ad_client_id"/>
carlos@0
   109
  </SqlMethod>
carlos@0
   110
  
carlos@0
   111
  <SqlMethod name="updateTaxCategoryId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   112
    <SqlMethodComment></SqlMethodComment>
carlos@0
   113
    <Sql>
carlos@0
   114
      <![CDATA[
carlos@649
   115
        UPDATE I_Tax
carlos@0
   116
			  SET C_TaxCategory_ID=(
carlos@0
   117
          SELECT C_TaxCategory_ID 
carlos@0
   118
          FROM C_TaxCategory C
carlos@649
   119
			    WHERE I_Tax.TC_Name=C.Name
carlos@649
   120
          AND C.AD_Client_ID=I_Tax.AD_Client_ID
carlos@0
   121
        )
carlos@0
   122
			  WHERE C_TaxCategory_ID IS NULL
carlos@0
   123
        AND TC_Name IS NOT NULL
carlos@0
   124
			  AND I_IsImported='N'
carlos@423
   125
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   126
      ]]>
carlos@0
   127
    </Sql>
carlos@0
   128
    <Parameter name="ad_client_id"/>
carlos@0
   129
  </SqlMethod>
carlos@0
   130
carlos@0
   131
  <SqlMethod name="updateBPTaxCategoryId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   132
    <SqlMethodComment></SqlMethodComment>
carlos@0
   133
    <Sql>
carlos@0
   134
      <![CDATA[
carlos@649
   135
        UPDATE I_Tax
carlos@0
   136
			  SET C_BP_TaxCategory_ID=(
carlos@0
   137
          SELECT C_BP_TaxCategory_ID 
carlos@0
   138
          FROM C_BP_TaxCategory C
carlos@649
   139
			    WHERE I_Tax.BPTC_Name=C.Name
carlos@649
   140
          AND C.AD_Client_ID=I_Tax.AD_Client_ID
carlos@0
   141
        )
carlos@0
   142
			  WHERE C_BP_TaxCategory_ID IS NULL
carlos@0
   143
        AND BPTC_Name IS NOT NULL
carlos@0
   144
			  AND I_IsImported='N'
carlos@423
   145
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   146
      ]]>
carlos@0
   147
    </Sql>
carlos@0
   148
    <Parameter name="ad_client_id"/>
carlos@0
   149
  </SqlMethod>
carlos@0
   150
carlos@0
   151
  <SqlMethod name="updateTaxId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   152
    <SqlMethodComment></SqlMethodComment>
carlos@0
   153
    <Sql>
carlos@0
   154
      <![CDATA[
carlos@649
   155
        UPDATE I_Tax
carlos@0
   156
			  SET C_Tax_ID=(
carlos@0
   157
          SELECT C_Tax_ID 
carlos@0
   158
          FROM C_Tax C
carlos@649
   159
			    WHERE I_Tax.T_Name=C.Name
carlos@649
   160
          AND C.AD_Client_ID=I_Tax.AD_Client_ID
carlos@0
   161
        )
carlos@0
   162
			  WHERE C_Tax_ID IS NULL
carlos@0
   163
        AND T_Name IS NOT NULL
carlos@0
   164
			  AND I_IsImported='N'
carlos@423
   165
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   166
      ]]>
carlos@0
   167
    </Sql>
carlos@0
   168
    <Parameter name="ad_client_id"/>
carlos@0
   169
  </SqlMethod>  
carlos@0
   170
  
carlos@0
   171
  <SqlMethod name="updateCountryFromId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   172
    <SqlMethodComment></SqlMethodComment>
carlos@0
   173
    <Sql>
carlos@0
   174
      <![CDATA[
carlos@649
   175
        UPDATE I_Tax
carlos@0
   176
			  SET C_Country_ID=(
carlos@0
   177
          SELECT MAX(C_Country_ID) 
carlos@0
   178
            FROM C_Country c
carlos@649
   179
  			   WHERE c.AD_Client_ID IN (0, I_Tax.AD_Client_ID)
carlos@649
   180
             AND  (UPPER(c.CountryCode) = UPPER(I_Tax.from_code_Country)
carlos@649
   181
               OR  UPPER(c.Name) = UPPER(I_Tax.from_code_Country)))
carlos@0
   182
			  WHERE C_Country_ID IS NULL
carlos@0
   183
        AND from_code_Country IS NOT NULL
carlos@0
   184
			  AND I_IsImported<>'Y'
carlos@423
   185
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   186
      ]]>
carlos@0
   187
    </Sql>
carlos@0
   188
    <Parameter name="ad_client_id"/>
carlos@0
   189
  </SqlMethod>
carlos@0
   190
  
carlos@0
   191
  <SqlMethod name="updateCountryFromError" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   192
    <SqlMethodComment></SqlMethodComment>
carlos@0
   193
    <Sql>
carlos@0
   194
      <![CDATA[
carlos@0
   195
        UPDATE I_Tax
carlos@0
   196
			  SET I_IsImported='E', I_ErrorMsg=COALESCE(TO_CHAR(I_ErrorMsg),'')||'ERR=Invalid Country From, '
carlos@0
   197
			  WHERE C_Country_ID IS NULL
carlos@0
   198
          AND from_code_Country IS NOT NULL
carlos@0
   199
  			  AND I_IsImported<>'Y'
carlos@423
   200
          AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   201
      ]]>
carlos@0
   202
    </Sql>
carlos@0
   203
    <Parameter name="ad_client_id"/>
carlos@0
   204
  </SqlMethod>
carlos@0
   205
  
carlos@0
   206
    <SqlMethod name="updateCountryToId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   207
    <SqlMethodComment></SqlMethodComment>
carlos@0
   208
    <Sql>
carlos@0
   209
      <![CDATA[
carlos@649
   210
        UPDATE I_Tax
carlos@0
   211
			  SET TO_Country_ID=(
carlos@0
   212
          SELECT MAX(C_Country_ID) 
carlos@0
   213
            FROM C_Country c
carlos@649
   214
  			   WHERE c.AD_Client_ID IN (0, I_Tax.AD_Client_ID)
carlos@649
   215
             AND  (UPPER(c.CountryCode) = UPPER(I_Tax.to_code_Country)
carlos@649
   216
               OR  UPPER(c.Name) = UPPER(I_Tax.to_code_Country)))
carlos@0
   217
			  WHERE TO_Country_ID IS NULL
carlos@0
   218
        AND to_code_Country IS NOT NULL
carlos@0
   219
			  AND I_IsImported<>'Y'
carlos@423
   220
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   221
      ]]>
carlos@0
   222
    </Sql>
carlos@0
   223
    <Parameter name="ad_client_id"/>
carlos@0
   224
  </SqlMethod>
carlos@0
   225
  
carlos@0
   226
  <SqlMethod name="updateCountryToError" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   227
    <SqlMethodComment></SqlMethodComment>
carlos@0
   228
    <Sql>
carlos@0
   229
      <![CDATA[
carlos@0
   230
        UPDATE I_Tax
carlos@0
   231
			  SET I_IsImported='E', I_ErrorMsg=COALESCE(TO_CHAR(I_ErrorMsg),'')||'ERR=Invalid Country To, '
carlos@0
   232
			  WHERE TO_Country_ID IS NULL
carlos@0
   233
          AND to_code_Country IS NOT NULL
carlos@0
   234
  			  AND I_IsImported<>'Y'
carlos@423
   235
          AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   236
      ]]>
carlos@0
   237
    </Sql>
carlos@0
   238
    <Parameter name="ad_client_id"/>
carlos@0
   239
  </SqlMethod>
carlos@0
   240
  
carlos@0
   241
  <SqlMethod name="updateRegionFromId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   242
    <SqlMethodComment></SqlMethodComment>
carlos@0
   243
    <Sql>
carlos@0
   244
      <![CDATA[
carlos@649
   245
        UPDATE I_Tax
carlos@0
   246
			  SET C_Region_ID=(
carlos@0
   247
          SELECT MAX(C_Region_ID) 
carlos@0
   248
            FROM C_Region r
carlos@649
   249
  			   WHERE r.AD_Client_ID IN (0, I_Tax.AD_Client_ID)
carlos@649
   250
             AND  (UPPER(r.Value) = UPPER(I_Tax.from_code_region)
carlos@649
   251
               OR  UPPER(r.Name) = UPPER(I_Tax.from_code_region)))
carlos@0
   252
			  WHERE C_Region_ID IS NULL
carlos@0
   253
        AND from_code_Region IS NOT NULL
carlos@0
   254
			  AND I_IsImported<>'Y'
carlos@423
   255
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   256
      ]]>
carlos@0
   257
    </Sql>
carlos@0
   258
    <Parameter name="ad_client_id"/>
carlos@0
   259
  </SqlMethod>
carlos@0
   260
  
carlos@0
   261
  <SqlMethod name="updateRegionFromError" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   262
    <SqlMethodComment></SqlMethodComment>
carlos@0
   263
    <Sql>
carlos@0
   264
      <![CDATA[
carlos@0
   265
        UPDATE I_Tax
carlos@0
   266
			  SET I_IsImported='E', I_ErrorMsg=COALESCE(TO_CHAR(I_ErrorMsg),'')||'ERR=Invalid Region From, '
carlos@0
   267
			  WHERE C_Region_ID IS NULL
carlos@0
   268
          AND from_code_Region IS NOT NULL
carlos@0
   269
  			  AND I_IsImported<>'Y'
carlos@423
   270
          AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   271
      ]]>
carlos@0
   272
    </Sql>
carlos@0
   273
    <Parameter name="ad_client_id"/>
carlos@0
   274
  </SqlMethod>
carlos@0
   275
  
carlos@0
   276
    <SqlMethod name="updateRegionToId" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   277
    <SqlMethodComment></SqlMethodComment>
carlos@0
   278
    <Sql>
carlos@0
   279
      <![CDATA[
carlos@649
   280
        UPDATE I_Tax
carlos@0
   281
			  SET TO_Region_ID=(
carlos@0
   282
          SELECT MAX(C_Region_ID) 
carlos@0
   283
            FROM C_Region r
carlos@649
   284
  			   WHERE r.AD_Client_ID IN (0, I_Tax.AD_Client_ID)
carlos@649
   285
             AND  (UPPER(r.Value) = UPPER(I_Tax.to_code_region)
carlos@649
   286
               OR  UPPER(r.Name) = UPPER(I_Tax.to_code_region)))
carlos@0
   287
			  WHERE TO_Region_ID IS NULL
carlos@0
   288
        AND to_code_Region IS NOT NULL
carlos@0
   289
			  AND I_IsImported<>'Y'
carlos@423
   290
        AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   291
      ]]>
carlos@0
   292
    </Sql>
carlos@0
   293
    <Parameter name="ad_client_id"/>
carlos@0
   294
  </SqlMethod>
carlos@0
   295
  
carlos@0
   296
  <SqlMethod name="updateRegionToError" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   297
    <SqlMethodComment></SqlMethodComment>
carlos@0
   298
    <Sql>
carlos@0
   299
      <![CDATA[
carlos@0
   300
        UPDATE I_Tax
carlos@0
   301
			  SET I_IsImported='E', I_ErrorMsg=COALESCE(TO_CHAR(I_ErrorMsg),'')||'ERR=Invalid Region To, '
carlos@0
   302
			  WHERE TO_Region_ID IS NULL
carlos@0
   303
          AND to_code_Region IS NOT NULL
carlos@0
   304
  			  AND I_IsImported<>'Y'
carlos@423
   305
          AND AD_Client_ID = TO_NUMBER(?)
carlos@0
   306
      ]]>
carlos@0
   307
    </Sql>
carlos@0
   308
    <Parameter name="ad_client_id"/>
carlos@0
   309
  </SqlMethod>
carlos@0
   310
  
carlos@0
   311
  
carlos@0
   312
  
carlos@0
   313
  
carlos@0
   314
  <SqlMethod name="insertTaxCategory" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   315
    <SqlMethodComment></SqlMethodComment>
carlos@0
   316
    <Sql>
carlos@0
   317
      <![CDATA[
carlos@0
   318
			  INSERT INTO C_TaxCategory 
carlos@0
   319
          (C_TaxCategory_ID, AD_Client_ID, AD_Org_ID,
carlos@0
   320
          IsActive,Created,CreatedBy,Updated,UpdatedBy,
asier@799
   321
          Name, Description)
carlos@0
   322
  		  SELECT TO_NUMBER(?), AD_Client_ID, AD_Org_ID,
carlos@0
   323
            'Y',now(),CreatedBy,now(),UpdatedBy,
asier@799
   324
            TC_Name, TC_Description
carlos@0
   325
			  	  FROM I_Tax
carlos@423
   326
				    WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   327
      ]]>
carlos@0
   328
    </Sql>
carlos@0
   329
    <Parameter name="CTaxCategoryID"/>
carlos@0
   330
    <Parameter name="ITaxID"/>
carlos@0
   331
  </SqlMethod>
carlos@0
   332
  
carlos@0
   333
  <SqlMethod name="updateTaxCategory" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   334
    <SqlMethodComment></SqlMethodComment>
carlos@0
   335
    <Sql>
carlos@0
   336
      <![CDATA[
carlos@0
   337
			  UPDATE C_TaxCategory 
carlos@0
   338
           SET updated = now(),
carlos@423
   339
               updatedBy = (SELECT updatedBy FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),
asier@799
   340
               description = COALESCE((SELECT TC_Description FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),description)
carlos@423
   341
         WHERE C_TaxCategory_ID = TO_NUMBER(?)
carlos@0
   342
      ]]>
carlos@0
   343
    </Sql>
carlos@0
   344
    <Parameter name="ITaxID"/>
carlos@0
   345
    <Parameter name="ITaxID"/>
carlos@0
   346
    <Parameter name="CTaxCategoryID"/>    
carlos@0
   347
  </SqlMethod>
carlos@0
   348
  
carlos@0
   349
    <SqlMethod name="insertBPTaxCategory" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   350
    <SqlMethodComment></SqlMethodComment>
carlos@0
   351
    <Sql>
carlos@0
   352
      <![CDATA[
carlos@0
   353
			  INSERT INTO C_BP_TaxCategory 
carlos@0
   354
          (C_BP_TaxCategory_ID, AD_Client_ID, AD_Org_ID,
carlos@0
   355
          IsActive,Created,CreatedBy,Updated,UpdatedBy,
carlos@0
   356
          Name, Description)
carlos@0
   357
  		  SELECT TO_NUMBER(?), AD_Client_ID, AD_Org_ID,
carlos@0
   358
            'Y',now(),CreatedBy,now(),UpdatedBy,
carlos@0
   359
            BPTC_Name, BPTC_Description
carlos@0
   360
			  	  FROM I_Tax
carlos@423
   361
				    WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   362
      ]]>
carlos@0
   363
    </Sql>
carlos@0
   364
    <Parameter name="CBPTaxCategoryID"/>
carlos@0
   365
    <Parameter name="ITaxID"/>
carlos@0
   366
  </SqlMethod>
carlos@0
   367
  
carlos@0
   368
  <SqlMethod name="updateBPTaxCategory" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   369
    <SqlMethodComment></SqlMethodComment>
carlos@0
   370
    <Sql>
carlos@0
   371
      <![CDATA[
carlos@0
   372
			  UPDATE C_BP_TaxCategory 
carlos@0
   373
           SET updated        = now(),
carlos@423
   374
               updatedBy      = (SELECT updatedBy FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),
carlos@423
   375
               description    = COALESCE((SELECT BPTC_Description FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),description)
carlos@423
   376
         WHERE C_BP_TaxCategory_ID = TO_NUMBER(?)
carlos@0
   377
      ]]>
carlos@0
   378
    </Sql>
carlos@0
   379
    <Parameter name="ITaxID"/>
carlos@0
   380
    <Parameter name="ITaxID"/>
carlos@0
   381
    <Parameter name="CTaxCategoryID"/>    
carlos@0
   382
  </SqlMethod>
carlos@0
   383
  
carlos@0
   384
   <SqlMethod name="insertDefaultTax" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   385
    <SqlMethodComment></SqlMethodComment>
carlos@0
   386
    <Sql>
carlos@0
   387
      <![CDATA[
carlos@0
   388
			  INSERT INTO C_Tax
carlos@0
   389
        (C_Tax_ID, AD_Client_ID, AD_Org_ID,
carlos@0
   390
          IsActive,Created,CreatedBy,Updated,UpdatedBy,
carlos@0
   391
          Name, ValidFrom, Rate,
carlos@0
   392
          c_TaxCategory_ID)
carlos@0
   393
        SELECT TO_NUMBER(?), AD_Client_ID, AD_Org_ID,
carlos@0
   394
            'Y',now(),CreatedBy,now(),UpdatedBy,
carlos@0
   395
            parent_Name, COALESCE(ValidFrom,now()), 0,
carlos@0
   396
            TO_NUMBER(?)
carlos@0
   397
			  	  FROM I_Tax
carlos@423
   398
				    WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   399
      ]]>
carlos@0
   400
    </Sql>
carlos@0
   401
    <Parameter name="TaxID"/>
carlos@0
   402
    <Parameter name="TaxCategory"/>    
carlos@0
   403
    <Parameter name="ITaxID"/>
carlos@0
   404
  </SqlMethod>
carlos@0
   405
  
carlos@0
   406
   <SqlMethod name="insertTax" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   407
    <SqlMethodComment></SqlMethodComment>
carlos@0
   408
    <Sql>
carlos@0
   409
      <![CDATA[
carlos@0
   410
			  INSERT INTO C_Tax
carlos@0
   411
        (C_Tax_ID, AD_Client_ID, AD_Org_ID,
carlos@0
   412
          IsActive,Created,CreatedBy,Updated,UpdatedBy,
carlos@0
   413
          Name, Description, ValidFrom, Rate,
carlos@0
   414
          c_TaxCategory_ID, c_BP_TaxCategory_ID, PARENT_TAX_ID,
carlos@0
   415
          TAXINDICATOR, ISSUMMARY, ISTAXEXEMPT,    
carlos@0
   416
          SOPOTYPE, LINE, CASCADE)
carlos@0
   417
  		  SELECT TO_NUMBER(?), AD_Client_ID, AD_Org_ID,
carlos@0
   418
            'Y',now(),CreatedBy,now(),UpdatedBy,
carlos@0
   419
            T_Name, T_Description, COALESCE(ValidFrom,now()), Rate,
carlos@0
   420
            TO_NUMBER(?), TO_NUMBER(?), TO_NUMBER(?),
carlos@0
   421
            TAXINDICATOR, COALESCE(ISSUMMARY,'N'), COALESCE(ISTAXEXEMPT,'N'),    
carlos@0
   422
            COALESCE(SOPOTYPE,'B'), LINE, COALESCE(CASCADE,'N')
carlos@0
   423
			  	  FROM I_Tax
carlos@423
   424
				    WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   425
      ]]>
carlos@0
   426
    </Sql>
carlos@0
   427
    <Parameter name="TaxID"/>
carlos@0
   428
    <Parameter name="TaxCategory"/>
carlos@0
   429
    <Parameter name="BPTaxCategory"/>
carlos@0
   430
    <Parameter name="ParentTax"/>
carlos@0
   431
    <Parameter name="ITaxID"/>
carlos@0
   432
  </SqlMethod>
carlos@0
   433
  
carlos@0
   434
  <SqlMethod name="updateTax" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   435
    <SqlMethodComment></SqlMethodComment>
carlos@0
   436
    <Sql>
carlos@0
   437
      <![CDATA[
carlos@0
   438
			  UPDATE C_Tax 
carlos@0
   439
           SET updated            = now(),
carlos@0
   440
               updatedBy          = (SELECT updatedBy FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),
carlos@0
   441
               description        = COALESCE((SELECT T_Description     FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)), description),
carlos@0
   442
               ValidFrom          = COALESCE((SELECT ValidFrom         FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)), ValidFrom),
carlos@0
   443
               Rate               = COALESCE((SELECT Rate              FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)), Rate),
carlos@0
   444
               C_TaxCategory_ID   = COALESCE(TO_NUMBER(?),C_TaxCategory_ID),
carlos@0
   445
               C_BP_TaxCategory_ID= COALESCE(TO_NUMBER(?),C_BP_TaxCategory_ID),
carlos@0
   446
               Parent_Tax_ID      = COALESCE(TO_NUMBER(?),Parent_Tax_ID),
carlos@649
   447
               TAXINDICATOR       = COALESCE((SELECT TAXINDICATOR      FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),TAXINDICATOR),   
carlos@649
   448
               ISSUMMARY          = COALESCE((SELECT ISSUMMARY         FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),ISSUMMARY),      
carlos@649
   449
               ISTAXEXEMPT        = COALESCE((SELECT ISTAXEXEMPT       FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),ISTAXEXEMPT),    
carlos@649
   450
               SOPOTYPE           = COALESCE((SELECT SOPOTYPE          FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),SOPOTYPE),       
carlos@649
   451
               LINE               = COALESCE((SELECT LINE              FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),LINE),           
carlos@649
   452
               CASCADE            = COALESCE((SELECT CASCADE           FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),CASCADE) 
carlos@0
   453
         WHERE C_Tax_ID = TO_NUMBER(?)
carlos@0
   454
      ]]>
carlos@0
   455
    </Sql>
carlos@0
   456
    <Parameter name="ITaxID"/>
carlos@0
   457
    <Parameter name="ITaxID"/>
carlos@0
   458
    <Parameter name="ITaxID"/>
carlos@0
   459
    <Parameter name="ITaxID"/>
carlos@0
   460
    <Parameter name="TaxCategoryID"/>
carlos@0
   461
    <Parameter name="BPTaxCategoryID"/>
carlos@0
   462
    <Parameter name="ParentTax"/>
carlos@0
   463
    <Parameter name="ITaxID"/>
carlos@0
   464
    <Parameter name="ITaxID"/>
carlos@0
   465
    <Parameter name="ITaxID"/>
carlos@0
   466
    <Parameter name="ITaxID"/>
carlos@0
   467
    <Parameter name="ITaxID"/>
carlos@0
   468
    <Parameter name="ITaxID"/>
carlos@0
   469
    <Parameter name="CTaxID"/>    
carlos@0
   470
  </SqlMethod>
carlos@0
   471
  
carlos@0
   472
  <SqlMethod name="hasLocation" type="preparedStatement" connection="true" return="string">
carlos@0
   473
    <SqlMethodComment></SqlMethodComment>
carlos@0
   474
    <Sql>
carlos@0
   475
      <![CDATA[
carlos@0
   476
        SELECT COUNT(*) AS N
carlos@0
   477
          FROM C_TAX
carlos@0
   478
         WHERE (C_Country_ID is not null
carlos@0
   479
            OR C_Region_ID is not null
carlos@0
   480
            OR TO_Country_ID is not null
carlos@0
   481
            OR TO_Region_ID is not null)
carlos@0
   482
           AND C_Tax_ID = TO_NUMBER(?)
carlos@0
   483
      ]]>
carlos@0
   484
    </Sql>
carlos@0
   485
    <Parameter name="taxID"/>
carlos@0
   486
  </SqlMethod>
carlos@0
   487
  
carlos@0
   488
  <SqlMethod name="updateTaxRegion" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   489
    <SqlMethodComment></SqlMethodComment>
carlos@0
   490
    <Sql>
carlos@0
   491
      <![CDATA[
carlos@0
   492
         UPDATE C_Tax
carlos@0
   493
            SET C_Country_ID  = (SELECT C_Country_ID  FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),
carlos@0
   494
                C_Region_ID   = (SELECT C_Region_ID   FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),
carlos@0
   495
                TO_Country_ID = (SELECT TO_Country_ID FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?)),
carlos@0
   496
                TO_Region_ID  = (SELECT TO_Region_ID  FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   497
          WHERE C_Tax_ID = TO_NUMBER(?)
carlos@0
   498
      ]]>
carlos@0
   499
    </Sql>
carlos@0
   500
    <Parameter name="ItaxID"/>
carlos@0
   501
    <Parameter name="ItaxID"/>
carlos@0
   502
    <Parameter name="ItaxID"/>
carlos@0
   503
    <Parameter name="ItaxID"/>
carlos@0
   504
    <Parameter name="taxID"/>
carlos@0
   505
  </SqlMethod>
carlos@0
   506
  
carlos@0
   507
  <SqlMethod name="existsLocation" type="preparedStatement" connection="true" return="string">
carlos@0
   508
    <SqlMethodComment></SqlMethodComment>
carlos@0
   509
    <Sql>
carlos@0
   510
      <![CDATA[
carlos@0
   511
        SELECT COUNT(*) AS N
carlos@0
   512
          FROM (SELECT 1
carlos@0
   513
                  FROM C_TAX
carlos@0
   514
                 WHERE COALESCE(C_Country_ID,0) =(SELECT COALESCE(C_Country_ID,0)  FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   515
                   AND COALESCE(C_Region_ID,0)  =(SELECT COALESCE(C_Region_ID,0)   FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   516
                   AND COALESCE(TO_Country_ID,0)=(SELECT COALESCE(TO_Country_ID,0) FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   517
                   AND COALESCE(TO_Region_ID,0) =(SELECT COALESCE(TO_Region_ID,0)  FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   518
                   AND C_Tax_ID = TO_NUMBER(?)
carlos@0
   519
                UNION
carlos@0
   520
                SELECT 1
carlos@0
   521
                  FROM C_TAX_ZONE
carlos@0
   522
                 WHERE COALESCE(FROM_Country_ID,0)=(SELECT COALESCE(C_Country_ID,0)   FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   523
                   AND COALESCE(FROM_Region_ID,0) =(SELECT COALESCE(C_Region_ID,0)    FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   524
                   AND COALESCE(TO_Country_ID,0)  =(SELECT COALESCE(TO_Country_ID,0)  FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   525
                   AND COALESCE(TO_Region_ID,0)   =(SELECT COALESCE(TO_Region_ID,0)   FROM I_Tax WHERE I_Tax_ID = TO_NUMBER(?))
carlos@0
   526
                   AND C_Tax_ID = TO_NUMBER(?)) AAA
carlos@0
   527
      ]]>
carlos@0
   528
    </Sql>
carlos@0
   529
    <Parameter name="ItaxID"/>
carlos@0
   530
    <Parameter name="ItaxID"/>
carlos@0
   531
    <Parameter name="ItaxID"/>
carlos@0
   532
    <Parameter name="ItaxID"/>
carlos@0
   533
    <Parameter name="CtaxID"/>
carlos@0
   534
    <Parameter name="ItaxID"/>
carlos@0
   535
    <Parameter name="ItaxID"/>
carlos@0
   536
    <Parameter name="ItaxID"/>
carlos@0
   537
    <Parameter name="ItaxID"/>
carlos@0
   538
    <Parameter name="CtaxID"/>
carlos@0
   539
  </SqlMethod>
carlos@0
   540
  
carlos@0
   541
    <SqlMethod name="insertTaxZone" type="preparedStatement" connection="true" return="rowCount">
carlos@0
   542
    <SqlMethodComment></SqlMethodComment>
carlos@0
   543
    <Sql>
carlos@0
   544
      <![CDATA[
carlos@0
   545
         INSERT INTO C_Tax_Zone
carlos@0
   546
           (C_Tax_ZONE_ID, AD_Client_ID, AD_Org_ID,
carlos@0
   547
            IsActive,Created,CreatedBy,Updated,UpdatedBy,
carlos@0
   548
            C_Tax_ID, From_Country_ID, From_Region_ID,
carlos@0
   549
            TO_country_ID, To_region_ID)
carlos@0
   550
         SELECT TO_NUMBER(?), AD_Client_ID, AD_Org_ID,
carlos@0
   551
                'Y',now(),CreatedBy,now(),UpdatedBy,
carlos@0
   552
                TO_NUMBER(?), C_Country_ID, C_Region_ID,
carlos@0
   553
                TO_Country_ID, To_Region_ID
carlos@0
   554
           FROM I_Tax
carlos@0
   555
          WHERE I_Tax_ID=TO_NUMBER(?)
carlos@0
   556
      ]]>
carlos@0
   557
    </Sql>
carlos@0
   558
    <Parameter name="taxZoneID"/>
carlos@0
   559
    <Parameter name="cTaxID"/>
carlos@0
   560
    <Parameter name="ItaxID"/>
carlos@0
   561
  </SqlMethod>
carlos@0
   562
  
carlos@0
   563
  
carlos@0
   564
carlos@0
   565
  <SqlMethod name="taxError" type="preparedStatement" return="rowCount">
carlos@0
   566
    <SqlMethodComment></SqlMethodComment>
carlos@0
   567
    <Sql>
carlos@0
   568
      <![CDATA[
carlos@0
   569
        UPDATE I_Tax
carlos@0
   570
			  SET I_IsImported='E', I_ErrorMsg=COALESCE(TO_CHAR(I_ErrorMsg),'')||?
carlos@423
   571
				WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   572
      ]]>
carlos@0
   573
    </Sql>
carlos@0
   574
    <Parameter name="error"/>
carlos@0
   575
    <Parameter name="i_tax_id"/>
carlos@0
   576
  </SqlMethod>
carlos@0
   577
carlos@0
   578
  <SqlMethod name="setImported" type="preparedStatement" connection="true" return="rowcount">
carlos@0
   579
    <SqlMethodComment></SqlMethodComment>
carlos@0
   580
    <Sql>
carlos@0
   581
      <![CDATA[
carlos@0
   582
				UPDATE I_Tax
carlos@0
   583
        SET I_IsImported='Y', 
carlos@0
   584
            C_TaxCategory_ID=TO_NUMBER(?),
carlos@0
   585
            Updated=now(), Processed='Y'
carlos@423
   586
        WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   587
      ]]>
carlos@0
   588
    </Sql>
carlos@0
   589
    <Parameter name="CTaxCategoryID"/>
carlos@0
   590
    <Parameter name="ITaxID"/>
carlos@0
   591
  </SqlMethod>
carlos@0
   592
carlos@0
   593
  <SqlMethod name="updateSetImportedError" type="preparedStatement" return="rowCount">
carlos@0
   594
    <SqlMethodComment></SqlMethodComment>
carlos@0
   595
    <Sql>
carlos@0
   596
      <![CDATA[
carlos@0
   597
        UPDATE I_Tax
carlos@0
   598
			  SET I_IsImported='E', I_ErrorMsg=COALESCE(TO_CHAR(I_ErrorMsg),'')||'Set Imported, '
carlos@423
   599
				WHERE I_Tax_ID = TO_NUMBER(?)
carlos@0
   600
      ]]>
carlos@0
   601
    </Sql>
carlos@0
   602
    <Parameter name="i_tax_id"/>
carlos@0
   603
  </SqlMethod>
carlos@0
   604
  
carlos@0
   605
  <SqlMethod name="updateNotImported" type="preparedStatement" return="rowcount">
carlos@0
   606
    <SqlMethodComment></SqlMethodComment>
carlos@0
   607
    <Sql>
carlos@0
   608
      <![CDATA[
carlos@0
   609
				UPDATE I_BPartner
carlos@0
   610
        SET I_IsImported='N', Updated=now()
carlos@0
   611
			  WHERE I_IsImported<>'Y'
carlos@423
   612
        AND AD_CLIENT_ID = TO_NUMBER(?)
carlos@0
   613
      ]]>
carlos@0
   614
    </Sql>
carlos@0
   615
    <Parameter name="adClientId"/>
carlos@0
   616
  </SqlMethod>
carlos@0
   617
carlos@0
   618
</SqlClass>