Request reference

WO0000000787229

Description

To get the activity of email in each case, it is required to call API https://solvay-restapi.onbmc.com/api/rx/application/datapage?dataPageType=com.bmc.arsys.rx.application.record.datapage.RecordInstanceDataPageQuery&recorddefinition=com.bmc.dsm.social-lib:SCL_Posts

It is setup  on context parameter l_VAR_helix_case_activity_url

The example data of output is here

Tools: Talend

Detail job

• J062_Helix_case_activity

...

1. Check parameter l_VAR_helix_case_activity_reload that it is incremental or reload to get the name of HELIX_CASE_ACTIVITY_RELOAD or HELIX_CASE_ACTIVITY
2. Get the time from STG.incremetnal_load based on the name from point 1
3. Set the last load time
4. Set query and filter only post_type = system#email in order to get only email activity
5. Call the detail job and pass parameters
6. Call the standard job to upload the files from GCS to ODS
7. Delete the data if the category is not related to Procurement or Buy - Provisioning or it is not existing in case_itsm by this SQL below (for both ODS and STG)
   Delete FROM "+context.l_LOCAL_VAR_ODS_DATASET+"."+(String)globalMap.get("odsTable")+" where case_id in (select distinct case_id from "+context.l_LOCAL_VAR_ODS_DATASET+"."+(String)globalMap.get("odsTable")+" ca 
   left join ODS.ODS_HLX_0000_F001_I_H_Cases_ITSM itsm on ca.case_id = itsm.Request_ID
   where (Request_ID is null   
   or JSON_EXTRACT_SCALAR(jsonContent, \"$['po2_flag']\") = '2000' 
   or NOT (coalesce(JSON_EXTRACT_SCALAR(jsonContent, \"$['category_tier_1']\"),\"\") = \"Procurement\" 
            and coalesce(JSON_EXTRACT_SCALAR(jsonContent, \"$['category_tier_2']\"),\"\") = \"Buy - Provisioning\")));
8. Update max of created_date on HELIX_CASE_ACTIVITY_RELOAD or HELIX_CASE_ACTIVITY, Incase the last extraction has the max created_date same as before, it will send email to inform inform l_VAR_helix_case_activity_email_recipient  and then update the log. 

...

...