Go Back
Peoplesoft to Salesforce: Degree Picklist
Publisher
:
Run In Lingk
Description
Peoplesoft to Salesforce: Degree Picklist - This recipe retrieves Degree Picklist from Peoplesoft and writes the data back to Salesforce
Browse the knowledge base
Twitter
E-Mail
# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - Peoplesoft to Salesforce: Degree Picklist # Recipe URL - https://app.lingk.io/a/10932/tf/18832 # Description - 3 - Peoplesoft to Salesforce: Degree Picklist # Peoplesoft to Salesforce Academic Plans # Insert the academic plans from Peoplesoft to the Account table in Salesforce. # Reads from a web service on Peoplesoft # This will retrieve academic parents record type data for parent ID's. # Inserts or updates the Account table in Salesforce for SF Academic Programs # Systems - Peoplesoft, Salesforce # Connectors - HttpV2, Salesforce # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Peoplesoft Models Used: #1. Plans ## Salesforce Objects Used: #1. 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 Academic Plan 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: peopleSoftAcadPlanData type: httpV2Reader properties: httpMethod: POST headerJson: { "Authorization":"Basic {{env.vars.apiAccessToken}}", "Content-Type":"application/json" } acceptInvalidCerts: false url: '{{env.vars.midStateInstanceURL}}' paginationType: requestBody body: { "isDebugMode": false, "rowLimit": 500, "pageNumber": 1, "noEffectiveDateLogic": false, "noEffectiveStatusLogic": true, "includeFieldTypes": false, "records": [ { "recordName": "ACAD_PLAN_TBL", "sqlWhereClause": " INSTITUTION = '{{env.inst-code}}'" } ] } schema: fields: - name: 'ACAD_CAREER' type: 'string' - name: 'ACAD_PLAN' type: 'string' - name: 'ACAD_PLAN_TYPE' type: 'string' - name: 'ACAD_PROG' type: 'string' - name: 'CIP_CODE' type: 'string' - name: 'DEGREE' type: 'string' - name: 'DESCR' type: 'string' - name: 'DESCRLONG' type: 'string' - name: 'DESCRSHORT' type: 'string' - name: 'DIPLOMA_DESCR' type: 'string' - name: 'DIPLOMA_INDENT' type: 'long' - name: 'DIPLOMA_PRINT_FL' type: 'string' - name: 'EFFDT' type: 'string' - name: 'EFF_STATUS' type: 'string' - name: 'EVALUATE_PLAN' type: 'string' - name: 'FA_ELIGIBILITY' type: 'string' - name: 'FIRST_TERM_VALID' type: 'string' - name: 'HEGIS_CODE' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'PLN_REQTRM_DFLT' type: 'string' - name: 'REQUIRED_GPA' type: 'long' - name: 'SAA_WHIF_DISP_ADVR' type: 'string' - name: 'SAA_WHIF_DISP_PREM' type: 'string' - name: 'SAA_WHIF_DISP_STD' type: 'string' - name: 'SCC_SFP_AY_I_UNTS' type: 'long' - name: 'SCC_SFP_AY_UNIT' type: 'string' - name: 'SCC_SFP_AY_UNTS' type: 'long' - name: 'SCC_SFP_FIRST_PROF' type: 'string' - name: 'SCC_SFP_OPEID' type: 'string' - name: 'SCC_SFP_PLAN_TYPE' type: 'string' - name: 'SCC_SFP_PROG_UNIT' type: 'string' - name: 'SCC_SFP_REQ_UNTS' type: 'long' - name: 'SFA_SPEC_PROG_FLG' type: 'string' - name: 'SSR_LAST_ADM_TERM' type: 'string' - name: 'SSR_LAST_PRS_DT' type: 'string' - name: 'SSR_NSC_CRD_LVL' type: 'string' - name: 'SSR_NSC_INCL_PLAN' type: 'string' - name: 'SSR_PROG_LENGTH' type: 'long' - name: 'SSR_PROG_LEN_TYPE' type: 'string' - name: 'SSR_SFP_BBAY_IND' type: 'string' - name: 'SSR_SFP_BBY_TRM_AY' type: 'long' - name: 'SSR_SFP_PROJ_CRSE' type: 'string' - name: 'STUDY_FIELD' type: 'string' - name: 'TRANSCRIPT_LEVEL' type: 'string' - name: 'TRNSCR_DESCR' type: 'string' - name: 'TRNSCR_INDENT' type: 'long' - name: 'TRNSCR_PRINT_FL' type: 'string' - name: 'rowNumber' type: 'long' - name: 'errorMessages' type: 'string' - name: 'meta' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'currentUser' type: 'string' - name: 'dbType' type: 'string' - name: 'dbname' type: 'string' - name: 'debugMessages' type: 'string' - name: 'psftTransactionId' type: 'string' - name: 'responseDTTM' type: 'string' - name: 'toolsVer' type: 'string' - name: 'pageNumber' type: 'long' - name: 'responseCode' type: 'long' ###### End - HTTP Reader connectors ###### ###### Start - Salesforce Reader connectors ###### # This will get us the existing Salesforce Academic Programs - name: sfAcademicProgramReader type: salesforceReader delayedRead: false properties: useBearerToken: true query: SELECT PeopleSoft_Academic_Program_ID__c ,Id,Academic_Program_Inactive__c FROM Account schema: fields: - name: 'Id' type: 'string' - name: 'PeopleSoft_Academic_Program_ID__c' type: 'string' - nane: 'Academic_Program_Inactive__c' type: 'boolean' # this will get us the acedemic parents record type rows for parent ids. - name: sfAcademicProgramParentIDReader type: salesforceReader delayedRead: false properties: useBearerToken: true query: SELECT Name ,PeopleSoft_Academic_Parent_Code__c ,Id FROM Account schema: fields: - name: 'PeopleSoft_Academic_Parent_Code__c' type: 'string' - name: 'Name' type: 'string' - name: 'Id' type: 'string' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### - name: sfAcadPlanWriter type: salesforceBulkWriter onError: continue isSerial: true properties: useBearerToken: true sfObject: Account batchSize: 100 ###### End - Salesforce Writer connectors ###### ###### Start - Local Writer connectors ###### # log file - name: ProgramLogFile type: localFileWriter format: excelFormat properties: fileName: "PStoSF_AcadPlan.xlsx" ###### End - Local 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" # _____ _ _ _ # / ____| | | | | | # | (___ | |_ __ _| |_ ___ _ __ ___ ___ _ __ | |_ ___ # \___ \| __/ _` | __/ _ \ '_ ` _ \ / _ \ '_ \| __/ __| # ____) | || (_| | || __/ | | | | | __/ | | | |_\__ \ # |_____/ \__\__,_|\__\___|_| |_| |_|\___|_| |_|\__|___/ # 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 *********************************************************************************************** # Print and count Plan data from Peoplesoft #- statement: print peopleSoftAcadPlanData - statement: (peopleSoftAcadPlanDataCount) => SELECT COUNT(*) as Num FROM peopleSoftAcadPlanData #- statement: print peopleSoftAcadPlanDataCount #################################### LOGS - Debugging purpuses ###################################### #- statement: (err)=> select errorMessages from peopleSoftAcadPlanData #- statement: print err #- statement: (code)=> select responseCode from peopleSoftAcadPlanData #- statement: print code # Get the acedemic programs already loaded in PS - statement: (sfAcademicProgramReader) => Select * from sfAcademicProgramReader WHERE RecordTypeId = '{{env.vars.academicPlanProgramRecordType}}' #- statement: print sfAcademicProgramReader # - statement: printschema sfAcademicProgramReader # - statement: (progFromSFCount) => SELECT COUNT(*) as Num FROM sfAcademicProgramReader # - statement: print progFromSFCount # This will get us the acedmemic program parent IDs. - statement: (sfAcademicProgramParentIDReader) => Select * from sfAcademicProgramParentIDReader WHERE RecordTypeId = '{{env.vars.academicParentRecordType}}' ORDER BY Name #- statement: print sfAcademicProgramParentIDReader # - statement: printschema sfAcademicProgramReader - statement: (academicParentCount) => SELECT COUNT(*) as Num FROM sfAcademicProgramParentIDReader #- statement: print academicParentCount # Grab out the values we need for salesforce # 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: (progsToWorkWith) => select '{{env.vars.academicPlanProgramRecordType}}' AS RecordTypeID , ap.ACAD_PLAN as PeopleSoft_Academic_Program_ID__c , ap.DESCR as Name , ap.ACAD_PROG ,case ap.EFF_STATUS when 'A' then 0 else 1 end as University_Department_Inactive__c, parentIdReader.Id as ParentId, case ap.EFF_STATUS when 'A' then 'false' when 'I' then 'true' else '' end as Academic_Program_Inactive__c FROM peopleSoftAcadPlanData ap left join sfAcademicProgramParentIDReader parentIdReader on parentIdReader.PeopleSoft_Academic_Parent_Code__c = ap.ACAD_PROG #- statement: print progsToWorkWith # Insert the programs needed from Peoplesoft into a local file as a log - statement: insert progsToWorkWith into ProgramLogFile # Print and count the programs from Peoplesoft needed - statement: (workingSetCount) => SELECT COUNT(*) as Num FROM progsToWorkWith #- statement: print workingSetCount # Retrieve the data of the programs that need are already in Salesforce and needs to be updated - statement: (updatePlans) => select prog.RecordTypeID , prog.University_Department_Inactive__c , prog.Name , prog.ParentId as ParentID , prog.PeopleSoft_Academic_Program_ID__c , prog.Academic_Program_Inactive__c , sfReader.Id as Id from progsToWorkWith as prog inner join sfAcademicProgramReader as sfReader on prog.PeopleSoft_Academic_Program_ID__c = sfReader.PeopleSoft_Academic_Program_ID__c # Print and count the plans that needs to be updated #- statement: print updatePlans - statement: (numUpdatesCount) => SELECT COUNT(*) as Num FROM updatePlans #- statement: print numUpdatesCount # Update the plans that needs to be updated in Salesforce - statement: (success,error)=>update updatePlans into sfAcadPlanWriter #- statement: print success #- statement: print error # Retrieve the records from Peoplesoft that is not found in Salesforce academic program # 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: (insertPlans) => select prog.RecordTypeID , prog.University_Department_Inactive__c , prog.Name , prog.ParentId as ParentID , prog.PeopleSoft_Academic_Program_ID__c , prog.Academic_Program_Inactive__c from progsToWorkWith as prog LEFT ANTI join sfAcademicProgramReader as sfReader on prog.PeopleSoft_Academic_Program_ID__c = sfReader.PeopleSoft_Academic_Program_ID__c # Print and count the plans that needs to be inserted #- statement: print insertPlans - statement: (numInsertCount) => SELECT COUNT(*) as Num FROM insertPlans #- statement: print numInsertCount # Insert the plans that needs to be inserted in Salesforce - statement: (success,error)=>insert insertPlans into sfAcadPlanWriter #- statement: print success #- statement: print error ###################################################### Final Logging of results ############################################### # - statement: (countSummation) => select concat('Number of academic programs from PeopleSoft = ', Num) from acadPlanDataCount # UNION # select concat('Number of academic departments from PeopleSoft = ', Num) from acadProgDataCount # UNION # select concat('Number of academic programs from SalesForce = ', Num) from progFromSFCount # UNION # select concat('Number of academic departments from SalesForce = ', Num) from universityDepartmentsCount # UNION # select concat('Number of programs from Our Working set = ', Num) from workingSetCount # UNION # select concat('Number of programs set for update = ', Num) from numUpdatesCount # UNION # select concat('Number of programs set for insert = ', Num) from numInsertCount # - statement: print countSummation # Add more statements to convert, join, aggregrate, transform, and integrate your data
PeopleSoft to Salesforce - University Picklist
Peoplesoft to Salesforce: Terms