AnsweredAssumed Answered

upgraded clarity from version 12.04 to 13.3 problem with portfolio tables - unsolved!!

Question asked by Pega5u5 on Jul 28, 2015
Latest reply on Jul 31, 2015 by am1

In a previous post by this title (upgraded clarity from version 12.04 to 13.3 problem with portfolio tables - unsolved!!) i got an awnser that i couldn't use or didnt'understand.

 

at this moment I have 2 questions

 

1.

I have this query that works in the old (v12) database

 

SELECT DISTINCT u.unique_name AS kpl,
  (pf.bdgt_cst_total) PFBUDGET
        FROM (prj_obs_associations a
        RIGHT JOIN prj_obs_units u ON a.unit_id = u.ID)
        INNER JOIN prj_obs_types t ON u.type_id = t.ID
        INNER JOIN inv_investments p ON a.record_id = p.ID
        INNER JOIN pma_portfolio_contents pfc ON p.id = pfc.invest_id
        INNER JOIN pma_portfolios pf ON pfc.PORTFOLIO_ID = pf.ID
  WHERE t.unique_name = 'obs_m_sth'
    AND a.table_name = 'SRM_PROJECTS'                    
    AND pfc.invest_type = 'project'
    AND TO_CHAR (pf.start_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')


 

in the new (v13) database I cannot find the pma_portfolios.bdgt_cst_total field. Where did it go and how can i implement it in said query

 

I can rewrite certain aspects to :

 

SELECT DISTINCT u.unique_name AS kpl,
  (pf.bdgt_cst_total) PFBUDGET
          FROM (prj_obs_associations a
          RIGHT JOIN prj_obs_units u ON a.unit_id = u.ID)
          INNER JOIN prj_obs_types t ON u.type_id = t.ID
          INNER JOIN inv_investments p ON a.record_id = p.ID
          INNER JOIN pfm_Investments pfc ON p.id = pfc.investment_id
          INNER JOIN pfm_portfolios pf ON pfc.PORTFOLIO_ID = pf.ID
    WHERE t.unique_name = 'obs_m_sth'
          AND a.table_name = 'SRM_PROJECTS'
          AND pfc.ODF_OBJECT_CODE = 'project'
          AND TO_CHAR (pf.start_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY');


 

but as expected this gives an error on the bdgt_cst_total field

 

2.

in the next (db v12) query, there is a subquery (with remarks as alias) which gets portfolio remarks from the odf_ca_portfolio table.

Has this functionality been deleted form the portfolio's? as i can't seem to find it.

SELECT u.UNIQUE_NAME AS KPL,
  u.Name,
  MAX(
  ( SELECT DISTINCT dbms_lob.substr( opf.prtf_rem, 1024, 1 ) opm
  FROM pma_portfolios pf,
    pma_portfolio_contents pfc,
    odf_ca_portfolio opf,
    inv_investments i
  WHERE pfc.INVEST_TYPE             = 'project'
  AND i.ID                          = pfc.invest_id
  AND pfc.portfolio_id              = opf.ID
  AND pf.ID                         = opf.id
  AND TO_CHAR(pf.START_DATE,'YYYY') = TO_CHAR(sysdate,'YYYY')
  AND i.code                        = p.code
  )) remarks
FROM (PRJ_OBS_ASSOCIATIONS a
RIGHT JOIN PRJ_OBS_UNITS u
ON a.UNIT_ID = u.ID)
INNER JOIN PRJ_OBS_TYPES t
ON u.TYPE_ID = t.ID
INNER JOIN inv_investments p
ON a.RECORD_ID      = p.ID
WHERE t.UNIQUE_NAME = 'obs_m_sth'
AND a.TABLE_NAME    = 'SRM_PROJECTS'
AND u.UNIQUE_NAME  IN('4SOS')
GROUP BY u.UNIQUE_NAME,
  u.Name
ORDER BY u.Name

 

i hope you guys can help me

Outcomes