PeopleSoft to Salesforce - University Picklist

#   _____           _              _____        __      
#  |  __ \         (_)            |_   _|      / _|     
#  | |__) |___  ___ _ _ __   ___    | |  _ __ | |_ ___  
#  |  _  // _ \/ __| | '_ \ / _ \   | | | '_ \|  _/ _ \ 
#  | | \ \  __/ (__| | |_) |  __/  _| |_| | | | || (_) |
#  |_|  \_\___|\___|_| .__/ \___| |_____|_| |_|_| \___/ 
#                    | |                                
#                    |_|

# Project Name - PeopleSoft to Salesforce - University Picklist

# Recipe URL - https://app.lingk.io/a/10932/tf/18833

# Description - 4 - PeopleSoft to Salesforce - University Picklist
                # Select all Academic parent from PS ACAD_PROG_TBL table.
                # This recipe is using HTTP V2 connector (with pagination) to get records from peopleSoft.
                # Insert/update records in salesforce Account object and set record tpe  = 'Academic parent'
                # Select all University Department from PS ACAD_ORG_TBL table.
                # Insert/update records in salesforce Account object and set record tpe  = 'University Department'

# Systems - Peoplesoft, Salesforce
# Connectors - HttpV2, Salesforce
# Data Flows - Single Direction
# Connection Type - API to API

# Data Models
## Peoplesoft Models Used:
#1. Programs

## Salesforce Objects Used:
#1. RecordType
#2. 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 Program 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: peopleSoftAcadProgData
  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,
        "noEffectiveDateLogic": false,
        "noEffectiveStatusLogic": false,
        "records": [
          {
            "recordName": "ACAD_PROG_TBL",
            "sqlWhereClause": " INSTITUTION = 'MSTCG'"
          }
        ]
      }
  schema:
    fields:
      - name: 'ACAD_CAREER'
        type: 'string'
      - name: 'ACAD_GROUP'
        type: 'string'
      - name: 'ACAD_ORG'
        type: 'string'
      - name: 'ACAD_PLAN'
        type: 'string'
      - name: 'ACAD_PROG'
        type: 'string'
      - name: 'CAMPUS'
        type: 'string'                            
      - name: 'DESCR'
        type: 'string'
      - name: 'DESCRSHORT'
        type: 'string'                         
      - name: 'EFFDT'
        type: 'string'
      - name: 'EFF_STATUS'
        type: 'string'                  
      - name: 'INSTITUTION'
        type: 'string'
      - name: 'rowNumber'
        type: 'long'

# PeopleSoft Academic organization reader (University Department)
  ## 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: peopleSoftAcadOrgData
  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": 300,
        "pageNumber": 1,
        "noEffectiveDateLogic": false,
        "noEffectiveStatusLogic": false,
        "records": [
          {
            "recordName": "ACAD_ORG_TBL",
            "sqlWhereClause": " INSTITUTION = 'MSTCG'"
          }
        ]
      }   
  schema:
    fields:
      - name: 'ACAD_ORG'
        type: 'string'
      - name: 'CAMPUS'
        type: 'string'
      - name: 'CAMPUS_EDIT'
        type: 'string'
      - name: 'COURSE_EDIT'
        type: 'string'
      - name: 'DESCR'
        type: 'string'
      - name: 'DESCRFORMAL'
        type: 'string'
      - name: 'DESCRSHORT'
        type: 'string'
      - name: 'EFFDT'
        type: 'string'
      - name: 'EFF_STATUS'
        type: 'string'
      - name: 'INSTITUTION'
        type: 'string'
      - name: 'INSTR_EDIT'
        type: 'string'
      - name: 'MANAGER_ID'
        type: 'string'
      - name: 'SUBJECT_EDIT'
        type: 'string'
      - name: 'rowNumber'
        type: 'long'

###### End - HTTP Reader connectors ######

###### Start - Salesforce Reader connectors ######

# SalesForce Account reader for both Academic Org and Academic Prog
-
  name: sfAccountReader
  type: salesforceReader
  delayedRead: false
  properties:
    useBearerToken: true
    query: 
      SELECT             
        PeopleSoft_Academic_Parent_Code__c,
        PeopleSoft_Univ_Department_Code__c,
        Id
      FROM Account               
    schema:
      fields:
      - name: 'Id'
        type: 'string'     
      - name: 'PeopleSoft_Academic_Parent_Code__c'
        type: 'string' 
      - name: 'PeopleSoft_Univ_Department_Code__c'
        type: 'string'

## Salesforce RecordType Reader
-
  name: sfRecordTypeReader
  type: salesforceReader
  delayedRead: false
  properties:
    useBearerToken: true
    query: 
      SELECT       
        developerName,
        Id
      FROM RecordType
    schema:
      fields: 
        - name: 'developerName'
          type: 'string'
        - name: 'Id'
          type: 'string'

###### End - Salesforce Reader connectors ######

###### Start - Salesforce Writer connectors ######

# Salesforce account writer
-
    name: sfAccountWriter
    type: salesforceBulkWriter
    onError: continue
    isSerial: true
    properties:
      onError: continue
      useBearerToken: true
      sfObject: Account
      batchSize: 100

###### End - Salesforce Writer connectors ######

###### Start - Local Writer connectors ######

# log file
-
    name: AcademicProgLogFile
    type: localFileWriter
    format: csvWriter
    properties:
      fileName: "PStoSF_AcadProg.csv"

-
    name: AcademicOrgLogFile
    type: localFileWriter
    format: csvWriter
    properties:
      fileName: "PStoSF_AcadOrg.csv"

###### End - Local Writer connectors ######

#   ______                         _       
#  |  ____|                       | |      
#  | |__ ___  _ __ _ __ ___   __ _| |_ ___ 
#  |  __/ _ \| '__| '_ ` _ \ / _` | __/ __|
#  | | | (_) | |  | | | | | | (_| | |_\__ \
#  |_|  \___/|_|  |_| |_| |_|\__,_|\__|___/

# # 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 ***********************************************************************************************

##### Start for Academic Program (Academic Parent) #####

# Print and count the Programs data from Peoplesoft
#- statement: print peopleSoftAcadProgData
- statement: (peopleSoftAcadProgDataCount) => SELECT COUNT(*) as Num FROM peopleSoftAcadProgData
#- statement: print peopleSoftAcadProgDataCount

- statement: (sfRecordTypeReader) => Select * From sfRecordTypeReader WHERE developerName IN ('Academic_Parent', 'University_Department') AND SobjectType = 'Account'

# Retrieve the Programs data that matches with Saalesforce where name of the record type reader is equals to "Academic_Parent"
- statement: |
    (finalAcademicPrograms) => 
      SELECT
        IFNULL(TRIM(ap.ACAD_PROG), '')                 `PeopleSoft_Academic_Parent_Code__c`,
        IFNULL(TRIM(ap.DESCR), '')                     `Name`,
        CASE WHEN IFNULL(TRIM(ap.ACAD_PROG), '') != '' THEN '{{env.vars.sfParentAccountID}}'     
        ELSE '' END                                    `ParentId`,
        IFNULL(rt.Id, '')                              `RecordTypeId`

FROM peopleSoftAcadProgData ap
      INNER JOIN sfRecordTypeReader rt ON 1=1
      WHERE rt.developerName = 'Academic_Parent'

# Print and count the final Academic Programs form the previous statement
#- statement: print finalAcademicPrograms
- statement: (finalAcademicProgramsCount) => SELECT COUNT(*) FROM finalAcademicPrograms
#- statement: print finalAcademicProgramsCount

# Retrieve and count the amount of data from the Accounts
- statement:
   (sfAccountReaderCount) => Select count(*) from sfAccountReader WHERE RecordType.developerName IN ('Academic_Parent', 'University_Department') AND RecordType.SobjectType = 'Account'

#### Start - Update the academic programs in Salesforce ####

# Retrieves the records from the finalAcademicPrograms table where the PeopleSoft_Academic_Parent_Code__c field from Salesforce is not equals to an empty value that will be updated
- statement: |
    (academicProgramsToUpdate) =>
        SELECT
          sf.Id,
          acc.Name,
          acc.ParentId         
        FROM finalAcademicPrograms acc
        INNER JOIN sfAccountReader sf ON IFNULL(TRIM(UPPER(acc.PeopleSoft_Academic_Parent_Code__c)), '') = IFNULL(TRIM(UPPER(sf.PeopleSoft_Academic_Parent_Code__c)), '')
        WHERE IFNULL(TRIM(sf.PeopleSoft_Academic_Parent_Code__c), '') != ''

#- statement: print academicProgramsToUpdate

- statement: (success,error) => UPDATE academicProgramsToUpdate INTO sfAccountWriter
#- statement: print success
#- statement: print error

#### End - Update the academic programs in Salesforce ####

#### Start - Insert the academic programs in 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: |
    (academicProgramsToInsert) =>
        SELECT
          acc.*       
        FROM finalAcademicPrograms acc
        LEFT ANTI JOIN sfAccountReader sf ON IFNULL(TRIM(UPPER(acc.PeopleSoft_Academic_Parent_Code__c)), '') = IFNULL(TRIM(UPPER(sf.PeopleSoft_Academic_Parent_Code__c)), '')

#- statement: print academicProgramsToInsert

- statement: (success,error) => INSERT academicProgramsToInsert INTO sfAccountWriter
#- statement: print success
#- statement: print error

#### End - Insert the academic programs in Salesforce ####

##### End for Academic Program (Academic Parent) #####

##### Start for Academic Organization (University Department) #####

#- statement: print peopleSoftAcadOrgData
- statement: (peopleSoftAcadOrgDataCount) => SELECT COUNT(*) as Num FROM peopleSoftAcadOrgData
#- statement: print peopleSoftAcadOrgDataCount

# CASE function is used as a dictionary and depending on the value received as a STRING, is the new status value that will be retrieved now as a BOOLEAN
- statement: |
    (finalAcademicOrganizations) => 
      SELECT
        IFNULL(TRIM(ap.ACAD_ORG), '')                  `PeopleSoft_Univ_Department_Code__c`,
        IFNULL(TRIM(ap.DESCR), '')                     `Name`,
        CASE WHEN IFNULL(TRIM(ap.ACAD_ORG), '') != '' THEN '{{env.vars.sfParentAccountID}}'     
        ELSE '' END                                    `ParentId`,
        IFNULL(rt.Id, '')                              `RecordTypeId`

FROM peopleSoftAcadOrgData ap
      INNER JOIN sfRecordTypeReader rt ON 1=1
      WHERE rt.developerName = 'University_Department'

#- statement: print finalAcademicOrganizations
- statement: (finalAcademicOrganizationsCount) => SELECT COUNT(*) FROM finalAcademicOrganizations
#- statement: print finalAcademicOrganizationsCount

- statement: |
    (academicOrgToUpdate) =>
        SELECT
          sf.Id,
          acc.Name,
          acc.ParentId         
        FROM finalAcademicOrganizations acc
        INNER JOIN sfAccountReader sf ON IFNULL(TRIM(UPPER(acc.PeopleSoft_Univ_Department_Code__c)), '') = IFNULL(TRIM(UPPER(sf.PeopleSoft_Univ_Department_Code__c)), '')
        WHERE IFNULL(TRIM(UPPER(sf.PeopleSoft_Univ_Department_Code__c)), '') != ''

#- statement: print academicOrgToUpdate

- statement: (success,error) => UPDATE academicOrgToUpdate INTO sfAccountWriter
#- statement: print success
#- statement: print error

# 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: |
    (academicOrganizationsToInsert) =>
        SELECT
          acc.*       
        FROM finalAcademicOrganizations acc
        LEFT ANTI JOIN sfAccountReader sf ON IFNULL(TRIM(UPPER(acc.PeopleSoft_Univ_Department_Code__c)), '') = IFNULL(TRIM(UPPER(sf.PeopleSoft_Univ_Department_Code__c)), '')

#- statement: print academicOrganizationsToInsert

- statement: (success,error) => INSERT academicOrganizationsToInsert INTO sfAccountWriter
#- statement: print success
#- statement: print error

##### End for Academic Organization (University Department) #####

# Add more statements to convert, join, aggregrate, transform, and integrate your data