# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - PeopleSoft to Salesforce - Program Enrollments # Recipe URL - https://app.lingk.io/a/10932/tf/18948 # Description - 8 - PeopleSoft to Salesforce - Program Enrollments # Insert the enrollments from Peoplesoft to the Program Enrollment table in Salesforce. # Reads from a web service on Peoplesoft # Shapes the data correctly for Salesforce # Inserts or updates the Program Enrollment table in Salesforce # Systems - Peoplesoft, Salesforce # Connectors - HttpV2, Salesforce # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Peoplesoft Models Used: #1. Program Enrollment ## Salesforce Objects Used: #1. hed__Program_Enrollment__c #2. Contacts #3. Accounts # 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 Program Enrollment 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 - name: peopleSoftProgramEnrollment type: httpV2Reader properties: headerJson: { "Authorization":"Basic {{env.vars.apiAccessToken}}", "Content-Type":"application/json" } url: '{{env.vars.midStateInstanceURL}}' httpMethod: POST acceptInvalidCerts: true paginationType: RequestBody body: { "isDebugMode": false, "rowLimit": 1000, "pageNumber": 1, "records": [ { "recordName": "ACAD_PROG", "sqlWhereClause": "A.INSTITUTION = '{{env.inst-code}}' AND A.PROG_STATUS != 'DE' AND A.SCC_ROW_UPD_DTTM >= %datein('{{env.vars.progEnrollLastUpdateDate}}') AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT)", "includeDescriptionsFor": ["PROG_STATUS", "PROG_ACTION", "PROG_REASON"] }, { "recordName": "ACAD_PLAN", "parentRecordName": "ACAD_PROG", "useParentEffectiveDate": true }, { "recordName": "PROG_RSN_TBL", "parentRecordName": "ACAD_PROG", "sqlWhereClause": "A.SETID = '{{env.inst-code}}' " } ] } schema: fields: - name: 'ACAD_CAREER' type: 'string' - name: 'ACAD_LOAD_APPR' type: 'string' - name: 'ACAD_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: 'ADVIS_STATUS' type: 'string' - name: 'COMPLETION_TERM' type: 'string' - name: 'DECLARE_DT' type: 'string' - name: 'DEGR_CHKOUT_STAT' 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: 'STDNT_DEGR' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' 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: 'APPL_PROG_NBR' type: 'long' - name: 'CAMPUS' type: 'string' - name: 'COMPLETION_TERM' type: 'string' - name: 'DATA_FROM_ADM_APPL' type: 'string' - name: 'DEGR_CHKOUT_STAT' 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_RSN_TBL' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'fields' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'DESCR' type: 'string' - name: 'DESCR250' type: 'string' - name: 'DESCRSHORT' type: 'string' - name: 'EFFDT' type: 'string' - name: 'EFF_STATUS' type: 'string' - name: 'PROG_ACTION' type: 'string' - name: 'PROG_REASON' type: 'string' - name: 'SCRIPT_TEXT' type: 'string' - name: 'SETID' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'PROG_STATUS' type: 'string' - name: 'PROG_STATUS_description' 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: '__parameters' type: 'string' - name: 'apiVersion' type: 'string' - name: 'data' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'ACAD_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_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: 'ADVIS_STATUS' type: 'string' - name: 'COMPLETION_TERM' type: 'string' - name: 'DECLARE_DT' type: 'string' - name: 'DEGR_CHKOUT_STAT' 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: 'STDNT_DEGR' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' 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: 'APPL_PROG_NBR' type: 'long' - name: 'CAMPUS' type: 'string' - name: 'COMPLETION_TERM' type: 'string' - name: 'DATA_FROM_ADM_APPL' type: 'string' - name: 'DEGR_CHKOUT_STAT' 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_RSN_TBL' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'fields' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'DESCR' type: 'string' - name: 'DESCR250' type: 'string' - name: 'DESCRSHORT' type: 'string' - name: 'EFFDT' type: 'string' - name: 'EFF_STATUS' type: 'string' - name: 'PROG_ACTION' type: 'string' - name: 'PROG_REASON' type: 'string' - name: 'SCRIPT_TEXT' type: 'string' - name: 'SETID' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'PROG_STATUS' type: 'string' - name: 'PROG_STATUS_description' 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: 'rowNumber' type: 'long' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'errorMessages' type: 'string' - name: 'nextPageNumber' type: 'string' - name: 'responseCode' type: 'long' - name: 'rowNumber' type: 'long' ###### End - HTTP Reader connectors ###### ###### Start - Salesforce Reader connectors ###### - name: sfContactReader type: salesforceReader delayedRead: true parameterizedBy: batchedStudentId properties: useBearerToken: true query: SELECT Id, University_Student_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: 'University_Student_ID__c' type: 'string' - name: sfsProgramEnrollmentReader type: salesforceReader delayedRead: true parameterizedBy: batchedProgramEnrollments properties: useBearerToken: true query: SELECT hed__Contact__c ,hed__Account__c ,Start_Term__c ,End_Term__c ,PeopleSoft_Program_Enrollment_Unique_Id__c ,Program_Status__c ,CaseSafe_ID_Program_Enrollment__c ,Id FROM hed__Program_Enrollment__c WHERE PeopleSoft_Program_Enrollment_Unique_Id__c IN ( {% for element in var.output %} '{{element.id}}'{% if not loop.last %},{% endif %} {% endfor %} ) schema: fields: - name: 'CaseSafe_ID_Program_Enrollment__c' type: 'string' - name: 'End_Term__c' type: 'string' - name: 'Start_Term__c' type: 'string' - name: 'hed__Account__c' type: 'string' - name: 'hed__Contact__c' type: 'string' - name: 'PeopleSoft_Program_Enrollment_Unique_Id__c' type: 'string' - name: 'Id' type: 'string' - name: sfAcademicProgramReader type: salesforceReader delayedRead: true properties: useBearerToken: true query: SELECT PeopleSoft_Academic_Program_ID__c ,Id FROM Account WHERE PeopleSoft_Academic_Program_ID__c != NULL OR PeopleSoft_Academic_Program_ID__c != '' schema: fields: - name: 'Id' type: 'string' - name: 'PeopleSoft_Academic_Program_ID__c' type: 'string' - 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' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: sfProgEnrollmentWriter type: salesforceBulkWriter onError: continue isSerial: true properties: onError: continue useBearerToken: true sfObject: hed__Program_Enrollment__c batchSize: 100 ###### End - Salesforce Writer connectors ###### ###### Start - Lingk Writer connectors ###### - name: signalWriter type: lingkWriter properties: apiEndpoint: https://www.lingkapis.com/v1/{{env.inst-code}}/sign-up/events ###### End - Lingk Writer connectors ###### ###### Start - LocalFile Writer connectors ###### - name: ProgramLogFile type: localFileWriter format: excelFormat properties: fileName: "PStoSF_UpdateProgramEnrollments.xlsx" - name: ProgramLogFile1 type: localFileWriter format: excelFormat properties: fileName: "PStoSF_InsertProgramEnrollments.xlsx" ###### End - LocalFile Writer connectors ###### # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ ## FORMATS specify how files should be processed as they are being read or written to writeFormats: - name: excelFormat type: excel properties: worksheets: "ProgramDataSent" useHeader: "true" # _______ _ # |__ __| | | # | | __ _ ___| | _____ # | |/ _` / __| |/ / __| # | | (_| \__ \ <\__ \ # |_|\__,_|___/_|\_\___/ # Batch task used for parameterized by statements/connectors tasks: ## This batch task is being used under the statements sections in order to send information back to a connector to form a batching 'for' loop # documentation - https://help.lingk.io/en/articles/241-tasks - name: batchTask type: dataOperation function: batch parameters: inputBatchGroupBy: # required id: desc inputBatchSize: 100 # 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 # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # 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 *********************************************************************************************** # inline function - flatten the array to make it a basic table - statement: (acadProg) => SELECT * from peopleSoftProgramEnrollment #- statement: print acadProg # Print and count the nomber of records from acadmic program - statement: (acadProgDataCount) => SELECT COUNT(*) as Num FROM acadProg #- statement: print acadProgDataCount # inline function - flatten the array to make it a basic table - statement: (acadPlan) => select inline (ACAD_PLAN.fields) from acadProg #- statement: print acadPlan # Print and count the nomber of records from acadmic plan fields - statement: (acadPlanDataCount) => SELECT COUNT(*) as Num FROM acadPlan #- statement: print acadPlanDataCount # inline function - flatten the array to make it a basic table - statement: (progReason) => select inline (PROG_RSN_TBL.fields) from acadProg #- statement: print progReason # Print and count the nomber of records from program reason - statement: (progReasonDataCount) => SELECT COUNT(*) as Num FROM progReason #- statement: print progReasonDataCount ## Select all reference objects from Salesforce #Term - statement: REFRESH sfTermReader - statement: (sfTermReaderCount) => SELECT COUNT(*) as Num FROM sfTermReader #- statement: print sfTermReaderCount # Academic Program (Account) - statement: refresh sfAcademicProgramReader - statement: (inAcademicProgramCount) => SELECT COUNT(*) as Num FROM sfAcademicProgramReader #- statement: print inAcademicProgramCount # SF Student (Contact) - statement: (distinctEmplId) => SELECT DISTINCT IFNULL(TRIM(EMPLID), '') `id` FROM acadProg #- statement: print distinctEmplId # Batch described in task section # 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 distinctEmplId --outputBatchTable batchedStudentId --result resultStatus # Updates the Salesforce connector with the values needed for the parametrizedBy property through the REFRESH function - statement: REFRESH sfContactReader - statement: print sfContactReader # Counts and print the amount records retrieved by Salesforce after being refreshed - statement: (sfContactReaderCount) => SELECT COUNT(*) as Num FROM sfContactReader #- statement: print sfContactReaderCount # Grab out the values we need for salesforce # - statement: (enrollmentsToWorkWith) => select distinct # cr.Id as hed__Contact__c # , sfAcademicProgramReader.Id as hed__Account__c # , sfAcademicProgramReader.PeopleSoft_Academic_Program_ID__c # , acadPlan.ACAD_PLAN # , startTerm.Id as Start_Term__c # , endTerm.Id as End_Term__c # , acadProg.REQ_TERM as startTerm # , IFNULL(acadprog.PROG_STATUS_description,'') as Program_Status__c # , acadprog.EMPLID as UniversityID # , concat_ws('-',acadprog.EMPLID,acadprog.ACAD_CAREER,acadprog.STDNT_CAR_NBR) as PeopleSoft_Program_Enrollment_Unique_Id__c # FROM acadProg # join acadPlan on acadProg.EMPLID = acadPlan.EMPLID # AND acadProg.ACAD_CAREER = acadPlan.ACAD_CAREER # and acadProg.STDNT_CAR_NBR = acadPlan.STDNT_CAR_NBR # join sfContactReader cr on cr.University_Student_ID__c = acadProg.EMPLID # left join sfAcademicProgramReader on sfAcademicProgramReader.PeopleSoft_Academic_Program_ID__c = acadPlan.ACAD_PLAN # left join sfTermReader as startTerm on startTerm.PeopleSoft_Term_ID__c = acadProg.REQ_TERM # left join sfTermReader as endTerm on endTerm.PeopleSoft_Term_ID__c = acadProg.EXP_GRAD_TERM # 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: (enrollmentsToWorkWith) => select distinct cr.Id as hed__Contact__c , sfAcademicProgramReader.Id as hed__Account__c , sfAcademicProgramReader.PeopleSoft_Academic_Program_ID__c , acadPlan.ACAD_PLAN , startTerm.Id as Start_Term__c , endTerm.Id as End_Term__c , acadProg.REQ_TERM as startTerm , IFNULL(acadprog.PROG_STATUS_description,'') as Program_Status__c , acadprog.EMPLID as UniversityID , concat_ws('-',acadprog.EMPLID,acadprog.ACAD_CAREER,acadprog.STDNT_CAR_NBR) as PeopleSoft_Program_Enrollment_Unique_Id__c ,progReason.DESCR as PeopleSoft_Action_Reason__c FROM acadProg join acadPlan on acadProg.EMPLID = acadPlan.EMPLID AND acadProg.ACAD_CAREER = acadPlan.ACAD_CAREER and acadProg.STDNT_CAR_NBR = acadPlan.STDNT_CAR_NBR join sfContactReader cr on cr.University_Student_ID__c = acadProg.EMPLID left join sfAcademicProgramReader on sfAcademicProgramReader.PeopleSoft_Academic_Program_ID__c = acadPlan.ACAD_PLAN left join sfTermReader as startTerm on startTerm.PeopleSoft_Term_ID__c = acadProg.REQ_TERM left join sfTermReader as endTerm on endTerm.PeopleSoft_Term_ID__c = acadProg.EXP_GRAD_TERM left join progReason on progReason.PROG_ACTION = acadProg.PROG_ACTION AND progReason.PROG_REASON = acadProg.PROG_REASON #- statement: print enrollmentsToWorkWith - statement: (workingSetCount) => SELECT COUNT(*) as Num FROM enrollmentsToWorkWith #- statement: print workingSetCount - statement: insert enrollmentsToWorkWith into ProgramLogFile ##### Start - Created program enrollment unique id batch ##### # Get existing program enrollment from SF wiht unique Id - statement: (programEnrollUniqueIdList) => SELECT DISTINCT PeopleSoft_Program_Enrollment_Unique_Id__c AS id FROM enrollmentsToWorkWith # Batch described in task section # 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 programEnrollUniqueIdList --outputBatchTable batchedProgramEnrollments --result resultStatus - statement: REFRESH sfsProgramEnrollmentReader # Count and print the total of Program Enrollments coming from Salesforce - statement: (insfEnrollmentCount) => SELECT COUNT(*) as Num FROM sfsProgramEnrollmentReader #- statement: print insfEnrollmentCount ##### End - Created program enrollment unique id batch ##### ##### Start - Push updated enrollments into a local file ##### # Retrieves the records that will be updated - statement: (updateEnrollment) => select enroll.hed__Contact__c , enroll.hed__Account__c , enroll.Start_Term__c , enroll.End_Term__c , enroll.Program_Status__c , enroll.PeopleSoft_Program_Enrollment_Unique_Id__c , enroll.PeopleSoft_Action_Reason__c ,sfReader.Id from enrollmentsToWorkWith as enroll inner join sfsProgramEnrollmentReader as sfReader on enroll.PeopleSoft_Program_Enrollment_Unique_Id__c = sfReader.PeopleSoft_Program_Enrollment_Unique_Id__c #- statement: print updateEnrollment - statement: (numUpdatesCount) => SELECT COUNT(*) as Num FROM updateEnrollment #- statement: print numUpdatesCount - statement: insert updateEnrollment into ProgramLogFile # - statement: (success,error)=>update updateEnrollment into sfProgEnrollmentWriter # - statement: print success # - statement: print error ##### End - Push updated enrollments into a local file ##### ##### Start - Push insert enrollments into a local file ##### # Retrieves the records that will be inserted - statement: (insertEnrollment) => select enroll.hed__Contact__c , enroll.hed__Account__c , enroll.Start_Term__c , enroll.End_Term__c , enroll.PeopleSoft_Program_Enrollment_Unique_Id__c ,enroll.Program_Status__c , enroll.PeopleSoft_Action_Reason__c from enrollmentsToWorkWith as enroll left anti join sfsProgramEnrollmentReader as sfReader on enroll.PeopleSoft_Program_Enrollment_Unique_Id__c = sfReader.PeopleSoft_Program_Enrollment_Unique_Id__c where enroll.Start_Term__c is not null AND enroll.hed__Account__c IS NOT NULL #- statement: print insertEnrollment - statement: (numInsertCount) => SELECT COUNT(*) as Num FROM insertEnrollment #- statement: print numInsertCount - statement: insert insertEnrollment into ProgramLogFile1 # - statement: (success,error)=> insert insertEnrollment into sfProgEnrollmentWriter # - statement: print success # - statement: print error ##### End - Push insert enrollments into a local file ##### # Insert current dateTime into env variable. - statement: | (lastModifiedDateTime) => SELECT current_date() progEnrollLastUpdateDate - statement: UPDATE lastModifiedDateTime INTO env.vars #- statement: print lastModifiedDateTime # Add more statements to convert, join, aggregrate, transform, and integrate your data