Go Back
PeopleSoft to Salesforce: Application Sync
Publisher
:
Run In Lingk
Description
This recipe syncs approved applicant data from a PeopleSoft staging table back to Salesforce.
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # 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
Canvas - Update SIS user id
Salesforce to PeopleSoft: Application Sync