Home > ERP, Technology, Work Life > Oracle WMS Cycle Count Bug

Oracle WMS Cycle Count Bug

We recently completed a Physical Inventory using Oracle WMS Cycle Count functionality and some custom programming. It was a arguably a success. I’ll post something about that in the future, hopefully, but here is something that is really aggravating me at the moment.

Two of the biggest problems we had appeared to be bugs with seeded forms/functionality. We had two different symptoms, that seemed like seeded (built in/out of the box) functionality was broken, and it took us quite some time to identify the root cause.

  • The Approve Cycle Counts form would not show us a portion of the cycle count entries
    • The counts were in the database
    • The counts were in our report
    • The counts would not show up on the form
  • Recounts would not be given to users
    • Initial counts would be made
    • Recounts would be manually queued to users using the Warehouse Control Board
    • As soon as the user would go to the telnet Directed Cycled Count Tasks screen, the task would go directly back to pending
    • The user would never see the task to work on

I logged two separate SRs with Oracle.  Both made little progress for several weeks.

Luckily, one of our developers identified the commonality between the records. There was an EXPORT_FLAG on the MTL_CYCLE_COUNT_ENTRIES table which was set to 1 for the offending records.  I updated the two SRs with these details.

Apparently, there’s functionality to “export” cycle count entries out and then back into the system. Instead of using a interface table, or another more transparent method, Oracle decided to use the base table, and have this “EXPORT_FLAG” indicate if a record was locked for interfacing/export.  It made very little sense to me.  And I was totally unaware of the functionality, which meant there was no way we were using it, as security for it would never have been granted, let alone created.

I still don’t get why I could see the task in the warehouse control board, and update it. Maybe I’ll open another SR to say Exported count entries are view-able on the control board.

Regardless, Out of the 22,000 locators which were counted, around 679 of them had this EXPORT_FLAG set to 1, and appeared not to have been touched by our custom programs (RICE, or Reports, Interfaces, Conversions, Extensions). Here is the query I used to find them.

select fuc.user_name, ful.user_name, ppf.full_name,cce.count_quantity_current, cce.system_quantity_current, cce.adjustment_quantity
from mtl_cycle_count_entries cce, fnd_user fuc, fnd_user ful, per_people_f ppf
where cce.cycle_count_header_id = 46001
and cce.export_flag = 1
and cce.count_quantity_prior is null –Approved on first count/not touched by RICE
and cce.count_quantity_current >0 — Not Empty
and cce.created_by = fuc.user_id
and cce.last_updated_by = ful.user_id
and cce.entry_status_code = 5 –Complete
and cce.approval_date is not null –Approved
and ppf.person_id = cce.counted_by_employee_id_current
AND CCE.CREATED_BY != 3850 –Not Ed Hayes

Oracle wouldn’t submit any bugs to development without a reproducible test case. I escalated the SRs as far as I could, and couldn’t get anywhere.  Since we do these transactions once a year, with 50 people doing 25,000 transactions over 4 days, its very difficult to replicate the problem. I tried the same items, same locators, same counts, I could not replicate the problem. During testing, none of the customizations caused any problems, and were running the whole time. I specifically created records both with and without the customizations, and never once did the EXPORT_FLAG get set.

All Oracle needed to do was do a search in their code/packages and find where they were updating the EXPORT_FLAG to 1 outside of the Export form. There would not have been many occurrences. I’m fairly certain it was 1 offending line of code. It would have taken their development team 5 minutes to find. Instead, they wasted hours, or days, of their time going back and forth on the SR, requesting and analyzing traces and data collections, and my time dealing with their nonsense. All for nothing.

In the end, we closed the two SRs, without resolution, and will add a 3 line update statement to our custom program.  Our program will adjust for Oracle’s apparent bug.

So aggravating.

  1. Mario
    January 10, 2018 at 8:12 PM

    I know….this is Oracle as it always been…well ….little worst than before.
    I need to ask you Ed if by any chance you know if there is feasible to query counts and adjustments for cycle counts for several years back.

    • January 11, 2018 at 11:59 AM

      Assuming that the count entries are still in the database table, it should be easy to write a select statement. I don’t have access to see the table columns right now and validate, I’d try putting the cce.creation_date column in the where clause with a date to compare against, and use the cce.adjustment_quantity in the select portion. You’d have to tie in the MTL_SYSTEM_ITEMS_B table to get item numbers.

  2. Abhai
    May 8, 2018 at 12:23 PM

    Ed, we have the exact same problem and your blog confirms my findings. Thank you! My question is, after you started updating the export_flag to 2 or NULL, are all the counts showing up on the approval screen?

    • May 8, 2018 at 12:34 PM

      Yes, all the counts shows up as expected.

  3. Abhai
    May 8, 2018 at 12:23 PM

    Also, is it possible for you to share the Oracle SR/Bug numbers?

    • May 8, 2018 at 12:35 PM

      I’ll take a look if I can find it later this evening.

    • May 9, 2018 at 12:40 PM

      3-13161972741 Cycle Count Entry not showing up in Cycle Count Approvals form
      3-13168940421 QUEUED Cycle count tasks not showing up in MSCA and going back to PENDING

  1. No trackbacks yet.

Leave a Reply to Ed Hayes Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: