Hi Dave,
I was talking about write data from PPM in a csv file with delimitor ","
Sorry I missed to include it entirely
Here is the details :
<!--Create a file handle-->
<file:writeFile delimiter="," embedded="false" fileName="${filePath}">
<!--Set column names in file-->
<file:comment value="Employee ID,Resource Name,Resource Manager,Primary Role,Employment Type,Cost Center,Financial Category,Investment ID,Investment Name,CAR #,Methodology,Task Name,Charge Code,Timesheet Status,Timesheet Start Date,Timesheet End Date,Time Entry Hours"/>
<!--Get project data-->
<sql:query dataSource="${clarityDS}" escapeText="0"
var="result"><![CDATA[
SELECT
SI_ODF_CA_TEAM_RES.SI_EMPLOYEE_ID EID,
RPT_TM_RESOURCES.FULL_NAME RF,
RESOURCEMANAGER.FULL_NAME RMF,
RPT_TM_PRIMARYROLE.FULL_NAME PRF,
RPT_TM_PERSON_TYPE.NAME PTN,
SI_TEAM_RES_DEPT.DEPARTCODE DPC,
SI_PRJ_FINANCIAL_CATEGORY.NAME FCN,
RPT_TM_INVESTMENTS.CODE INC,
RPT_TM_INVESTMENTS.NAME INN,
SI_ODF_CA_PROJECT.SI_CAR_NUMBER CAR,
SI_PRJ_METHODOLOGY.NAME MEN,
RPT_TM_TASKS.PRNAME PRN,
RPT_TM_CHARGECODE.PRNAME CPRN,
RPT_TM_TIMESHEET_STATUS.NAME TSN,
RPT_TM_TIMEPERIOD.PRSTART TPRS,
RPT_TM_TIMEPERIOD.PRFINISH TPRF,
SUM(RPT_TM_TIMEENTRY.PRACTSUM / 3600) TEH
FROM
PRJ_RESOURCES RPT_TM_PRJ_RESOURCES
JOIN SRM_RESOURCES RPT_TM_RESOURCES ON (RPT_TM_PRJ_RESOURCES.PRID=RPT_TM_RESOURCES.ID)
LEFT JOIN SRM_RESOURCES RPT_TM_PRIMARYROLE ON (RPT_TM_PRJ_RESOURCES.PRPRIMARYROLEID=RPT_TM_PRIMARYROLE.ID)
LEFT JOIN SRM_RESOURCES RESOURCEMANAGER ON RPT_TM_RESOURCES.MANAGER_ID = RESOURCEMANAGER.USER_ID
LEFT JOIN (
SELECT RECORD_ID RESOURCE_ID,
OBST.NAME OBS_TYPE_NAME,
OBSU.LEVEL1_NAME,
OBSU.LEVEL2_NAME,
OBSU.LEVEL3_NAME,
OBSU.LEVEL4_NAME,
OBSU.LEVEL5_NAME,
OBSU.LEVEL6_NAME,
OBSU.LEVEL7_NAME,
OBSU.LEVEL8_NAME,
OBSU.LEVEL9_NAME,
OBSU.LEVEL10_NAME,
OBSU.PATH OBS_PATH,
OBSU.OBS_UNIT_ID
FROM PRJ_OBS_ASSOCIATIONS OBSA, NBI_DIM_OBS OBSU, PRJ_OBS_TYPES OBST
WHERE OBSA.TABLE_NAME = 'SRM_RESOURCES'
AND OBSA.UNIT_ID = OBSU.OBS_UNIT_ID
AND OBSU.OBS_TYPE_ID = OBST.ID
AND OBST.UNIQUE_NAME = 'si_rm_obs'
) RPT_TM_OBS_STRUCTURE ON (RPT_TM_OBS_STRUCTURE.RESOURCE_ID=RPT_TM_RESOURCES.ID)
RIGHT OUTER JOIN PRTIMESHEET RPT_TM_TIMESHEET ON (RPT_TM_RESOURCES.ID=RPT_TM_TIMESHEET.PRRESOURCEID)
LEFT OUTER JOIN PRTIMEPERIOD RPT_TM_TIMEPERIOD ON (RPT_TM_TIMEPERIOD.PRID=RPT_TM_TIMESHEET.PRTIMEPERIODID)
LEFT OUTER JOIN PRTIMEENTRY RPT_TM_TIMEENTRY ON (RPT_TM_TIMESHEET.PRID=RPT_TM_TIMEENTRY.PRTIMESHEETID)
LEFT OUTER JOIN IMM_INCIDENTS RPT_TM_IMM_INCIDENTS ON (RPT_TM_IMM_INCIDENTS.ID=RPT_TM_TIMEENTRY.INCIDENT_ID)
LEFT OUTER JOIN INV_INVESTMENTS RPT_TM_INCIDENT_INVESTMENT ON (RPT_TM_INCIDENT_INVESTMENT.ID=RPT_TM_IMM_INCIDENTS.INVESTMENT_ID)
LEFT OUTER JOIN PRCHARGECODE RPT_TM_CHARGECODE ON (RPT_TM_CHARGECODE.PRID=RPT_TM_TIMEENTRY.PRCHARGECODEID)
LEFT OUTER JOIN PRASSIGNMENT RPT_TM_PRASSIGNMENT ON (RPT_TM_TIMEENTRY.PRASSIGNMENTID=RPT_TM_PRASSIGNMENT.PRID)
LEFT OUTER JOIN PRTASK RPT_TM_TASKS ON (RPT_TM_PRASSIGNMENT.PRTASKID=RPT_TM_TASKS.PRID)
LEFT OUTER JOIN INV_INVESTMENTS RPT_TM_INVESTMENTS ON (RPT_TM_TASKS.PRPROJECTID=RPT_TM_INVESTMENTS.ID)
LEFT OUTER JOIN ODF_CA_PROJECT SI_ODF_CA_PROJECT ON (SI_ODF_CA_PROJECT.ID=RPT_TM_INVESTMENTS.ID)
LEFT OUTER JOIN (
select name, lookup_code, lookup_enum from cmn_lookups_v where lookup_type = UPPER('SI_RUN_TYPE') and language_code = 'en'
) SI_PRJ_FINANCIAL_CATEGORY ON (SI_ODF_CA_PROJECT.SI_FIN_CAT=SI_PRJ_FINANCIAL_CATEGORY.LOOKUP_CODE)
LEFT OUTER JOIN (
select name, lookup_code, lookup_enum from cmn_lookups_v where lookup_type IN (UPPER('SI_INVEST_METHODOLOGY'),'SI_PRJ_TYPE') and language_code = 'en'
) SI_PRJ_METHODOLOGY ON (SI_ODF_CA_PROJECT.SI_INVEST_METHOD=SI_PRJ_METHODOLOGY.LOOKUP_CODE)
LEFT OUTER JOIN (
SELECT LOOKUP_CODE, LOOKUP_ENUM, ID, NAME
FROM CMN_LOOKUPS_V
WHERE LOOKUP_TYPE = 'TIMESHEET_STATUS' AND LANGUAGE_CODE = 'en'
) RPT_TM_TIMESHEET_STATUS ON (RPT_TM_TIMESHEET_STATUS.LOOKUP_ENUM=RPT_TM_TIMESHEET.PRSTATUS)
LEFT OUTER JOIN (
SELECT LOOKUP_CODE, LOOKUP_ENUM, ID, NAME
FROM CMN_LOOKUPS_V
WHERE LOOKUP_TYPE = 'SRM_RESOURCE_TYPE' AND LANGUAGE_CODE = 'en'
) RPT_TM_PERSON_TYPE ON (RPT_TM_PERSON_TYPE.ID=RPT_TM_RESOURCES.PERSON_TYPE)
INNER JOIN ODF_CA_RESOURCE SI_ODF_CA_TEAM_RES ON (RPT_TM_RESOURCES.ID=SI_ODF_CA_TEAM_RES.ID)
LEFT OUTER JOIN DEPARTMENTS SI_TEAM_RES_DEPT ON (SI_ODF_CA_TEAM_RES.SI_RES_CC=SI_TEAM_RES_DEPT.ID)
WHERE
RPT_TM_TIMEPERIOD.PRSTART >= ADD_MONTHS(TRUNC(SYSDATE),-6)-EXTRACT(DAY FROM TRUNC(SYSDATE))+1
GROUP BY
SI_ODF_CA_TEAM_RES.SI_EMPLOYEE_ID,
RPT_TM_RESOURCES.FULL_NAME,
RPT_TM_PRIMARYROLE.FULL_NAME,
RPT_TM_TIMESHEET_STATUS.NAME,
RPT_TM_TIMEPERIOD.PRFINISH,
RPT_TM_INVESTMENTS.CODE,
RPT_TM_INVESTMENTS.NAME,
SI_PRJ_METHODOLOGY.NAME,
RPT_TM_TIMEPERIOD.PRSTART,
SI_PRJ_FINANCIAL_CATEGORY.NAME,
SI_TEAM_RES_DEPT.DEPARTCODE,
RPT_TM_TASKS.PRNAME,
RPT_TM_CHARGECODE.PRNAME,
RPT_TM_PERSON_TYPE.NAME,
SI_ODF_CA_PROJECT.SI_CAR_NUMBER,
RESOURCEMANAGER.FULL_NAME
HAVING
SUM(RPT_TM_TIMEENTRY.PRACTSUM / 3600) > 0
]]></sql:query>
<!--Iterate rows in query and create rows in file-->
<core:forEach items="${result.rows}" trim="true" var="row">
<file:line>
<file:column value=""${row.EID.replaceAll('"', '""')}""/>
<file:column value=""${row.RF.replaceAll('"', '""')}""/>
<file:column value=""${row.RMF.replaceAll('"', '""')}""/>
<file:column value=""${row.PRF.replaceAll('"', '""')}""/>
<file:column value=""${row.PTN.replaceAll('"', '""')}""/>
<file:column value=""${row.DPC.replaceAll('"', '""')}""/>
<file:column value=""${row.FCN.replaceAll('"', '""')}""/>
<file:column value=""${row.INC.replaceAll('"', '""')}""/>
<file:column value=""${row.INN.replaceAll('"', '""')}""/>
<file:column value=""${row.CAR.replaceAll('"', '""')}""/>
<file:column value=""${row.MEN.replaceAll('"', '""')}""/>
<file:column value=""${row.PRN.replaceAll('"', '""')}""/>
<file:column value=""${row.CPRN.replaceAll('"', '""')}""/>
<file:column value=""${row.TSN.replaceAll('"', '""')}""/>
<file:column value=""${row.TPRS.replaceAll('"', '""')}""/>
<file:column value=""${row.TPRF.replaceAll('"', '""')}""/>
<file:column value=""${row.TEH.replaceAll('"', '""')}""/>
</file:line>
</core:forEach>
<!--Flush buffer and close file handle-->
</file:writeFile>