# _____ _ _____ __ # | __ \ (_) |_ _| / _| # | |__) |___ ___ _ _ __ ___ | | _ __ | |_ ___ # | _ // _ \/ __| | '_ \ / _ \ | | | '_ \| _/ _ \ # | | \ \ __/ (__| | |_) | __/ _| |_| | | | || (_) | # |_| \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ # | | # |_| # Project Name - PeopleSoft to Salesforce - Student Account # Recipe URL - https://app.lingk.io/a/10932/tf/18951 # Description - 9 - PeopleSoft to Salesforce - Student Account Sync # Select all student account from PeopleSoft ACCOUNT_SF table. # Read all aid-years information from PeopleSoft AID_YEAR_TBL table. # Reject non-matching records due to SF Contact. # Sum account balance and anticipated aid amount per aid year. # Insert/Update record into SF by Contact__c. # Insert student account requested fields with their SF id in Lingk environment file. # Systems - Peoplesoft, Salesforce # Connectors - HttpV2, Salesforce # Data Flows - Single Direction # Connection Type - API to API # Data Models ## Peoplesoft Models Used: #1. Student Account connector ## Salesforce Objects Used: #1. Contacts #2. Student_Account__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 Student Account 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: peopleSoftStudentAccountData type: httpV2Reader properties: httpMethod: POST headerJson: { "Authorization":"Basic {{env.vars.apiAccessToken}}", "Content-Type":"application/json" } acceptInvalidCerts: false url: '{{env.vars.midStateInstanceURL}}' paginationType: requestBody body: { "rowLimit": 1000, "pageNumber": 1, "records": [ { "recordName": "ACCOUNT_SF", "sqlWhereClause": "BUSINESS_UNIT = '{{env.inst-code}}' AND INCLUDE_IN_BALANCE <> 'N' AND SCC_ROW_UPD_DTTM >= %DateTimeIn('{{env.vars.studentAccLastUpdateDate}}')" }, { "recordName": "SSF_DEP_DUE_VW", "parentRecordName": "ACCOUNT_SF" }, { "recordName": "STDNT_AID_ATRBT", "parentRecordName": "ACCOUNT_SF", "includeDescriptionsFor": [ "PROCESSING_STATUS" ], }, { "recordName": "STDNT_SS_AN_VW", "parentRecordName": "STDNT_AID_ATRBT" } ] } schema: fields: - name: 'ACCOUNT_BALANCE' type: 'string' - name: 'ACCOUNT_NBR' type: 'string' - name: 'ACCOUNT_TERM' type: 'string' - name: 'BUSINESS_UNIT' type: 'string' - name: 'EMPLID' type: 'string' - name: 'INCLUDE_IN_BALANCE' type: 'string' - name: 'SCC_ROW_UPD_DTTM' type: 'string' - name: 'SSF_DEP_DUE_VW' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'fields' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'BALANCE_REMAIN' type: 'string' - name: 'BUSINESS_UNIT' type: 'string' - name: 'EMPLID' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'STDNT_AID_ATRBT' 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: 'AID_YEAR' type: 'string' - name: 'COUNSELOR' type: 'string' - name: 'PROCESSING_STATUS_description' type: 'string' - name: 'PROCESSING_STATUS' type: 'string' - name: 'EMPLID' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'STDNT_SS_AN_VW' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'fields' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'AID_YEAR' type: 'string' - name: 'BUSINESS_UNIT' type: 'string' - name: 'EMPLID' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'STRM' type: 'string' - name: 'TOTAL_ANTIC_AID' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'objectName' type: 'string' - name: 'objectType' type: 'string' - name: 'STDNT_SS_AN_VW' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'fields' type: 'array' items: name: 'arrayElement' type: 'object' objectSchema: name: objectSchemaSchema fields: - name: 'AID_YEAR' type: 'string' - name: 'BUSINESS_UNIT' type: 'string' - name: 'EMPLID' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'STRM' type: 'string' - name: 'TOTAL_ANTIC_AID' type: 'string' ## PeopleSoft Aid Year Connector - name: peopleSoftAidYear type: httpV2Reader properties: httpMethod: POST headerJson: { "Authorization":"Basic {{env.vars.apiAccessToken}}", "Content-Type":"application/json" } acceptInvalidCerts: false url: '{{env.vars.midStateInstanceURL}}' paginationType: requestBody body: { "rowLimit": 1000, "pageNumber": 1, "records": [ { "recordName": "AID_YEAR_TBL", "sqlWhereClause": "INSTITUTION = '{{env.inst-code}}' " } ] } schema: fields: - name: 'ACAD_YEAR_END' type: 'string' - name: 'ACAD_YEAR_START' type: 'string' - name: 'AID_YEAR' type: 'string' - name: 'DESCR' type: 'string' - name: 'DESCRSHORT' type: 'string' - name: 'INSTITUTION' type: 'string' - name: 'rowNumber' type: 'long' ###### End - HTTP Reader connectors ###### ###### Start - Salesforce Reader connectors ###### # 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' # Salesforce Student Account Reader - name: sfStudentAccountReader type: salesforceReader delayedRead: true parameterizedBy: batchedStudentAccountId properties: useBearerToken: true query: SELECT Id, Name, Contact__c FROM Student_Account__c WHERE Contact__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: 'Contact__c' type: 'string' ###### End - Salesforce Reader connectors ###### ###### Start - Salesforce Writer connectors ###### # Salesforce Service Indicator Writer - name: sfStudentAccountWriter type: salesforceBulkWriter onError: continue isSerial: true properties: onError: continue useBearerToken: true sfObject: Student_Account__c batchSize: 50 ###### Start - Local Writer connectors ###### # Local File Writer - name: updatedStudentAccFileWriter type: localFileWriter format: csvWriter properties: fileName: "UpdatedStudentAccount.csv" - name: insertedStudentAccFileWriter type: localFileWriter format: csvWriter properties: fileName: "InsertedStudentAccount.csv" ###### End - Local Writer connectors ###### # _______ _ # |__ __| | | # | | __ _ ___| | _____ # | |/ _` / __| |/ / __| # | | (_| \__ \ <\__ \ # |_|\__,_|___/_|\_\___/ # 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: 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 # ______ _ # | ____| | | # | |__ ___ _ __ _ __ ___ __ _| |_ ___ # | __/ _ \| '__| '_ ` _ \ / _` | __/ __| # | | | (_) | | | | | | | | (_| | |_\__ \ # |_| \___/|_| |_| |_| |_|\__,_|\__|___/ ## FORMATS specify how files should be processed as they are being read or written to 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 peopleSoftStudentAccountData - statement: (peopleSoftStudentAccountDataCount) => SELECT COUNT(*) FROM peopleSoftStudentAccountData #- statement: PRINT peopleSoftStudentAccountDataCount #- statement: print peopleSoftAidYear - statement: (peopleSoftAidYearCount) => SELECT COUNT(*) FROM peopleSoftAidYear #- statement: PRINT peopleSoftAidYearCount # inline function - flatten the array to make it a basic table - statement: (inlineDepartmentDue) => SELECT inline(SSF_DEP_DUE_VW.fields) FROM peopleSoftStudentAccountData #- statement: print inlineDepartmentDue # inline function - flatten the array to make it a basic table - statement: (inlineStudentAttribute) => SELECT inline(STDNT_AID_ATRBT.fields) FROM peopleSoftStudentAccountData #- statement: PRINT inlineStudentAttribute # inline function - flatten the array to make it a basic table - statement: (inlineStudentAidData) => SELECT inline(STDNT_SS_AN_VW.fields) FROM inlineStudentAttribute #- statement: PRINT inlineStudentAidData ## 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 AID_YEAR ## The PARTITION BY is dividing the rows into small partitions by AID_YEAR preference field - statement: (distinctStudentAidData) => SELECT *, ROW_NUMBER() OVER (PARTITION BY EMPLID, AID_YEAR ORDER BY AID_YEAR DESC) AS Row FROM inlineStudentAidData - statement: (finalDistinctStudentAidData) => SELECT * FROM distinctStudentAidData WHERE Row = 1 # 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: | (finalStudentAidData) => SELECT DISTINCT att.EMPLID, att.INSTITUTION, att.AID_YEAR, att.PROCESSING_STATUS_description, aid.TOTAL_ANTIC_AID, ay.DESCRSHORT FROM inlineStudentAttribute att INNER JOIN finalDistinctStudentAidData aid ON TRIM(att.EMPLID) = TRIM(aid.EMPLID) AND TRIM(att.AID_YEAR) = TRIM(aid.AID_YEAR) AND TRIM(att.INSTITUTION) = '{{env.inst-code}}' LEFT JOIN peopleSoftAidYear ay ON TRIM(att.AID_YEAR) = TRIM(ay.AID_YEAR) AND TRIM(ay.INSTITUTION) = '{{env.inst-code}}' - statement: | (aggrigatedAidValues) => SELECT EMPLID, INSTITUTION, AID_YEAR, PROCESSING_STATUS_description, IFNULL(SUM(CAST(TRIM(TOTAL_ANTIC_AID) AS DOUBLE)), '') `TOTAL_ANTIC_AID`, DESCRSHORT FROM finalStudentAidData GROUP BY EMPLID, INSTITUTION, AID_YEAR, PROCESSING_STATUS_description, DESCRSHORT #- statement: print aggrigatedAidValues ## Get Salesforce Student Info - statement: (distinctStudentId) => SELECT DISTINCT TRIM(EMPLID) `id` FROM peopleSoftStudentAccountData # 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 # 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 * FROM peopleSoftStudentAccountData s LEFT ANTI JOIN sfContactReader sf ON IFNULL(TRIM(s.EMPLID), '') = IFNULL(TRIM(sf.University_Student_ID__c), '') - statement: (rejectedRecordByNonMatchingContactCount) => SELECT COUNT(*) FROM rejectedRecordByNonMatchingContact #- statement: PRINT rejectedRecordByNonMatchingContactCount # 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: | (studentAccountBalanceTotal) => SELECT s.EMPLID, s.BUSINESS_UNIT, IFNULL(SUM(CAST(TRIM(s.ACCOUNT_BALANCE) AS DOUBLE)), '') `ACCOUNT_BALANCE`, IFNULL(SUM(CAST(TRIM(dd.BALANCE_REMAIN) AS DOUBLE)), '') `BALANCE_REMAIN` FROM peopleSoftStudentAccountData s LEFT JOIN inlineDepartmentDue dd ON TRIM(s.EMPLID) = TRIM(dd.EMPLID) AND IFNULL(UPPER(TRIM(dd.BUSINESS_UNIT)), '') = '{{env.inst-code}}' GROUP BY s.EMPLID, s.BUSINESS_UNIT #- statement: print studentAccountBalanceTotal - statement: (studentAccountBalanceTotalCount) => SELECT COUNT(*) FROM studentAccountBalanceTotal #- statement: PRINT studentAccountBalanceTotalCount ## Collect info from all tables required for Student Account. # 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: (completeInfoForStudentAccount) => SELECT IFNULL(TRIM(s.EMPLID), '') `EMPLID`, IFNULL(TRIM(s.ACCOUNT_BALANCE), '') `ACCOUNT_BALANCE`, IFNULL(TRIM(s.BALANCE_REMAIN), '') `BALANCE_REMAIN`, IFNULL(TRIM(ad.DESCRSHORT), '') `AID_YEAR`, IFNULL(TRIM(ad.PROCESSING_STATUS_description), '') `PROCESSING_STATUS_description`, IFNULL(TRIM(ad.TOTAL_ANTIC_AID), '') `TOTAL_ANTIC_AID` FROM studentAccountBalanceTotal s LEFT JOIN aggrigatedAidValues ad ON TRIM(s.EMPLID) = TRIM(ad.EMPLID) AND IFNULL(UPPER(TRIM(ad.INSTITUTION)), '') = '{{env.inst-code}}' #- statement: PRINT completeInfoForStudentAccount - statement: (completeInfoForStudentAccountCount) => SELECT COUNT(*) FROM completeInfoForStudentAccount #- statement: PRINT completeInfoForStudentAccountCount ## changes done for AID_YEAR field. ## Map Student Account info with SF fields - statement: (mappedStudentAccount) => SELECT sf.Id `Contact__c`, CAST(IFNULL(s.ACCOUNT_BALANCE, '') AS DOUBLE) `Account_Total_Balance__c`, CAST(IFNULL(s.BALANCE_REMAIN, '') AS DOUBLE) `Deposit_Due__c`, CAST(IFNULL(s.TOTAL_ANTIC_AID, '') AS DOUBLE) `Anticipated_Aid__c`, IFNULL(s.AID_YEAR, '') `Aid_Year__c`, IFNULL(s.PROCESSING_STATUS_description, '') `Aid_Processing_Status__c` FROM completeInfoForStudentAccount s INNER JOIN sfContactReader sf ON TRIM(s.EMPLID) = TRIM(sf.University_Student_ID__c) #- statement: PRINT mappedStudentAccount - statement: (mappedStudentAccountCount) => SELECT COUNT(*) FROM mappedStudentAccount #- statement: PRINT mappedStudentAccountCount - statement: (distinctStudentAccountId) => SELECT DISTINCT Contact__c `id` FROM mappedStudentAccount # 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 distinctStudentAccountId --outputBatchTable batchedStudentAccountId --result resultStatus - statement: REFRESH sfStudentAccountReader - statement: (sfStudentAccountReaderCount) => SELECT COUNT(*) FROM sfStudentAccountReader #- statement: PRINT sfStudentAccountReaderCount - statement: (studentAccountToUpdate) => SELECT sf.Id, m.Account_Total_Balance__c, m.Deposit_Due__c, m.Anticipated_Aid__c, m.Contact__c, m.Aid_Year__c, m.Aid_Processing_Status__c FROM mappedStudentAccount m INNER JOIN sfStudentAccountReader sf ON IFNULL(TRIM(m.Contact__c), '') = IFNULL(TRIM(sf.Contact__c), '') #- statement: PRINT studentAccountToUpdate - statement: (studentAccountToUpdateCount) => SELECT COUNT(*) FROM studentAccountToUpdate #- statement: PRINT studentAccountToUpdateCount # 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: (studentAccountToInsert) => SELECT m.* FROM mappedStudentAccount m LEFT ANTI JOIN sfStudentAccountReader sf ON IFNULL(TRIM(m.Contact__c), '') = IFNULL(TRIM(sf.Contact__c), '') #- statement: PRINT studentAccountToInsert - statement: (studentAccountToInsertCount) => SELECT COUNT(*) FROM studentAccountToInsert #- statement: PRINT studentAccountToInsertCount ###### Start - Update Student Account ###### - statement: (updateResults, updateErrors) => UPDATE studentAccountToUpdate INTO sfStudentAccountWriter #- statement: PRINT updateResults #- statement: PRINT updateErrors ###### End - Update Student Account ###### ###### Start - Insert Student Account ###### - statement: (insertResults, insertErrors) => INSERT studentAccountToInsert INTO sfStudentAccountWriter #- statement: PRINT insertResults #- statement: PRINT insertErrors ###### End - Insert Student Account ###### #################################### LOGS - Debugging purpuses ###################################### # - statement: PRINT peopleSoftStudentAccountDataCount # - statement: PRINT sfContactReaderCount # - statement: PRINT rejectedRecordByNonMatchingContactCount # - statement: PRINT completeInfoForStudentAccountCount # - statement: PRINT mappedStudentAccountCount # - statement: PRINT sfStudentAccountReaderCount # - statement: PRINT studentAccountToUpdateCount # - statement: PRINT studentAccountToInsertCount ###### Start - Insert Student Account in LocalFile ###### - statement: REFRESH sfStudentAccountReader - statement: | (existingStudentAccountInfo) => SELECT m.* FROM studentAccountToUpdate m INNER JOIN sfStudentAccountReader sf ON IFNULL(TRIM(m.Contact__c), '') = IFNULL(TRIM(sf.Contact__c), '') - statement: INSERT existingStudentAccountInfo INTO updatedStudentAccFileWriter - statement: | (existingStudentAccountInfo) => SELECT sf.Id, m.* FROM studentAccountToInsert m INNER JOIN sfStudentAccountReader sf ON IFNULL(TRIM(m.Contact__c), '') = IFNULL(TRIM(sf.Contact__c), '') - statement: INSERT existingStudentAccountInfo INTO insertedStudentAccFileWriter ###### End - Insert Student Account in LocalFile ###### ## Update current date time in env variable - statement: | (lastModifiedDateTime) => SELECT date_format(from_utc_timestamp(current_timestamp(), 'America/New_York'), "yyyy-MM-dd-HH.mm.ss.SSSSSS") AS `studentAccLastUpdateDate` - statement: UPDATE lastModifiedDateTime INTO env.vars # Add more statements to convert, join, aggregrate, transform, and integrate your data