Clarity PPM

Expand all | Collapse all

Issue with Transaction Class field

Jump to Best Answer
  • 1.  Issue with Transaction Class field

    Posted 05-18-2017 11:51 PM

    Hi,

    I tried to create a portlet for Budget plan along with the investment details. When I tried with it, I get duplication of transaction class( almost list all transaction class, whereas the investment uses only 2/3 classes). 

     

    Below is the query that I used:

     

    SELECT
    rownum rn,
    i.id id,
    i.name name,
    i.code code,
    i.manager_id mgrId,
    mgr.full_name mgrName,
    ocp.az_set_is_id setisid,
    ocp.az_setis_contact setiscontact,
    res.full_name resname,
    oci.obj_work_Status workstatus,
    stat.name status,
    dobs.unit_id did,
    dobs.path dpath,
    dept.unit_id dpid,
    dept.path dppath,
    fp.period_type_code,
    prt.name prtname,
    fp.name fpname,
    fp.code cpcode,
    cl.name cstname,
    prcc.prname prname,
    TRAN.DESCRIPTION tclass,
    tran.id tdesc,
    stp.start_date sdate,
    fsp.end_date-1 enddate,
    cst.start_date startdate,
    cst.finish_date-1 finishdate,
    cst.slice slice,
    ROUND(((cst.finish_date-cst.start_date)*cst.slice),2) costvalue
    FROM INV_INVESTMENTS I
    inner JOIN FIN_PLANS FP
    ON I.ID =FP.OBJECT_ID
    AND FP.OBJECT_CODE ='project'
    AND FP.PLAN_TYPE_CODE ='FORECAST'
    AND FP.IS_PLAN_OF_RECORD=1
    LEFT OUTER JOIN FIN_COST_PLAN_DETAILS FP_DET
    ON FP.ID=FP_DET.PLAN_ID
    LEFT OUTER JOIN TRANSCLASS TRAN
    ON FP_DET.TRANSACTION_CLASS_ID = TRAN.ID
    LEFT OUTER JOIN odf_ca_project ocp
    ON ocp.id=i.id
    LEFT OUTER JOIN odf_ca_inv oci
    ON oci.id=i.id
    LEFT OUTER JOIN srm_Resources res
    ON res.user_id=ocp.az_setis_contact
    LEFT OUTER JOIN srm_resources mgr
    on i.manager_id=mgr.user_id
    LEFT OUTER JOIN cmn_lookups_v stat
    ON stat.lookup_code =oci.obj_work_status
    AND language_code ='en'
    AND stat.lookup_type='OBJ_INVESTMENT_WORK_STATUS'
    LEFT OUTER JOIN srm_Resources res
    ON res.user_id=ocp.az_setis_contact
    LEFT OUTER JOIN fin_cost_plan_details fcp
    ON fp.id=fcp.plan_id
    LEFT OUTER JOIN odf_ssl_cst_dtl_cost cst
    ON cst.prj_object_id=fcp.id
    LEFT OUTER JOIN BIZ_COM_PERIODS stp
    ON stp.id=fp.start_period_id
    LEFT OUTER JOIN BIZ_COM_PERIODS fsp
    ON fsp.id=fp.end_period_id
    LEFT OUTER JOIN cmn_lookups_v prt
    ON prt.lookup_type ='PERIOD_TYPE'
    AND prt.language_code='en'
    AND prt.lookup_code =fp.period_type_code
    LEFT OUTER JOIN cmn_lookups_v cl
    ON cl.lookup_type ='LOOKUP_FIN_COSTTYPECODE'
    AND cl.language_code='en'
    AND cl.id =fcp.cost_type_id
    LEFT OUTER JOIN prchargecode prcc
    ON prcc.prid=i.chargecodeid
    left outer join (
    select poa.record_id,poa.unit_id,ndo.obs_unit_id,ndo.path,ndo.obs_type_id,ndo.obs_type_name
    from prj_obs_associations poa
    inner join nbi_dim_obs ndo on poa.unit_id=ndo.obs_unit_id
    inner join inv_investments i on i.id=poa.record_id and i.is_active=1
    where ndo.obs_type_id=5004001
    )dobs on dobs.record_id=i.id
    left outer join (
    select poa.record_id,poa.unit_id,ndo.obs_unit_id,ndo.path,ndo.obs_type_id,ndo.obs_type_name
    from prj_obs_associations poa
    inner join nbi_dim_obs ndo on poa.unit_id=ndo.obs_unit_id
    inner join inv_investments i on i.id=poa.record_id and i.is_active=1
    where ndo.obs_type_id=5000002
    )dept on dept.record_id=i.id
    WHERE i.is_active=1

     

     

    Could you please help me out to sort it down?

     

    Thanks in advance.

     

    Monica



  • 2.  Re: Issue with Transaction Class field
    Best Answer

    Posted 05-25-2017 04:45 AM

    This worked:

     

    SELECT
     rownum rn,
     i.id id,
     i.name name,
     i.code code,
     i.manager_id mgrId,
     mgr.full_name mgrName,
     ocp.az_set_is_id setisid,
     ocp.az_setis_contact setiscontact,
     res.full_name resname,
     oci.obj_work_Status workstatus,
     stat.name status,
     dobs.unit_id did,
     dobs.path dpath,
     dept.unit_id dpid,
     dept.path dppath,
     fp.period_type_code,
     prt.name prtname,
     fp.name fpname,
     fp.code cpcode,
     cl.name cstname,
     prcc.prname prname,
     TRAN.DESCRIPTION tclass,
     tran.id tdesc,
     stp.start_date sdate,
     fsp.end_date-1 enddate,
     cst.start_date startdate,
     cst.finish_date-1 finishdate,
     cst.slice slice,
     ROUND(((cst.finish_date-cst.start_date)*cst.slice),2) costvalue
    FROM
     INV_INVESTMENTS I 
     left outer JOIN FIN_PLANS FP ON I.ID =FP.OBJECT_ID  AND FP.OBJECT_CODE  ='project' AND FP.PLAN_TYPE_CODE  ='BUDGET'  AND FP.IS_PLAN_OF_RECORD=1
     LEFT OUTER JOIN odf_ca_project ocp ON ocp.id=i.id
     LEFT OUTER JOIN odf_ca_inv oci ON oci.id=i.id
     LEFT OUTER JOIN srm_Resources res ON res.user_id=ocp.az_setis_contact
     LEFT OUTER JOIN srm_resources mgr on i.manager_id=mgr.user_id
     LEFT OUTER JOIN cmn_lookups_v stat ON stat.lookup_code =oci.obj_work_status AND language_code ='en' AND stat.lookup_type='OBJ_INVESTMENT_WORK_STATUS'
     LEFT OUTER JOIN srm_Resources res ON res.user_id=ocp.az_setis_contact
     LEFT OUTER JOIN fin_cost_plan_details fcp ON fp.id=fcp.plan_id
     LEFT OUTER JOIN TRANSCLASS TRAN ON fcp.TRANSACTION_CLASS_ID = TRAN.ID
     LEFT OUTER JOIN odf_ssl_cst_dtl_cost cst ON cst.prj_object_id=fcp.id
     LEFT OUTER JOIN BIZ_COM_PERIODS stp ON stp.id=fp.start_period_id
     LEFT OUTER JOIN BIZ_COM_PERIODS fsp ON fsp.id=fp.end_period_id
     LEFT OUTER JOIN cmn_lookups_v prt ON prt.lookup_type   ='PERIOD_TYPE' AND prt.language_code='en' AND prt.lookup_code  =fp.period_type_code
     LEFT OUTER JOIN cmn_lookups_v cl ON cl.lookup_type ='LOOKUP_FIN_COSTTYPECODE' AND cl.language_code='en' AND cl.id           =fcp.cost_type_id
     LEFT OUTER JOIN prchargecode prcc ON prcc.prid=i.chargecodeid
     left outer join (
         select
          poa.record_id,poa.unit_id,ndo.obs_unit_id,ndo.path,ndo.obs_type_id,ndo.obs_type_name
         from
          prj_obs_associations poa
          inner join nbi_dim_obs ndo on poa.unit_id=ndo.obs_unit_id
          inner join inv_investments i on i.id=poa.record_id and i.is_active=1
         where
          ndo.obs_type_id=5004001
         ) dobs on dobs.record_id=i.id
     left outer join (
         select
          poa.record_id,poa.unit_id,ndo.obs_unit_id,ndo.path,ndo.obs_type_id,ndo.obs_type_name
         from
          prj_obs_associations poa
          inner join nbi_dim_obs ndo on poa.unit_id=ndo.obs_unit_id
          inner join inv_investments i on i.id=poa.record_id and i.is_active=1
          where ndo.obs_type_id=5000002
         ) dept on dept.record_id=i.id
    WHERE
     i.is_active=1

     

    Thanks.

    Regards,

    Monica