Data Conversion Tool: VAT_ITEM: Not All Data Is Migrated From DC Schema To MERCH Table (Doc ID 3070667.1)

Last updated on FEBRUARY 04, 2025

Applies to:

Symptoms

On : 24.1 version, Data Conversion

ACTUAL BEHAVIOR
---------------
Data Conversion Tool: VAT_ITEM: Not All data is migrated from DC schema to MERCH table

Currently testing Hot Fix 24.1.401.3 applied for Data Conversion adding Standard VAT record for Zero VAT Items and identified an issue.

All business validations have succeeded, not all data from RMS01_DC.svc_vat_item are migrated to vat_item,
As per DC Tool, all records loaded to DC staging, validations successful, business validations successful. There are no records failing any validations at any step.

When comparing the record count between RMS01_DC.SVC_VAT_ITEM and VAT_ITEM (merch schema), 2 records are missing in the VAT_ITEM table. This seems to be a defect as records are not fully migrated to core table even without any validation failures.

The DC Tool report is misleading as well since it says that all records are migrated but in fact 2 records are missing in the core table.


STEPS
-----------------------
The issue can be reproduced at will with the following steps:

VAT_ITEM counts not matching with the corresponding records in the SVC_VAT_ITEM in RMS01_DC schema.

1. Run the data conversion process. Post conversion, verify the counts between the svc table
2. Verified that the vat_item*.dat files record count matches with the count of records in RMS01_DC.VAT_ITEM. All records loading to DC Schema staging table.
3. Verify the count of records in rms01_dc.svc_vat_item: SELECT COUNT(1) FROM RMS01_DC.svc_vat_item;
  Count=2318958
4. Verify the count in MFCS schema for vat_item table post execution of ITEM entity: SELECT COUNT(1) FROM RMS01.vat_item;
  Count: 2318956
5. As per above, 2 records are missing i.e. not migrated from RMS01_DC.SVC_VAT_ITEM to core table in MFCS.
6. Run the below query to fetch the records not migrated to VAT_ITEM
  SELECT * FROM RMS01_DC.svc_vat_item svi
  WHERE NOT EXISTS (SELECT item FROM vat_item vi WHERE vi.item=svi.item AND vi.active_date = svi.active_date AND vi.vat_code=svi.vat_code);
7. For the item id=’110102078’, rms01_dc.svc_vat_item has 3 records, but only 1 record has been migrated to vat_item. You would expect all the records in vat_item.

Records with active_date 06-FEB-2023 and 07-FEB-2023 are not migrated. This appears to be a defect.
Moreover, the conversion report that is extracted shows that all records for VAT_ITEM* are migrated to core tables. It does not show any failures. As per the report downloaded from DC Tool, it shows that 2318958 records have been moved to vat_item after business validations which is incorrect, potentially a defect with the tool which is misleading.

Cause

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