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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/2830/viewspace-525031/
415
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
