saravsclicks

COGS_SGA

import os,fnmatch
import os.path
from os.path import isfile, join

DimDict = {"COG_SGA":"UD4"}
#list of locations to duplicate the insert statements
strLocNameList = "'HFM_COG_SGA_J_D','HFM_COG_SGA_A_M'"

#strLocNameList = "'HFM_AR_AGING_BMSG_J_D','HFM_AR_AGING_BSSG_J_D','HFM_BS_BMSG_J_D','HFM_BS_BSSG_J_D','HFM_DEF_REV_BMSG_J_D','HFM_DEF_REV_BSSG_J_D','HFM_REV_BMSG_J_D','HFM_REV_BSSG_J_D'"

def writeToFile(strPath, strFile, strBuf, strFlag):
   strFilePath = strPath + "/" + strFile
   objFile = open(strFilePath, strFlag)
   objFile.write(strBuf + "\n")
   objFile.close()
   return "ok"

strOutboxPath = fdmContext["OUTBOXDIR"] + "/"
strInboxPath = fdmContext["INBOXDIR"]

dirStruct = strInboxPath + "/" + "Staging" + "/"

nDataKey = 0
strDATAKEYSQL = "SELECT MAX(DATAKEY) as DATAKEYVAL from tDATAMAP"
rsDataKey = fdmAPI.executeQuery (strDATAKEYSQL, [])
strDatakey = "1"
strRecCount = 0
while (rsDataKey.next()):
strDatakey = rsDataKey.getString("DATAKEYVAL")

nDataKey = long(float(strDatakey))
XXX = writeToFile(strOutboxPath,"TEST.log",strDATAKEYSQL,"w")
XXX = writeToFile(strOutboxPath,"TEST.log",str(nDataKey),"a")

for file in os.listdir(dirStruct):
   if isfile(dirStruct+file):
       strFileName = str(file)
       firstPos = strFileName.find("_")
       secondPos = strFileName.find("_",firstPos+1)
       ThirdPos = strFileName.find("_",secondPos+1)
       FouthPos = strFileName.find("_",ThirdPos+1)
       lengthstr = len(strFileName)-4
       strDimName = strFileName[FouthPos+1:lengthstr]

       isDimExists = 0
       strLocID = 1
       if DimDict.has_key(strDimName):
           strDimName = DimDict[strDimName]
           #strLocKeyList = "15"
           #retrieve the partition key list from the partition names
           strLocKey = "Select listagg(partitionkey,',') within group (order by partitionkey) as LOCKEY from TPOVPARTITION where partname IN ( " + str(strLocNameList) + ")"
           XXX = writeToFile(strOutboxPath,"TEST.log",strLocKey,"a")
           rsA2 = fdmAPI.executeQuery(strLocKey, [])
           if rsA2.next():
               strLocKeyList = rsA2.getString("LOCKEY")
           fdmAPI.closeConnection()

           strLocKeyArray = strLocKeyList.split(",")
           if os.path.isfile(join(dirStruct,file)):
               objdataFile = open(dirStruct+file,'r')
               LineContent = objdataFile.readlines()
               XXX = writeToFile(strOutboxPath,"TEST.log","-----------------------Script Begning Preparing the list to update the source Keys's--------------------------------","w")
               Count = 1
               strAllSrcKey = ""
               strAllMaps = ""
               DeleteList = ""
               strTableCommand = "INTO TDATAMAP (PARTITIONKEY,DIMNAME,SRCKEY,TARGKEY,DATAKEY,TDATAMAPTYPE,system_generated_flag) VALUES \n"
               for strLocID in strLocKeyArray:
                   strAllSrcKey = ""
                   strAllMaps = ""
                   DeleteList = ""
                   for strlines in LineContent:
                       if strlines.find(",") > 0:
                           strSrcKey = strlines.split(",")[0].strip().upper()
                           strTgtKey = strlines.split(",")[1].strip().upper()
                           if len(DeleteList) > 0:
                               DeleteList = DeleteList + ",'" + strSrcKey + "'"
                           else:
                               DeleteList = "'" + strSrcKey + "'"
                           if Count == 1:
                               strAllSrcKey = "'" + strSrcKey + "'"
                               strAllMaps = strTableCommand + " (" + str(strLocID) + ",'" + strDimName + "' , '" + strSrcKey + "' , '" + strTgtKey.strip() + "'," + str(nDataKey) + ", 'ERP','N') \n"
                           else:
                               strAllSrcKey = strAllSrcKey + ",'" + strSrcKey + "'"
                               strAllMaps = strAllMaps + strTableCommand + " (" + str(strLocID) + ",'" + strDimName + "' , '" + strSrcKey + "' , '" + strTgtKey + "'," + str(nDataKey) + ", 'ERP','N') \n"
                           Count = Count + 1
                           strRecCount = strRecCount + 1
                           nDataKey = nDataKey + 1
                           if strRecCount == 1000 or strRecCount > 1000 :
                               StrDeleteSQL = "DELETE FROM TDATAMAP WHERE PARTITIONKEY IN ( " + str(strLocID) + " ) AND UPPER(DIMNAME) = '" + strDimName + "' AND SRCKEY IN ( " + str(DeleteList) + ")"
                               strInsertSQL = "Insert ALL" + "\n" + strAllMaps + " \n Select * from dual"
                               if len(DeleteList.strip()) > 0 :
                                   fdmAPI.executeDML(StrDeleteSQL, [], False)
                                   fdmAPI.commitTransaction()
                               fdmAPI.executeDML(strInsertSQL, [], False)
                               fdmAPI.commitTransaction()
                               fdmAPI.closeConnection()
                               DeleteList = ""
                               strAllMaps = ""
                               Count = 1
                               strRecCount = 0

                   StrDeleteSQL = "DELETE FROM TDATAMAP WHERE PARTITIONKEY IN ( " + str(strLocID) + " ) AND UPPER(DIMNAME) = '" + strDimName + "' AND SRCKEY IN ( " + str(DeleteList) + ")"
                   strInsertSQL = "Insert ALL" + "\n" + strAllMaps + " \n Select * from dual"
                   XXX = writeToFile(strOutboxPath,"TEST.log","***********************Generated SQL's*************************","a")
                   XXX = writeToFile(strOutboxPath,"TEST.log","=====================Insert SQL================================","a")
                   XXX = writeToFile(strOutboxPath,"TEST.log",strInsertSQL,"a")
                   XXX = writeToFile(strOutboxPath,"TEST.log","=====================Delete SQL================================","a")
                   XXX = writeToFile(strOutboxPath,"TEST.log",StrDeleteSQL,"a")
                   #                       Sectio to excute the generated SQL on FDMEE DB to update the mps will be enable once the SQL are varified and syntatical error free
                   if len(DeleteList.strip()) > 0 :
                       fdmAPI.executeDML(StrDeleteSQL, [], False)
                       fdmAPI.commitTransaction()
                   fdmAPI.executeDML(strInsertSQL, [], False)
                   fdmAPI.commitTransaction()
                   fdmAPI.closeConnection()
       else:
           XXX = writeToFile(strOutboxPath,"TEST.log","Given Dimension does not Exists in the Application","a")

NEW_BMSG-MGMT-BU
import os,fnmatch
import os.path
from os.path import isfile, join

DimDict = {"BILLTYPE":"UD1","MGMT_BU":"UD2","SOLUTION":"UD3","GEOGRAPHY":"UD4"} #,"ENTITY":"ENTITY","CUSTOM1":"UD1","CUSTOM2":"UD2","CUSTOM3":"UD3","CUSTOM4":"UD4","CUSTOM5":"UD5","CUSTOM6":"UD6","ICP":"ICP"}
#list of locations to duplicate the insert statements
strLocNameList = "'HFM_DEF_REV_BMSG_J_D','HFM_REV_BMSG_J_D','HFM_AR_AGING_BMSG','HFM_BS_BMSG_J_D'"

#strLocNameList = "'HFM_AR_AGING_BMSG','HFM_AR_AGING_BSSG','HFM_BS_BMSG_J_D','HFM_BS_BSSG_J_D','HFM_DEF_REV_BMSG_J_D','HFM_DEF_REV_BSSG_J_D','HFM_REV_BMSG_J_D','HFM_REV_BSSG_J_D'"

def writeToFile(strPath, strFile, strBuf, strFlag):
   strFilePath = strPath + "/" + strFile
   objFile = open(strFilePath, strFlag)
   objFile.write(strBuf + "\n")
   objFile.close()
   return "ok"

strOutboxPath = fdmContext["OUTBOXDIR"] + "/"
strInboxPath = fdmContext["INBOXDIR"]

dirStruct = strInboxPath + "/" + "Staging" + "/"

nDataKey = long(float(strDatakey))
XXX = writeToFile(strOutboxPath,"TEST.log",strDATAKEYSQL,"w")
XXX = writeToFile(strOutboxPath,"TEST.log",str(nDataKey),"a")

New_BSSG-SEGMENT
import os,fnmatch
import os.path
from os.path import isfile, join

DimDict = {"BILLTYPE":"UD1","SEGMENT":"UD2","SOLUTION":"UD3","GEOGRAPHY":"UD4"} #,"ENTITY":"ENTITY","CUSTOM1":"UD1","CUSTOM2":"UD2","CUSTOM3":"UD3","CUSTOM4":"UD4","CUSTOM5":"UD5","CUSTOM6":"UD6","ICP":"ICP"}
#list of locations to duplicate the insert statements
strLocNameList = "'HFM_AR_AGING_BSSG','HFM_BS_BSSG_J_D','HFM_DEF_REV_BSSG_J_D','HFM_REV_BSSG_J_D'"

#strLocNameList = "'HFM_AR_AGING_BMSG','HFM_AR_AGING_BSSG','HFM_BS_BMSG_J_D','HFM_BS_BSSG_J_D','HFM_DEF_REV_BMSG_J_D','HFM_DEF_REV_BSSG_J_D','HFM_REV_BMSG_J_D','HFM_REV_BSSG_J_D'"

def writeToFile(strPath, strFile, strBuf, strFlag):
   strFilePath = strPath + "/" + strFile
   objFile = open(strFilePath, strFlag)
   objFile.write(strBuf + "\n")
   objFile.close()
   return "ok"

strOutboxPath = fdmContext["OUTBOXDIR"] + "/"
strInboxPath = fdmContext["INBOXDIR"]

dirStruct = strInboxPath + "/" + "Staging" + "/"

nDataKey = long(float(strDatakey))
XXX = writeToFile(strOutboxPath,"TEST.log",strDATAKEYSQL,"w")
XXX = writeToFile(strOutboxPath,"TEST.log",str(nDataKey),"a")

REV_CON_CURR
import os,fnmatch
import os.path
from os.path import isfile, join

DimDict = {"SEGMENT":"UD2","GEOGRAPHY":"UD4"} #,"ENTITY":"ENTITY","CUSTOM1":"UD1","CUSTOM2":"UD2","CUSTOM3":"UD3","CUSTOM4":"UD4","CUSTOM5":"UD5","CUSTOM6":"UD6","ICP":"ICP"}
#list of locations to duplicate the insert statements
strLocNameList = "'HFM_REV_CON_CUR'"

#strLocNameList = "'HFM_AR_AGING_BMSG','HFM_AR_AGING_BSSG','HFM_BS_BMSG_J_D','HFM_BS_BSSG_J_D','HFM_DEF_REV_BMSG_J_D','HFM_DEF_REV_BSSG_J_D','HFM_REV_BMSG_J_D','HFM_REV_BSSG_J_D'"

def writeToFile(strPath, strFile, strBuf, strFlag):
   strFilePath = strPath + "/" + strFile
   objFile = open(strFilePath, strFlag)
   objFile.write(strBuf + "\n")
   objFile.close()
   return "ok"

strOutboxPath = fdmContext["OUTBOXDIR"] + "/"
strInboxPath = fdmContext["INBOXDIR"]

dirStruct = strInboxPath + "/" + "Staging" + "/"

nDataKey = long(float(strDatakey))
XXX = writeToFile(strOutboxPath,"TEST.log",strDATAKEYSQL,"w")
XXX = writeToFile(strOutboxPath,"TEST.log",str(nDataKey),"a")

def deriveCurrency(strfield, strrecord):
return strfield[0:3]

def trimAttr1(strfield, strrecord):
#strfield = strfield.strip(" ")
return strfield[0:20]