Changing One Column In One Row Through WIP Mass Load Resulting In Many MLOG Changes (Doc ID 1548444.1)

Last updated on MAY 15, 2023

Applies to:

Symptoms

As part of the VCP Collections process, snapshots are used to capture changes in a customer's system and are later updated through the Refresh Collection Snapshot process. When a record is changed, these changes are captured in an MLOG$ table and then pulled to VCP during the Planning Data Pull.

The problem is that when a single record is updated on a discrete job through the WIP Mass Load program, many updates are being done to other components on the job resulting in huge numbers of changes in the MLOG table. Because of these needless changes, the peformance of the Refresh Collection Snapshots program has slowed to a crawl.

In the customer's situation, they have 1000s of jobs open at one time. They have a process in place where they can change certain item attributes. For example, WIP Supply Type, Supply Subinventory, Supply Locator, etc. These changes are made through a customer form that then updates the item attributes through the Item Open Interface. These changes then need to be reflected on all open jobs, so the WIP Mass Load program runs to update the appropriate attribute on the jobs. If the WIP Supply Type or Supply Subinventory change for item B, then all jobs with item B as a component must be updated through the WIP Mass Load. If the item is part of a bill that contains 100s of components, it appears (by looking at the MLOG tables) that all of these components are getting updated when in fact only 1 component is updated. In this example, the update is made to the WIP_REQUIREMENT_OPERATIONS table. The corresponding MLOG table is MLOG$_WIP_REQUIREMENT_OPER.



STEPS
-----------------------
1. Created 6 items
  a. BF-WML-A - Finished Goods template
  b. BF-WML-B - Purchased Items template
  c. BF-WML-C - Copy of BF-WML-B
  d. BF-WML-D - Copy of BF-WML-B
  e. BF-WML-E - Copy of BF-WML-B
  f. BF-WML-F - Copy of BF-WML-B

  IID Item Name
  ------ ---------
  485955 BF-WML-A
  485956 BF-WML-B
  485957 BF-WML-C
  485958 BF-WML-D
  485959 BF-WML-E
  485960 BF-WML-F
  
  All items were assigned to M1 (ORGANIZATION_ID 207).
  
2. Created routing for BF-WML-A with 3 operations
  Op 10 - Assembly
  Op 20 - Testing
  Op 30 - Packing
  
3. Created a bill of material for BF-WML-A and assigned all the other items as components. The component sequence is in the order listed above and all the items are used in Op 10. For each component, the WIP Supply Type is setup as Assembly Pull.

4. Created WIP job BF-WML-JOB-1 for assembly BF-WML-A
  - WIP_ENTITY_ID = 879074
  
5. Ran the Refresh Collection Snapshots program in Automatic mode for All WIP Snapshots to ensure that the WIP.MLOG$_WIP_REQUIREMENT_OPER table has 0 rows in it.

6. Used the following 2 scripts to insert a single record into the WIP_JOB_SCHEDULE_INTERFACE and WIP_JOB_DTLS_INTERFACE table. The intent here is to change the WIP_SUPPLY_TYPE from Assembly Pull (2) to Operation Pull (3) for BF-WML-B (IID 458956).

INSERT INTO wip_job_schedule_interface (
  created_by,
  creation_date,
  last_update_date,
  last_updated_by,
  group_id,
  process_phase,
  process_status,
  organization_id,
  load_type,
  wip_entity_id,
  header_id
  )
VALUES (
  1013436, -- CREATED_BY
  SYSDATE, -- CREATION_DATE
  SYSDATE, -- LAST_UPDATE_DATE
  1013436, -- LAST_UPDATED_BY
  10001, -- GROUP_ID
  2, -- PROCESS_PHASE
  1, -- PROCESS_STATUS
  207, -- ORGANIZATION_ID
  3, -- LOAD_TYPE
  879074, -- WIP_ENTITY_ID
  1111 -- HEADER_ID
  );
  
INSERT INTO wip_job_dtls_interface (
  created_by,
  creation_date,
  last_update_date,
  last_updated_by,
  group_id,
  load_type,
  substitution_type,
  operation_seq_num,
  parent_header_id,
  process_phase,
  process_status,
  inventory_item_id_old,
  wip_entity_id,
  wip_supply_type
  )
VALUES (
  1013436, -- CREATED_BY
  SYSDATE, -- CREATION_DATE
  SYSDATE, -- LAST_UPDATE_DATE
  1013436, -- LAST_UPDATED_BY
  10001, -- GROUP_ID
  2, -- LOAD_TYPE
  3, -- SUBSTITUTION_TYPE
  10, -- OPERATION_SEQ_NUM
  1111, -- PARENT_HEADER_ID
  2, -- PROCESS_PHASE
  1, -- PROCESS_STATUS
  485956, -- INVENTORY_ITEM_ID for BF-WML-B
  879074, -- WIP_ENTITY_ID for BF-WML-JOB-1
  2 -- WIP_SUPPLY_TYPE for Operation Pull (changing from Assembly Pull)
  );
  
7. Ran the WIP Mass Load program for Group ID 10010.

8. Checked the number of records in WIP.MLOG$_WIP_REQUIREMENT_OPER and found 16 records.

9. Running the process again (changing the WIP_SUPPLY_TYPE and the GROUP_ID) resulted in 16 more records.

10. Manually changing the WIP Supply Type through the UI only caused 1 record to be inserted into the MLOG table.

SELECT
  ml.m_row$$,
  wro.rowid,
  ml.snaptime$$,
  ml.dmltype$$,
  ml.old_new$$,
  ml.change_vector$$,
  wro.inventory_item_id,
  wro.operation_seq_num,
  TO_CHAR(last_update_date, 'DD-MON-YYYY HH24:MI:SS'),
  wro.wip_supply_type
FROM
  wip.mlog$_wip_requirement_oper ml,
  wip_requirement_operations wro
WHERE
  ml.m_row$$ = wro.ROWID
ORDER BY
  1;
 
Observations:
- 4 rows for INVENTORY_ITEM_ID 485956 (BF-WML-B)
- 3 rows for all other INVENTORY_ITEM_IDs
- Timestamp on LAST_UPDATE_DATE shows all records with basically the same time (within 1 second of each other)
- The CHANGE_VECTOR$$ seems to be the same for all 3 non-updated records and 3 of the 4 records for the item that was updated.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.