Post date: May 14, 2020 3:5:28 AM
Use query attached
1) You may use the given below query to find out the batch which has balance in WIP for a given date range. You please tweak the from and to date as per your business requirement.
select xeh.source_document_id, sum(xel.base_amount)
from gmf_xla_extract_headers xeh, gmf_xla_extract_lines xel
where xeh.header_id = xel.header_id
and xel.journal_line_type = 'WIP'
and xeh.transaction_date >= TO_DATE('01/03/2020 00:00:00','dd/mm/yy hh24:mi:ss')
and transaction_date <= TO_DATE('31/03/20 23:59:59','dd/mm/yy hh24:mi:ss')
group by xeh.source_document_id
having sum(xel.base_amount) <> 0
It is observed that you have consumed the material on Oct 2019 and same was cancelled in Jan 2020 period.
Item Cost in Oct 2019 is 29075.52918 X Qty 600 , value is 17445317.51 whereas Item cost in Jan20 is 27595.97883 X qty -600, value is -16557587.3 hence difference value is 887730.2071 .
This is expected and system is working as per its design.
As per the valuation logic, WIP issue and WIP Issue Return will be valued at existing Item cost of the month . Since WIP issue and its WIP Issue Return happened in two different month and item cost is different in these two months, there is a balance in WIP account.
If you do not want to have balance in WIP account in case of cancelled batch, you have to cancel the batch in the same month of material issue or you have to ensure that there is no difference in the item cost between WIP Issue and WIP Issue Return.
In this case, you have to pass manual JV to make the WIP balance as zero.