PeopleSoft to Salesforce: Application Sync

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

# Project Name - PeopleSoft to Salesforce: Application Sync

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

# Description - 16 - PeopleSoft to Salesforce: Application Sync
                # This recipe syncs approved applicant data from a PeopleSoft staging table back to Salesforce.

# Select all Applications from ADM_APPL_DATA, ADM_APPL_PROG, ADM_APPL_PLAN & ZW_SF_APP_HDR table in PeopleSoft where INSTITUTION = <Inst-Code>.
                # Select academic program table with the maximum EFFSEQ (which shows current applicant status).
                # Select academic plans respective to the academic program.
                # Reject applications for which EMPLID is blank.
                # Collect all needed information from PS tables. 
                # Reject records due to non-matching Contact and Account.
                # Collect & modify PS application data with salesforce Contact, Account and Term to get final application to process.
                # If record match by hed__Applicant__c & hed__Applying_To__c then update else insert.
                # Insert application requested fields with their SF id in the Lingk environment file.

# Systems - Peoplesoft, Salesforce
# 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. Contact

# Add Recipe notes / Change log information here!

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

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

connectors:

# 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 - HTTP Reader connectors ######

## PeopleSoft Person Connector
  ## HTTP V2 connector in comparison with V1, handles pagination through the connector properties
  ## Authentication and URL values are provided via environment variables  
    # documentation - https://help.lingk.io/en/articles/202-custom-environment-variables

## PeopleSoft Application Connector
- 
  name: peopleSoftAdmissionApplicationData
  type: httpV2Reader 
  properties: 
    httpMethod: POST
    headerJson: |
        {
        "Authorization":"Basic {{env.vars.apiAccessToken}}",
         "Content-Type":"application/json"
         }
    acceptInvalidCerts: true
    url: '{{env.vars.midStateInstanceURL}}'
    paginationType: RequestBody      
    body: | 
          {
              "isDebugMode": false,
              "rowLimit": 1000,
              "pageNumber": 1,
              "records": [
                  {
                      "recordName": "ADM_APPL_DATA",
                          "sqlWhereClause": " A.INSTITUTION = '{{env.inst-code}}' and SCC_ROW_UPD_DTTM >= %DateTimeIn('{{env.vars.outboundAppDateTime}}') "
                  },
                  {
                    "recordName":"ADM_APPL_PROG",
                    "parentRecordName": "ADM_APPL_DATA",
                    "useParentEffectiveDate":true
                  },
                  {
                    "recordName":"ADM_APPL_PLAN",
                    "parentRecordName": "ADM_APPL_PROG",
                    "useParentEffectiveDate":true
                  },
                  {
                    "recordName":"ZW_SF_APP_HDR",
                    "parentRecordName": "ADM_APPL_DATA",
                    "includeDescriptionsFor": ["ZW_SF_APP_STATUS", "ZW_SF_CAN_REASON"],
                      "excludeFields": [
                          "ZW_SF_IN_DATA",
                          "COMMENTS"
                      ]
                  }
              ]
          }
  schema:
    fields:
      - name: 'ACADEMIC_LEVEL'
        type: 'string'
      - name: 'ACAD_CAREER'
        type: 'string'
      - name: 'ACAD_YEAR'
        type: 'string'
      - name: 'ADMIT_TYPE'
        type: 'string'
      - name: 'ADM_APPL_CMPLT_DT'
        type: 'string'
      - name: 'ADM_APPL_COMPLETE'
        type: 'string'
      - name: 'ADM_APPL_CTR'
        type: 'string'
      - name: 'ADM_APPL_DT'
        type: 'string'
      - name: 'ADM_APPL_METHOD'
        type: 'string'
      - name: 'ADM_APPL_NBR'
        type: 'string'
      - name: 'ADM_APPL_PROG'
        type: 'object'
        objectSchema:
          name: objectSchemaSchema
          fields:
            - name: 'fields'
              type: 'array'
              items:
                name: 'arrayElement'
                type: 'object'
                objectSchema:
                  name: objectSchemaSchema
                  fields:
                    - name: 'ACAD_CAREER'
                      type: 'string'
                    - name: 'ACAD_LOAD_APPR'
                      type: 'string'
                    - name: 'ACAD_PROG'
                      type: 'string'
                    - name: 'ACAD_PROG_DUAL'
                      type: 'string'
                    - name: 'ACTION_DT'
                      type: 'string'
                    - name: 'ADMIT_TERM'
                      type: 'string'
                    - name: 'ADM_APPL_NBR'
                      type: 'string'
                    - name: 'ADM_APPL_PLAN'
                      type: 'object'
                      objectSchema:
                        name: objectSchemaSchema
                        fields:
                          - name: 'fields'
                            type: 'array'
                            items:
                              name: 'arrayElement'
                              type: 'object'
                              objectSchema:
                                name: objectSchemaSchema
                                fields:
                                  - name: 'ACAD_CAREER'
                                    type: 'string'
                                  - name: 'ACAD_PLAN'
                                    type: 'string'
                                  - name: 'ADM_APPL_NBR'
                                    type: 'string'
                                  - name: 'APPL_PROG_NBR'
                                    type: 'long'
                                  - name: 'DECLARE_DT'
                                    type: 'string'
                                  - name: 'EFFDT'
                                    type: 'string'
                                  - name: 'EFFSEQ'
                                    type: 'long'
                                  - name: 'EMPLID'
                                    type: 'string'
                                  - name: 'PLAN_SEQUENCE'
                                    type: 'long'
                                  - name: 'REQ_TERM'
                                    type: 'string'
                                  - name: 'SCC_ROW_ADD_DTTM'
                                    type: 'string'
                                  - name: 'SCC_ROW_ADD_OPRID'
                                    type: 'string'
                                  - name: 'SCC_ROW_UPD_DTTM'
                                    type: 'string'
                                  - name: 'SCC_ROW_UPD_OPRID'
                                    type: 'string'
                                  - name: 'SSR_APT_INSTANCE'
                                    type: 'long'
                                  - name: 'SSR_YR_OF_PROG'
                                    type: 'string'
                                  - name: 'STDNT_CAR_NBR'
                                    type: 'long'
                          - name: 'objectName'
                            type: 'string'
                          - name: 'objectType'
                            type: 'string'
                    - name: 'APPL_PROG_NBR'
                      type: 'long'
                    - name: 'CAMPUS'
                      type: 'string'
                    - name: 'EFFDT'
                      type: 'string'
                    - name: 'EFFSEQ'
                      type: 'long'
                    - name: 'EMPLID'
                      type: 'string'
                    - name: 'EXP_GRAD_TERM'
                      type: 'string'
                    - name: 'INSTITUTION'
                      type: 'string'
                    - name: 'JOINT_PROG_APPR'
                      type: 'string'
                    - name: 'PROG_ACTION'
                      type: 'string'
                    - name: 'PROG_REASON'
                      type: 'string'
                    - name: 'PROG_STATUS'
                      type: 'string'
                    - name: 'REQ_TERM'
                      type: 'string'
                    - name: 'SCC_ROW_ADD_DTTM'
                      type: 'string'
                    - name: 'SCC_ROW_ADD_OPRID'
                      type: 'string'
                    - name: 'SCC_ROW_UPD_DTTM'
                      type: 'string'
                    - name: 'SCC_ROW_UPD_OPRID'
                      type: 'string'
                    - name: 'SSR_APT_INSTANCE'
                      type: 'long'
                    - name: 'SSR_COHORT_ID'
                      type: 'string'
                    - name: 'SSR_RS_CANDIT_NBR'
                      type: 'string'
                    - name: 'SSR_SHIFT'
                      type: 'string'
                    - name: 'SSR_YR_OF_PROG'
                      type: 'string'
                    - name: 'STDNT_CAR_NBR'
                      type: 'long'
      - name: 'EMPLID'
        type: 'string'
      - name: 'INSTITUTION'
        type: 'string'
      - name: 'SCC_ROW_ADD_DTTM'
        type: 'string'
      - name: 'SCC_ROW_ADD_OPRID'
        type: 'string'
      - name: 'SCC_ROW_UPD_DTTM'
        type: 'string'
      - name: 'SCC_ROW_UPD_OPRID'
        type: 'string'
      - name: 'SSF_GL_SENT_AMT'
        type: 'string'
      - name: 'STDNT_CAR_NBR'
        type: 'long'
      - name: 'ZW_SF_APP_HDR'
        type: 'object'
        objectSchema:
          name: objectSchemaSchema
          fields:
            - name: 'fields'
              type: 'array'
              items:
                name: 'arrayElement'
                type: 'object'
                objectSchema:
                  name: objectSchemaSchema
                  fields:
                    - name: 'EMPLID'
                      type: 'string'
                    - name: 'ZW_CONTACT_ID'
                      type: 'string'
      - name: 'rowNumber'
        type: 'long'

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

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

## Salesforce Academic Program Reader
-
    name: sfAccountReader
    type: salesforceReader
    delayedRead: true
    parameterizedBy: batchedAcdPlan
    properties:
      useBearerToken: true
      query: 
        SELECT 
          Id,
          Name,
          PeopleSoft_Academic_Program_ID__c 
        FROM Account
        WHERE PeopleSoft_Academic_Program_ID__c IN (
            {% for element in var.output %}
            '{{element.id}}'{% if not loop.last %},{% endif %}
            {% endfor %}
          ) 
    schema:
      fields: 
        - name: 'Id'
          type: 'string'
        - name: 'Name'
          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,
          Name,
          PeopleSoft_Term_ID__c 
        FROM hed__Term__c

schema:
      fields:
        - name: 'Id'
          type: 'string'
        - name: 'Name'
          type: 'string'
        - name: 'PeopleSoft_Term_ID__c'
          type: 'string'

## Salesforce Contact Reader
-
    name: sfContactReader
    type: salesforceReader
    delayedRead: true
    parameterizedBy: batchedStudentsId
    properties:
      useBearerToken: true
      query: 
        SELECT 
          Id,
          FirstName,
          LastName,
          MiddleName,
          University_Student_ID__c,
          Casesafe_Contact_ID__c  
        FROM Contact
        WHERE University_Student_ID__c 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: 'LastName'
          type: 'string'
        - name: 'MiddleName'
          type: 'string'
        - name: 'Casesafe_Contact_ID__c'
          type: 'string'
        - name: 'University_Student_ID__c'
          type: 'string'

## Salesforce Application Reader
-
    name: sfApplicationReader
    type: salesforceReader
    delayedRead: true
    parameterizedBy: batchedApplicationList
    properties:
      useBearerToken: true
      query: |
        SELECT 
          Id,
          hed__Applicant__c,
          hed__Application_Type__c,
          hed__Term__c,
          Mid_State_Application_Number__c,
          hed__Applying_To__c 
        FROM hed__Application__c  
        WHERE Mid_State_Application_Number__c IN (
            {% for element in var.output %}
            '{{element.id}}'{% if not loop.last %},{% endif %}
            {% endfor %}
          ) 
    schema:
      fields:
        - name: 'Id'
          type: 'String'
        - name: 'hed__Applicant__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: 'hed__Applying_To__c'
          type: 'String'

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

###### Start - Salesforce Writer connectors ######

## Salesforce Application Writer
-
    name: sfApplicationWriter
    type: salesforceBulkWriter
    onError: continue
    isSerial: true
    properties:
      onError: continue
      useBearerToken: true
      sfObject: hed__Application__c
      batchSize: 100

###### End - Salesforce Writer connectors ######

###### Start - Local Writer connectors ######

# Local File Writer
-   
    name: finalMappedRecordFile
    type: localFileWriter
    format: csvWriter
    properties:
      fileName: "finalMappedApplicationRecords.csv"

###### End - Local Writer connectors ######

###### Start - Lingk Writer connectors ######

## Lingk event writer
- 
    name: signalWriter
    type: lingkWriter
    properties: 
      apiEndpoint: https://www.lingkapis.com/v1/{{env.inst-code}}/sign-up/events

###### End - Lingk 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: text
    type: text

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 ***********************************************************************************************

- statement: refresh peopleSoftAdmissionApplicationData
  #- statement: print peopleSoftAdmissionApplicationData
  - statement: (peopleSoftAdmissionApplicationDataCount) => SELECT COUNT(*) FROM peopleSoftAdmissionApplicationData
  #- statement: print peopleSoftAdmissionApplicationDataCount

# inline function - flatten the array to make it a basic table  
  - statement: (academicProgramData) => SELECT inline(ADM_APPL_PROG.fields) FROM peopleSoftAdmissionApplicationData

## Select academic program table with the maximum EFFSEQ (which shows current applicant status)
  ## Using the ROW_NUMBER function assigns a unique, sequential number to each row, starting with one,
   # according to the ordering of rows within the partition from the information pulled in the previous statement for the academicProgramData
  ## The PARTITION BY is dividing the rows into small partitions by EFFSEQ preference field
  - statement: |
      (groupingAcadData) => 
        SELECT
          *,
          row_number() OVER (PARTITION BY EMPLID, ADM_APPL_NBR ORDER BY EFFSEQ DESC) AS Row
        FROM academicProgramData

- statement: (finalAcademicProgramData) => SELECT * FROM groupingAcadData WHERE Row = 1
  - statement: (finalAcademicProgramDataCount) => SELECT COUNT(*) FROM finalAcademicProgramData
  #- statement: print finalAcademicProgramDataCount

# inline function - flatten the array to make it a basic table  
  - statement: (academicPlanData) => SELECT inline(ADM_APPL_PLAN.fields) FROM academicProgramData

## Select academic plas respective to the academic program
  ## Using the ROW_NUMBER function assigns a unique, sequential number to each row, starting with one,
   # according to the ordering of rows within the partition from the information pulled in the previous statement for the academicProgramData
  ## The PARTITION BY is dividing the rows into small partitions by EFFSEQ preference field  
  - statement: |
      (groupingAcadPlanData) => 
        SELECT
          *,
          row_number() OVER (PARTITION BY EMPLID, ADM_APPL_NBR ORDER BY EFFSEQ DESC) AS Row
        FROM academicPlanData

- statement: (finalAcademicPlanData) => SELECT * FROM groupingAcadPlanData WHERE Row = 1
  - statement: (finalAcademicPlanDataCount) => SELECT COUNT(*) FROM finalAcademicPlanData
  #- statement: print finalAcademicPlanDataCount

- statement: (sfAppDataInPS) => SELECT inline(ZW_SF_APP_HDR.fields) FROM peopleSoftAdmissionApplicationData
  - statement: (sfAppDataInPSCount) => SELECT COUNT(*) FROM sfAppDataInPS
  #- statement: print sfAppDataInPSCount

## Retrieve the records from all the Peoplesoft tables joined
  - statement: |
      (psApplicationInfo) =>
          SELECT
            app.EMPLID,
            plan.ACAD_PLAN,
            plan.REQ_TERM,
            plan.ACAD_CAREER,
            app.SCC_ROW_ADD_DTTM,
            app.ADM_APPL_NBR,
            prg.CAMPUS,
            app.SCC_ROW_UPD_DTTM,
            sf.ZW_CONTACT_ID,
            prg.PROG_STATUS

FROM peopleSoftAdmissionApplicationData app
          LEFT JOIN finalAcademicPlanData plan ON IFNULL(TRIM(app.EMPLID), '') = IFNULL(TRIM(plan.EMPLID), '')
            AND IFNULL(TRIM(app.ADM_APPL_NBR), '') = IFNULL(TRIM(plan.ADM_APPL_NBR), '')
          LEFT JOIN finalAcademicProgramData prg ON IFNULL(TRIM(app.EMPLID), '') = IFNULL(TRIM(prg.EMPLID), '')
            AND IFNULL(TRIM(app.ADM_APPL_NBR), '') = IFNULL(TRIM(prg.ADM_APPL_NBR), '')
          LEFT JOIN sfAppDataInPS sf ON IFNULL(TRIM(app.EMPLID), '') = IFNULL(TRIM(sf.EMPLID), '')
            AND IFNULL(TRIM(sf.EMPLID), '') != ''
          WHERE IFNULL(app.EMPLID, '') != ''

#- statement: print psApplicationInfo
  - statement: (psApplicationInfoCount) => SELECT COUNT(*) FROM psApplicationInfo
  #- statement: print psApplicationInfoCount

## Creating EMPLID batch to get Contacts from SF
  - statement: (distinctStudents) => 
                  SELECT DISTINCT 
                    EMPLID   `id` 
                  FROM psApplicationInfo

# This statement executes a batch process task where the name, inputTable, outputTable and the results are the parameters needed  
  - statement: |
        execute task 
            --name batchTask
            --inputTable distinctStudents
            --outputBatchTable batchedStudentsId
            --result resultStatus

- statement: REFRESH sfContactReader
  - statement: (sfContactReaderCount) => SELECT COUNT(*) FROM sfContactReader
  #- statement: print sfContactReaderCount

## Creating academic program batch to get related academic program account from SF
  - statement: (distincrAcademicPlan) => SELECT DISTINCT ACAD_PLAN     `id` FROM psApplicationInfo

# This statement executes a batch process task where the name, inputTable, outputTable and the results are the parameters needed  
  - statement: |
        execute task 
            --name batchTask
            --inputTable distincrAcademicPlan
            --outputBatchTable batchedAcdPlan
            --result resultStatus

- statement: REFRESH sfAccountReader
  - statement: (sfAccountReaderCount) => SELECT COUNT(*) FROM sfAccountReader
  #- statement: print sfAccountReaderCount

- statement: REFRESH sfTermReader

## Retrieve the Application info from the join of the Peoplesoft tables along with salesforce Contact, Account and Term to get final application to process
  # 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: |
      (finalApplicationInfo) =>
          SELECT
            CASE WHEN IFNULL(TRIM(UPPER(app.ACAD_CAREER)), '') = 'UGRD' THEN 'UGrad - Degree Seeking Program'
              ELSE IFNULL(app.ACAD_CAREER, '') END           `hed__Application_Type__c`,

date_format(to_date(IFNULL(app.SCC_ROW_ADD_DTTM, '')), "yyyy-MM-dd")
                                                             `hed__Initial_Creation_Date__c`,

date_format(to_date(IFNULL(app.SCC_ROW_ADD_DTTM, '')), "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'")
                                                             `hed__Application_Date__c`,

IFNULL(app.ADM_APPL_NBR, '')                     `Mid_State_Application_Number__c`,

CASE IFNULL(app.CAMPUS, '')     
              WHEN 'AF' THEN 'Adams Friendship'
              WHEN 'MF' THEN 'Marshfield'
              WHEN 'SP' THEN 'Stevens Point'
              WHEN 'WR' THEN 'Wisconsin Rapids'
              WHEN 'VC' THEN 'Online Classes Only' 
            ELSE IFNULL(app.CAMPUS, '') END                  `Application_Campus__c`,

date_format(to_date(IFNULL(app.SCC_ROW_UPD_DTTM, '')), "yyyy-MM-dd")  
                                                             `Application_Action_Date__c`,

CASE WHEN IFNULL(ZW_CONTACT_ID, '') != '' THEN IFNULL(ZW_CONTACT_ID, '')
              ELSE stu.Id END                                `hed__Applicant__c`,
            acc.Id                                           `hed__Applying_To__c`,
            trm.Id                                           `hed__Term__c`,
            'Created in PeopleSoft'                          `hed__Application_Status__c`

FROM psApplicationInfo app
          INNER JOIN sfContactReader stu ON TRIM(app.EMPLID) = TRIM(stu.University_Student_ID__c)
          INNER JOIN sfAccountReader acc ON TRIM(app.ACAD_PLAN) = TRIM(acc.PeopleSoft_Academic_Program_ID__c)
          LEFT JOIN sfTermReader trm ON TRIM(app.REQ_TERM) = TRIM(trm.PeopleSoft_Term_ID__c)

#- statement: print finalApplicationInfo
  - statement: (finalApplicationInfoCount) => SELECT COUNT(*) FROM finalApplicationInfo
  #- statement: print finalApplicationInfoCount

## Creating SF Application Id batch to get applications from SF
  - statement: (sfApplicationList) => SELECT DISTINCT Mid_State_Application_Number__c `id` FROM finalApplicationInfo

# This statement executes a batch process task where the name, inputTable, outputTable and the results are the parameters needed  
  - statement: |
        execute task 
            --name batchTask
              --inputTable sfApplicationList
            --outputBatchTable batchedApplicationList
            --result resultStatus

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

###### Start - Update Salesforce matching application ######

- statement: |
      (matchingApplicationToUpdate) =>
          SELECT
            sf.Id,
            app.hed__Application_Type__c,
            app.hed__Initial_Creation_Date__c,
            app.hed__Application_Date__c,
            app.Mid_State_Application_Number__c,
            app.Application_Campus__c,
            app.Application_Action_Date__c,
            app.hed__Term__c,
            'Created in PeopleSoft'    `hed__Application_Status__c`
          FROM finalApplicationInfo app
          INNER JOIN sfApplicationReader sf ON ifnull(app.Mid_State_Application_Number__c,"")= IFNULL(sf.Mid_State_Application_Number__c,"")

#- statement: print matchingApplicationToUpdate
  - statement: (matchingApplicationToUpdateCount) => SELECT COUNT(*) FROM matchingApplicationToUpdate
  #- statement: print matchingApplicationToUpdateCount

- statement: (success, error) => UPDATE matchingApplicationToUpdate INTO sfApplicationWriter
  #- statement: print success
  #- statement: print error

###### End - Update Salesforce matching application ######

###### Start - Insert Salesforce matching application ######

- statement: |
      (applicationToInsert) =>
          SELECT
            app.*
          FROM finalApplicationInfo app
          LEFT ANTI JOIN sfApplicationReader sf ON ifnull(app.Mid_State_Application_Number__c,"") = IFNULL(sf.Mid_State_Application_Number__c,"")

#- statement: print applicationToInsert
  - statement: (applicationToInsertCount) => SELECT COUNT(*) FROM applicationToInsert
  #- statement: print applicationToInsertCount

- statement: (success, error) => INSERT applicationToInsert INTO sfApplicationWriter
  #- statement: print success
  #- statement: print error

###### End - Insert Salesforce matching application ######

#################################### LOGS - Debugging purpuses ######################################

# - statement: print peopleSoftAdmissionApplicationDataCount
  # - statement: print finalAcademicProgramDataCount
  # - statement: print finalAcademicPlanDataCount
  # - statement: print sfAppDataInPSCount
  # - statement: print blankEmplIdApplicationCount
  # - statement: print psApplicationInfoCount
  # - statement: print sfContactReaderCount
  # - statement: print sfAccountReaderCount
  # - statement: print rejectedAppsByNonMatchingContactCount
  # - statement: print rejectedAppsByNonMatchingAccountCount
  # - statement: print finalApplicationInfoCount
  # - statement: print sfApplicationReaderCount
  # - statement: print matchingApplicationToUpdateCount
  # - statement: print applicationToInsertCount

###### Start - Insert Application Info in a LocalFile ######

- statement: REFRESH sfApplicationReader
  - statement: |
      (existingApplicationInfo) =>
          SELECT
            sf.Id,
            app.*
          FROM finalApplicationInfo app
          LEFT JOIN sfApplicationReader sf ON IFNULL(TRIM(app.hed__Applicant__c), '') = IFNULL(TRIM(sf.hed__Applicant__c), '')
            AND IFNULL(TRIM(app.hed__Applying_To__c), '') = IFNULL(TRIM(sf.hed__Applying_To__c), '')

- statement: INSERT existingApplicationInfo INTO finalMappedRecordFile

###### End - Insert Application Info in a LocalFile ######

###### Start - Update Dates env vars ######

- statement: |
      (lastModifiedDateTime) => 
          SELECT concat(date_format(from_utc_timestamp(current_timestamp(), 'America/New_York'), "yyyy-MM-dd"),'-01.01.01.000001') AS  `outboundAppDateTime`                  
  - statement: UPDATE lastModifiedDateTime INTO env.vars

###### End - Update Dates env vars ######

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