Problem

Recently, I encountered an interesting challenge. An existing integration used a counterparties table to create requests, storing the link between warehouse and legal entity (the company has several legal entities).

At a certain date, changes occurred—one legal entity was closed and replaced by a new one. Technically, it would be possible to add start and end date fields, but in this particular case, storing history was unnecessary since the warehouse-legal entity link is always unique.

The main difficulty was that the procedure uses the table, and in Oracle, you cannot create a true synonym inside a procedure that dynamically changes the table name based on conditions.

The integration used the XXT_BAIKAL_ADDRESS table, which stored warehouse addresses, FIAS codes, remarks, contact details linked to LOCATION_GID (warehouse ID in Oracle Transportation Management) for forming the JSON structure of the transportation request.

Solution: Using a Conditional VIEW

The solution we found was convenient and simple—create a view (VIEW) that automatically selects the required table depending on the current date:

CREATE OR REPLACE VIEW xxt_baikal_address_current AS
SELECT * FROM xxt_baikal_address
WHERE sysdate < COALESCE((select START_DATE_ACTIVE 
                 from FND_LOOKUP_VALUES_VL@PROD_APPS
                 where lookup_type = 'XXTSC_LEGAL_SUCCESSOR'
                 and description = 'Слияние Юрлиц'),sysdate-1)
UNION ALL
SELECT * FROM xxt_baikal_address_copy
WHERE sysdate >= (select START_DATE_ACTIVE 
                  from FND_LOOKUP_VALUES_VL
                  where lookup_type = 'XXT_LEGAL_SUCCESSOR'
                  and description = 'Слияние Юрлиц');

COALESCE is used as a safeguard: if the value is missing for any reason, the view will only return data from xxt_baikal_address.

How It Works

  1. Before the change date: the view returns data from the original table xxt_baikal_address
  2. After the change date: the view automatically switches to the new table xxt_baikal_address_copy
  3. Switch date: stored in the reference table FND_LOOKUP_VALUES_VL, making it easy to change without modifying code

Implementation

In our code, only one change was needed—replace references to the table xxt_baikal_address with xxt_baikal_address_current.

An alternative option would be to create a synonym with the original table name:

  1. Rename the existing table to xxt_baikal_address_old
  2. Name the new table xxt_baikal_address_new
  3. Create a VIEW named xxt_baikal_address

In this case, no changes to the procedure code would be required.

Advantages of the Approach

  1. Minimal code changes — only the table name changes or a synonym is created
  2. Automatic switching — after the specified date, the system starts using the new data automatically
  3. Flexibility — the switch date can be changed in the reference table without code changes
  4. No downtime — the solution is implemented without stopping the system
  5. Transparency — the VIEW code clearly shows the condition and switching logic

Conclusion

This approach seemed ideal for a situation where data needed to be "substituted" from a certain date without changing the procedure logic. Using a conditional view elegantly solved the problem and ensured correct integration operation after organizational changes in the company.

This method can be useful in many cases where business processes change from a certain date, and changes to existing code are undesirable or require significant effort.