Salesforce to PeopleSoft: Application Sync

#   _____           _              _____        __      
#  |  __ \         (_)            |_   _|      / _|     
#  | |__) |___  ___ _ _ __   ___    | |  _ __ | |_ ___  
#  |  _  // _ \/ __| | '_ \ / _ \   | | | '_ \|  _/ _ \ 
#  | | \ \  __/ (__| | |_) |  __/  _| |_| | | | || (_) |
#  |_|  \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ 
#                    | |                                
#                    |_|

# Project Name - Salesforce to PeopleSoft: Application Sync

# Recipe URL - https://app.lingk.io/a/10932/tf/19185

# Description - 15 - Salesforce to PeopleSoft: Application Sync
                ## This recipe syncs Application Data from Salesforce to a PeopleSoft staging table. Applications are then reviewed and processed manually.

## Recipe Steps
                # 1. Read last recipe execution date time from env variable and select those sf applications created after that time.
                # 2. Insert current dateTime into env variable.
                # 3. Create array for education history, because there may be multiple education history per student.
                # 4. Create payload/json for PS API request.
                # 5. Post request to PS.

# Systems - Salesforce, Peoplesoft
# Connectors - HttpV2, Salesforce
# Data Flows - Single Direction
# Connection Type - API to API

# Data Models
## Peoplesoft Models Used:
#1. ADM_APPL_DATA

## Salesforce Objects Used:
#1. Account
#2. hed__Term__c
#3. hed__Application__c

# Add Recipe notes / Change log information here!

#    _____                            _                 
#   / ____|                          | |                
#  | |     ___  _ __  _ __   ___  ___| |_ ___  _ __ ___ 
#  | |    / _ \| '_ \| '_ \ / _ \/ __| __/ _ \| '__/ __|
#  | |___| (_) | | | | | | |  __/ (__| || (_) | |  \__ \
#   \_____\___/|_| |_|_| |_|\___|\___|\__\___/|_|  |___/
#

# CONNECTORS specify what data will be pulled into the in-memory database during processing

# Configure HTTP & Salesforce credentials in your Environment before running this recipe
  # HTTP Setup - https://help.lingk.io/en/articles/304-http-connector-setup-guide
  # Salesforce Setup - https://help.lingk.io/en/articles/267-salesforce-connector-setup-guide

###### Start - Salesforce Reader connectors ######

connectors:

## Salesforce Contact reader
-
    name: sfStudentReader
    type: salesforceReader
    delayedRead: true
    parameterizedBy: studentIdList
    properties:
      useBearerToken: true
      query: |
        SELECT 
          Id,
          FirstName,
          MiddleName,
          LastName,
          Birthdate,
          HomePhone,
          Casesafe_Contact_ID__c,
          hed__Former_First_Name__c,
          hed__Former_Last_Name__c,
          hed__Social_Security_Number__c,
          MailingCity,
          MailingCountry,
          MailingPostalCode,
          MailingState,
          MailingStreet,
          MobilePhone,
          OtherPhone,
          hed__Citizenship_Status__c,
          hed__Gender__c,
          hed__Preferred_Email__c,
          University_Student_ID__c,
          Preferred_First_Name__c,
          Preferred_Middle_Name__c,
          Preferred_Last_Name__c,
          hed__Deceased__c,
          Personal_Email__c,
          hed__AlternateEmail__c,
          hed__UniversityEmail__c,
          hed__WorkEmail__c,
          hed__WorkPhone__c,
          hed__PreferredPhone__c,
          hed__Mailing_County__c,
          hed__Ethnicity__c,
          hed__Race__c,
          hed__FERPA__c,
          FERPA_Restriction_Category__c,
          hed__Military_Service__c,
          hed__Financial_Aid_Applicant__c,
          Financial_Aid_Applicant_Date__c,
          University_Enrollment_Term__c,
          Academic_Advisor__c 
        FROM Contact 
        WHERE Id IN (
            {% for element in var.output %}
            '{{element.id}}'{% if not loop.last %},{% endif %}
            {% endfor %}
          )

schema:
      fields:
        - name: 'Id'
          type: 'string'
        - name: 'FirstName'
          type: 'string'
        - name: 'MiddleName'
          type: 'string'
        - name: 'LastName'
          type: 'string'
        - name: 'Birthdate'
          type: 'string'
        - name: 'HomePhone'
          type: 'string'
        - name: 'Casesafe_Contact_ID__c'
          type: 'string'
        - name: 'hed__Former_First_Name__c'
          type: 'string'
        - name: 'hed__Former_Last_Name__c'
          type: 'string'        
        - name: 'hed__Social_Security_Number__c'
          type: 'string'        
        - name: 'MailingCity'
          type: 'string'        
        - name: 'MailingCountry'
          type: 'string'   
        - name: 'MailingState'
          type: 'string'
        - name: 'MailingStreet'
          type: 'string'
        - name: 'MailingPostalCode'
          type: 'string'
        - name: 'MobilePhone'
          type: 'string'
        - name: 'OtherPhone'
          type: 'string'    
        - name: 'hed__Citizenship_Status__c'
          type: 'string'
        - name: 'hed__Gender__c'
          type: 'string'
        - name: 'hed__Preferred_Email__c'
          type: 'string'
        - name: 'University_Student_ID__c'
          type: 'string'
        - name: 'Preferred_First_Name__c'
          type: 'string'
        - name: 'Preferred_Middle_Name__c'
          type: 'string'
        - name: 'Preferred_Last_Name__c'
          type: 'string'
        - name: 'hed__Deceased__c'
          type: 'string'
        - name: 'Personal_Email__c'
          type: 'string'
        - name: 'hed__AlternateEmail__c'
          type: 'string'        
        - name: 'hed__UniversityEmail__c'
          type: 'string'
        - name: 'hed__WorkEmail__c'
          type: 'string'        
        - name: 'hed__WorkPhone__c'
          type: 'string'        
        - name: 'hed__PreferredPhone__c'
          type: 'string'        
        - name: 'hed__Mailing_County__c'
          type: 'string'   
        - name: 'hed__Ethnicity__c'
          type: 'string'
        - name: 'hed__Race__c'
          type: 'string'
        - name: 'hed__FERPA__c'
          type: 'string'
        - name: 'FERPA_Restriction_Category__c'
          type: 'string'
        - name: 'hed__Military_Service__c'
          type: 'string'    
        - name: 'hed__Financial_Aid_Applicant__c'
          type: 'string'
        - name: 'Financial_Aid_Applicant_Date__c'
          type: 'string'
        - name: 'University_Enrollment_Term__c'
          type: 'string'
        - name: 'Academic_Advisor__c'
          type: 'string'

## Salesforce Application Reader
## Selects applications created after last recipe run date time
-
    name: sfApplicationReader
    type: salesforceReader
    delayedRead: true
    properties:
      useBearerToken: true
      query: |
        SELECT 
          Id,
          HS_Graduation_Year__c,
          hed__Applicant__c,
          hed__Application_Date__c,
          hed__Application_Type__c,
          hed__Term__c,
          Mid_State_Application_Number__c,
          CreatedDate,
          hed__Applying_To__c,
          hed__Initial_Creation_Date__c,
          Application_Campus__c,
          Application_Action_Date__c,
          Highest_Degree_Parent__c,
          Application_Previously_Attended_University__c,
          Application_Nursing_Assistant_Complete__c,
          Application_WI_Caregiver__c,
          Other_High_School_Name__c,
          High_School_State__c,
          hed__Term__r.PeopleSoft_Term_ID__c,
          hed__Applying_To__r.PeopleSoft_Academic_Program_ID__c 
        FROM hed__Application__c 
        WHERE (Mid_State_Application_Number__c = '' OR Mid_State_Application_Number__c = NULL) 
         AND (hed__Applying_To__c != '' OR hed__Applying_To__c != NULL) 
         AND CreatedDate > {{executionContext.lastRun.lastSuccessfulRunDate}}

schema:
      fields:
        - name: 'Id'
          type: 'String'
        - name: 'HS_Graduation_Year__c'
          type: 'string'
        - name: 'hed__Applicant__c'
          type: 'string' 
        - name: 'hed__Application_Date__c'
          type: 'string' 
        - name: 'hed__Application_Type__c'
          type: 'string' 
        - name: 'hed__Term__c'
          type: 'string'
        - name: 'Mid_State_Application_Number__c'
          type: 'String'
        - name: 'CreatedDate'
          type: 'string'
        - name: 'hed__Applying_To__c'
          type: 'string' 
        - name: 'hed__Initial_Creation_Date__c'
          type: 'string' 
        - name: 'Application_Campus__c'
          type: 'string' 
        - name: 'Application_Action_Date__c'
          type: 'string'
        - name: 'Highest_Degree_Parent__c'
          type: 'string'
        - name: 'Application_Previously_Attended_University__c'
          type: 'string'
        - name: 'Application_Nursing_Assistant_Complete__c'
          type: 'string'
        - name: 'Application_WI_Caregiver__c'
          type: 'string'
        - name: 'Other_High_School_Name__c'
          type: 'string'
        - name: 'High_School_State__c'
          type: 'string'
        - name: 'hed__Term__r.PeopleSoft_Term_ID__c'
          type: 'string'
        - name: 'hed__Applying_To__r.PeopleSoft_Academic_Program_ID__c'
          type: 'string'

## Salesforce Education History Reader
-
    name: sfEducationHistoryReader
    type: salesforceReader
    delayedRead: true
    parameterizedBy: studentIdList
    properties:
      useBearerToken: true
      query: |
        SELECT 
          Id,
          hed__Account__c,
          hed__Contact__c,
          hed__End_Date__c,
          hed__Degree_Earned__c,
          hed__GPA__c,
          hed__Graduation_Date__c,
          PeopleSoft_ACAD_HISTORY_EXT_ORG_ID__c,
          hed__Account__r.PeopleSoft_External_Org_ID__c 
        FROM hed__Education_History__c 
        WHERE hed__Contact__c IN ( 
            {% for element in var.output %} 
            '{{element.id}}'{% if not loop.last %},{% endif %} 
            {% endfor %} 
        )

schema:
      fields:
        - name: 'Id'
          type: 'string'
        - name: 'hed__Account__c'
          type: 'string'  
        - name: 'hed__Contact__c'
          type: 'string'
        - name: 'hed__End_Date__c'
          type: 'string'
        - name: 'hed__Degree_Earned__c'
          type: 'string'
        - name: 'hed__Graduation_Date__c'
          type: 'string'
        - name: 'PeopleSoft_ACAD_HISTORY_EXT_ORG_ID__c'
          type: 'string'
        - name: 'hed__Account__r.PeopleSoft_External_Org_ID__c'
          type: 'string'
        - name: 'hed__GPA__c'
          type: 'string'

## Salesforce Academic program (Account) Reader
-
  name: sfAcademicProgramReader
  type: salesforceReader
  delayedRead: false
  properties:
    useBearerToken: true
    query: 
      SELECT 
        Id,            
        PeopleSoft_Academic_Program_ID__c 
      FROM Account
      WHERE RecordTypeId IN (SELECT Id FROM RecordType WHERE developerName = 'Academic_Program' AND SobjectType = 'Account')  
    schema:
      fields:
      - name: 'Id'
        type: 'string'     
      - name: 'PeopleSoft_Academic_Program_ID__c'
        type: 'string'

## Salesforce Term Reader
-
    name: sfTermReader
    type: salesforceReader
    delayedRead: true
    properties:
      useBearerToken: true
      query: 
        SELECT 
          Id,
          PeopleSoft_Term_ID__c 
        FROM hed__Term__c
    schema:
      fields:
        - name: 'Id'
          type: 'string'
        - name: 'PeopleSoft_Term_ID__c'
          type: 'string'

###### End - Salesforce Reader connectors ######

###### Start - HTTP Reader connectors ######

- 
    name: postApplicationToPS
    type: httpReader
    delayedRead: true
    parameterizedBy: peopleSoftApiRequest
    properties:
      url: https://{{env.url_host}}/PSIGW/RESTListeningConnector/{{env.val}}/ZW_SF_APPLICATION.v1/
      headersJson: >
        { 
          "Content-Type": "application/json",
          "Authorization": "{{env.api_token}}"
        } 
      httpMethod: POST
      body: |
        {{var|tojson}}

###### End - HTTP Reader connectors ######

###### Start - LocalFile Writer connectors ######

-   
    name: lastSuccessfulRun
    type: localFileWriter
    format: csvWriter
    properties:
      fileName: "lastSuccessfulRun.csv"

###### End - LocalFile Writer connectors ######

#   _______        _        
#  |__   __|      | |       
#     | | __ _ ___| | _____ 
#     | |/ _` / __| |/ / __|
#     | | (_| \__ \   <\__ \
#     |_|\__,_|___/_|\_\___/

# Batch task used for parameterized by statements/connectors

tasks:
-
    name: batchTask
    type: dataOperation 
    function: batch   
    parameters:
       inputBatchGroupBy: # required
         id: desc      
       inputBatchSize: 200 # inputBatchSize # required (to test)
       inputBatchFields: "id" # comma delimited for other fields #inputBatchFields #required
       batchedColumnAlias: output # outputColumnName - optional with a default of "output"
       # inputTable: # optional
       # result: #optional

#   ______                         _       
#  |  ____|                       | |      
#  | |__ ___  _ __ _ __ ___   __ _| |_ ___ 
#  |  __/ _ \| '__| '_ ` _ \ / _` | __/ __|
#  | | | (_) | |  | | | | | | (_| | |_\__ \
#  |_|  \___/|_|  |_| |_| |_|\__,_|\__|___/

readFormats:
-
    name: csv
    type: delimited
    properties:
      delimiter: ','
      header: true
      inferSchema: true

writeFormats:
-   
    name: csvWriter
    type: delimited
    properties:
      quoteAllFields: true
      delimiter: ','
      header: true

#    _____ _        _                            _       
#   / ____| |      | |                          | |      
#  | (___ | |_ __ _| |_ ___ _ __ ___   ___ _ __ | |_ ___ 
#   \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __|
#   ____) | || (_| | ||  __/ | | | | |  __/ | | | |_\__ \
#  |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/

# STATEMENTS specify how the data should be processed while in memory

statements:

#******************************************************************** D I S C L A I M E R ***********************************************************************************************
#                                                                                                                                                                                       * 
# Note that in an effort to keep recipes optimized for DPH (Data Processing Hours), print statements should be commented out after development has concluded for a recipe.              *
# For more information on DPH optimization, please visit the following help article - https://help.lingk.io/en/articles/212-minimizing-data-processing-hours-on-the-lingk-platform      *
#                                                                                                                                                                                       * 
#******************************************************************** D I S C L A I M E R ***********************************************************************************************

## Read last recipe execution date time from env variable and select those sf applications created after that time.
  - statement: (lastRun) => SELECT "{{env.vars.inboundApplicationLastRunTime}}"  `lastSuccessfulRunDate`
  #- statement: print lastRun
  - statement: INSERT lastRun INTO executionContext

- statement : REFRESH sfApplicationReader
  #- statement : PRINT sfApplicationReader
  - statement : (sfApplicationReaderCount) => SELECT COUNT(*) FROM sfApplicationReader
  #- statement : PRINT sfApplicationReaderCount

- statement : |
      (sfStudentIdList) =>
          SELECT DISTINCT 
            hed__Applicant__c                               `id`
          FROM sfApplicationReader
          WHERE IFNULL(hed__Applicant__c, '') != ''

- statement : |
        execute task 
            -- name batchTask
            --inputTable sfStudentIdList
            --outputBatchTable studentIdList
            --result resultStatus

- statement : REFRESH sfStudentReader
  #- statement : PRINT sfStudentReader
  - statement : (sfStudentReaderCount) => SELECT COUNT(*) FROM sfStudentReader
  #- statement : PRINT sfStudentReaderCount

- statement : REFRESH sfEducationHistoryReader
  #- statement : PRINT sfEducationHistoryReader
  - statement : (sfEducationHistoryReaderCount) => SELECT COUNT(*) FROM sfEducationHistoryReader
  #- statement : PRINT sfEducationHistoryReaderCount

## Create array for education history, because there may be multiple education history per student
  - statement: |
      (educationHistoryArray) =>
          SELECT
            hed__Contact__c,
            collect_list(
              named_struct(
                "Id", `Id`,
                "hed__Account__c", IFNULL(`hed__Account__r.PeopleSoft_External_Org_ID__c`, ''),
                "hed__Contact__c", IFNULL(`hed__Contact__c`, ''),
                "hed__End_Date__c", IFNULL(`hed__End_Date__c`, ''),
                "hed__Degree_Earned__c", IFNULL(`hed__Degree_Earned__c`, ''),
                "hed__Graduation_Date__c", IFNULL(`hed__Graduation_Date__c`, ''),
                "PeopleSoft_ACAD_HISTORY_EXT_ORG_ID__c", IFNULL(`PeopleSoft_ACAD_HISTORY_EXT_ORG_ID__c`, ''),
                "hed__GPA__c", IFNULL(`hed__GPA__c`, '')
              )
            )                                                                       `educationHistory`
          FROM sfEducationHistoryReader
          GROUP BY hed__Contact__c
  #- statement: print educationHistoryArray

- statement: REFRESH sfAcademicProgramReader
  - statement: REFRESH sfTermReader

## Create json for PS api request
  # CASE function works as a dictionary and retrieve a specific value depending to the type field value pulled from the table as long as it is not null.
  # The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table)
  - statement : |
      (studentSfApplicationInfo) =>
          SELECT
            IFNULL(TRIM(app.Id), '')                                            `applicationId`,
            IFNULL(TRIM(app.HS_Graduation_Year__c), '')                         `HS_Graduation_Year__c`,
            IFNULL(TRIM(app.hed__Applicant__c), '')                             `hed__Applicant__c`,
            IFNULL(TRIM(app.hed__Application_Date__c), '')                      `hed__Application_Date__c`,
            IFNULL(TRIM(app.hed__Application_Type__c), '')                      `hed__Application_Type__c`,
            IFNULL(TRIM(app.`hed__Term__r.PeopleSoft_Term_ID__c`), '')          `hed__Term__c`,
            IFNULL(TRIM(app.Mid_State_Application_Number__c), '')               `Mid_State_Application_Number__c`,
            IFNULL(TRIM(app.CreatedDate), '')                                   `CreatedDate`,
            IFNULL(TRIM(app.`hed__Applying_To__r.PeopleSoft_Academic_Program_ID__c`), '')      
                                                                                `hed__Applying_To__c`,
            IFNULL(TRIM(app.hed__Initial_Creation_Date__c), '')                 `hed__Initial_Creation_Date__c`,
            CASE IFNULL(TRIM(app.Application_Campus__c), '')
              WHEN 'Adams Friendship' THEN 'AF'
              WHEN 'Marshfield' THEN 'MF'
              WHEN 'Stevens Point' THEN 'SP'
              WHEN 'Wisconsin Rapids' THEN 'WR'
              WHEN 'Online Classes Only' THEN 'VC'
            ELSE IFNULL(TRIM(app.Application_Campus__c), '')
            END                                                                 `Application_Campus__c`,
            IFNULL(TRIM(app.Application_Action_Date__c), '')                    `Application_Action_Date__c`,

IFNULL(TRIM(app.`hed__Term__r.PeopleSoft_Term_ID__c`), '')          `PeopleSoft_Term_ID__c`,

IFNULL(TRIM(app.Highest_Degree_Parent__c), '')                      `Highest_Degree_Parent__c`,
            IFNULL(TRIM(app.Application_Previously_Attended_University__c), '')       `Application_Previously_Attended_University__c`,

CASE WHEN IFNULL(TRIM(UPPER(app.Application_Nursing_Assistant_Complete__c)), '') = 'TRUE' THEN 'Yes'
              ELSE 'No' END                                                     `Application_Nursing_Assistant_Complete__c`,

CASE WHEN IFNULL(TRIM(UPPER(app.Application_WI_Caregiver__c)), '') = 'TRUE' THEN 'Yes'
              ELSE 'No' END                                                     `Application_WI_Caregiver__c`,

IFNULL(TRIM(app.Other_High_School_Name__c), '')                     `Other_High_School_Name__c`,
            IFNULL(TRIM(app.High_School_State__c), '')                          `High_School_State__c`,

IFNULL(TRIM(st.Birthdate), '')                                      `Birthdate`,
            IFNULL(TRIM(st.Casesafe_Contact_ID__c), '')                         `Casesafe_Contact_ID__c`,
            IFNULL(TRIM(st.FirstName), '')                                      `FirstName`,
            IFNULL(TRIM(st.HomePhone), '')                                      `HomePhone`,
            IFNULL(TRIM(st.Id), '')                                             `contactId`,
            IFNULL(TRIM(st.LastName), '')                                       `LastName`,
            IFNULL(TRIM(st.hed__Former_First_Name__c), '')                      `hed__Former_First_Name__c`,
            IFNULL(TRIM(st.hed__Former_Last_Name__c), '')                       `hed__Former_Last_Name__c`,
            IFNULL(TRIM(st.hed__Social_Security_Number__c), '')                 `hed__Social_Security_Number__c`,
            IFNULL(TRIM(st.MailingCity), '')                                    `MailingCity`,
            IFNULL(TRIM(st.MailingPostalCode), '')                              `MailingPostalCode`,
            IFNULL(TRIM(st.MailingCountry), '')                                 `MailingCountry`,
            IFNULL(TRIM(st.MailingState), '')                                   `MailingState`,
            IFNULL(TRIM(st.MailingStreet), '')                                  `MailingStreet`,
            IFNULL(TRIM(st.MiddleName), '')                                     `MiddleName`,
            IFNULL(TRIM(st.MobilePhone), '')                                    `MobilePhone`,
            IFNULL(TRIM(st.OtherPhone), '')                                     `OtherPhone`,
            IFNULL(TRIM(st.hed__Citizenship_Status__c), '')                     `hed__Citizenship_Status__c`,
            IFNULL(TRIM(st.hed__Gender__c), '')                                 `hed__Gender__c`,
            IFNULL(TRIM(st.hed__Preferred_Email__c), '')                        `hed__Preferred_Email__c`,
            IFNULL(TRIM(st.University_Student_ID__c), '')                             `University_Student_ID__c`,
            IFNULL(TRIM(st.Preferred_First_Name__c), '')                        `Preferred_First_Name__c`,
            IFNULL(TRIM(st.Preferred_Middle_Name__c), '')                       `Preferred_Middle_Name__c`,
            IFNULL(TRIM(st.Preferred_Last_Name__c), '')                         `Preferred_Last_Name__c`,
            IFNULL(TRIM(st.hed__Deceased__c), '')                               `hed__Deceased__c`,
            IFNULL(TRIM(st.Personal_Email__c), '')                              `Personal_Email__c`,
            IFNULL(TRIM(st.hed__AlternateEmail__c), '')                         `hed__AlternateEmail__c`,
            IFNULL(TRIM(st.hed__UniversityEmail__c), '')                        `hed__UniversityEmail__c`,
            IFNULL(TRIM(st.hed__WorkEmail__c), '')                              `hed__WorkEmail__c`,
            IFNULL(TRIM(st.hed__WorkPhone__c), '')                              `hed__WorkPhone__c`,
            IFNULL(TRIM(st.hed__PreferredPhone__c), '')                         `hed__PreferredPhone__c`,
            IFNULL(TRIM(st.hed__Mailing_County__c), '')                         `hed__Mailing_County__c`,

CASE WHEN IFNULL(TRIM(st.hed__Ethnicity__c), '') = 'Hispanic or Latino' THEN 'HISPA'
            ELSE '' END                                                         `hed__Ethnicity__c`,

replace(
              replace(
                replace(
                  IFNULL(TRIM(st.hed__Race__c), ''), 'American Indian or Alaska Native', 'AMIND'),
                'Black or African American', 'BLACK'),
              'Native Hawaiian or Other Pacific Islander', 'PACIF')             `hed__Race__c`,

IFNULL(TRIM(st.hed__FERPA__c), '')                                  `hed__FERPA__c`,
            IFNULL(TRIM(st.FERPA_Restriction_Category__c), '')                  `FERPA_Restriction_Category__c`,
            IFNULL(TRIM(st.hed__Military_Service__c), '')                       `hed__Military_Service__c`,
            IFNULL(TRIM(st.hed__Financial_Aid_Applicant__c), '')                `hed__Financial_Aid_Applicant__c`,
            IFNULL(TRIM(st.Financial_Aid_Applicant_Date__c), '')                `Financial_Aid_Applicant_Date__c`,
            IFNULL(TRIM(st.University_Enrollment_Term__c), '')                        `University_Enrollment_Term__c`,
            IFNULL(TRIM(st.Academic_Advisor__c), '')                             `Academic_Advisor__c`

FROM sfApplicationReader app
          LEFT JOIN sfStudentReader st ON TRIM(st.Id) = TRIM(app.hed__Applicant__c)

#- statement : PRINT studentSfApplicationInfo
  - statement : (studentSfApplicationInfoCount) => SELECT COUNT(*) FROM studentSfApplicationInfo
  #- statement : PRINT studentSfApplicationInfoCount

# The "name_struct" function - creates a struct with the given field names and values.
  # The 'LEFT ANTI JOIN' function retrieves the rows from the first table (table after the FROM statement) that do not match with the rows on the second table (right table)
  - statement : 
      (peopleSoftApiRequest) =>
          SELECT
            named_struct(
              "Id", app.applicationId,
              "HS_Graduation_Year__c", app.HS_Graduation_Year__c,
              "hed__Applicant__c", app.hed__Applicant__c,
              "hed__Application_Date__c", app.hed__Application_Date__c,
              "hed__Application_Type__c", "UGrad - Degree Seeking Program",
              "hed__Term__c", app.hed__Term__c,
              "hed__Applying_To__c", app.hed__Applying_To__c,
              "hed__Initial_Creation_Date__c", app.hed__Initial_Creation_Date__c,
              "Mid_State_Application_Number__c", app.Mid_State_Application_Number__c,
              "Application_Campus__c", app.Application_Campus__c,
              "Application_Action_Date__c", app.Application_Action_Date__c,
              "PeopleSoft_Term_ID__c", app.PeopleSoft_Term_ID__c,
              "Highest_Degree_Parent__c", app.Highest_Degree_Parent__c,
              "Application_Nursing_Assistant_Complete__c", app.Application_Nursing_Assistant_Complete__c,
              "Application_WI_Caregiver__c", app.Application_WI_Caregiver__c,
              "Other_High_School_Name__c", app.Other_High_School_Name__c,
              "High_School_State__c", app.High_School_State__c
            )                                                                         `application`,

named_struct(
              "Birthdate", app.Birthdate,
              "Casesafe_Contact_ID__c", app.Casesafe_Contact_ID__c,
              "FirstName", app.FirstName,
              "HomePhone", app.HomePhone,
              "Id", app.contactId,
              "LastName", app.LastName,
              "FormerFirstName", app.hed__Former_First_Name__c,
              "FormerLastName", app.hed__Former_Last_Name__c,
              "SSN", app.hed__Social_Security_Number__c,
              "MailingCity", app.MailingCity,
              "MailingPostalCode", app.MailingPostalCode,
              "MailingCountry", app.MailingCountry,
              "MailingState", app.MailingState,
              "MailingStreet", app.MailingStreet,
              "MiddleName", app.MiddleName,
              "MobilePhone", app.MobilePhone,
              "OtherPhone", app.OtherPhone,
              "hed__Citizenship_Status__c", app.hed__Citizenship_Status__c,
              "hed__Gender__c", app.hed__Gender__c,
              "hed__Preferred_Email__c", app.hed__Preferred_Email__c,
              "University_Student_ID__c", app.University_Student_ID__c,
              "Preferred_First_Name__c", app.Preferred_First_Name__c,
              "Preferred_Middle_Name__c", app.Preferred_Middle_Name__c,
              "Preferred_Last_Name__c", app.Preferred_Last_Name__c,
              "hed__Deceased__c", app.hed__Deceased__c,
              "Personal_Email__c", app.Personal_Email__c,
              "hed__AlternateEmail__c", app.hed__AlternateEmail__c,
              "hed__UniversityEmail__c", app.hed__UniversityEmail__c,
              "hed__WorkEmail__c", app.hed__WorkEmail__c,
              "hed__WorkPhone__c", app.hed__WorkPhone__c,
              "hed__PreferredPhone__c", app.hed__PreferredPhone__c,
              "hed__Mailing_County__c", app.hed__Mailing_County__c,
              "hed__Ethnicity__c", app.hed__Ethnicity__c,
              "hed__Race__c", app.hed__Race__c,
              "hed__FERPA__c", app.hed__FERPA__c,
              "FERPA_Restriction_Category__c", app.FERPA_Restriction_Category__c,
              "hed__Military_Service__c", app.hed__Military_Service__c,
              "hed__Financial_Aid_Applicant__c", app.hed__Financial_Aid_Applicant__c,
              "Financial_Aid_Applicant_Date__c", app.Financial_Aid_Applicant_Date__c,
              "University_Enrollment_Term__c", app.University_Enrollment_Term__c,
              "Academic_Advisor__c", app.Academic_Advisor__c
            )                                                                         `contact`,

edu.educationHistory                                                      `educationHistory`

FROM studentSfApplicationInfo app
          LEFT JOIN educationHistoryArray edu ON IFNULL(TRIM(app.hed__Applicant__c), '') = IFNULL(TRIM(edu.hed__Contact__c), '')

#- statement : PRINT peopleSoftApiRequest
  - statement : (peopleSoftApiRequestCount) => SELECT COUNT(*) FROM peopleSoftApiRequest
  #- statement : PRINT peopleSoftApiRequestCount

- statement: REFRESH postApplicationToPS
  #- statement: PRINT postApplicationToPS
#  - statement: (currentDateTime) => SELECT current_timestamp()  `inboundApplicationLastRunTime`

## Insert current dateTime into env variable.
  # - statement: (currentDateTime) => SELECT concat(date_format(from_utc_timestamp(current_timestamp(), 'America/New_York'), "yyyy-MM-dd"),'T01:01:00.000Z')  `inboundApplicationLastRunTime`
  # - statement: UPDATE currentDateTime INTO env.vars

#date_format(from_utc_timestamp(current_timestamp(), 'America/New_York')

# Add more statements to convert, join, aggregrate, transform, and integrate your data