...

Tools: Talend

From source to ODS 

...

1. Connect to Salesforce (SFC)
2. Query SFC on the case table  ( select only required fields FROM  Case
   WHERE  CreatedDate >= 2020-01-01T00:00:00Z
   and PO2_CASE_Organization__c like '%ECO%' )
3. Write output to file in local PC
4. Put the file to bucket cs-ew1-prj-data-dm-marketing-dev-staging/Case/
5. Delete the output file in local PC
6. Write log on the main flow job

...

1. Check execution time format
2. Select data from DataOcean.V_sfc_case sfc and DataOcean.V_BWH_no_of_delivery_orders (script) 
   1. Create a template internal table (period x gbu x plant x serivity)

      1. calendar to have period in format YYYY-MM back to n month, which control by variable  l_VAR_IND_DASH_customer_complaint_nb_month_to_DM  

      2. gbu_mapping to have text and key, which get from variable l_VAR_IND_DASH_customer_complaint_gbu_mapping = internal table for gbu, add more in the variable if we have new gbu

      3. severity_mapping to have list of severity value = not specified, Low, High, Major, Medium, Anomaly, Critical and Standard which get from variable l_VAR_IND_DASH_customer_complaint_severity_mapping 

      4. site_cal to have all plant and gbu from  V_sfc_case sfc cross join with severity_mapping and calendar  to have template table that have all period, gbu, plant and serivity

   2. Create an aggregate internal table of customer complaint (cc_aggr ) from V_sfc_case sfc 
   3. Create an aggregate internal table of delivery (del_aggr ) from V_BWH_no_of_delivery_orders 
   4. Select the final data by calculate rolling 12 months 
3. Delete all data in DM.FACT_customer_complaints
4. Load data from point 2 to FACT_customer_complaints
5. In case of error, email inform DataOps (l_VAR_IND_DASH_email)
6. Update log

...