Go Back
Peoplesoft to Salesforce: Course Connection Sync
Publisher
:
Run In Lingk
Description
This recipe retrieves Course Connection from Peoplesoft and writes the data back to Salesforce
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - Peoplesoft to Salesforce: Course Connection Sync # Recipe URL - https://app.lingk.io/a/10932/tf/19136 # Description - 13 - Peoplesoft to Salesforce: Course Connection Sync # This recipe retrieves Course Connection from Peoplesoft and writes the data back to Salesforce # Select all course connection from STDNT_ENRL, SC_STDNTENRL_CR table in PeopleSoft where INSTITUTION = 'inst-code' AND LAST_UPD_DT_STMP >= %datein {{env.vars.courseConnectionLastUpdateDate}}. # This recipe is using HTTP V2 connector (with pagination) to get student enrollments from peopleSoft. # Reject non-matching records due to Contact & Course Offering. # Transform fields and insert/update record into SF by PeopleSoft_Course_Connection_Unique_Id__c, hed__Course_Offering__c, hed__Contact__c. # Insert course connection requested fields with their SF id in Lingk environment file. # Update current date in environment. # Systems - Peoplesoft, Salesforce # Connectors - HttpV2, Salesforce # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Peoplesoft Models Used: #1. STDNT_ENRL #2. SC_STDNTENRL_CR ## Salesforce Objects Used: #1. Contact #2. hed__Course_Offering__c #3. RecordType #4. hed__Course_Enrollment__c # 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 Course Connection Connector - name: peopleSoftCourseConnectionData 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": "STDNT_ENRL", "sqlWhereClause": " A.INSTITUTION = '{{env.inst-code}}' AND A.LAST_UPD_DT_STMP >= %datein('{{env.vars.courseConnectionLastUpdateDate}}')", "includeDescriptionsFor": ["STDNT_ENRL_STATUS", "ENRL_STATUS_REASON"] }, { "recordName": "SC_STDNTENRL_CR", "parentRecordName": "STDNT_ENRL", "includeDescriptionsFor": [ "SC_RECOGNZ_CREDIT" ] } ] } schema: fields: - name: 'ACAD_CAREER' type: 'string' - name: 'ACAD_PROG' type: 'string' - name: 'ASSOCIATED_CLASS' type: 'long' - name: 'ASSOCIATION_99' type: 'string' - name: 'AUDIT_GRADE_BASIS' type: 'string' - name: 'CLASS_NBR' type: 'long' - name: 'CLASS_PRMSN_NBR' type: 'long' - name: 'CRSE_CAREER' type: 'string' - name: 'CRSE_COUNT' type: 'long' - name: 'CRSE_GRADE_INPUT' type: 'string' - name: 'CRSE_GRADE_OFF' type: 'string' - name: 'DROP_CLASS_IF_ENRL' type: 'long' - name: 'DYN_CLASS_NBR' type: 'long' - name: 'EARN_CREDIT' type: 'string' - name: 'EMPLID' type: 'string' - name: 'ENRL_ACTION_LAST' type: 'string' - name: 'ENRL_ACTN_PRC_LAST' type: 'string' - name: 'ENRL_ACTN_RSN_LAST' type: 'string' - name: 'ENRL_ADD_DT' type: 'string' - name: 'ENRL_DROP_DT' type: 'string' - name: 'ENRL_REQ_SOURCE' type: 'string' - name: 'ENRL_STATUS_REASON' type: 'string' - name: 'ENRL_STATUS_REASON_description' type: 'string' - name: 'GRADE_CATEGORY' type: 'string' - name: 'GRADE_DT' type: 'string' - name: 'GRADE_POINTS' type: 'long' - name: 'GRADE_POINTS_FA' type: 'long' - name: 'GRADING_BASIS_DT' type: 'string' - name: 'GRADING_BASIS_ENRL' type: 'string' - name: 'GRADING_SCHEME_ENR' type: 'string' - name: 'GRD_PTS_PER_UNIT' type: 'long' - name: 'INCLUDE_IN_GPA' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'INSTRUCTOR_ID' type: 'string' - name: 'LAST_DROP_DT_STMP' type: 'string' - name: 'LAST_DROP_TM_STMP' type: 'string' - name: 'LAST_ENRL_DT_STMP' type: 'string' - name: 'LAST_ENRL_TM_STMP' type: 'string' - name: 'LAST_UPD_DT_STMP' type: 'string' - name: 'LAST_UPD_ENREQ_SRC' type: 'string' - name: 'LAST_UPD_TM_STMP' type: 'string' - name: 'MANDATORY_GRD_BAS' type: 'string' - name: 'NOTIFY_STDNT_CHNG' type: 'string' - name: 'OPRID' type: 'string' - name: 'REPEAT_CANDIDATE' type: 'string' - name: 'REPEAT_CODE' type: 'string' - name: 'REPEAT_DT' type: 'string' - name: 'RQMNT_DESIGNTN' type: 'string' - name: 'RQMNT_DESIGNTN_GRD' type: 'string' - name: 'RQMNT_DESIGNTN_OPT' type: 'string' - name: 'RSRV_CAP_NBR' type: 'long' - name: 'SC_STDNTENRL_CR' 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: 'BUSINESS_UNIT' type: 'string' - name: 'CLASS_NBR' type: 'long' - name: 'EMPLID' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'PROJECT_ID' type: 'string' - name: 'SC_FIRE_DEPT_ID' type: 'string' - name: 'SC_POST_911' type: 'long' - name: 'SC_PROG_FEE_CD1' type: 'string' - name: 'SC_PROG_FEE_CD2' type: 'string' - name: 'SC_PROG_FEE_CD3' type: 'string' - name: 'SC_RECOGNZ_CREDIT' type: 'string' - name: 'STRM' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'SEL_GROUP' type: 'string' - name: 'SESSION_CODE' type: 'string' - name: 'SESSN_ENRL_CNTL' type: 'string' - name: 'STATUS_DT' type: 'string' - name: 'STDNT_ENRL_STATUS' type: 'string' - name: 'STDNT_ENRL_STATUS_description' type: 'string' - name: 'STDNT_POSITIN' type: 'long' - name: 'STRM' type: 'string' - name: 'UNITS_ATTEMPTED' type: 'string' - name: 'UNT_BILLING' type: 'double' - name: 'UNT_PRGRSS' type: 'double' - name: 'UNT_PRGRSS_FA' type: 'double' - name: 'UNT_TAKEN' type: 'double' - name: 'rowNumber' type: 'long' # A.LAST_UPD_DT_STMP >= %datein('2022-05-03') ###### End - HTTP Reader connectors ###### ###### Start - Salesforce Reader connectors ###### # Salesforce Course Offering Reader - name: sfCourseOfferingReader type: salesforceReader delayedRead: true parameterizedBy: batchedOfferingId properties: useBearerToken: true query: SELECT Id, Name, PeopleSoft_Course_Offering_Unique_Id__c FROM hed__Course_Offering__c WHERE PeopleSoft_Course_Offering_Unique_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_Course_Offering_Unique_Id__c' type: 'string' # Salesforce Contact Reader - name: sfContactReader type: salesforceReader delayedRead: true parameterizedBy: batchedStudentId properties: useBearerToken: true query: SELECT Id, University_Student_ID__c, Name 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: 'University_Student_ID__c' type: 'string' - name: 'Name' type: 'string' - name: sfRecordTypeReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT Id, Name FROM RecordType WHERE Name = 'Student' AND SobjectType = 'hed__Course_Enrollment__c' schema: fields: - name: 'Id' type: 'string' - name: 'Name' type: 'string' # Salesforce Course Connection - name: sfCourseConnectionReader type: salesforceReader delayedRead: true parameterizedBy: batchedConnectionId properties: useBearerToken: true query: SELECT Id, Name, hed__Course_Offering__c, hed__Contact__c, PeopleSoft_Course_Connection_Unique_Id__c, Status_Text__c, RecordTypeId, hed__Display_Grade__c, Recognized_Credit_Code__c, Program_Fee_Code__c, Workforce_Grant__c, hed__Credits_Attempted__c, hed__Credits_Earned__c FROM hed__Course_Enrollment__c WHERE PeopleSoft_Course_Connection_Unique_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: 'hed__Course_Offering__c' type: 'string' - name: 'hed__Contact__c' type: 'string' - name: 'PeopleSoft_Course_Connection_Unique_Id__c' type: 'string' - name: 'Status_Text__c' type: 'string' - name: 'RecordTypeId' type: 'string' - name: 'hed__Display_Grade__c' type: 'string' - name: 'Recognized_Credit_Code__c' type: 'string' - name: 'Program_Fee_Code__c' type: 'string' - name: 'Workforce_Grant__c' type: 'string' - name: 'hed__Credits_Attempted__c' type: 'string' - name: 'hed__Credits_Earned__c' type: 'string' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### # Salesforce Course Connection Writer - name: sfCourseConnectionWriter type: salesforceBulkWriter onError: continue isSerial: true properties: onError: continue useBearerToken: true sfObject: hed__Course_Enrollment__c batchSize: 200 ###### End - Salesforce Writer connectors ###### ###### Start - Local Writer connectors ###### # Local File Writer - name: UpdatedCrseConnectionFileWriter type: localFileWriter format: csvWriter properties: fileName: "UpdatedCourseConnection.csv" - name: InsertedCrseConnectionFileWriter type: localFileWriter format: csvWriter properties: fileName: "InsertedCourseConnection.csv" ###### End - Local 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 # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ 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: print peopleSoftCourseConnectionData - statement: (peopleSoftCourseConnectionDataCount) => SELECT COUNT(*) FROM peopleSoftCourseConnectionData #- statement: PRINT peopleSoftCourseConnectionDataCount # inline function - flatten the array to make it a basic table - statement: (inlineStudentEnroll) => SELECT inline(SC_STDNTENRL_CR.fields) FROM peopleSoftCourseConnectionData # - statement: PRINT inlineStudentEnroll ## Get Salesforce Student Info - statement: (distinctStudentId) => SELECT DISTINCT TRIM(EMPLID) `id` FROM peopleSoftCourseConnectionData # 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 distinctStudentId --outputBatchTable batchedStudentId --result resultStatus - statement: REFRESH sfContactReader # - statement: (sfContactReaderCount) => SELECT COUNT(*) FROM sfContactReader # - statement: PRINT sfContactReaderCount ## Get Salesforce Course Offering Info # The Concat_ws function adds different values using the "-" char - statement: (distinctOfferingId) => SELECT DISTINCT concat_ws('-', TRIM(STRM), TRIM(CLASS_NBR)) `id` FROM peopleSoftCourseConnectionData # 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 distinctOfferingId --outputBatchTable batchedOfferingId --result resultStatus # - statement: print batchedOfferingId - statement: REFRESH sfCourseOfferingReader # - statement: (sfCourseOfferingReaderCount) => SELECT COUNT(*) FROM sfCourseOfferingReader # - statement: PRINT sfCourseOfferingReaderCount ## Reject non-matching records due to # Contact # 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: (rejectedRecordByNonMatchingContact) => SELECT DISTINCT CLASS_NBR FROM peopleSoftCourseConnectionData co LEFT ANTI JOIN sfContactReader sf ON IFNULL(TRIM(co.EMPLID), '') = IFNULL(TRIM(sf.University_Student_ID__c), '') # - statement: (rejectedRecordByNonMatchingContactCount) => SELECT COUNT(*) FROM rejectedRecordByNonMatchingContact # - statement: PRINT rejectedRecordByNonMatchingContactCount # Course Offering # 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: (rejectedRecordBynonMatchingCourseOffering) => SELECT * FROM peopleSoftCourseConnectionData co LEFT ANTI JOIN sfCourseOfferingReader sf ON concat_ws('-', IFNULL(TRIM(co.STRM), ''), IFNULL(TRIM(co.CLASS_NBR), '')) = IFNULL(TRIM(sf.PeopleSoft_Course_Offering_Unique_Id__c), '') # - statement: (rejectedRecordBynonMatchingCourseOfferingCount) => SELECT COUNT(*) FROM rejectedRecordBynonMatchingCourseOffering # - statement: PRINT rejectedRecordBynonMatchingCourseOfferingCount - statement: REFRESH sfRecordTypeReader # - statement: PRINT sfRecordTypeReader ### Course Connection records for Students # The Concat_ws function adds different values using the "-" char # 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: (mappedCourseConnection) => SELECT concat_ws('-', TRIM(cc.EMPLID), TRIM(cc.ACAD_CAREER), TRIM(cc.INSTITUTION), TRIM(cc.STRM), TRIM(cc.CLASS_NBR)) `PeopleSoft_Course_Connection_Unique_Id__c`, st.Id `hed__Contact__c`, IFNULL(TRIM(cc.STDNT_ENRL_STATUS_description), '') `Status_Text__c`, co.Id `hed__Course_Offering__c`, (SELECT First(Id) FROM sfRecordTypeReader) `RecordTypeId`, IFNULL(TRIM(cc.CRSE_GRADE_OFF), '') `hed__Display_Grade__c`, IFNULL(TRIM(se.SC_RECOGNZ_CREDIT), '') `Recognized_Credit_Code__c`, IFNULL(TRIM(se.SC_PROG_FEE_CD1), '') `Program_Fee_Code__c`, IFNULL(TRIM(se.PROJECT_ID), '') `Workforce_Grant__c`, IFNULL(TRIM(cc.UNT_PRGRSS), '') `hed__Credits_Attempted__c`, IFNULL(TRIM(cc.UNT_TAKEN), '') `hed__Credits_Earned__c` FROM peopleSoftCourseConnectionData cc INNER JOIN sfCourseOfferingReader co ON concat_ws('-', TRIM(cc.STRM), TRIM(cc.CLASS_NBR)) = TRIM(co.PeopleSoft_Course_Offering_Unique_Id__c) INNER JOIN sfContactReader st ON TRIM(cc.EMPLID) = TRIM(st.University_Student_ID__c) LEFT JOIN inlineStudentEnroll se ON TRIM(cc.EMPLID) = TRIM(se.EMPLID) AND TRIM(cc.STRM) = TRIM(se.STRM) AND TRIM(cc.CLASS_NBR) = TRIM(se.CLASS_NBR) WHERE TRIM(cc.EMPLID) != '' AND TRIM(cc.ACAD_CAREER) != '' AND TRIM(cc.INSTITUTION) != '' AND TRIM(cc.STRM) != '' AND TRIM(cc.CLASS_NBR) != '' # - statement: PRINT mappedCourseConnection - statement: (mappedCourseConnectionCount) => SELECT COUNT(*) FROM mappedCourseConnection #- statement: PRINT mappedCourseConnectionCount - statement: (distinctConnectionId) => SELECT DISTINCT PeopleSoft_Course_Connection_Unique_Id__c `id` FROM mappedCourseConnection # 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 distinctConnectionId --outputBatchTable batchedConnectionId --result resultStatus - statement: REFRESH sfCourseConnectionReader - statement: (sfCourseConnectionReaderCount) => SELECT COUNT(*) FROM sfCourseConnectionReader #- statement: PRINT sfCourseConnectionReaderCount # Retrieves the course connection that will be updated in Salesforce - statement: (courseConnectionToUpdate) => SELECT s.Id, c.PeopleSoft_Course_Connection_Unique_Id__c, c.hed__Contact__c, c.Status_Text__c, c.hed__Course_Offering__c, c.RecordTypeId, c.hed__Display_Grade__c, c.Recognized_Credit_Code__c, c.Program_Fee_Code__c, c.Workforce_Grant__c, c.hed__Credits_Attempted__c, c.hed__Credits_Earned__c FROM mappedCourseConnection c INNER JOIN sfCourseConnectionReader s ON IFNULL(UPPER(TRIM(s.PeopleSoft_Course_Connection_Unique_Id__c)), '') = IFNULL(UPPER(TRIM(c.PeopleSoft_Course_Connection_Unique_Id__c)), '') AND IFNULL(UPPER(TRIM(s.hed__Course_Offering__c)), '') = IFNULL(UPPER(TRIM(c.hed__Course_Offering__c)), '') AND IFNULL(UPPER(TRIM(s.hed__Contact__c)), '') = IFNULL(UPPER(TRIM(c.hed__Contact__c)), '') WHERE TRIM(s.Status_Text__c) != TRIM(c.Status_Text__c) OR TRIM(s.RecordTypeId) != TRIM(c.RecordTypeId) OR TRIM(s.hed__Display_Grade__c) != TRIM(c.hed__Display_Grade__c) OR TRIM(s.Recognized_Credit_Code__c) != TRIM(c.Recognized_Credit_Code__c) OR TRIM(s.Program_Fee_Code__c) != TRIM(c.Program_Fee_Code__c) OR TRIM(s.Workforce_Grant__c) != TRIM(c.Workforce_Grant__c) OR TRIM(s.hed__Credits_Attempted__c) != TRIM(c.hed__Credits_Attempted__c) OR TRIM(s.hed__Credits_Earned__c) != TRIM(c.hed__Credits_Earned__c) # - statement: PRINT courseConnectionToUpdate - statement: (courseConnectionToUpdateCount) => SELECT COUNT(*) FROM courseConnectionToUpdate #- statement: PRINT courseConnectionToUpdateCount # 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: (courseConnectionToInsert) => SELECT c.* FROM mappedCourseConnection c LEFT ANTI JOIN sfCourseConnectionReader s ON IFNULL(UPPER(TRIM(s.PeopleSoft_Course_Connection_Unique_Id__c)), '') = IFNULL(UPPER(TRIM(c.PeopleSoft_Course_Connection_Unique_Id__c)), '') AND IFNULL(UPPER(TRIM(s.hed__Course_Offering__c)), '') = IFNULL(UPPER(TRIM(c.hed__Course_Offering__c)), '') AND IFNULL(UPPER(TRIM(s.hed__Contact__c)), '') = IFNULL(UPPER(TRIM(c.hed__Contact__c)), '') # - statement: PRINT courseConnectionToInsert - statement: (courseConnectionToInsertCount) => SELECT COUNT(*) FROM courseConnectionToInsert #- statement: PRINT courseConnectionToInsertCount ###### Start - Update SF course connection ###### - statement: (updateResults, updateErrors) => UPDATE courseConnectionToUpdate INTO sfCourseConnectionWriter #- statement: PRINT updateResults #- statement: PRINT updateErrors ###### End - Update SF course connection ###### ###### Start - Insert SF course connection ###### - statement: (insertResults, insertErrors) => INSERT courseConnectionToInsert INTO sfCourseConnectionWriter #- statement: PRINT insertResults #- statement: PRINT insertErrors ###### End - Insert SF course connection ###### #################################### LOGS - Debugging purpuses ###################################### # - statement: PRINT peopleSoftCourseConnectionDataCount # - statement: PRINT sfContactReaderCount # - statement: PRINT rejectedRecordByNonMatchingContactCount # - statement: PRINT sfCourseOfferingReaderCount # - statement: PRINT rejectedRecordBynonMatchingCourseOfferingCount # - statement: PRINT mappedCourseConnectionCount # - statement: PRINT sfCourseConnectionReaderCount # - statement: PRINT courseConnectionToUpdateCount # - statement: PRINT courseConnectionToInsertCount ###### Start - Request with SF id in local file ###### ## Insert request with SF id in local file - statement: REFRESH sfCourseConnectionReader - statement: | (UpdatedCourseConnectionInfo) => SELECT c.* FROM courseConnectionToUpdate c INNER JOIN sfCourseConnectionReader s ON IFNULL(UPPER(TRIM(s.PeopleSoft_Course_Connection_Unique_Id__c)), '') = IFNULL(UPPER(TRIM(c.PeopleSoft_Course_Connection_Unique_Id__c)), '') - statement: INSERT UpdatedCourseConnectionInfo INTO UpdatedCrseConnectionFileWriter - statement: | (InsertedCourseConnectionInfo) => SELECT s.Id, c.* FROM courseConnectionToInsert c INNER JOIN sfCourseConnectionReader s ON IFNULL(UPPER(TRIM(s.PeopleSoft_Course_Connection_Unique_Id__c)), '') = IFNULL(UPPER(TRIM(c.PeopleSoft_Course_Connection_Unique_Id__c)), '') - statement: INSERT InsertedCourseConnectionInfo INTO InsertedCrseConnectionFileWriter ###### End - Request with SF id in local file ###### ###### Start - Update Dates env vars ###### ## Update current date time in env variable - statement: | (lastModifiedDateTime) => SELECT date_format(from_utc_timestamp(current_timestamp(), 'America/New_York'), "yyyy-MM-dd") AS `courseConnectionLastUpdateDate` - statement: UPDATE lastModifiedDateTime INTO env.vars ###### End - Update Dates env vars ###### # Add more statements to convert, join, aggregrate, transform, and integrate your data
Salesforce to Peoplesoft: Academic Advisor Sync
HTTP Connector - Basic GET Example