Sure, Suman! I'm new to timesheet and I'm reworking on an existing portlet, so it would be great if you can incorporate some basic details about timesheet tables too! Here is my query:
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
, 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 ts.prid = te.prtimesheetid
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
, ppav.numval1
, proj_obs.obs_unit_id
, pacc.id
, pacr.resource_class