AnsweredAssumed Answered

Manual activity Report

Question asked by Rajkiran Paul on Jul 9, 2020
Latest reply on Jul 14, 2020 by Clive Morrish

How can we create  manual activity list report with the below column for last 30 days .

 

Activity Id

Task

Change Request

User ID

Department

Branch Name

State

Request type

Completed Date

Application Name

Activity State

 

 

 I am working on the  below query but this is not giving proper data on report.

 

 

select

pCR.Change_Request_ID as "Change Request Id",

pApp.acti_id as "Activity Id",

Concat(Concat(pCR.Type,' :'),pCR.OPERAND_NAME) as "Task",

puser.USER_ID as "User ID",

puser.BRANCH_NAME as "Branch Name",

puser.DEPARTMENT as "Department",

puser.STATE as "State",

xyz.NAME as "Application name",

pCRI.Name as "Change Request",

pCRI.COMPLETED_DATE_NO_TIME as "Completed Date",

pCR.REQUEST_TYPE as "Request type",

pCR.STATE as "Activity State"

from avuser.PV_CHANGE_REQUEST_DETAIL pCR

left join avuser.pv_users puser on (pCR.AFFECTED_USER_ID=puser.ID)

left join avuser.PV_CHANGE_REQUEST pCRI on (pCR.Change_Request_ID=pCRI.ID)

left join avuser.wp_proci_node pApp on (pApp.acti_id=pCR.ID)

left join avuser.T_APPLICATIONS xyz on (xyz.ID=pCR.VALUE_APP_ID) where pCRI.REQUEST_DATE_NO_TIME >(SYSDATE-30)

 

 

 

Kindly help me on this.

Outcomes