Program logic

The EXECUTE_TRANSFER program transfers the planning data by executing the mappings in scope, using this logic:

  • Logs a new task log record for the current table using this formula: 
    task_name format = <mapping ID> - <mapping name>
  • Generates DELETE / INSERT / MERGE / UPDATE statements based on the input parameters using the following logic:
    Refresh Mode Target Delete Mode Target Update Mode Transfer Mode Delete Valid in Target Inbox Action
    Full Physical - All -

    DELETE (physically) records in target

    INSERT records to target.
    NotDeleted - DELETE (physically) records in target INSERT records to target, where deleted_flag = "N" in source.
    Logical - All -

    MERGE + UPDATE:

    Update matching records in target.

    Note: ‘’+’’ indicates non-matching records as logically deleted in target.
    NotDeleted -

    MERGE + UPDATE:

    Update matching records in target, where deleted_flag = "N" in source.

    Note: ‘’+’’ indicates non-matching records as logically deleted in target.
    Full, Incremental, or Partial None Insert All - INSERT: Insert records to target.
    NotDeleted - INSERT: Insert records to target, where deleted_flag = "N" in source.
    Merge All - MERGE: Update matching records in target.
    NotDeleted - MERGE: Update matching records in target, where deleted_flag = "N" in source.
    Incremental or Partial Physical - All Y
    • DELETE (physically) valid records in target.
    • DELETE (physically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target.
    -
    • DELETE (physically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target.
    NotDeleted Y
    • DELETE (physically) valid records in target.
    • DELETE (physically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target, where deleted_flag = "N" in source.
    -
    • DELETE (physically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target, where deleted_flag = "N" in source.
    Logical - All Y
    • DELETE (logically) valid records in target.
    • DELETE (logically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target.
    -
    • DELETE (logically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target.
    NotDeleted Y
    • DELETE (logically) valid records in target.
    • DELETE (logically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target, where deleted_flag = "N" in source.
    -
    • DELETE (logically) records in target, where deleted_flag = "Y" in source.
    • MERGE: Update matching records in target, where deleted_flag = "N" in source.
  • Applies column-level mapping transformations.
  • Applies hash-key transformations.
  • If the INCLUDE_USER_DATA parameter is set to Y, includes all records from the corresponding Planning Configuration table to the source data set.
    Note: Records are considered ready for direct transfer to Core. The program includes user data in the source data set after applying all transformations through mapping definition. However, before applying selection filters such as SOURCE and SNAPSHOT_COLUMN. For example: 
    <TARGET_TABLE> = SC_ITEM
<SOURCE_TABLE> = SI_ITEM, determined via the <mapping name>
<PLANNING_CONFIGURATION_TABLE> = SM_ITEM, determined via the entity name (ITEM)

The final source data set is determined as follows:
SELECT *
FROM (  // Apply all mapping transformations
        SELECT * 
        FROM SI_ITEM
) t
UNION
SELECT * 
FROM SM_ITEM
  • Applies SOURCE selection if the SOURCE parameter is specified as follows: 
    <current record selection criteria> = “source = <SOURCE>"
  • Verifies SNAPSHOT_COLUMN. If the REFRESH_MODE parameter is set to Incremental and the SNAPSHOT_COLUMN parameter is specified:
  • Creates a new instance of Snapshot Manager:
  • Snapshot table name = <mapping name> (that is, one snapshot table log per mapping execution)
  • Snapshot UTC time flag = "Y" (that is, use UTC when defining <WINDOW_START> and <WINDOW_END>)
  • Applies the value specified in the SNAPSHOT_COLUMN parameter: 
    <current record selection criteria> = “<SNAPSHOT_COLUMN> >= <WINDOW_START>"
  • Sets control columns for transferred records:
    • // Inserted records:
      • created_by = "EXECUTE_TRANSFER" + <execution id>
      • creation_time = Systimestamp in UTC
      • modified_by = "EXECUTE_TRANSFER" + <execution id>
      • modification_time = Systimestamp in UTC
    • // Merged or logically deleted records:
      • modified_by = "EXECUTE_TRANSFER" + <execution id>
      • modification_time = Systimestamp in UTC
  • Generates SQL statements for the transfer: DELETE, INSERT, MERGE and/or UPDATE statements.
  • Executes the generated SQL statements.
  • Commits changes if the SQL statements for the <current mapping> are successfully executed.
  • Completes the task log record with the actual status.