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
- Before the change date: the view returns data from the original table
xxt_baikal_address
- After the change date: the view automatically switches to the new table
xxt_baikal_address_copy
- 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:
- Rename the existing table to
xxt_baikal_address_old
- Name the new table
xxt_baikal_address_new
- Create a VIEW named
xxt_baikal_address
In this case, no changes to the procedure code would be required.
Advantages of the Approach
- Minimal code changes — only the table name changes or a synonym is created
- Automatic switching — after the specified date, the system starts using the new data automatically
- Flexibility — the switch date can be changed in the reference table without code changes
- No downtime — the solution is implemented without stopping the system
- 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.