Hello again,
Good Evening!!!
I'm getting stuck in this stage. Project validation has been done and i divided into 2 categories, which is success and Failure.
My clarification is, how to concatenate all the validated project's error information in single field ( i.e., Custom Attribute)
Below codes(text are bold) are validated for both success and failure. I need to concatenate only the error description. It may be comes inside the update statement.
<!--Validating the data >
<gel:script xmlns:core="jelly:core" xmlns:email="jelly:email" xmlns:file="jelly:com.niku.union.gel.FileTagLibrary" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:util="jelly:util" xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary">
<gel:setDataSource dbId="Niku" />
<core:set var="todayDate">
<gel:formatDate format="ddMMyy" />
</core:set>
<core:set var="logPath" value="/fs0/clarity1/share/Clarity/NE/fromCmp/log/CI_LOG_MESSAGES_${todayDate}.log" />
<core:set var="ErrLog" value="/fs0/clarity1/share/Clarity/NE/fromCmp/log/errorLog/CI_LOG_ERROR_${todayDate}.log" />
<core:file append="true" trim="false" omitXmlDeclaration="true" name="${logPath}">
${TS} - Process Started at ${TS}
${TS} - Validating the data from Staging Table
${TS} - Select the projects for Validations</core:file>
<core:catch var="exception1">
<sql:query var="invcode">
select distinct inv.id "PROJECT_ID",inv.code,invp.IS_PROGRAM,invp.IS_TEMPLATE,inv.IS_ACTIVE "PROJECT_ACTIVE",inv.STATUS "PROJECT_STATUS",
pac.STATUS "FINANCIAL_STATUS",prt.PRISTASK,pac.LOCATIONID
from
INV_PROJECTS invp, INV_INVESTMENTS inv, PAC_MNT_PROJECTS pac,ODF_CA_PROJECT odf, PRTASK prt,odf_ca_ne_actual_if_his2 odfh
Where invp.PRID=inv.ID
and pac.ID=inv.ID
and odf.ID=pac.ID
and prt.PRPROJECTID=odf.id
and odf.Z_RGN =4
and odf.z_lead_comp in(38,39)
and (pac.DEPARTCODE Like 'NESAS%' OR pac.DEPARTCODE Like 'NTCE%')
and inv.code=odfh.Z_PROJECT_ID
</sql:query>
</core:catch>
<core:if test="${exception1 != null}">
<core:file append="true" trim="false" omitXmlDeclaration="true" name="${ErrLog}">
${TS} - Project Extraction failed
${TS} - ERROR : ${exception1}
</core:file>
</core:if>
<core:if test="${invcode.rowCount > 0}">
<gel:log>Project_Count : ${invcode.rowCount}</gel:log>
<core:forEach items="${invcode.rows}" trim="true" var="projFile">
<core:set var="projectcode" value="${projFile.code}" />
<core:set var="PROJECT_ID" value="${projFile.PROJECT_ID}" />
<core:set var="PROJECTIS_PROGRAM" value="${projFile.IS_PROGRAM}" />
<core:set var="PROJECTIS_TEMPLATE" value="${projFile.IS_TEMPLATE}" />
<core:set var="PROJECT_ACTIVE" value="${projFile.PROJECT_ACTIVE}" />
<core:set var="PROJECT_STATUS" value="${projFile.PROJECT_STATUS}" />
<core:set var="FINANCIAL_STATUS" value="${projFile.FINANCIAL_STATUS}" />
<core:set var="PRISTASK" value="${projFile.PRISTASK}" />
<core:set var="LOCATIONID" value="${projFile.LOCATIONID}" />
<gel:log>Project_CODE : ${projectcode}</gel:log>
<!--gel:log>ProjectID : ${PROJECT_ID}</gel:log>
<gel:log>IS_PROGRAM : ${PROJECTIS_PROGRAM}</gel:log>
<gel:log>IS_TEMPLATE : ${PROJECTIS_TEMPLATE}</gel:log>
<gel:log>PROJECT_ACTIVE : ${PROJECT_ACTIVE}</gel:log>
<gel:log>PROJECT_STATUS : ${PROJECT_STATUS}</gel:log>
<gel:log>FINANCIAL_STATUS : ${FINANCIAL_STATUS}</gel:log>
<gel:log>PRISTASK : ${PRISTASK}</gel:log>
<gel:log>LOCATIONID : ${LOCATIONID}</gel:log-->
<core:if test="${PROJECTIS_PROGRAM == 1}" var="F1">
<sql:update var="Failure_Records1"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Project should not be a Program' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR - Program Query has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${PROJECTIS_TEMPLATE == 1}" var="F2">
<sql:update var="Failure_Records2"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Project should not be a Template' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR - Template Query has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${PROJECT_ACTIVE == 0}" var="F3">
<sql:update var="Failure_Records3"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Project should not be Inactive' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR - Project Active Query has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${PROJECT_STATUS != 1}" var="F4">
<sql:update var="Failure_Records4"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Project is not "Approved" Status' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR - Project Status has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${FINANCIAL_STATUS!='O'}" var="F5">
<sql:update var="Failure_Records5"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Project Financial Status is Not Open' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR -Financial Status Query has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${PRISTASK == 0}" var="F6">
<sql:update var="Failure_Records6"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Task Not Available' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR - Task Query has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${LOCATIONID == null}" var="F7">
<sql:update var="Failure_Records7"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Failure',z_error_info = 'Project Location is NULL' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>ERROR - Location Query has been updated in Actual History Screen</gel:log>
</core:if>
<core:if test="${PROJECTIS_PROGRAM == 0 && PROJECTIS_TEMPLATE == 0 && PROJECT_ACTIVE == 1 && PROJECT_STATUS == 1 && FINANCIAL_STATUS =='O' && PRISTASK == 1 && LOCATIONID != null}">
<sql:update var="Success_Records"> update odf_ca_ne_actual_if_his2 set z_process_flag = 'Success',z_error_info = 'Project details are valid' where Z_PROJECT_ID='${projectcode}'</sql:update>
<gel:log>SUCCESS - Project is validated and updated in Actual History Screen</gel:log>
</core:if>
</core:forEach>
</core:if>
<core:file append="true" trim="false" omitXmlDeclaration="true" name="${logPath}">
${TS} - Process Ends - All Projects are validated.</core:file>
</gel:script>
Thanks in Advance!!!
Regards,
Karthick