Hello Experts,

In continuation to my earlier post (FDMEE Email Alert with Attachment), where I could get the information related to LOADSTATUS, VALSTATUS, EXPSTATUS into E-Mail. How about getting the Mapping error(s) display directly in E-Mail for all the Dimensions?

Interesting… Let’s see the real-time scenarios with a couple of “can we?”

Can Functional User get mapping failed notification up front and login to FDMEE only to update mapping, after collating the required information?

Can we void Admin Team / Support Team send a separate email to the Business seeking list of mapping error(s)?

Yes, We Can get the required information upfront from with below AftVaidate Event Script using FDMEE. I hope the below script helps you, in pulling the results into the email.

If you have any query, drop me a comment to hfm.trainer@gmail.com

#=========================================================================================
#FDMEE Email Alert with Log Attached and Mapping Error
#Purpose: To notify user(s) if there is a failure of the Data Load to Any Location in
# FDMEE with respective Log file attached to email for quick action. In case of Mapping
# fail, User get the information about failed mapping in email.
#
#Author: Satyanadh Kolluri (www.satyanadh.in)
#          Oracle Knowledge Documents
#
#Change Tracker:
#Ticket# Date Change Description
#======= ========== ==============================================================
#
#
#
#
#=========================================================================================

#Global Import
import com.hyperion.aif.scripting.API as API
import com.sunopsis.dwg.DwgObject as DwgObject
import java.sql.DriverManager as SQLDriverManager
import java.sql.DriverManager.getConnection
import smtplib
import os
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText

#FDMEE Convert to Variable
sTargetAppName = fdmContext["TARGETAPPNAME"]
fdmProcessID = fdmContext["LOADID"]
sRuleName = fdmContext["RULENAME"]
sLocName = fdmContext["LOCNAME"]
sPeriod = fdmContext["PERIODNAME"]
sSource = fdmContext["SOURCENAME"]

#FDMEE Process ID Details
ProcessID = fdmAPI.getBatchJobDetails(fdmProcessID)
status = fdmAPI.getProcessStates(fdmProcessID)
expStatus = str(status["EXPSTATUS"])
valStatus = str(status["VALSTATUS"])
impStatus = str(status["IMPSTATUS"])
statusmsg = fdmAPI.getCustomMessage(fdmProcessID)
#Connection to FDMEE Table
#Getting Encrypted Password
#[Source: I can't believe my eyes] Thanks! Francisco Amores]

#Change [CON_NAME] to respective DB for Oracle DB use FDMEE_DATA_SERVER and SQL: FDMEE_DATA_SERVER_MSSQL
sqlFDMPwd = """SELECT PASS FROM SNP_CONNECT WHERE CON_NAME = 'FDMEE_DATA_SERVER_MSSQL'"""
exeFDMPwd = fdmAPI.executeQuery(sqlFDMPwd, [])
while exeFDMPwd.next():
    encrPWD = exeFDMPwd.getString("PASS")
#Decrypting Password
FDMPwd = DwgObject.snpsDecypher(encrPWD)

#Get Context Info
appID = fdmContext["APPID"]
userName = fdmContext["USERNAME"]

FDMDb = fdmAPI.getProfileOptionValue("ODI_MASTER_REPOSITORY_URL", appID, userName)
FDMUser = fdmAPI.getProfileOptionValue("ODI_MASTER_REPOSITORY_USER", appID, userName)

#Opening Database Connection
dbConn = SQLDriverManager.getConnection(FDMDb, FDMUser, FDMPwd)
stmt=dbConn.createStatement()

#Mapping Failed Query
#Query is built to retrieve ONLY up to UD5, if needed modify query to your count
MapFQuery = """select Dimension_Name, Source_Value from (select distinct loadid As 'Load_ID', 'Account' As 'Dimension_Name', ACCOUNT 'Source_Value'
from tdataseg where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.ACCOUNTX IS NULL OR TDATASEG.ACCOUNTX = ''))
union select distinct loadid As 'Load_ID', 'Entity' As 'Dimension_Name', ENTITY 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.ENTITYX IS NULL OR TDATASEG.ENTITYX = '')) union
select distinct loadid As 'Load_ID', 'ICP' As 'Dimension_Name', ICP 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.ICPX IS NULL OR TDATASEG.ICPX = '')) union
select distinct loadid As 'Load_ID', 'UD1' As 'Dimension_Name', UD1 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.UD1X IS NULL OR TDATASEG.UD1X = '')) union
select distinct loadid As 'Load_ID', 'UD2' As 'Dimension_Name', UD2 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.UD2X IS NULL OR TDATASEG.UD2X = '')) union
select distinct loadid As 'Load_ID', 'UD3' As 'Dimension_Name', UD3 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.UD3X IS NULL OR TDATASEG.UD3X = '')) union
select distinct loadid As 'Load_ID', 'UD4' As 'Dimension_Name', UD4 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.UD4X IS NULL OR TDATASEG.UD4X = '')) union
select distinct loadid As 'Load_ID', 'UD5' As 'Dimension_Name', UD5 'Source_Value' from tdataseg
where valid_flag = 'N' AND ( (1=0) OR (TDATASEG.UD5X IS NULL OR TDATASEG.UD5X = ''))) a
where Load_ID = %s""" %fdmProcessID

#Executing Query
rlst = stmt.executeQuery(MapFQuery)
options = list()
options.append("| \t Dimension Name \t | Source Value \t |")
rlstore = stmt.getResultSet()
while(rlstore.next()):
    Dimension_Name = rlstore.getString("Dimension_Name")
    Source_Value = rlstore.getString("Source_Value")
    options.append("| \t %s \t | %s \t |" %(Dimension_Name,Source_Value))
                               
mResults = '\r\n<br>'.join((options))
dbConn.close()

#Email Output Preparation
global sBatchDetails, sBatchDetails1, sBatchDetails2, sBatchDetails3, sBatchDetails4, sBatchDetails5, sBatchDetails6, sBatchDetails7,  sBatchDetails8, sBatchDetails9, sBatchDetails10
if str(status["IMPSTATUS"]) == "1" and str(status["VALSTATUS"]) == "0":
    try:
        sBatchDetails = "============================================================= \n"
        sBatchDetails1 = "Location Name : %s \n" % sLocName
        sBatchDetails2 = "Period : %s \n" % sPeriod
        sBatchDetails3 = "Process ID : %s \n" % fdmProcessID
        sBatchDetails4 = "Rule Executed : %s \n" % sRuleName
        sBatchDetails5 = "Data Source %s \n" % sSource
        sBatchDetails6 = "\n"
        sBatchDetails7 = "Status : Validation Failed. Following are the mapping errors.\n"
        sBatchDetails8 = "\n"
        sBatchDetails9 = "%s \n" % mResults
        sBatchDetails10 = "============================================================= \n"

        #Send Email
        #Subject
        mail_subject = "DEV - FDMEE Load Failed for %s" % (sLocName)

        #Sender [Enter your Sender Email Address]
        mail_sender = "Hyperion_FDMEE@satyanadh.in"

        #To List  [Enter your To List under section mail_to, CC List under mail_cc, if by Application Specific modify Application name else remove elif string]
        if str(sTargetAppName) == "HFMMGMT":
            mail_to = ['hyperion@satyanadh.in']
            mail_cc = ['XXX@YYY.com', 'XXXGroup@yyy.com']
        elif str(sTargetAppName) == "HFMLGL":
            mail_to = ['hyperion@satyanadh.in']
            mail_cc = ['XXX@YYY.com', 'XXXGroup@yyy.com']

        #Body
        mail_txt = "Hi, \n DEV - FDMEE Load Status: %s\n%s\n%s\n%s\n%s\n%s\n%s\n%s\n%s\n%s\n%s\n \n Regards, \n Hyperion Administrator" %(sBatchDetails, sBatchDetails1, sBatchDetails2, sBatchDetails3, sBatchDetails4, sBatchDetails5, sBatchDetails6, sBatchDetails7, sBatchDetails8, sBatchDetails9, sBatchDetails10)
        mail_html = """\
        <html>
        <head></head>
        <body>
        <pr>Hi,<br>
        Please find below the status for Data Load from DEV Instance <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        %s <br>
        <br>
        Regards,<br>
        Hyperion Admin <br>
        <br>
        <br>
        Note: This is an Auto Generated Email, please do not reply to this email id.
        </p>
        </body>
        </html>
        """ % (sBatchDetails, sBatchDetails1, sBatchDetails2, sBatchDetails3, sBatchDetails4, sBatchDetails5, sBatchDetails6, sBatchDetails7, sBatchDetails8, sBatchDetails9, sBatchDetails10)

        #Mail Consolidate
        #MIMEMultipart changed from alternative to mixed, this helps in displaying the email output in iPhone / Android in readable format
        mail = MIMEMultipart('mixed')
        mail['Subject'] = mail_subject
        mail['FROM']=mail_sender
        mail['To']=", ".join(mail_to)
        mail_p2=MIMEText(mail_html, 'html', 'utf-8')
        #mail.attach(mail_p1)
        mail.attach(mail_p2)
    
        #Attachment
        os.chdir("D:\Oracle\Middleware\user_projects\HFM1\FDMEE\outbox\logs")
        LogFile = "%s_%s.log" % (sTargetAppName, fdmProcessID)
        fLog = file(LogFile)
        part = MIMEText(fLog.read())
        part.add_header('Content-Disposition', 'attachment; filename="%s"' % LogFile)
        mail.attach(part)

        #Connection to SMTP
        #Enter SMTP Server Details, In case your server do require authentication modify authentication parameter below and uncomment
        s = smtplib.SMTP("mail.satyanadh.in:25")
        s.sendmail(mail_sender, mail_to+mail_cc, mail.as_string())
        s.quit()

    except e:
        print str(e)

Output of the Email Snapshot: