ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Journal import don't success

Journal import don't success

原创 Linux操作系统 作者:hotwish 时间:2009-01-03 11:31:13 0 删除 编辑

How to setup your resource account when you want your
resource to be costed into the correct account without
warning or error such as :

  "Unbalanced Journal Error Codes
   ------------------------------
   WU01    Warning: This journal entry is unbalanced. 
           It is accepted because suspense posting is
           allowed in this set of books."
or

  "Unbalanced Journal Error Codes
   ------------------------------
   EU02    This journal entry is unbalanced and suspense
           posting is not allowed in this set of books."


SCOPE & APPLICATION
-------------------

This document is intended for all users, consultants and
support analysts of the Oracle application which are using
 BOM (bills of materials, routings, resource),
 WIP (discrete jobs),
 INV (Transfert transaction to GL)
 and GL (Journal import) from 10.7 to 11.0.3

==== extract of Journal Import Execution Report ====
Error                                                        
Code  Journal Entry Name Batch Name         Total Debits Total Credits
----- ------------------ -----------------  ------------ -------------
EU02  WIP                336 FRO Inventory        430.00        130.00
.
.
...
Unbalanced Journal Error Codes
------------------------------
EU02    This journal entry is unbalanced and suspense posting is
        not allowed in this set of books.
==== end of extract of Journal Import Execution Report ====

    3) to fix the error EU02 :
       ---------------------
     3.1) fix for future jobs :
       you need to find the resource who is making this error
       Therefore you can run those selects to sort it out :

       A) Select set_of_books_id SOB , user_je_category_name  CAT,
          user_je_source_name SOURCE, status, reference21
          from gl_interface
          where reference21= '&batch_name';

          Batch_name is equals to the batch name number you
          will get in the output
.
          this will show you result such as :
          SOB  CAT             SOURCE STATUS   REFERENCE21
          ---- ------ --------------- -------- -----------
             1 WIP          Inventory EU02,P           336
             1 WIP          Inventory EU02,P           336


    B) SELECT M.currency_code CURRENCY CUR,
       M.REFERENCE_ACCOUNT ACCOUNT,
       DECODE(sign(SUM(NVL(M.TRANSACTION_VALUE,
       M.BASE_TRANSACTION_VALUE))),1,
       SUM(NVL(M.TRANSACTION_VALUE,M.BASE_TRANSACTION_VALUE)),0) d,
       DECODE(sign(SUM(NVL(M.TRANSACTION_VALUE,
       M.BASE_TRANSACTION_VALUE))),-1,
       (-1*SUM(NVL(M.TRANSACTION_VALUE,M.BASE_TRANSACTION_VALUE))),0) c,
       DECODE(sign(SUM(M.BASE_TRANSACTION_VALUE)),1,
       SUM(M.BASE_TRANSACTION_VALUE),0) e,
       DECODE(sign(SUM(M.BASE_TRANSACTION_VALUE)),
       -1,(-1*SUM(M.BASE_TRANSACTION_VaLUE)),0)  f
       FROM WIP_TRANSACTION_ACCOUNTS M
       WHERE gl_batch_id = &reference21
      GROUP BY
     M.CURRENCY_CODE,
     M.REFERENCE_ACCOUNT ;

     reference21 is equals to reference21 in the select above

     this will show you result such as :
     CUR ACCOUNT          D          C          E          F
     --- ------- ---------- ---------- ---------- ----------
              -1          0        300          0        300
            1012        430          0        430          0
            1182          0        130          0        130

    C) what we are focus on is the line with reference_account=-1

       SELECT M.REFERENCE_ACCOUNT ACCOUNT, M.resource_id, M.wip_entity_id,   
       B.RESOURCE_CODE, B.description
       FROM WIP_TRANSACTION_ACCOUNTS M, bom_resources B
       where M.REFERENCE_ACCOUNT=-1
         and M.resource_id=B.resource_id
         and gl_batch_id = &reference21;

       reference21 is equals to reference21 in the select above

       this will show you result such as :
          ACCOUNT RESOURCE_ID WIP_ENTITY_ID RESOURCE_C DESCRIPTION
          ------- ----------- ------------- ---------- ------------
               -1         615          6713 DECOUPE    Découpe
               -1         615          6713 DECOUPE    Découpe

    D) therefore you know that you need to fix the resource DECOUPE
       in BOMFDORS by doing what is required at step "FIRST" above.
       So this will avoid this error for future jobs

  3.2) fix for old jobs that are already stuck in gl_interface
       => how to fix the error EU02 :
       look Note 135925.1 CASE 2 :


  4) Why are you getting the warning WU01 :
     ------------------------------------

     /GL/setup/financials/books/define for your set of books
     if allow suspense posting flag is on

     you will get in the output of this journal import (GLLEZL) :
     " Journal Import Execution Report" :

==== extract of Journal Import Execution Report ====
Warning Batch Name                  Accounted Debits Accounted Credits
------- --------------------------- ---------------- -----------------
WU01    337 FRO Inventory                        430               130
 
Unbalanced Journal Error Codes
------------------------------
WU01    Warning: This journal entry is unbalanced.  It is accepted
        because suspense posting is allowed in this set of books.
==== end of extract of Journal Import Execution Report ====

  5) to fix the warning WU01 :
     -----------------------
  5.1) fix for future jobs :
       you need to find the resource who is making this error
       therefore you can run those selects to sort it out :

    A) Select set_of_books_id SOB , user_je_category_name  CAT,
       user_je_source_name SOURCE, status, reference21
       from gl_interface
       where reference21= '&batch_name';

Batch_name is equals to the batch name number you will get in the output

   As this is a warning and not an error you will have no rows stucked
   in gl_interface

    B) SELECT M.currency_code CUR, M.REFERENCE_ACCOUNT ACCOUNT,
       DECODE(sign(SUM(NVL(M.TRANSACTION_VALUE,
       M.BASE_TRANSACTION_VALUE))),1,
       SUM(NVL(M.TRANSACTION_VALUE,M.BASE_TRANSACTION_VALUE)),0) d,
       DECODE(sign(SUM(NVL(M.TRANSACTION_VALUE,
       M.BASE_TRANSACTION_VALUE))),-1,
       (-1*SUM(NVL(M.TRANSACTION_VALUE,M.BASE_TRANSACTION_VALUE))),0) c,
       DECODE(sign(SUM(M.BASE_TRANSACTION_VALUE)),1,
       SUM(M.BASE_TRANSACTION_VALUE),0) e,
       DECODE(sign(SUM(M.BASE_TRANSACTION_VALUE)),
       -1,(-1*SUM(M.BASE_TRANSACTION_VaLUE)),0)  f
       FROM WIP_TRANSACTION_ACCOUNTS M
       WHERE gl_batch_id = &reference21
      GROUP BY
     M.CURRENCY_CODE,
     M.REFERENCE_ACCOUNT ;

reference21 is equals the batch name number you will get in the output

this will show you result such as :
    CUR   ACCOUNT          D          C          E          F
    --- ---------- ---------- ---------- ---------- ----------
                -1          0        300          0        300
              1012        430          0        430          0
              1182          0        130          0        130

    C) what we are focus on is the line with reference_account=-1

       SELECT M.REFERENCE_ACCOUNT ACCOUNT, M.resource_id,
       M.wip_entity_id, B.RESOURCE_CODE, B.description
       FROM WIP_TRANSACTION_ACCOUNTS M, bom_resources B
       where M.REFERENCE_ACCOUNT=-1
         and M.resource_id=B.resource_id
         and gl_batch_id = &reference21;

       reference21 is equals to reference21 in the select above

       this will show you result such as :
       ACCOUNT RESOURCE_ID WIP_ENTITY_ID RESOURCE_C DESCRIPTION
          ---- ----------- ------------- ---------- ------------
            -1         615          6714 DECOUPE    Découpe
            -1         615          6714 DECOUPE    Découpe

    D) therefore you know that you need to fix the resource DECOUPE
       in BOMFDORS by doing what is required at step "FIRST" above.
       So this will avoid this error for future jobs

  5.2) fix for old jobs with warning WU01
  => look Note 154177.1

Here is other case

--Error                                                                            Total
Code  Journal Entry Name                    Batch Name                           Lines Period Name     Total Debits    Total Credits
----- ------------------------------------- ------------------------------------ ----- ----------- ---------------- ----------------
EU02  MTL HKD                               7772 AZH Inventory 4072045: A           17 Dec-2008      37,901,059.95    37,904,283.24

query the amount balance
select set_of_books_id sob,user_je_category_name cat,user_je_source_name source,
status, reference21,sum(entered_dr),sum(entered_cr),sum(accounted_dr),sum(accounted_cr)
from gl_interface gi
where created_by=18333 and status='EU02,P'
group by set_of_books_id ,user_je_category_name ,user_je_source_name ,status,reference21

SOB CAT SOURCE STATUS REFERENCE21 SUM(ENTERED_DR) SUM(ENTERED_CR) SUM(ACCOUNTED_DR) SUM(ACCOUNTED_CR) balance
23.00 MTL Inventory EU02,P 7772 37,901,059.95 37,904,283.24 37,901,059.95 37,904,283.24 3,223.2900000000

query the detail

SELECT set_of_books_id sob,user_je_category_name cat,code_combination_id,
user_je_source_name source, status, reference21,entered_dr,entered_cr,accounted_dr,accounted_cr
from gl_interface gi
where created_by=18333 and status='EU02,P'

SOB CAT CODE_COMBINATION_ID SOURCE STATUS REFERENCE21 ENTERED_DR ENTERED_CR ACCOUNTED_DR ACCOUNTED_CR
23.00 MTL 1,893.00 Inventory EU02,P 7772 0.00 ######## 0.00 10,945,366.68
23.00 MTL 1,895.00 Inventory EU02,P 7772 0.00 89,798.64 0.00 89,798.64
23.00 MTL 1,899.00 Inventory EU02,P 7772 35,958,153.68 0.00 35,958,153.68 0.00
23.00 MTL 2,053.00 Inventory EU02,P 7772 0.00 ######## 0.00 7,827,662.67
23.00 MTL 2,513.00 Inventory EU02,P 7772 0.00 ######## 0.00 4,300,377.89
23.00 MTL 2,709.00 Inventory EU02,P 7772 273,931.77 0.00 273,931.77 0.00
23.00 MTL 2,710.00 Inventory EU02,P 7772 26,034.78 0.00 26,034.78 0.00
23.00 MTL 2,713.00 Inventory EU02,P 7772 18,930.63 0.00 18,930.63 0.00
23.00 MTL 3,295.00 Inventory EU02,P 7772 270,657.17 0.00 270,657.17 0.00
23.00 MTL 3,815.00 Inventory EU02,P 7772 395,237.02 0.00 395,237.02 0.00
23.00 MTL 12,027.00 Inventory EU02,P 7772 237,762.99 0.00 237,762.99 0.00
23.00 MTL 13,967.00 Inventory EU02,P 7772 0.00 24,783.35 0.00 24,783.35
23.00 MTL 15,350.00 Inventory EU02,P 7772 493,512.27 0.00 493,512.27 0.00
23.00 MTL 21,239.00 Inventory EU02,P 7772 0.00 ######## 0.00 9,775,818.37
23.00 MTL 26,194.00 Inventory EU02,P 7772 226,839.64 0.00 226,839.64 0.00
23.00 MTL 26,870.00 Inventory EU02,P 7772 0.00 ######## 0.00 4,735,320.15
23.00 MTL 30,062.00 Inventory EU02,P 7772 0.00 ######## 0.00 205,155.49

query the ccid=-1 in the MTA table(ccid=-1 amount is 3223.29)

select reference_account,accounting_line_type,sum(base_transaction_value)
--,rate_or_amount,primary_quantity
from mtl_transaction_accounts where gl_batch_id=7772 --and reference_account=30062
group by reference_account,accounting_line_type

select reference_account,accounting_line_type,base_transaction_value,mta.*
--,rate_or_amount,primary_quantity
from mtl_transaction_accounts mta where gl_batch_id=7772 and reference_account=-1

select * from  mtl_material_txns_val_v where transaction_id in (select transaction_id from mtl_transaction_accounts mta where gl_batch_id=7772 and reference_account=-1)

solution(updat eht mta ccid to the right id and update the gl_interface amount)

Total mta is 398460.31 the ccid=-1 is 3223.29 so it caude inv transfer to gl interface amount is 395237.02=total 398460.31 -3223.29

40.902.62112.0000  ccid=3815

so should add 3223.29 to gl interface

update mtl_transaction_accounts mta set reference_account=3815 where gl_batch_id=7772 and reference_account=-1

update gl_interface set entered_dr=398460.31,accounted_dr=398460.31
where created_by=18333 and status='EU02,P' and code_combination_id=3815

Journal import is ok

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/2830/viewspace-525031/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-28

  • 博文量
    125
  • 访问量
    254453