AnsweredAssumed Answered

View all months actuals

Question asked by otahri on Feb 11, 2013
Latest reply on Feb 14, 2013 by Dave
Hi all,

I'm building a portlet to view actuals per month , when i execute my query, i have just the months where i have actuals and not the other months.
what i need to doo is to view the other months even when i don't have actuals .

here is my qyery:

SELECT

@SELECT:DIM:USER_DEF:IMPLIED:DIM_MOIS:AE.mois:id_key@,

@SELECT:DIM:USER_DEF:IMPLIED:DIM_REALISE:AE.cle:cle@,

@SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM_REALISE:AE.dpmo:dpmo@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM_REALISE:AE.cell:cell@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM_REALISE:AE.ent:ent@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM_REALISE:AE.TRIMESTRE:TRIMESTRE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DIM_REALISE:AE.MOIS:MOIS@,

@SELECT:METRIC:USER_DEF:IMPLIED:AE.cout:cout@


from

(
select

TO_CHAR(temp.dpmo) ||TO_CHAR(temp.cell) ||TO_CHAR(temp.ent) cle,
temp.dpmo dpmo,
temp.cell cell,
temp.ent ent,
temp.Trimestre Trimestre,
temp.mois mois,
temp.cout cout

from
(

select
req2.DPMO DPMO,
req2.cellule_comptable cellule,
req2.filtre_entreprise entreprise,
req2.Trimestre Trimestre,
req2.mois mois,
SUM(req2.conso_jh * req2.taux_jh * req2.pourcent * 100) cout

from

(select

(select obs.level4_name
from nbi_dim_obs obs
where obs.obs_unit_id = av.oav_department) DPMO,

(select cell.name
from odf_ca_oav_cellul_compatble cell
where cell.id=av.oav_cpt_cell) cellule_comptable,

(select c.company_id
from srm_companies c
where con.xl_company = c.company_id) filtre_entreprise,

ressval.id_ress,
conso.conso conso_jh,
conso.Trimestre Trimestre,
conso.mois mois,
regie.xf_taux taux_jh,
NVL(av.oav_pourcentage,1) pourcent

FROM


odf_ca_xo_contrat con,
odf_ca_xos_avenant av,
odf_ca_xos_regie regie,



(select valreg.id_regie, ress.id id_ress
from srm_resources ress,
odf_ca_resource oress,
(select val.value val, reg.id id_regie
from odf_multi_valued_lookups val, odf_ca_xos_regie reg
where val.pk_id=reg.id
and val.object='xos_regie'

)valreg
where ress.unique_name = valreg.val
and ress.id=oress.id
and oress.oav_interimaire =0
)ressval,

(SELECT

ASG.prresourceid ress_id,

TO_CHAR (SLICE_DATE,'Q') Trimestre,
TO_CHAR (SLICE_DATE,'MM') Mois,

SUM(PBS.SLICE) /8 CONSO

FROM PRTASK TSK, PRASSIGNMENT ASG,
PRJ_BLB_SLICEREQUESTS PBSR, PRJ_BLB_SLICES PBS


WHERE
ASG.PRTASKID(+)=TSK.PRID
AND PBS.SLICE_REQUEST_ID=PBSR.ID
AND PBS.PRJ_OBJECT_ID=ASG.PRID
AND PBSR.REQUEST_NAME='MONTHLYRESOURCEACTCURVE'
AND TO_NUMBER(TO_CHAR (SLICE_DATE,'YYYY'))= NVL(@WHERE:PARAM:USER_DEF:INTEGER:ANNEE@,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')))

Group by ASG.prresourceid, slice_date
) conso



Where av.odf_parent_id = con.id
and regie.odf_parent_id= av.id
and ressval.id_regie=regie.id
and conso.ress_id = ressval.id_ress
)req2
group by req2.dpmo, req2.cellule_comptable, req2.filtre_entreprise, req2.Trimestre, req2.mois
)temp

)ae
WHERE @FILTER@



Thank you for your answers.

Attachments

Outcomes