Notes

Objective of Document:

Merging of DT jobs(poi report override)

Steps:

1. When a file is uploaded from UI, file gets copied in shared location in etl folder.
2. After this job will be instantiated with the proc “DTExtract_Job_Instantiate”. DT job is first kept in INIT state, then in READY state for Scheduler class to pick it.
3. Once the job is picked it goes to ‘processIt’ function of DataTransformMessageBean.
4. Then proc will be called ‘get_upload_file_detail_POI’(new proc) which will get the name of all files uploaded.
5. It will merge all the files in a workbook where each sheet will refer to one .txt file uploaded and get stored in the etl/etl_map_combine_* folder.
6. Once the job is instantiated, proc “CPL_UPDATE_JOB_POI(new proc)” will be executed to update the details in the tables “ETL_FILE_COMBINE_UPLOAD_DETAIL”, etl_file_combine_map_detail”, “ETL_FILE_UPLOAD_DETAILS” and job table.
7. It will mark the current job with info column with (Merging Parent) and mark other jobs with (merged with ‘job_id’) and the job table with new transaction_pk(etl_map_combine_*).
8. After DT job will be picked and in first step, it will check if DT job is merging Parent, it mark all the merged job to complete.
9. For Email_report step of Stage_override & DD, we will use the table “ETL_FILE_UPLOAD_DETAILS” to get user_ID.

Java changes:

New File:

DTSchedulerJob.java in ejb/datatransformmdb package

Execute()
getAllFilesLists()
processFileUploadUtility()
mergeAllWorkBooks()
insertDetail() -> to call CPL_UPDATE_JOB_POI

Files to be modified:

1. DemandAllocationToDB.java in ejb/engineallocationmdb package

In the Email_report step one more condition will be added to check if DT job of corresponding DD job is merging parent or not, if it is then it will take user_ids from “ETL_FILE_UPLOAD_DETAILS” table.

2. StageOverrideController.java in ejb/stageoverridemdb package

In the Email_report step one more condition will be added to check if DT job of corresponding SO job is merging parent or not, if it is then it will take user_ids from “ETL_FILE_UPLOAD_DETAILS” table.

SQL changes:

get_upload_file_detail_POI

This proc will be called to get list of all the files name to be merged together.

CPL_UPDATE_JOB_POI

This will be executed to update the details in the tables “ETL_FILE_COMBINE_UPLOAD_DETAIL”, etl_file_combine_map_detail”, “ ETL_FILE_UPLOAD_DETAILS” and job table with new transaction_pk.
It will mark the current job with info column with (Merging Parent) and mark other jobs with (merged with ‘job_id’)