/
SIC_PF_StructureCanonical_To_EBMR :-

SIC_PF_StructureCanonical_To_EBMR :-

Owned by Subrat Sandilya
Last updated: Sept 25, 2024
13 min read

Overview

  • This process flow is used for StructureCanonical To EBMR conversion.

  • This process flow is published as child flow which is called from Process flow “SIC_PF_Inbound_Input_To_Canonical “.

High-Level Description:

Key Components:

  1. File Handling:

    • LFS: Picks up EBMR files from the "in-progress" folder.

    • FTPS and FTPT: Handle file transfer, including moving files to error folders if issues occur.

  2. Data Mapping and Validation:

    • DM (Data Mapping): Several steps transform the file data, retrieve group policy numbers, and validate records.

    • Validation: EBMR validation rules (e.g., rules 0.x, 2.x, 3.x) are applied to ensure data accuracy, with thresholds and error handling incorporated.

  3. Database Interaction:

    • ADT (Advanced Database Target): Loads and updates phases, record counts, and status into the File Queue table.

    • Data related to both valid and erroneous records are inserted into respective database tables, such as the File Name Detail and Application Data tables.

  4. Error Handling:

    • DM merges error reports and TXLO parses them for logging and future reference.

    • Error records are inserted into the Application Data table, and notifications are sent in case of failures.

  5. Transformation:

    • Data is transformed from the canonical structure into the EBMR format, followed by further transformation into the UFF2 format.

    • The transformed EBMR files are then transferred to SFTP locations for further processing or UI display.

  6. Notifications:

    • Failure or threshold notifications are sent via email, alerting stakeholders of any issues that occur during the conversion or validation process.

Once the execution of the flow is done UI output is as follows:

Third Page - File Download.png

Detailed Description of the Flow:

 

image-20240925-072609.png

 

 

image-20240925-072635.png

 

 

image-20240925-072654.png

 

 

image-20240925-072609.png

 

 

image-20240925-072635.png

 

 

image-20240925-072654.png

 

  • LFS: “SIC_LFS_EBMR” picks EBMR files from inprogress.

  • DM: “SIC_DM_firstAvlbGroupPolicyNumber” gets first Group Policy Number.

  • DM: “SIC_DM_CreateGroupPolicyList” creates a Group Policy list.

  • DM: “SIC_DM_CreateFinalListOfUniqueGroupPolicyNumber” finalizes unique Group Policy numbers.

  • ADS: “SIC_EBMR_AdvSource_AccountStructure” validates account structure based on the 0.2 rule.

  • XMLO: “SIC_XMLO_Structure_Canonical_EBMR” structures Canonical EBMR layout.

  • DM: “SIC_DM_EBMR_DomainValue_Transfomation” Domain & Date conversion before sending it to validation mapping.

  • TXLO: “SIC_TXLO_AccountStructure_DbQuery” parses DB query report.

  • DM: “SIC_DM_EBMR_DomainValue_Transfomation” converts domain and date values.

  • TXLO: “SIC_TXLO_AccountStructure_DbQuery” parses the DB query report.

  • LFT: “SIC_LFT_DBQuery_EBMR” generates DB Query Report in the inprogress folder.

  • DM: “SIC_DM_EBMR_Update_FileQueue_ACCOUNT_STRUCTURE_CHECK” updates phase and EBMR record count in File Queue.

  • ADT: “SIC_DBT_FileQueue_Update” loads phase and EBMR record count into File Queue.\

  • DM: “SIC_DM_EBMR_Validation” validates EBMR rules 0.x, 2.x, 3.x, 5b.x (except 5b4).

  • DM: “SIC_DM_EBMR_Validation_Update_FileQueue” updates File Queue for phase, good record count, error record count, and pass percentage.

  • ADT: “SIC_DBT_FileQueue_Update” loads details into File Queue for phase, good record count, error record count, and pass percentage.

  • DM: “SIC_DM_PassPercentCheck_EBMR” sets the flag for threshold check and email message.

  • DM: “SIC_GenericErrorFlowMapping_EBMR” sets file received time on context.

  • DM: “SIC_DM_EBMR_Transfomation_Update_FileQueue_Threshold” updates the Core phase and status in the File Queue for threshold.

  • ADT: “SIC_DBT_FileQueue_Update” loads Core phase and status in File Queue for threshold.

  • FTPS: “SIC_FTPS_PickFromInputFolder” picks the source file from the input folder.

  • FTPT: “SIC_FTPT_PutToErrorFolder” drops the source file into the error folder.

  • MN: “SIC_PF_ThresholdNotMet_Failure_Email_Notification_EBMR” sends a failure email notification for threshold not met.

  • “SIC_DM_EBMR_CanonicalTo_UFF2_Transformation” Data mapping to Transform EBMR Canonical to UFF2 format.

  • “SIC_TXLO_UFF2” Advance Text layout to Parse UFF2 output file

  • DM: “SIC_DM_EBMR_Transformations” transforms data from Canonical XML Schema to CDI STAGE table.

  • ADT: “SIC_ADT_EBMR_INGESTION_STAGE” inserts data into Oracle DB.

  • XMLO: “SIC_XMLO_Structure_Canonical_EBMR” parses EBMR valid records.

  • SFTP: “SIC_SFTP_EBMR_Target” drops EBMR valid records on the SFTP location.

  • LFT: “SIC_LAN_V3_EBMR_Output” drops EBMR valid records on LAN location for UI display.

  • DM: “SIC_DM_OUTPUT_File_Name_Detail_Insert_EBMR” inserts the source file name and details into the File Name Detail table.ADT: “SIC_DBT_FIle_Name_Detail” loads the source file name and details into the File Name Detail table.

  • DM: “SIC_DM_EBMR_Transformations” transforms data from Canonical XML Schema to CDI STAGE table.

  • “SIC_SFTP_EBMR_UFF2_Output” FTP Target for Drop UFF2 output file on SIC SFTP location

  • “SIC_SFTP_dstage_EBMR_UFF2_Output” FTP Target for Drop UFF2 output file on Data Stage server location.

  • “SIC_LAN_UFF2_EBMR_Output” FTP Target for Drop UFF2 output file on LAN location to show it on UI

  • “SIC_DM_UFF2_OUTPUT_File_Name_Detail_Insert_EBMR” Data mapping to Insert UFF2 output File Name and Other File Details in File Name Detail table

  •  “SIC_DBT_FIle_Name_Detail_UFF2” Advance database target for Load UFF2 output File Name and other file details in File Name Detail table

  • DM: “SIC_DM_GOODRecord_ApplicationData_Insert_EBMR” inserts EBMR good records into the application data table.

  • ADT: “SIC_DBTL_ApplicationData_V3” loads EBMR good records into the ApplicationData table.

  • DM: “SIC_DM_EBMR_Transfomation_Update_FileQueue” updates Core status and phase in FileQueue.

  • ADT: “SIC_DBT_FileQueue_Update” loads Core status and phase in FileQueue.

  • DM: “SIC_DM_EBMR_merge” generates an EBMR error report.

  • TXLO: “SIC_TXLO_ErrorRecord_Output” parses the EBMR error report.

  • LFT: “SIC_LAN_V3_EBMR_ErrorFile” drops the EBMR error report on the shared location.

  • DM: “SIC_DM_Error_ApplicationData_Insert_EBMR” inserts error records into ApplicationData.

  • ADT: “SIC_DBTL_ApplicationData_V3” loads error records into ApplicationData.

  • ML: “SIC_PF_Canonical_To_EBMR_Failure_Email_Notification” sends email notifications on failures.

Activities Used In Flow:-

  1. LFS - Picking EBMR Structured file from Adeptia LAN: “SIC_LFS_EBMR” is used for Split EBMR Structured file  from in-progress folder 

Process Level Variables : 

 EBMRPhase : 'P0003' - 

PhaseID: 'P0001' - It is used to display the phase on UI.

Error_Code: E0065 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0065’ the error message is fetched from the solution database and displayed on UI.

ABC: 101787,361520 - 

Activity Level Variables : 

Filename: EBMR_orch_output_$$varfileName$$ - picking File name using this variable.

  1. DM - Getting the first Available Group Policy Number: “SIC_DM_firstAvlbGroupPolicyNumber” This mapping is used for getting the First Available Group Policy Number from the source file

Process Level Variables :

Error_Code: E0100 - It is used to display the Error message on UI. If this activity failed due to some reason then based on this code ‘E0100’ the error message is fetched from the solution database and displayed on UI.

  1. DM - To Create unique Group Policy number List: “SIC_DM_CreateGroupPolicyList”  Data Mapping  Creating GroupPolicy List

Process Level Variables : 

EBMRPhase : 'P0011' - 

PhaseID: 'P0018' - It is used to display the phase on UI.

Error_Code: E0075 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0075’ the error message is fetched from the solution database and displayed on UI.

  1. DM - Create Final List Of Unique Group Policy Numbers: “SIC_DM_CreateFinalListOfUniqueGroupPolicyNumber” This Data Mapping - Creating Final List Of Unique Group Policy Numbers after removing the comma.

Process Level Variables : 

Error_Code: E0101 - It is used to display the Error message on UI. If this activity failed due to some reason then based on this code ‘E0101’ the error message is fetched from the solution database and displayed on UI.

  1. ADS -  Account Structure lookup: “SIC_EBMR_AdvSource_AccountStructure” This is an Advanced Database Source for account structure validation that is 0.2 rule

  2. ADS - Get sfg_id And person_rel By GROUP_ID: “SIC_ADS_sfg_id_And_person_rel_By_GROUP_ID” This is  Advanced Database Source for Get sfg_id And person_rel By GROUP_ID.

  3. XMLO - Structure Canonical EBMR Layout: “SIC_XMLO_Structure_Canonical_EBMR” it is a Structure Canonical EBMR XMLO Layout for domain mapping

  4. DM - Domain and Date conversion: “SIC_DM_EBMR_DomainValue_Transfomation” Data Mapping - Domain & Date conversion before sending it to validation mapping

Process Level Variables : 

EBMRPhase : 'P0003' - 

PhaseID: 'P0017' - It is used to display the phase on UI.

Error_Code: E0067 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0067’ the error message is fetched from the solution database and displayed on UI.

  1. TXLO -  To parse the DB query report: “SIC_TXLO_AccountStructure_DbQuery” This text schema is used to parse the DB query report

  2. LFT - Generating DB Query Report: “SIC_LFT_DBQuery_EBMR” is Generating a DB Query Report on this lan target under in progress folder.

  3. DM - Update FQ for Account Structure phase and total EBMR record count: “SIC_DM_EBMR_Update_FileQueue_ACCOUNT_STRUCTURE_CHECK” is used for the Updating phase for Account Structure validation and total EBMR record count in File Queue table.

Process Level Variables : 

EBMRPhase : 'P0003' - 

PhaseID: 'P0017' - It is used to display the phase on UI.

Error_Code: E0070 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0070’ the error message is fetched from the solution database and displayed on UI.

  1. ADT - Load the updated phase and count into the table: “SIC_DBT_FileQueue_Update” Load the phase for Account Structure validation and total EBMR record count into the File Queue table

  2. DM - Common Framework Validation EBMR : “SIC_DM_EBMR_Validation” EBMR Validation mapping for validation rules 0.x, 2.x, 3.x, 5b.x(Except 5b4)

Process Level Variables : 

EBMRPhase : 'P0010' - 

PhaseID: 'P0017' - It is used to display the phase on UI.

Error_Code: E0072 - It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0072’ the error message is fetched from the solution database and displayed on UI.

  1. DM - Updating FileQueue table for Phase, Good Record Count, Error Record Count, Pass Percentage: “SIC_DM_EBMR_Validation_Update_FileQueue” it is Updating File Queue table for Phase, Good Record Count, Error Record Count, Core Pass Percentage

Process Level Variables : 

EBMRPhase : 'P0010' - 

PhaseID: 'P0017' - It is used to display the phase on UI.

Error_Code: E0073 - It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0073’ the error message is fetched from the solution database and displayed on UI.

TotalEBMRGoodCoverageCount: $$TotalEBMRGoodCoverageCount$$ - It is used to count the Total EBMR Good Coverage.

  1. ADT - Load details in FileQueue table for Phase, Good Record Count, Error Record Count, Pass Percentage: “SIC_DBT_FileQueue_Update” It is used to Load details in File Queue table for Phase, Good Record Count, Error Record Count, Pass Percentage.

  2. DM - Setting up a flag on context for threshold check and email message: “SIC_DM_PassPercentCheck_EBMR” It is Setting a flag on context for threshold check i.e good record percentage and email message 

  1. DM - Setting file received file time on context: “SIC_GenericErrorFlowMapping_EBMR” its Setting file received file time on context.

  1. DM - Update Core phase and Core Status in FileQueue: “SIC_DM_EBMR_Transfomation_Update_FileQueue_Threshold” It is used to Update Core phase and Core Status in FileQueue table for Threshold

  1. ADT - Load Core phase and Core Status in FileQueue: “SIC_DBT_FileQueue_Update” It is used to Load Core phase and Core Status in FileQueue table for Threshold

  2. FTPS - Pick file From Input Folder: “SIC_FTPS_PickFromInputFolder” It is used to  Pick source file From the Input Folder

  3. FTPT - Drop source file into Error Folder: “SIC_FTPT_PutToErrorFolder” It is used to Drop source file into Error Folder

  4. MN - Sending Email Notification for  Threshold Not Met: “SIC_PF_ThresholdNotMet_Failure_Email_Notification_EBMR” is used for Threshold Not Met Failure Email Notification

  5. DM - To Transform EBMR Canonical To UFF2 format: “SIC_DM_EBMR_CanonicalTo_UFF2_Transformation” Data mapping to Transform EBMR Canonical to UFF2 format.

Process Level Variables : 

DB_URL: $$URL$$ - Get DB Url CDI Stage Table.

DB_User_Name: $$UserName$$ - Get the User Name of the Database CDI stage table

DB_Password: $$Password$$ - Get Password of Database CDI stage table

PhaseID: 'P0017' - it is used to display the phase on UI.

EBMRPhase: 'P0010' 

Error_Code: E0137 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0076’ the error message is fetched from the solution database and displayed on UI.

  1. ADTXL - To parse UFF2 output file : “SIC_TXLO_UFF2” Advance Text layout to Parse UFF2 output file

  2. DM - Transforming the Data From Canonical XML Schema to CDI STAGE Table : “SIC_DM_EBMR_Transformations” It is used to Transformation From Canonical XML Schema to CDI STAGE table

Process Level Variables : 

DB_URL: $$URL$$ - Get DB Url CDI Stage Table.

DB_User_Name: $$UserName$$ - Get the User Name of the Database CDI stage table

DB_Password: $$Password$$ - Get Password of Database CDI stage table

PhaseID: 'P0018' - it is used to display the phase on UI.

EBMRPhase: 'P0011'  

Error_Code: E0076 -  It is used to display the Error message on UI. If this activity failed for some reason then on the basis of this code ‘E0076’ the error message is fetched from the solution database and displayed on UI.

  1. ADT - Load Data to CDI STAGE Table: “SIC_ADT_EBMR_INGESTION_STAGE” It is used to Insert into Oracle DB

  2. XMLO - EBMR Valid Record Layout: “SIC_XMLO_Structure_Canonical_EBMR” is used to Parsing EBMR Good record Coming From Validation Mapping

  3. SFTP - EBMR Good record: “SIC_SFTP_EBMR_Target” It is used to Drop the EBMR Good record file on the SIC SFTP location

  4. LFT - Drop EBMR Good record: “SIC_LAN_V3_EBMR_Output” It is used to Drop the EBMR Good record file on the LAN location to show it on UI

  5. DM - Insert Source File Name and Other File Details in File Name Detail table: “SIC_DM_OUTPUT_File_Name_Detail_Insert_EBMR” it is used to Insert Source File Name and Other File Details in File Name Detail table.

Process Level Variables : 

fileName : $$SIC_LAN_V3_EBMR_Output.params$$ - 

FILE_UNIQUE_ID: $$FILE_UNIQUE_ID$$ - Getting file Unique ID.

partnerName : $$partnerName$$ - Getting Partner Name

MasterAgreementNumber_GroupID : $$MasterAgreementNumber_GroupID$$ Getting MasterAgreementNumber_GroupID 

EBMRPhase: 'P0011'

PhaseID: 'P0018' - It is used to display the phase on the UI

Error_Code: E0088 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0088’ the error message is fetched from the solution database and displayed on UI.

  1. ADT - Load Source File Name and other file details in File Name Detail table: “SIC_DBT_FIle_Name_Detail” It is used to Load Source File Name and other file details in the File Name Detail table.

  2. UFF2 File Creation GateWay: enableUFF2GenerationFlag is true then it will create the UFF2 Transform file

  3. SFTP - Drop UFFT2 Output file On SFTP Location: “SIC_SFTP_EBMR_UFF2_Output” FTP Target for Drop UFF2 output file on SIC SFTP location

  4. SFTP - Drop to Data stage server location: “SIC_SFTP_dstage_EBMR_UFF2_Output” FTP Target for Drop UFF2 output file on Data Stage server location.

  5. LFT - Drop file on LAN location for GUI: “SIC_LAN_UFF2_EBMR_Output” FTP Target for Drop UFF2 output file on LAN location to show it on UI

  6. DM - Insert UFF2 File Name and Other File Details in File Name Detail table: “SIC_DM_UFF2_OUTPUT_File_Name_Detail_Insert_EBMR” Data mapping to Insert UFF2 output File Name and Other File Details in File Name Detail table

Process Level Variables : 

EBMRPhase : 'P0010'

PhaseID : 'P0017' - It is used to display the phase on UI.

Error_Code : E0142 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the bases of this code ‘E0090’ the error message fetch from solution database and display on UI.

  1. ADT - Load UFF2 File Name and other file details in File Name Detail table: “SIC_DBT_FIle_Name_Detail_UFF2” Advance database target fot Load UFF2 output File Name and other file details in File Name Detail table

  2. DM - InsertEBMR Good Records Into ApplicationData Table: “SIC_DM_GOODRecord_ApplicationData_Insert_EBMR” it is used to Insert EBMR Good Records Into ApplicationData Table to show it on UI

Process Level Variables : 

EBMRPhase: 'P0011'

PhaseID: 'P0018' - It is used to display the phase on UI.

Error_Code: E0090 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0090’ the error message is fetched from the solution database and displayed on UI.

  1. ADT - Load EBMR Good Records Into ApplicationData Table: “SIC_DBTL_ApplicationData_V3” it is used to Load EBMR Good Records Into ApplicationData Table for showing it on UI

  2. DM - Update Core status and phase in FileQueue : “SIC_DM_EBMR_Transfomation_Update_FileQueue” it is used to Update Core status to success and phase in FileQueue table

  3. ADT - Load Core status and phase in FileQueue : “SIC_DBT_FileQueue_Update” it is used to Load Core status to success and phase in FileQueue table

  4. DM - Generating EBMR Error Report : “SIC_DM_EBMR_merge” it is used to Generating EBMR Error Report

  5. TXLO - Parse The EBMR Error Report : “SIC_TXLO_ErrorRecord_Output” it is used to Parse The EBMR Error Report

  6. LFT - Drop The EBMR Error Report : “SIC_LAN_V3_EBMR_ErrorFile” it is used to Drop The EBMR Error Report on Shared Location so that the ErrorMerge Flow will be able to Pick the File.

  7. DM - Insert The Error Record Into The ApplicationData : “SIC_DM_Error_ApplicationData_Insert_EBMR” it is used to Insert The Error Record Into The ApplicationData

Process Level Variables : 

EBMRPhase : 'P0011' -

PhaseID: 'P0018' - It is used to display the phase on UI

Error_Code: E0082 -  It is used to display the Error message on UI. If this activity failed due to some reason then on the basis of this code ‘E0082’ the error message is fetched from the solution database and displayed on UI.

FILE_UNIQUE_ID: $$FILE_UNIQUE_ID$$ - Getting file unique ID.

  1. ADT - Load The Error Record Into The ApplicationData: “SIC_DBTL_ApplicationData_V3” it is used to Load The Error Record Into The ApplicationData

  2. ML - Sending Email Notification On Failures: “SIC_PF_Canonical_To_EBMR_Failure_Email_Notification” it is used to Sending Email Notification On Failures

Conclusion:

The SIC_PF_StructureCanonical_To_EBMR process flow is responsible for converting data from the Structure Canonical format to the EBMR format. It handles file retrieval, data transformation, validation, and error reporting. Key steps include retrieving EBMR files, validating group policy numbers and applying EBMR-specific rules, transforming the data, and updating database records. The flow also manages error handling by merging error reports and sending notifications in case of failures or unmet thresholds. Once validated, the EBMR files are transformed into UFF2 format and transferred to SFTP locations for further processing and UI display.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Related content