AnsweredAssumed Answered

Timesheet Query issue

Question asked by Monica_Rampradeep_Raj on Feb 7, 2017
Latest reply on Feb 9, 2017 by Monica_Rampradeep_Raj

Hi Guys, I was trying to modify a portlet where the data is recursive, i.e. i see data displaying timesheet data even when the timesheet is adjusted, which is different from the posted one. Given for a project, a resource is charged twice as query returns both posted and adjusted time record. So I tried to alter query and I've paste my query below:

 

SELECT tp.prid period_int_id
, tp.prfinish period_ending
, srm.id res_int_id
, srm.full_name res_name
, st.name timesheet_status
, inv.id proj_int_id
, inv.odf_object_code task_type
, inv.name || '/' || tsk.prname proj_task_name
, rcc.name res_cost_center
, res_obs.obs_unit_id res_obs_id
, res_obs.level2_name res_function
, rol.full_name res_role
, dep_obs.level3_name res_region
, dep_obs.level4_name res_country
, srm.date_of_hire res_hire_date
, mgr.full_name proj_manager
, odfp.cost_center proj_cost_center
, odfp.ge_region proj_region
, odfp.ge_country proj_country
, odfp.ge_company_code proj_company_code
, odfp.ge_cps_number proj_cps_number
, odfp.ge_exp_prj_code exp_proj_code
, odfp.ge_local_sap_prj_num local_sap_proj_num
, odfp.ge_wbs_element proj_wbs_element
, odfp.ge_asiapac_int_order proj_asiapac_int_order
, odfp.ge_se_dummy_code proj_se_dummy_code
, odfp.ge_uk_dummy_code proj_uk_dummy_code
, odfp.ge_status proj_ge_status
, odfp.az_ge_task_type ge_task_type
, proj_obs.obs_unit_id proj_obs_id
, pacc.id res_class_id
, pacr.resource_class res_class
, ppav.numval1 labour_rate
, SUM(te.practsum/3600) hours_billed
, ppav.numval1*SUM(te.practsum/3600) proj_billing_charge
FROM prtimeentry te
INNER JOIN prtimesheet ts
ON   CASE
    WHEN ts.prisadjustment=0
    AND ts.prid           = te.prtimesheetid
    THEN 1
    WHEN ts.prisadjustment=1
    AND ts.pradjustedid   =te.prtimesheetid
    THEN 1
  END=1
INNER JOIN prtimeperiod tp
ON tp.prid = ts.prtimeperiodid
INNER JOIN srm_resources srm
ON srm.id = ts.prresourceid
INNER JOIN odf_ca_resource odfr
ON odfr.id = srm.id
INNER JOIN prj_resources res
ON res.prid = srm.id
INNER JOIN pac_mnt_resources pacr
ON pacr.id = srm.id
INNER JOIN srm_resources rol
ON rol.id = res.prprimaryroleid
INNER JOIN cmn_lookups_v st
ON st.lookup_type    = 'TIMESHEET_STATUS'
AND st.language_code = 'en'
AND st.lookup_enum   = ts.prstatus
INNER JOIN prassignment asn
ON asn.prid = te.prassignmentid
INNER JOIN prtask tsk
ON tsk.prid = asn.prtaskid
INNER JOIN inv_investments inv
ON inv.id = tsk.prprojectid
INNER JOIN odf_ca_project odfp
ON odfp.id = inv.id
INNER JOIN srm_resources mgr
ON mgr.user_id = inv.manager_id
LEFT OUTER JOIN pac_fos_resource_class pacc
ON pacc.resource_class = pacr.resource_class
LEFT OUTER JOIN ppa_matrixvalues ppav
ON ppav.value2 = pacr.resource_class
LEFT OUTER JOIN ppa_matrix ppa
ON ppa.matrixkey = ppav.matrixkey
AND ppa.description = 'Labor Rate Matrix'
LEFT OUTER JOIN cmn_lookups_v rcc
ON rcc.lookup_type = 'AZ_RES_COST_CENTER'
AND rcc.language_code = 'en'
AND rcc.lookup_enum = odfr.az_cost_center
LEFT OUTER JOIN
(
SELECT obsa.record_id
, obsu.obs_unit_id
, obsu.level2_name
FROM prj_obs_associations obsa
INNER JOIN nbi_dim_obs obsu
ON obsu.obs_unit_id = obsa.unit_id
AND obsu.obs_type_name  = 'Delivery and Resource Management'
WHERE obsa.table_name = 'SRM_RESOURCES'
) res_obs
ON res_obs.record_id = srm.id
LEFT OUTER JOIN
(
SELECT obsa.record_id
, obsu.obs_unit_id
, obsu.level3_name
, obsu.level4_name
FROM prj_obs_associations obsa
INNER JOIN nbi_dim_obs obsu
ON obsu.obs_unit_id = obsa.unit_id
AND obsu.obs_type_name  = 'AstraZeneca Dept Organisation'
WHERE obsa.table_name = 'SRM_RESOURCES'
) dep_obs
ON dep_obs.record_id = srm.id
LEFT OUTER JOIN
(
SELECT obsa.record_id
, obsu.obs_unit_id
FROM prj_obs_associations obsa
INNER JOIN nbi_dim_obs obsu
ON obsu.obs_unit_id = obsa.unit_id
AND obsu.obs_type_name  = 'AstraZeneca Dept Organisation'
WHERE obsa.table_name = 'SRM_PROJECTS'
) proj_obs
ON proj_obs.record_id = inv.id
GROUP BY tp.prid
, tp.prfinish
, srm.id
, srm.full_name
, st.name
, inv.odf_object_code
, inv.id
, inv.name
, tsk.prname
, rcc.name
, res_obs.obs_unit_id
, res_obs.level2_name
, rol.full_name
, dep_obs.level3_name
, dep_obs.level4_name
, srm.date_of_hire
, mgr.full_name
, odfp.cost_center
, odfp.ge_region
, odfp.ge_country
, odfp.ge_company_code
, odfp.ge_cps_number
, odfp.ge_exp_prj_code
, odfp.ge_local_sap_prj_num
, odfp.ge_wbs_element
, odfp.ge_asiapac_int_order
, odfp.ge_se_dummy_code
, odfp.ge_uk_dummy_code
, odfp.ge_status
, odfp.az_ge_task_type
, ppav.numval1
, proj_obs.obs_unit_id
, pacc.id
, pacr.resource_class

 

I tried to include a case statement in ON clause :

 

FROM prtimeentry te
INNER JOIN prtimesheet ts
ON
  CASE
    WHEN ts.prisadjustment=0
    AND ts.prid           = te.prtimesheetid
    THEN 1
    WHEN ts.prisadjustment=1
    AND ts.pradjustedid   =te.prtimesheetid
    THEN 1
  END=1

 

I didn't get any error when I try to run the query, but this keeps running and not loading any data.

 

Can you please help me out of this issue?

 

Monica

Outcomes