标签：DataFix

1 获取801.1244010000.000000.000.00000000 的CCID
Select code_combination_id
      From gl_code_combinations
      Where segment1 = '801'
      And segment2 = '1244010000'
      And segment3 = '000000'
      And segment4 = '000'
      And segment5 = '00000000'
#返回：1051
2获取802.1244010000.000000.000.00000000 的CCID
Select code_combination_id
      From gl_code_combinations
      Where segment1 = '802'
      And segment2 = '1244010000'
      And segment3 = '000000'
      And segment4 = '000'
      And segment5 = '00000000'
#返回：2316
获取 科目= '1244010000' 的所有帐户
Select code_combination_id
      From gl_code_combinations
      And segment2 = '1244010000'
#返回：
2316
1051
说明该科目相关的帐户只有两个明细帐户，无汇总帐户。
 
3 获取加拿大帐套的ID
Select set_of_books_id
From gl_sets_of_books
Where Name = 'GiTi Canada Set of Books'
#返回1002
4 获取帐户错误的日记帐头
Select Distinct gl.je_header_id
From gl_je_lines gl, gl_je_headers gh
Where gl.je_header_id = gh.je_header_id
And gh.set_of_books_id = 1002
And gl.code_combination_id = 1051;
#返回两笔：
25418
25337
 
5 获取加拿大OU的org_id
Select organization_id  from  hr_operating_units where Name =’某客户(Canada),LTD.
#返回127
6 获取CC1的组织ID
Select organization_id
From mtl_parameters
Where organization_code = 'CC1'
#返回：126
6 获取库存模块错误的会计分录的所有事务来源类型
SELECT DISTINCT mta.transaction_source_type_id
FROM   inv.mtl_transaction_accounts mta
WHERE mta.organization_id = 126
AND   mta.reference_account = 1051
#返回：1

    --rcv_accounting_events
    Select Count(1)
    From po.rcv_accounting_events a
    Where a.org_id = 127
    And a.debit_account_id = 1051;
返回：18
    --rcv_receiving_sub_ledger
    Select Count(1)
    From rcv_receiving_sub_ledger a
    Where a.set_of_books_id = 1002
    And a.code_combination_id = 1051;
返回：18
    Select Count(1)
    From rcv_receiving_sub_ledger a
    Where a.set_of_books_id = 1003
    And a.code_combination_id = 1051;
返回：0
    -- mtl_transaction_accounts
    Select Count(1)
    From inv.mtl_transaction_accounts mta
    Where mta.organization_id = 126
    And mta.reference_account = 1051
    And mta.transaction_source_type_id = 1;
返回：18
    --  gl_interface
    Select Count(1)
    From gl.gl_interface gi
    Where gi.code_combination_id = 1051
    And gi.set_of_books_id = 1002;
返回：0
    Select Count(1)
    From gl.gl_interface gi
    Where gi.code_combination_id = 1051
    And gi.set_of_books_id = 1003;
返回：18
    --gl_je_lines
    Select Distinct gl.je_header_id
    From gl_je_lines gl, gl_je_headers gh
    Where gl.je_header_id = gh.je_header_id
    And gh.set_of_books_id = 1002
    And gl.code_combination_id = 1051;
 
返回：
    25337
25418
 
    Select Distinct gl.je_header_id
    From gl_je_lines gl, gl_je_headers gh
    Where gl.je_header_id = gh.je_header_id
    And gh.set_of_books_id = 1003
And gl.code_combination_id = 1051;
返回：
无记录

BEGIN
 
    -- update rcv_parameters
    Update po.rcv_parameters p
    Set p.CLEARING_ACCOUNT_ID = 2316
    Where p.organization_id = 126;
 
    --update rcv_accounting_events
    Update po.rcv_accounting_events a
    Set a.debit_account_id = 2316
    Where a.org_id = 127
    And a.debit_account_id = 1051;
 
    --update rcv_receiving_sub_ledger
    Update rcv_receiving_sub_ledger a
    Set a.code_combination_id = 2316
    Where a.set_of_books_id = 1002
    And a.code_combination_id = 1051;
 
    -- Update Inv trnasactions account
     Update inv.mtl_transaction_accounts mta Set
     mta.reference_account = 2316
    Where mta.organization_id = 126
    And mta.reference_account = 1051
    And mta.transaction_source_type_id = 1;
 
    -- update gl_interface
    Update gl.gl_interface gi
    Set gi.code_combination_id = 2316
    Where gi.code_combination_id = 1051
    And gi.set_of_books_id = 1002;
 
    Update gl.gl_interface gi
    Set gi.code_combination_id = 2316
    Where gi.code_combination_id = 1051
    And gi.set_of_books_id = 1003;    
 
    --update gl_je_lines for head 25418 and 25337
    Update gl.gl_je_lines a
    Set a.code_combination_id = 2316
    Where a.je_header_id = 25418
    And a.code_combination_id = 1051;
 
    Update gl.gl_je_lines a
    Set a.code_combination_id = 2316
    Where a.je_header_id = 25337
    And a.code_combination_id = 1051;
 
End;