Create

The Create logic block is used in the Integrate logic block to insert successfully integrated records into another table. To create this type of logic block for your integration table , you must create a new logic block with the following configurations: 

ActionType
Table Name
Security OptionUse NATE container
Create<hd table name>Transaction<hd table name>IntegrationPrivateFalse

In the Logic Builder, build your logic block according to the steps outlined below:

The requirements below cover if the records being integrated have an IntegrationAction of Create and will handle creation of the record in the system if one does not already exist.  Optionally, it can be coded in a way to give the ability to update records. How to code the update of records depends on various factors.  For example, does the table use table triggers or an action block, is the action block coded in a way to handle this scenario, or does the logic block mode need to be used. If the ability to update existing records is desired, the IntegrationAction of Create should still be used. This allows for logic to be written in a way to handle inserts and updates in the same data set. The requirements below do not cover if the IntegrationAction of Update is passed in.  While this is a possible use case, this would require that ALL records being integrated are known to already exist in the system.

RequirementsPseudo-code
  • Set values FirstRecord is true and DataFound is true (only if using action blocks) to track if header is inserted to HD structure.
  • Create HD structure of type TEMP. 
    • This ensures that the triggers are only run when we save the HD instead of as we insert records into the HD. When NATE is turned off (which is the recommended configuration for integration logic blocks) and a new HD table is created, it inserts to DB right then and calls the triggers. This can cause issues for fields not yet set by the API. Pass header record to action block LBs if any are built over the detail table so you don't need to fetch the header. There also won't be any record disposition without NATE, so use the system mode. You can hard-code mode the mode to 'Create' when calling the CreateXX LB from the ProcessXXGroup LB.
  • Fetch and lock <hd table name>Integration by Import Run Number, Group Level, and a  Integration Status of NEW, and order by nwImportRunNumber ASC.
    • If Integration Status = NEW (this is needed in case another parallel process picked up records to process after this fetch occurred.)
      • If data is found, check if FirstRecord is true.
        • If true, insert header into the HD structure where you must pass in nwImportRunNumber, nwImportRowNumber, nwExternalId, and always pass in the fetched integration record in the Additional Mappings of the Insert action. This will allow partner's extensions and customer's customized field to flow through the tables.
        • Set the value FirstRecord is false.
  • After the fetch and lock, insert details into the HD structure where you must pass in nwImportRunNumber, nwImportRowNumber, and nwExternalId, and always pass in the fetched integration record in the Additional Mappingsof the Insert action. This will allow partner's extensions and customer's customized field to flow through the tables.
    • If your transaction allows the header to be saved without any detail records, use the HeaderOnly data item to determine if you should try to insert details to your HD structure. 
  • Set a PROCESSED Integration Status. This ensure that if any records error, they receive an ERROR Integration Status. This eliminates the need for a second fetch of records with no error.
  • Update <hd table name>Integration 
    • Configure the following if using an action block:
      • If data is found, call the action block.
      • If data is not found, set value DataFound is False.
      • NOTE: The action block (or table trigger) should allow for any auto number fields to be passed in via the integrations. If passed in, use it. This will make the integration more performant. It is limited to the data item type (text can accept alphanumeric), it does not matter if it's historical or non-historical, and it does not need to match the auto number format that is defined. 

// Variable to track if header was inserted to hd structure

Set FirstRecord = True

Set DataFound = True (only need this  if  using action blocks)

Create HD Structure - Structure Type: Temporary

// Fetch all records with matching header

// Sort by GroupLevel1 and ImportRowNumber, so always using the same header record

Fetch and Lock <hd table name>Integration by nwImportRunNumber and GroupLevel1 and IntegrationStatus = NEW, Order By nwImportRowNumber ASC

Data Found

        If IntegrationStatus = NEW 

If FirstRecord = True

Insert Header into HD Structure - pass in nwImportRunNumber, nwImportRowNumber, nwExternalId, and always pass the fetched integration record in the Additional Mappings of the insert action. 

Set FirstRecord = False

End

// Insert all details into hd structure

           // Should allow for any auto number fields to be passed in. 

           // Optional: If the transaction allows a header to be saved without details, use the HeaderOnly data item in the integration table to determine whether you insert detail records to the HD structure or not. 

           If HeaderOnly = false //Optional

Insert Detail into HD Structure - pass in nwImportRunNumber, nwImportRowNumber, nwExternalDetailId, and always pass the fetched integration record in the Additional Mappings of the insert action.

           End If

// Set the integration records to processed. 

// If error occurs, they will get status updated to error, this will eliminate a 2nd Fetch of records when no error

Set <hd table name>Integration.IntegrationStatus = PROCESSED

Update <hd table name>Integration

       End

No Data

Set DataFound = False (only if using action blocks) 

// **If using action block, need to call it here

If DataFound = True

// Call action block 

// **If using table triggers the insert above will call the triggers 

Integrate

The Integrate logic block is used in the Process Run list form action of the integration application to begin the integration process. To create this type of logic block for your integration table , you must create a new logic block with the following configurations: 
It is important to note, that while these standards are written to incorporate using the queue parallel processing action, we always need to think about table contention. Parallel processing can only work when there is no contention. If your process could be updating the same record on another table for multiple records that are being imported, you could have contention issues. This means there would be multiple threads that are spawned that are all trying to update the same record at the same time which will lock each other out or cause a time out. To eliminate this type of contention you can use a bucket in your parallel processing action. Define your bucket so that all rows that will update the same record will run sequentially in the same thread and ensure there is no contention. The order of processing records is not guaranteed.

Action
Type
Table Name
Security Option
Use NATE container
Integrate<hd table name>Background Task
<hd table name>IntegrationPublic-Secured
False

In the Logic Builder, build your logic block according to the steps outlined below:

RequirementsPseudo-code
  • If the nwImportRunNumber field is empty, set CoreMsg89 to indicate to the user that an import run number is required.
  • Use the Queue Parallel Processing action to run integration by ImportRunNumber and Integration Status NEW for distinct group levels
    • The Queue Parallel Processing action will call the "per record" logic block in update mode.  

// Validate import run number

If nwImportRunNumber is empty

   True

      Set Message CoreMsg89

EndIf

//Queue Parallel Processing Action

Queue Parallel Processing - Table: <hd table name>Integration, where <hd table name>Integration.nwImportRunNumber = <hd table name>Integration.nwImportRunNumber AND <hd table name>Integration.IntegrationStatus = NEW

Distinct Fields: GroupLevel1        

Input Mapping: PurgeProcessed

Per Record Logic Block: Process<hd table name>Group, Is Dynamically Mapped Record = true

Summary Logic Block: Integration<hd table name>Summary

Data Source Name: <hd table name>Integration

 Process Group

The Process Group logic block is used in the Integrate logic block and is used to process each group. This logic block calls the Create logic block and performs the error handling. To create this type of logic block for your integration table , you must create a new logic block with the following configurations:

Action
Type
Table Name
Security Option
Use NATE container
Process<hd table name>GroupBackground Task
<hd table name>IntegrationPrivateFalse

In the Logic Builder, build your logic according the steps outlined below:

RequirementsPseudo-code
  • Call the DeleteIntegrationsErrorLogMessages logic block in a new database transaction to delete any existing error log records for Import Run Number, Source Application and Group Level.

  • Call the Create<hd table name> logic block in a new database transaction that logs messages into a message list. 

    • This logic block call must be in its own boundary so that system level errors are caught. If this logic block is not called in a new database transaction, system errors will be captured but will not stop the records from being written to the destination table. 

  • If errors exist in the message list

    • Loop through messages from Create<hd table name> logic block.

      • If the Import Row Number field is empty, fetch and lock Integration by Import Run Number and Import Row Number from Integration.

        • If data is found, insert integrations message/error to the IntegrationsErrorLog subtable and update fetched integration record. 

      • If the Import Rown Number field is not empty, fetch and lock Integration by Import Run Number and Import Row Number from the message list.

        • If data is found, insert integrations message/error to the IntegrationsErrorLog subtable and update fetched integration record.


    • Fetch Integration to loop records by Import Run Number and Group Level for a group and set them as ERROR.

      • If data is found, then set Integration Status = ERROR and update Integration.

// Delete any existing error log records for import run number and group level, before processing

Call DeleteIntegrationsErrorLogMessages pass nwImportRunNumber, SourceApplication, and GroupLevel1 (from what was sent into this logic block - pass nwImportRunNumber into ImportRunNumber) - New Trans Boundary = True, Adopt All Messages

// Call Create to process all records for group 

Call Create<hd table name> - pass nwImportRunNumber and GroupLevel1 - New Trans Boundary

Set Message List Name = CreateMessageList

If Create<hd table name>MessageList contains errors

   Loop through Create<hd table name>MessageList

       //Fetch integration record to update with message, conditional to check if we have nwImportRowNumber from message.

       //If not, system level exception and should get written to first record

       If nwImportRowNumber from loop of message list is empty

// First record is not always row 1, so fetch row number passed into this logic block

           Fetch and Lock <hd table name>Integration by nwImportRunNumber and nwImportRowNumber (passed in values)

              Data Found

                 //Assign nwImportRunNumber and nwImportRowNumber from message list

 Insert integrations message/error to subtable 

                 Update the <hd table name>Integration record fetched

        Else // have nwImportRunNumber and nwImportRowNumber in message list

           Fetch and Lock <hd table name>Integration by nwImportRunNumber and nwImportRowNumber (from message list)

              Data Found

                 // Assign nwImportRunNumber and nwImportRowNumber from message list

                 Insert integrations message/error to subtable

                 Update the <hd table name>Integration record fetch

        End If

   End Loop through message list

   Fetch and Lock Integration by ImportRunNumber, GroupLevel1

      Set IntegrationStatus = ERROR 

      Update <hd table name>Integration

Integration Summary 

The Integration Summary logic block is called by the Queue Parallel Processing action once all groups are processed in parallel. To create this type of logic block for your integration table , you must create a new logic block with the following configurations: 

Action
Type
Table Name
Security Option
Use NATE container
Integration<hd table name>SummaryTransaction
<hd table name>IntegrationPrivateFalse

Note: This logic block could also be Transaction type if a popup notification is needed.

In the Logic Builder, build your logic according the steps outlined below:

RequirementsPseudo-code
  • Determine if any errors exist
    • If errors exist - issue background task summary message indicating the integration completed with errors.
      • NOTE: It is not recommended that the Aggregate action is used here to determine the number of errors. It was determined that this will not be of any value, since what is important is the number of groups that errored. So in this instance it is just overhead and per our standard should be changed to a simply fetch with limit 1, to let the user know if errors exist or not. We need an enhancement for the tool to be able to count the number of records processed and errored for HDs.
    • If no errors exist - issue background task summary message indicating the integration completed successfully.
    • If purge process is set to true, call Queue Parallel Processing action to purge
      • All "Processed" records should be purged even if other records are at "Errored". Only the Errored ones should be left in the integration table after the purge completes. 
  • NOTE: Purging of records should always be done in this logic block. In the future, we want to display the number of records processed and the number of records errored. We cannot acheive this, if we are purging as we process records. 

// Check if error exist

Fetch from <hd table name>IntegrationErrors where <hd table name>Integration.nwImportRunNumber = <hd table name>IntegrationErrors.nwImportRunNumber and <hd table name>IntegrationErrors.Integration Status = Error. Limit 1.

   Data Found: Create Message using BaseMsg724 (or your own family message item), Background Task Summary, Warning, Log with Integration nwImportRunNumber

   Data Not Found: Create Message using BaseMsg725 (or your own family message item), Background Task Summary, Success, Log with Integration nwImportRunNumber

If <hd table name>Integration.PurgeProcessed = true

      Queue Parallel Processing - Table: <hd table name>Integration, fetch where <hd table name>Integration.nwImportRunNumber = <hd table name>Integration.nwImportRunNumber AND <hd table name>Integration.IntegrationStatus=PROCESSED 

      Per Record Logic Block: Purge<hd table name>Integration, Is Dynamically Mapped Record = true

      Input Mapping - ParallelPurgeProcessed = True

Reset status

The Reset status logic block is used in the Reset Run Status list form action and Reset Group Status row action of the integration application to reset record statuses. To create this type of logic block for your integration table, you must create a new logic block with the following configurations: 

Action
Type
Table Name
Security Option
Use NATE container
Reset<hd table name>IntegrationStatusBackground Task
<hd table name>IntegrationPublic-SecuredFalse

In the Logic Builder, build your logic according the steps outlined below:

RequirementsPseudo-code
  • If the Import Run Number field does not have a value, set CoreMsg89 to let the user know an import run number is required.
  • If the Import Run Number field has a value, fetch and lock records from <hd table name>Integration by Import Run Number, Group Level (mark as optional), and an ERROR Integration Status. 
    • If data is found, set records to a NEW Integration Status and then update the record. 
    • If no data is found, set CoreMsg93 to let the user know that there are currently no records with an ERROR Integration Status to reset.

If nwImportRunNumber is empty

Set Message CoreMsg89

End If

// This will set records for a run, and optionally GroupLevel1, from ERROR to NEW

Fetch and Lock <hd table name>Integration by nwImportRunNumber, GroupLevel1 (mark as optional) and IntegrationStatus = ERROR

Data Found

Set Status = NEW

Update record

No Data

Set warning CoreMsg93

Purge

This logic block is called from both the Purge Run action on the UI and when Purge Processed is set to true. When called from the UI we ignore the PurgeProcessed flag and Purge all records for the run number. When called from the integration, only Processed records are Purged. To create this type of logic block for your integration table, you must create a new logic block with the following configurations: 

Action
Type
Table Name
Security Option
Use NATE container
Purge<hd table name>IntegrationTransaction
<hd table name>IntegrationPublic-Secured
False

In the Logic Builder, build your logic block according the steps outlined below:

RequirementsPseudo-code
  • If the Import Run Number field does not have a value, set CoreMsg89to let the user know an import run number is required.
  • If the Import Run Number field has a value, fetch and lock records from <hd table name>Integration by the Import Run Number.
    • If data is found, delete records from the <table name>Integration table. 
    • If no data is found, set CoreMsg88 to let the user know that records with the selected run number were not found, were already processed, or a run number was not selected.

If nwImportRunNumber <hd table name>Integration is empty

   True - Set Message CoreMsg89

EndIf

//Since the logic blocks's table and the dynamic expressions are on the same table, put the logic block table values into variables in order to get dynamic expression to function correctly

Set new variable ImportRunNumber = <hd table name>Integration.nwImportRunNumber

Set new variable  <hd table name>IntegrationnwId = <hd table name>Integration.nwId

Start new dynamic expression - ExpressionType = New, Table = <hd table name>Integration, Name: Purge<hd table name>IntegrationDDS

//No expression in new dynamic expression at this time.

If ParallelPurgeProcessed (value created in LB inputs and outputs) is True

   //Override an existing expression: Purge

   Build Dynamic Expression: Expression Name: Purge<hd table name>IntegrationDDS, ExpressionType: Existing, Expression: <hd table name>Integration.nwId (field) = <HD table name>IntegrationnwID (variable) 

Else

   //Override an existing expression: Purge

   Build Dynamic Expression: ExpressionName:Purge<hd table name>IntegrationDDS, ExpressionType: Existing, , Expression: <hd table name>Integration.nwImportRunNumber = ImportRunNumber (variable)

End If

Fetch and Lock records from <hd table name>Integration - Name: Fetch<hd table name>Integration, Use Dynamic Expression built above. Purge<hd table name>IntegrationDDS

Data found

Delete all records from Fetch<hd table name>Integration

No Data

Set message CoreMsg88