Scenario 11: How to maintain Audit Table for mapping/session/workflow.

Scenario 11: How to maintain Audit Table for mapping/session/workflow without accessing Repository tables?

Let's Chat: It's easy to maintain but it's take significant time to implement so, have a cup of tea. Splash water on face :-) Ready? Let's Start.

Theory/Analysis

For maintaining Audit table manually without accessing Repository tables we have to use predefined variables. Few you can find at Designer level and few at Workflow Manager level. We will use Pre Session Variable assignment option, available in components tab of session. (To use this option session must be Non-Reusable session)
We will create one mapping to process these variable values.

Practically:

Step 1: Create Audit Table

Audit Table Structure:
ID NUMBER,
FOLDER_NAME VARCHAR2(20),
MAPPING_NAME VARCHAR2(20),
WORKFLOW_NAME VARCHAR2(20),
SESSION_NAME VARCHAR2(20),
ERROR_CODE NUMBER,
ERROR_MSG VARCHAR2(500),
SRC_FAILED_ROWS NUMBER,
SRC_SUCCESS_ROWS NUMBER,
START_TIME DATE,
END_TIME DATE,
STATUS NUMBER,
TGT_FAILED_ROWS NUMBER,
TGT_SUCCESS_ROWS NUMBER

Step 2:  Create a source file.
It's just a dummy file. Ex: Create text file with only one value in that. I am taking code column in file with value 1234.

Step 3: Create Nine Mapping Variable
(Mapping Menu-->Parameters/Variable)

Name                       Type              Data Type         Aggregation
M_ERROR_CODE   Variable           Integer            count
M_ERROR_MSG     Variable           String (500)     max
M_SRC_FAILED_ROWS Variable   Integer            count
M_SRC_SUCCESS_ROWS  Variable Integer         count
M_START_TIME       Variable        Date/Time
M_END_TIME           Variable        Date/Time
M_STATUS                Variable         Integer              count
M_TGT_FAILED_ROWS Variable   Integer              count
M_TGT_SUCCESS_ROWS Variable  Integer           count
M_MAPPING_NAME Variable  String(100)           count
M_SESSION_NAME Variable  String(100)           count

Step 4: Drag and drop your source and target (Audit_Table).

Step 5:
Expression
Create total Thirteen Ports

FOLDER_NAME (Output Port) = $PMFolderName
MAPPING_NAME (Output Port) = $$M_MAPPING_NAME
WORKFLOW_NAME (Output Port)= $PMWorkflowName
SESSION_NAME (Output Port)= $$M_SESSION_NAME
O_M_ERROR_CODE (Output Port)= $$M_ERROR_CODE
O_M_ERROR_MSG (Output Port)= $$M_ERROR_MSG
O_M_SRC_FAILED_ROWS (Output Port)= $$M_SRC_FAILED_ROWS
O_M_SRC_SUCCESS_ROWS (Output Port)= $$M_SRC_SUCCESS_ROWS
O_M_START_TIME (Output Port)= $$M_START_TIME
O_M_END_TIME (Output Port)= $$M_END_TIME
O_M_STATUS (Output Port)= $$M_STATUS
O_M_TGT_FAILED_ROWS (Output Port)= $$M_TGT_FAILED_ROWS
O_M_TGT_SUCCESS_ROWS (Output Port)= $$M_TGT_SUCCESS_ROWS

Note: In Status column you will get value 103 for successful run and 105 for failed run.

Step 6:
Sequence Generator
Create a Sequence Generator Transformation.
We will use this value for ID column.
(No, Need to Change any properties, Use it with default properties)

Step 7: Connect to Target.
Connect NEXTVAL from sequence generator to ID and other values from expression to appropriate columns in target.

Step 8: Create session in same workflow for which you want to maintain Audit Information.

Step 9: Your actual session for which you are maintaining audit create as Non-Reusable Session.  (This Step required to get the Mapping name and Session name of actual mapping and session and assign those to workflow variable to be used by the session through which you are maintaining audit table. )
Session-->Right Click-->Edit-->Components Tab-->Post SessionVariable Assignment
$$W_MAPPING_NAME=$PMMappingName
$$W_SESSION_NAME=$PMSessionName

Step 10: Create Nine Workflow Variable (Workflow Menu-->Edit-->Variable Tab)
W_ERROR_CODE Integer
W_ERROR_MSG   nstring
W_SRC_FAILED_ROWS Integer
W_SRC_SUCCESS_ROWS Integer
W_START_TIME Date/Time
W_END_TIME Date/Time
W_STATUS Integer
W_TGT_FAILED_ROWS Integer
W_TGT_SUCCESS_ROWS Integer
W_MAPPING_NAME nstring
W_SESSION_NAME nstring

Note: Check Persistent check box for all variables.

Step 11:
Assignment Task
Create Assignment Task-->Assign Value for all Workflow Variable
$$W_ERROR_CODE=$S_FIRST_LAST_REC.ErrorCode
$$W_ERROR_MSG=$S_FIRST_LAST_REC.ErrorMsg
$$W_SRC_FAILED_ROWS=$S_FIRST_LAST_REC.SrcFailedRows
$$W_SRC_SUCCESS_ROWS=$S_FIRST_LAST_REC.SrcSuccessRows
$$W_START_TIME=$S_FIRST_LAST_REC.StartTime
$$W_END_TIME=$S_FIRST_LAST_REC.EndTime
$$W_STATUS=$S_FIRST_LAST_REC.Status
$$W_TGT_FAILED_ROWS=$S_FIRST_LAST_REC.TgtFailedRows
$$W_TGT_SUCCESS_ROWS=$S_FIRST_LAST_REC.TgtSuccessRows

Step 12:
Assign Workflow Variable to Mapping Variable
Session-->Right Click-->Edit-->Components Tab-->Pre SessionVariable Assignment

$$M_ERROR_CODE=$$W_ERROR_CODE
$$M_ERROR_MSG=$$W_ERROR_MSG
$$M_SRC_FAILED_ROWS=$$W_SRC_FAILED_ROWS
$$M_SRC_SUCCESS_ROWS=$$W_SRC_SUCCESS_ROWS
$$M_START_TIME=$$W_START_TIME
$$M_END_TIME=$$W_END_TIME
$$M_STATUS=$$W_STATUS 
$$M_TGT_FAILED_ROWS=$$W_TGT_FAILED_ROWS
$$M_TGT_SUCCESS_ROWS=$$W_TGT_SUCCESS_ROWS
$$M_MAPPING_NAME=$$W_MAPPING_NAME
$$M_SESSION_NAME=$$W_SESSION_NAME

We are done now. Your workflow will looks like below Image.

BINGO!!!

Common Error:
VAR_27001 Error: There is a recursive reference from mapping variable:[$$Mapping_Variable_Name] back to itself. Error detected when resolving initial value of mapping variable:[$$Mapping_Variable_Name]
Reason: By mistake may be your mapping workflow variables are conflicting with each other or default (System Variable Name)
Solution: Give different name for Mapping/Workflow variable.

Feel free to post your doubt/your marks for my effort in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com