This Blog aims to showcase a Case Study on how Travel Expense Reimbursement can be evaluated in a company using Workload Automation. Typically every company would have a Portal where Employees would login and report all expenses made against a Travel and the same is reimbursed based on their Eligibility . Consider a company has developed a similar Portal and an Employee fills out a Form with all expenses incurred during a Travel. The expenses incurred during the travel is sent from the Portal in the form of json data ,let’s say below was the json Data sent from the Portal : {"Lodging": 58000, "AirTicket": 7000, "Conveyance": 20000, "AirportTransfer": 5000, "EmployeeID": 55164281} The json Data captured is sent through a Python program to a Mongo DB , lets say the below Python Program is the program called from the Portal to send the Data : import pymongo from pymongo import collection from pymongo import MongoClient import pprint myclient = MongoClient("mongodb+srv://XXXXXXX:XXXXXXX@cluster0-31gzu.mongodb.net/NewDB?retryWrites=true&w=majority") db = myclient.DBNew print(db) NewCol = db.collection print(NewCol) post = [{"Lodging": 46000, "AirTicket": 19000, "Conveyance": 36000, "AirportTransfer": 5000, "EmployeeID": 50897688}] #posts = db.posts #print(posts) result = NewCol.insert_many(post) print(result.inserted_ids) Post importing the pymongo Driver , establishing a Client Connection , switching to the DB and getting at the Collection Object , the last lines of the Program describe the actual query run against the DB , the below would be called through an IWS Job as follows : The job would call the python Program using the python binary through an IWS Native Windows Job Type on a Windows Agent. The Job would run against the MongoDB and store the JSON Data relating to the Travel expense as follows in a collection as a Document : Eligibility Criteria (managed through DB2 Database) : The Eligibility Criteria under each Expense Head is stored in a DB2 Database as follows : The expenses are captured under 4 Heads , Boarding Miscellaneous , Conveyance , Airport Transfer and Air Ticket Charges. We would be using 4 Jobs which would query the EXPREIMBURSE Table to capture expenses under each Eligibility Head , these would be DB2 Jobs using the JDBC Driver readily available on the IWS MDM for DB2 . The DB2 Job Type would take in the Native JDBC Driver path available on IWS MDM’s Dynamic Agent and DB2 credentials , the actual SQL Query and Query Output redirected to a specific file for each Eligibility Criteria , in this case the Lodging Eligibility is stored under /tmp/Lodging_Elig. Likewise we would have similar jobs for Eligibility of Conveyance , Airticket , Airport Transfer etc : Query Expense Jobs : Inorder to extract the Expense under each head from the MongoDB “NewDB” , we would again have Python Programs which would run find_one functions from the Collection Class and would be as below : import pymongo from pymongo import collection from pymongo import MongoClient import pprint myclient = MongoClient("mongodb+srv://XXXXXXX:XXXXXX@cluster0-31gzu.mongodb.net/NewDB?retryWrites=true&w=majority") db = myclient.NewDB #print(db) NewCol = db.collection #print(NewCol) post = [{"Lodging": 50000, "AirTicket": 70000, "Conveyance": 30000, "AirportTransfer": 5000, "EmployeeID": 5162143}] #posts = db.posts #print(posts) result = NewCol.find_one({"EmployeeID": 55164281}, {"AirTicket": 1}) print(result['AirTicket']) The Query only picks the AirTicket Column of the Employee ID in question. Likewise we would have 4 different programs for each Expense Head : import pymongo from pymongo import collection from pymongo import MongoClient import pprint myclient = MongoClient("mongodb+srv://XXXXXX:XXXXXXX@cluster0-31gzu.mongodb.net/NewDB?retryWrites=true&w=majority") db = myclient.NewDB #print(db) NewCol = db.collection #print(NewCol) post = [{"Lodging": 50000, "AirTicket": 70000, "Conveyance": 30000, "AirportTransfer": 5000, "EmployeeID": 5162143}] #posts = db.posts #print(posts) result = NewCol.find_one({"EmployeeID": 50897688}, {"Conveyance": 1}) print(result['Conveyance']) --------------------------------------------------------------------------- import pymongo from pymongo import collection from pymongo import MongoClient import pprint myclient = MongoClient("mongodb+srv://XXXXXXXX:XXXXXXXX@cluster0-31gzu.mongodb.net/NewDB?retryWrites=true&w=majority") db = myclient.NewDB #print(db) NewCol = db.collection #print(NewCol) post = [{"Lodging": 50000, "AirTicket": 70000, "Conveyance": 30000, "AirportTransfer": 5000, "EmployeeID": 5162143}] #posts = db.posts #print(posts) result = NewCol.find_one({"EmployeeID": 50897688}, {"Lodging": 1}) print(result['Lodging']) --------------------------------------------------------------------------- import pymongo from pymongo import collection from pymongo import MongoClient import pprint import json myclient = MongoClient("mongodb+srv://XXXXXXXX:XXXXXXX@cluster0-31gzu.mongodb.net/NewDB?retryWrites=true&w=majority") db = myclient.NewDB NewCol = db.collection post = [{"Lodging": 50000, "AirTicket": 70000, "Conveyance": 30000, "AirportTransfer": 5000, "EmployeeID": 5162143}] #posts = db.posts #print(posts) result = NewCol.find_one({"EmployeeID": 50897688}, {"AirportTransfer": 1}) #result_final = str(result[1]) #result_final = str(str(result[67])+str(result[68])+str(result[69])+str(result[70])) #print(result_final) print(result['AirportTransfer']) We would run the below jobs to run the above Programs which would query under each expense head , the jobs would be Native Windows Jobs calling the Python binary followed by the full path of the Python Program : Store Jobs for Expenses : Store Jobs for expenses would be executable Job Type and would set the TWS Environment before calling the jobprop binary of IWS to store the Joblog of the preceding Expense Job in a IWS Variable: The jobprop uses Variable passing features of IWS to store the Expenses incurred against Air Tricket in a Variable EXPENSE_AIRTICKET : Likewise Airport Transfer Expenses would be stored in an IWS Variable called EXP_AIRTRANS : The same applies for Lodging and Conveyance and every expense head is captured in a separate IWS Variable through jobprop and passed onto other jobs in the IWS Jobstream. Store Jobs for Eligibility : These set of jobs store the Eligibility for each Expense Head from the DB2 table EMP.EXPREIMBURSE and store the same in an IWS Variable using jobprop utility : The Jobprop utility of IWS is used to store the variable ELIGLODGING , Eligibility for Lodging , this is initially captured from the Output File of the DB2 Job : /tmp/Lodging_Elig and then passed using the jobprop utility to an IWS Variable ELIGLODGING . The same concept is applied for all the Store Eligibility Jobs and stored in IWS Variables passing from the Output files of the DB2 Jobs. Check Jobs : The below set of Jobs evaluates the Expense under each Expense Head against Eligibility and decides whether it is within the limit or exceeding the limit beyond eligibility . These would pass SUCC Output Conditions : BEYELIGAIRTRANS (in the below case)using Conditional Dependencies of Workload Automation. The Job would be of type Executable and using the Variable Passing Features of IWS would fetch the Variable from preceding Predecessors : Store Eligibility and Store Expense Jobs : In the above executable Job type , as you can see , the ELGAIRTRANS and EXPAIRTRANS Variables pick the variables passed from preceding Eligibility and Expense Jobs and are stored and compared through a simple if and the difference if Expense is more than Eligibility is stored through jobprop utility in a variable BEYONDAITRANS and RC=1 is passed . The RC=1 Return Code is mapped to condition BEYELIGAIRTRANS . Likewise we have similar Check Jobs for Airport Ticket , Lodging and Conveyance : Mailing Jobs : The Conditions passed by the CHECK Jobs if satisfied would trigger a Mailing Job which would send out a mail through SendMail command to the Manager of the Employee stating the exact Expense head where the Expense exceeds the Eligibility and the exact amount beyond which it exceeds the expense head : Conditional Dependencies within the Jobstream : The RC=1 is passed as a Condition from the Check Job into the Mailing Job , incase the Employee Eligibility for an Expense Head is more than Eligibility Amount . The Overall Jobstream Definition would look as below : SCHEDULE MASTER_DA#EXPENSE_REIMBURS : AGENT#EXPENSE_SUBMIT_PORTAL AGENT#EXPENSE_AIRPORT_TRANSFER FOLLOWS EXPENSE_SUBMIT_PORTAL AGENT#EXPENSE_AIRTICKET FOLLOWS EXPENSE_SUBMIT_PORTAL AGENT#EXPENSE_CONVEYANCE FOLLOWS EXPENSE_SUBMIT_PORTAL AGENT#EXPENSE_LODGING FOLLOWS EXPENSE_SUBMIT_PORTAL MASTER_DA#QUERY_ELIGIBILITY_AIRPORTTRANS FOLLOWS EXPENSE_SUBMIT_PORTAL MASTER_DA#QUERY_ELIGIBILITY_AIRTICKET FOLLOWS EXPENSE_SUBMIT_PORTAL MASTER_DA#QUERY_ELIGIBILITY_CONVEYENCE FOLLOWS EXPENSE_SUBMIT_PORTAL MASTER_DA#QUERY_ELIGIBILITY_LODGING FOLLOWS EXPENSE_SUBMIT_PORTAL MASTER_DA#STORE_ELIGIBILITY_AIRPORTTRANS FOLLOWS QUERY_ELIGIBILITY_AIRPORTTRANS MASTER_DA#STORE_ELIGIBILITY_AIRTICKET FOLLOWS QUERY_ELIGIBILITY_AIRTICKET MASTER_DA#STORE_ELIGIBILITY_CONVEYANCE FOLLOWS QUERY_ELIGIBILITY_CONVEYENCE MASTER_DA#STORE_ELIGIBILITY_LODGING FOLLOWS QUERY_ELIGIBILITY_LODGING MASTER_DA#STORE_EXPENSE_AIRPORT_TRANSFER FOLLOWS EXPENSE_AIRPORT_TRANSFER MASTER_DA#STORE_EXPENSE_AIRTICKET FOLLOWS EXPENSE_AIRTICKET MASTER_DA#STORE_EXPENSE_CONVEYANCE FOLLOWS EXPENSE_CONVEYANCE MASTER_DA#STORE_EXPENSE_LODGING FOLLOWS EXPENSE_LODGING MASTER_DA#CHECK_AIRPORT_TRANSFER FOLLOWS STORE_ELIGIBILITY_AIRPORTTRANS FOLLOWS STORE_EXPENSE_AIRPORT_TRANSFER MASTER_DA#CHECK_AIRTICKET FOLLOWS STORE_ELIGIBILITY_AIRTICKET FOLLOWS STORE_EXPENSE_AIRTICKET MASTER_DA#CHECK_CONVEYANCE FOLLOWS STORE_ELIGIBILITY_CONVEYANCE FOLLOWS STORE_EXPENSE_CONVEYANCE MASTER_DA#CHECK_LODGING FOLLOWS STORE_ELIGIBILITY_LODGING FOLLOWS STORE_EXPENSE_LODGING DAUNIX#MAIL_AIRPORT_TRANSFER FOLLOWS CHECK_AIRPORT_TRANSFER IF BEYELIGAIRTRANS DAUNIX#MAIL_AIRTICKET FOLLOWS CHECK_AIRTICKET IF BEYELIGAIRTKT DAUNIX#MAIL_CONVEYANCE FOLLOWS CHECK_CONVEYANCE IF BEYELIGCONV DAUNIX#MAIL_LODGING FOLLOWS CHECK_LODGING IF BEYELIGLODG END So, with Conditional Dependencies and Variable passing Features of Workload Automation, we can Automate the Expense Reimbursement Process and notify Manager’s in specific cases when the Eligibility is exceeded. Author BIO
0 Comments
Your comment will be posted after it is approved.
Leave a Reply. |
Archives
August 2023
Categories
All
|