In this post, I am going to describe the Master Data Query utility that was developed using Postgres which is a part of an enterprise system platform that has an inherent capability to exchange data between systems.
The Master Data Query engine gives you a complete picture of any master record. It not only returns the data about the master record itself but drills down to child, grandchild data, and beyond.
For example, in a government system it could return the following data about a land parcel based on its legal land description, limits results to three levels deep, and return the data that was effective on May 31, 2023.
1. Details about the Land Parcel as of May 31, 2023:
1.1 Title holders of the land parcel as of May 31, 2023
1.1.1 Details about title holders
1.2 Oil wells located on the land parcel as of May 31, 2023
1.2.1 Revenue journal entries for the oil well
1.2.2 Production data for the oil well
1.3 Encumbrances registered against the land parcel as of May 31, 2023
1.3.1 Details about the holders of the encumbrances
The data returned for each entity is based on views automatically generated by the system based on the data dictionary.
Considering the procedure's power, flexibility, and usefulness, it is created with a small amount of code. There is a procedure to generate the master data index which is 164 lines of code and a function that returns the select statements for the data which is 320 lines of code.
https://github.com/bkjenner/3dEnterpriseSystems/blob/main/500%20Procedures/spsysMasterDataIndexGenerate.sql
https://github.com/bkjenner/3dEnterpriseSystems/blob/main/200%20Functions/fnsysMasterDataQuery.sql
The Master Data Query procedure is possible as a result of all of the critical components of the 3dES platform such as primary keys, data dictionary,
If you are interested in learning more about how the Master Data Query Procedure works, please check out www.3d-ess/videos (020.110 Master Data Query)
Thank you for your attention. I appreciate your likes, questions, and comments.