CURSOR C_MAIN(AUDIT_P IN VARCHAR2, AUDIT_PRE_P IN VARCHAR2, FILTER_DMC IN VARCHAR2, ORG_ID IN NUMBER, WANT_IS_CLOSED IN VARCHAR2, WANT_BY_PROJECT_TYPE IN VARCHAR2 RESP_ID IN NUMBER) IS ( SELECT ---------------------- LABELS DEAL_CODE AS A12 --Deal Management Code ,DEAL_CODE_DESCRIPTION AS B12 --Description of the DMC ,DEAL_CODE_TYPE AS C12 --Deal Code type ,DECODE(:WANT_BY_PROJECT_TYPE,'N',PROJECT_TYPE,'ALL') AS D12 --Project type ,COMPANY ,AREA_UNIT ,SECTOR_UNIT ,PROFIT_CENTER ---------------------- MANUAL FIELDS ,'' AS N12 --Estimate to Complete ,'' AS O12 --Gap ,'' AS T12 --GMD ,'' AS Y12 --RI Annual Target ,'' AS AD12 -- Risk Exposure ,'' AS AE12 -- Exposure on Cost to Complete ,'' AS AF12 -- Exposure on Revenue ,'' AS AG12 -- Total (R) ,'' AS AH12 -- % / GR ,'' AS AI12 -- Trend ,'' AS AO12 -- Gap (P-R) (k) ,'' AS AQ12 -- Comments and actions ,'' AS AT12 -- Est. to compl. ,'' AS AU12 -- Gap ,'' AS AX12 -- Est. to compl. ,'' AS AY12 -- Gap ,'' AS BB12 -- Est. to compl. ,'' AS BC12 -- Gap -- TODO ,'RES_IND_BUD_CUM + AL12 – AF12' AS Z12 --Pot. Marg. M+P-R ,'Y12 – First RES_IND_BUD_CUM' AS AB12 --: TODOTODO ,'=SOMME( AI12:AL12)' AS AN12 -- Total (P) ---------------------- DATA ,SUM(REV_GR_BUD_LP) AS E12 --Revenue Budget (EGR) ,SUM(REV_GR_BUD_CUM) AS F12 --Contract to Date ,SUM(AUTHORIZED_AMT_CUM) AS G12 --Current Authorised Amount ,SUM(REV_GR_BUD_LP - REV_GR_ACT_LP ) AS H12 --Backlog ,SUM(REV_GR_BUD_CUM - REV_GR_ACT_CUM) AS I12 --Contract to Date ,SUM(ECSE_BUD_LP + ICSE_BUD_LP + SD_COST_BUD_LP) AS J12 --Costs ,SUM(ECSE_BUD_CUM + ICSE_BUD_CUM + SD_COST_BUD_CUM) AS K12 --Budget Contract to Date ,SUM(ECSE_ACT_CUM + ICSE_ACT_CUM + SD_COST_ACT_CUM ) AS L12 --Actual Contract to Date ,SUM( ECSE_BUD_LP + ICSE_BUD_LP + SD_COST_BUD_LP - ECSE_BUD_CUM - ICSE_BUD_CUM - SD_COST_BUD_CUM ) AS M12 --OPA Estimate to Complete:J12 - K12 ,SUM(DECODE(REV_GR_BUD_LP,0,NULL,GMD_BUD_LP / REV_GR_BUD_LP)) AS P12 --Budget GMD / EGR (%) ,SUM(DECODE(REV_GR_BUD_CUM,0,NULL,GMD_BUD_CUM / REV_GR_BUD_CUM)) AS Q12 --Contract to Date ,SUM(DECODE(NVL(REV_GR_BUD_LP - REV_GR_ACT_LP,0),0,NULL ,( ECSE_BUD_LP + ICSE_BUD_LP + SD_COST_BUD_LP - ECSE_ACT_LP - ICSE_ACT_LP - SD_COST_ACT_LP ) /( REV_GR_BUD_LP - REV_GR_ACT_LP ) ) ) AS R12 --Budget GMD on Backlog ,SUM(DECODE(NVL(REV_GR_BUD_CUM - REV_GR_ACT_CUM,0),0,NULL ,( ECSE_BUD_CUM + ICSE_BUD_CUM + SD_COST_BUD_CUM - ECSE_ACT_CUM - ICSE_ACT_CUM - SD_COST_ACT_CUM ) /(REV_GR_BUD_CUM - REV_GR_ACT_CUM) ) ) AS S12 --Contract to Date ,SUM(DECODE(REV_GR_BUD_LP,0,NULL,RES_IND_BUD_LP / REV_GR_BUD_LP)) AS U12 --Budget Operating Margin RI/EGR (%) ,SUM(DECODE(REV_GR_BUD_CUM,0,NULL,RES_IND_BUD_CUM / REV_GR_BUD_CUM))AS V12 --Contract to Date ,SUM(RES_IND_BUD_LP) AS W12 --Absolute Budget End Dec-2005 ,SUM(RES_IND_BUD_CUM) AS X12 --Absolute Budget Contract to Date ,SUM(RES_IND_ACT_CUM) AS AA12 --Marg. Taken totale MIS (K) ,SUM(DECODE(REV_GR_BUD_LP,0,NULL,RES_IND_BUD_LP / REV_GR_BUD_LP)) AS AC12 -- % / GR :W12/E12 ,SUM(PROV_RISQ_1513_CUM) AS AJ12 -- Risk Allowances ,SUM(CNTG_RES_CUM) AS AK12 -- Reserve against Cost to Complete ,SUM(RSV_REV_RED_DISCOUNT_CUM) AS AL12 -- RfRR-Discounts ,SUM(RSV_REV_RED_PENALTY_CUM) AS AM12 -- RfRR-Penalties ,SUM(BILL_ADJ_EC_CUM) AS AP12 -- Billing Adjustment (k) ,SUM(ECSE_BUD_CUM) ,SUM(ECSE_ACT_CUM) AS AS12 -- Spent in MIS ,SUM(ICSE_BUD_CUM) AS AV12 -- ICSE ,SUM(ICSE_ACT_CUM) AS AW12 -- Spent in MIS ,SUM(SD_COST_BUD_CUM) AS AZ12 -- Cost of Labour ,SUM(SD_COST_ACT_CUM) AS BA12 -- Spent in MIS FROM ( SELECT PRO.PROJECT_TYPE, ( SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = :ORG_ID ) AS COMPANY ,( SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = T1.AU_ID ) AS AREA_UNIT ,( SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = T1.SU_ID ) AS SECTOR_UNIT ,( SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS WHERE ORGANIZATION_ID = T1.PC_ID ) AS PROFIT_CENTER --TODO ,DPT.NAME AS DPT --TODO ,REPLACE(PRO.NAME,';',',') AS DPT_NAME ,NVL(PCLC.CLASS_CATEGORY,'') AS DEAL_CODE_TYPE -- A12 ,NVL(PPCL.CLASS_CODE,'') AS DEAL_CODE -- B12 ,NVL(PCLC.DESCRIPTION,'') AS DEAL_CODE_DESCRIPTION -- C12 ,NVL(T2.PDCT_BRUT_BUDGET ,0) AS REV_GR_BUD_LP -- E12 ,NVL(T1.PDCT_BRUT_BUDGET ,0) AS REV_GR_BUD_CUM -- F12 ,NVL(T1.MT_AUTHOR_BUDGET ,0) AS AUTHORIZED_AMT_CUM -- G12 ,NVL(T2.PDCT_BRUT_REALISE ,0) AS REV_GR_ACT_LP -- H12 ,NVL(T1.PDCT_BRUT_REALISE ,0) AS REV_GR_ACT_CUM -- I12 ,NVL(T2.CRE_BUDGET ,0) AS ECSE_BUD_LP -- J12 ,NVL(T2.CRI_BUDGET ,0) AS ICSE_BUD_LP -- J12 ,NVL(T2.COUT_JS_BUDGET ,0) AS SD_COST_BUD_LP -- J12 ,NVL(T1.CRE_BUDGET ,0) AS ECSE_BUD_CUM -- K12 ,NVL(T1.CRI_BUDGET ,0) AS ICSE_BUD_CUM -- K12 ,NVL(T1.COUT_JS_BUDGET ,0) AS SD_COST_BUD_CUM -- k12 ,NVL(T1.CRE_REALISE ,0) AS ECSE_ACT_CUM -- L12 ,NVL(T1.CRI_REALISE ,0) AS ICSE_ACT_CUM -- L12 ,NVL(T1.COUT_JS_REALISE ,0) AS SD_COST_ACT_CUM -- L12 ,NVL(T2.GMD_BUDGET ,0) AS GMD_BUD_LP -- P12 ,NVL(T1.GMD_BUDGET ,0) AS GMD_BUD_CUM -- Q12 ,NVL(T2.CRE_REALISE ,0) AS ECSE_ACT_LP -- R12 ,NVL(T2.CRI_REALISE ,0) AS ICSE_ACT_LP -- R12 ,NVL(T2.COUT_JS_REALISE ,0) AS SD_COST_ACT_LP -- R12 ,NVL(T2.INDC_RESULT_BUDGET ,0) AS RES_IND_BUD_LP -- U12 ,NVL(T1.INDC_RESULT_BUDGET ,0) AS RES_IND_BUD_CUM -- V12 ,NVL(T1.INDC_RESULT_REALISE ,0) AS RES_IND_ACT_CUM -- AA12 ,NVL(T1.PROV_RISQ_1513_REALISE,0) AS PROV_RISQ_1513_CUM -- AJ12 ,NVL(T1.PROV_ALEAS_BUDGET ,0) AS CNTG_RES_CUM -- AK12 ,NVL(T1.PRCA_DISCOUNT_BUDGET ,0) AS RSV_REV_RED_DISCOUNT_CUM -- AL12 ,NVL(T1.PRCA_PENALTY_BUDGET ,0) AS RSV_REV_RED_PENALTY_CUM -- AM12 ,NVL(T1.FAE_FEA_EC_REALISE ,0) AS BILL_ADJ_EC_CUM -- AP12 FROM PA_CLASS_CATEGORIES PCLC, PA_PROJECT_CLASSES PPCL, ( SELECT T1B.PROJECT_ID, T1B.AU_ID, T1B.SSU_ID, T1B.SU_ID, T1B.PC_ID, T1B.MT_AUTHOR_BUDGET, T1A.FAE_FEA_EC_REALISE, T1A.FAE_FEA_IS_REALISE, T1A.FAE_FEA_IEA_REALISE, T1B.PROV_ALEAS_BUDGET, T1A.FACTU_HT_EC_REALISE, T1A.FACTU_TTC_EC_REALISE, T1A.FACTU_HT_IS_REALISE, T1A.FACTU_TTC_IS_REALISE, T1A.FACTU_HT_IEA_REALISE, T1A.FACTU_TTC_IEA_REALISE, T1B.CRE_BUDGET, T1A.CRE_REALISE, T1B.CRI_BUDGET, T1A.CRI_REALISE, T1B.MT_CTRACTUEL_BUDGET, T1B.PDCT_BRUT_BUDGET, T1A.PDCT_BRUT_REALISE, T1B.PNG_BUDGET, T1A.PNG_REALISE, T1B.PDCT_INT_BUDGET, T1A.PDCT_INT_REALISE, T1B.PA_BUDGET, T1A.PA_REALISE, T1B.PTI_BUDGET, T1A.PTI_REALISE, T1B.PUO_BUDGET, T1A.PUO_REALISE, T1B.PND_BUDGET, T1A.PND_REALISE, T1B.GMD_BUDGET, T1A.GMD_REALISE, T1B.COUT_JS_BUDGET, T1A.COUT_JS_REALISE, T1B.INDC_RESULT_BUDGET, T1A.INDC_RESULT_REALISE, T1B.NB_JS_BUDGET, T1A.NB_JS_REALISE, T1B.NB_JS_CLEAR_BUDGET, T1A.NB_JS_CLEAR_REALISE, T1B.NB_JO_BUDGET, T1A.NB_JO_REALISE, T1B.NB_JO_CLEAR_BUDGET, T1A.NB_JO_CLEAR_REALISE, T1B.TL_AMT_BUDGET, T1A.TL_AMT_REALISE, T1B.FC_BUDGET, T1A.FC_REALISE, T1B.SC_BUDGET, T1A.SC_REALISE, T1B.RSV_LOSS_BUDGET, T1A.RSV_LOSS_REALISE, T1B.PRCA_BUDGET, T1B.PRCA_DISCOUNT_BUDGET, T1B.PRCA_PENALTY_BUDGET, T1A.PROV_RISQ_1513_REALISE, T1B.CLEARING_REVENUE_BUDGET, T1A.CLEARING_REVENUE_REALISE, T1B.CLEARING_COST_BUDGET, T1A.CLEARING_COST_REALISE, T1B.OBC_BUDGET, T1A.OBC_REALISE, T1B.OTC_BUDGET, T1A.OTC_REALISE, T1B.OEC_BUDGET, T1A.OEC_REALISE, T1B.CITH_BUDGET, T1A.CITH_REALISE, T1B.CITWU_BUDGET, T1A.CITWU_REALISE FROM ( SELECT IND.PROJECT_ID, IND.AU_ID, IND.SSU_ID, IND.SU_ID, IND.PC_ID, NVL(IND.AUTHORIZED_AMT,0) AS MT_AUTHOR_BUDGET, NVL(IND.PROV_ALEAS,0) AS PROV_ALEAS_BUDGET, NVL(IND.FACTU_HT,0) AS FACTU_HT_BUDGET, NVL(IND.CRE,0) AS CRE_BUDGET, NVL(IND.CRI,0) AS CRI_BUDGET, NVL(IND.CONTRACT_AMT,0) AS MT_CTRACTUEL_BUDGET, NVL(IND.PB,0) AS PDCT_BRUT_BUDGET, NVL(IND.PNG,0) AS PNG_BUDGET, NVL(IND.PI,0) AS PDCT_INT_BUDGET, NVL(IND.PA,0) AS PA_BUDGET, NVL(IND.PTI,0) AS PTI_BUDGET, NVL(IND.PUO,0) AS PUO_BUDGET, NVL(IND.PND,0) AS PND_BUDGET, NVL(IND.GMD,0) AS GMD_BUDGET, NVL(IND.CJS,0) AS COUT_JS_BUDGET, NVL(IND.IR,0) AS INDC_RESULT_BUDGET, NVL(IND.NBJS_DPT,0) AS NB_JS_BUDGET, NVL(IND.NBJS_CLEAR,0) AS NB_JS_CLEAR_BUDGET, NVL(IND.NBJO_DPT,0) AS NB_JO_BUDGET, NVL(IND.NBJO_CLEAR,0) AS NB_JO_CLEAR_BUDGET, NVL(IND.TL_AMT,0) AS TL_AMT_BUDGET, NVL(IND.FC,0) AS FC_BUDGET, NVL(IND.SC,0) AS SC_BUDGET, NVL(IND.RSV_LOSS,0) AS RSV_LOSS_BUDGET, NVL(IND.PRCA,0) AS PRCA_BUDGET, NVL(IND.PRCA_DISCOUNT,0) AS PRCA_DISCOUNT_BUDGET, NVL(IND.PRCA_PENALTY,0) AS PRCA_PENALTY_BUDGET, NVL(IND.CLEARING_REVENUE,0) AS CLEARING_REVENUE_BUDGET, NVL(IND.CLEARING_COST,0) AS CLEARING_COST_BUDGET, NVL(IND.OBC,0) AS OBC_BUDGET, NVL(IND.OTC,0) AS OTC_BUDGET, NVL(IND.OEC,0) AS OEC_BUDGET, NVL(IND.CITH,0) AS CITH_BUDGET, NVL(IND.CITWU,0) AS CITWU_BUDGET FROM XXST_INFO_PROJ_V IND WHERE IND.PERIOD_NAME = :AUDIT_P AND IND.ACTUAL_FLAG = 'B' ) T1B, ( SELECT IND.PROJECT_ID, NVL(IND.AUTHORIZED_AMT,0) AS MT_AUTHOR_REALISE, NVL(IND.PROV_ALEAS,0) AS PROV_ALEAS_REALISE, NVL(IND.FACTU_HT_EC,0) AS FACTU_HT_EC_REALISE, NVL(IND.FACTU_TTC_EC,0) AS FACTU_TTC_EC_REALISE, NVL(IND.FACTU_HT_IS,0) AS FACTU_HT_IS_REALISE, NVL(IND.FACTU_TTC_IS,0) AS FACTU_TTC_IS_REALISE, NVL(IND.FACTU_HT_IEA,0) AS FACTU_HT_IEA_REALISE, NVL(IND.FACTU_TTC_IEA,0) AS FACTU_TTC_IEA_REALISE, NVL(FAE_FEA_EC_DRAFT,0) AS FAE_FEA_EC_REALISE, NVL(FAE_FEA_IS_DRAFT,0) AS FAE_FEA_IS_REALISE, NVL(FAE_FEA_IEA_DRAFT,0) AS FAE_FEA_IEA_REALISE, NVL(IND.CRE,0) AS CRE_REALISE, NVL(IND.CRI,0) AS CRI_REALISE, NVL(IND.CONTRACT_AMT,0) AS MT_CTRACTUEL_REALISE, NVL(IND.PB_DRAFT,0) AS PDCT_BRUT_REALISE, NVL(IND.PNG_DRAFT,0) AS PNG_REALISE, NVL(IND.PI_DRAFT,0) AS PDCT_INT_REALISE, NVL(IND.PA_DRAFT,0) AS PA_REALISE, NVL(IND.PTI_DRAFT,0) AS PTI_REALISE, NVL(IND.PUO_DRAFT,0) AS PUO_REALISE, NVL(IND.PND_DRAFT,0) AS PND_REALISE, NVL(IND.GMD_DRAFT,0) AS GMD_REALISE, NVL(IND.CJS,0) AS COUT_JS_REALISE, NVL(IND.IR_DRAFT,0) AS INDC_RESULT_REALISE, NVL(IND.NBJS_DPT,0) AS NB_JS_REALISE, NVL(IND.NBJS_CLEAR,0) AS NB_JS_CLEAR_REALISE, NVL(IND.NBJO_DPT,0) AS NB_JO_REALISE, NVL(IND.NBJO_CLEAR,0) AS NB_JO_CLEAR_REALISE, NVL(IND.TL_AMT,0) AS TL_AMT_REALISE, NVL(IND.FC,0) AS FC_REALISE, NVL(IND.SC,0) AS SC_REALISE, NVL(IND.RSV_LOSS,0) AS RSV_LOSS_REALISE, NVL(IND.PROV_RISQ_1513,0) AS PROV_RISQ_1513_REALISE, NVL(IND.CLEARING_REVENUE,0) AS CLEARING_REVENUE_REALISE, NVL(IND.CLEARING_COST,0) AS CLEARING_COST_REALISE, NVL(IND.OBC,0) AS OBC_REALISE, NVL(IND.OTC,0) AS OTC_REALISE, NVL(IND.OEC,0) AS OEC_REALISE, NVL(IND.CITH,0) AS CITH_REALISE, NVL(IND.CITWU,0) AS CITWU_REALISE FROM XXST_INFO_PROJ_V IND WHERE IND.PERIOD_NAME = :AUDIT_P AND IND.ACTUAL_FLAG = 'A' ) T1A WHERE T1B.PROJECT_ID = T1A.PROJECT_ID ) T1, ( SELECT T2B.PROJECT_ID, T2B.MT_AUTHOR_BUDGET, T2A.FAE_FEA_IS_REALISE, T2A.FAE_FEA_EC_REALISE, T2A.FAE_FEA_IEA_REALISE, T2B.PROV_ALEAS_BUDGET, T2A.FACTU_HT_EC_REALISE, T2A.FACTU_TTC_EC_REALISE, T2A.FACTU_HT_IS_REALISE, T2A.FACTU_TTC_IS_REALISE, T2A.FACTU_HT_IEA_REALISE, T2A.FACTU_TTC_IEA_REALISE, T2B.CRE_BUDGET, T2A.CRE_REALISE, T2B.CRI_BUDGET, T2A.CRI_REALISE, T2B.MT_CTRACTUEL_BUDGET, T2B.PDCT_BRUT_BUDGET, T2A.PDCT_BRUT_REALISE, T2B.PNG_BUDGET, T2A.PNG_REALISE, T2B.PDCT_INT_BUDGET, T2A.PDCT_INT_REALISE, T2B.PA_BUDGET, T2A.PA_REALISE, T2B.PTI_BUDGET, T2A.PTI_REALISE, T2B.PUO_BUDGET, T2A.PUO_REALISE, T2B.PND_BUDGET, T2A.PND_REALISE, T2B.GMD_BUDGET, T2A.GMD_REALISE, T2B.COUT_JS_BUDGET, T2A.COUT_JS_REALISE, T2B.INDC_RESULT_BUDGET, T2A.INDC_RESULT_REALISE, T2B.NB_JS_BUDGET, T2A.NB_JS_REALISE, T2B.NB_JS_CLEAR_BUDGET, T2A.NB_JS_CLEAR_REALISE, T2B.NB_JO_BUDGET, T2A.NB_JO_REALISE, T2B.NB_JO_CLEAR_BUDGET, T2A.NB_JO_CLEAR_REALISE, T2B.TL_AMT_BUDGET, T2A.TL_AMT_REALISE, T2B.FC_BUDGET, T2A.FC_REALISE, T2B.SC_BUDGET, T2A.SC_REALISE, T2B.RSV_LOSS_BUDGET, T2A.RSV_LOSS_REALISE, T2B.PRCA_BUDGET, T2B.PRCA_DISCOUNT_BUDGET, T2B.PRCA_PENALTY_BUDGET, T2A.PROV_RISQ_1513_REALISE, T2B.CLEARING_REVENUE_BUDGET, T2A.CLEARING_REVENUE_REALISE, T2A.CLEARING_COST_REALISE, T2B.OBC_BUDGET, T2A.OBC_REALISE, T2B.OTC_BUDGET, T2A.OTC_REALISE, T2B.OEC_BUDGET, T2A.OEC_REALISE, T2B.CITH_BUDGET, T2A.CITH_REALISE, T2B.CITWU_BUDGET, T2A.CITWU_REALISE FROM ( SELECT IND.PROJECT_ID, NVL(IND.AUTHORIZED_AMT,0) AS MT_AUTHOR_BUDGET, NVL(IND.PROV_ALEAS,0) AS PROV_ALEAS_BUDGET, NVL(IND.FACTU_HT,0) AS FACTU_HT_BUDGET, NVL(IND.CRE,0) AS CRE_BUDGET, NVL(IND.CRI,0) AS CRI_BUDGET, NVL(IND.CONTRACT_AMT,0) AS MT_CTRACTUEL_BUDGET, NVL(IND.PB,0) AS PDCT_BRUT_BUDGET, NVL(IND.PNG,0) AS PNG_BUDGET, NVL(IND.PI,0) AS PDCT_INT_BUDGET, NVL(IND.PA,0) AS PA_BUDGET, NVL(IND.PTI,0) AS PTI_BUDGET, NVL(IND.PUO,0) AS PUO_BUDGET, NVL(IND.PND,0) AS PND_BUDGET, NVL(IND.GMD,0) AS GMD_BUDGET, NVL(IND.CJS,0) AS COUT_JS_BUDGET, NVL(IND.IR,0) AS INDC_RESULT_BUDGET, NVL(IND.NBJS_DPT,0) AS NB_JS_BUDGET, NVL(IND.NBJS_CLEAR,0) AS NB_JS_CLEAR_BUDGET, NVL(IND.NBJO_DPT,0) AS NB_JO_BUDGET, NVL(IND.NBJO_CLEAR,0) AS NB_JO_CLEAR_BUDGET, NVL(IND.TL_AMT,0) AS TL_AMT_BUDGET, NVL(IND.FC,0) AS FC_BUDGET, NVL(IND.SC,0) AS SC_BUDGET, NVL(IND.RSV_LOSS,0) AS RSV_LOSS_BUDGET, NVL(IND.PRCA,0) AS PRCA_BUDGET, NVL(IND.PRCA_DISCOUNT,0) AS PRCA_DISCOUNT_BUDGET, NVL(IND.PRCA_PENALTY,0) AS PRCA_PENALTY_BUDGET, NVL(IND.CLEARING_REVENUE,0) AS CLEARING_REVENUE_BUDGET, NVL(IND.CLEARING_COST,0) AS CLEARING_COST_BUDGET, NVL(IND.OBC,0) AS OBC_BUDGET, NVL(IND.OTC,0) AS OTC_BUDGET, NVL(IND.OEC,0) AS OEC_BUDGET, NVL(IND.CITH,0) AS CITH_BUDGET, NVL(IND.CITWU,0) AS CITWU_BUDGET FROM XXST_INFO_PROJ_V IND WHERE IND.PERIOD_NAME = :AUDIT_PRE_P AND IND.ACTUAL_FLAG = 'B' ) T2B, ( SELECT IND.PROJECT_ID, NVL(IND.AUTHORIZED_AMT,0) AS MT_AUTHOR_REALISE, NVL(IND.PROV_ALEAS,0) AS PROV_ALEAS_REALISE, NVL(IND.FACTU_HT_EC,0) AS FACTU_HT_EC_REALISE, NVL(IND.FACTU_TTC_EC,0) AS FACTU_TTC_EC_REALISE, NVL(IND.FACTU_HT_IS,0) AS FACTU_HT_IS_REALISE, NVL(IND.FACTU_TTC_IS,0) AS FACTU_TTC_IS_REALISE, NVL(IND.FACTU_HT_IEA,0) AS FACTU_HT_IEA_REALISE, NVL(IND.FACTU_TTC_IEA,0) AS FACTU_TTC_IEA_REALISE, NVL(FAE_FEA_EC_DRAFT,0) AS FAE_FEA_EC_REALISE, NVL(FAE_FEA_IS_DRAFT,0) AS FAE_FEA_IS_REALISE, NVL(FAE_FEA_IEA_DRAFT,0) AS FAE_FEA_IEA_REALISE, NVL(IND.CRE,0) AS CRE_REALISE, NVL(IND.CRI,0) AS CRI_REALISE, NVL(IND.CONTRACT_AMT,0) AS MT_CTRACTUEL_REALISE, NVL(IND.PB_DRAFT,0) AS PDCT_BRUT_REALISE, NVL(IND.PNG_DRAFT,0) AS PNG_REALISE, NVL(IND.PI_DRAFT,0) AS PDCT_INT_REALISE, NVL(IND.PA_DRAFT,0) AS PA_REALISE, NVL(IND.PTI_DRAFT,0) AS PTI_REALISE, NVL(IND.PUO_DRAFT,0) AS PUO_REALISE, NVL(IND.PND_DRAFT,0) AS PND_REALISE, NVL(IND.GMD_DRAFT,0) AS GMD_REALISE, NVL(IND.CJS,0) AS COUT_JS_REALISE, NVL(IND.IR_DRAFT,0) AS INDC_RESULT_REALISE, NVL(IND.NBJS_DPT,0) AS NB_JS_REALISE, NVL(IND.NBJS_CLEAR,0) AS NB_JS_CLEAR_REALISE, NVL(IND.NBJO_DPT,0) AS NB_JO_REALISE, NVL(IND.NBJO_CLEAR,0) AS NB_JO_CLEAR_REALISE, NVL(IND.TL_AMT,0) AS TL_AMT_REALISE, NVL(IND.FC,0) AS FC_REALISE, NVL(IND.SC,0) AS SC_REALISE, NVL(IND.RSV_LOSS,0) AS RSV_LOSS_REALISE, NVL(IND.PROV_RISQ_1513,0) AS PROV_RISQ_1513_REALISE, NVL(IND.CLEARING_REVENUE,0) AS CLEARING_REVENUE_REALISE, NVL(IND.CLEARING_COST,0) AS CLEARING_COST_REALISE, NVL(IND.OBC,0) AS OBC_REALISE, NVL(IND.OTC,0) AS OTC_REALISE, NVL(IND.OEC,0) AS OEC_REALISE, NVL(IND.CITH,0) AS CITH_REALISE, NVL(IND.CITWU,0) AS CITWU_REALISE FROM XXST_INFO_PROJ_V IND WHERE IND.PERIOD_NAME = :AUDIT_PRE_P AND IND.ACTUAL_FLAG = 'A' ) T2A WHERE T2B.PROJECT_ID = T2A.PROJECT_ID ) T2, PA_PROJECTS_ALL PRO, HR_ALL_ORGANIZATION_UNITS DPT WHERE PPCL.PROJECT_ID=T1.PROJECT_ID AND PCLC.CLASS_CATEGORY=PPCL.CLASS_CATEGORY AND PCLC.CLASS_CATEGORY LIKE (NVL(:FILTER_DMC,'DEAL_CODE%')) AND ( PRO.PROJECT_TYPE LIKE 'EC%' OR PRO.PROJECT_TYPE LIKE 'ISIS%' OR PRO.PROJECT_TYPE LIKE 'ISEC%' ) AND T1.PROJECT_ID = T2.PROJECT_ID(+) AND T1.PROJECT_ID = PRO.PROJECT_ID AND PRO.CARRYING_OUT_ORGANIZATION_ID IN ( SELECT T.ORGANIZATION_ID FROM ( SELECT HOU.ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI, HR_ALL_ORGANIZATION_UNITS HOU2 WHERE HOI.ORGANIZATION_ID = HOU.ORGANIZATION_ID AND HOU2.ORGANIZATION_ID = :ORG_ID AND HOI.ORG_INFORMATION1 = TO_CHAR(HOU2.ORGANIZATION_ID) AND HOI.ORG_INFORMATION_CONTEXT = 'Exp Organization Defaults' ) T, HR_ALL_ORGANIZATION_UNITS H WHERE T.ORGANIZATION_ID = H.ORGANIZATION_ID AND H.TYPE='DEPARTMENT' AND ( ( SELECT INSTR(RESPONSIBILITY_KEY,'_',12) FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_ID=:RESP_ID ) = 0 OR ( SELECT SUBSTR(RESPONSIBILITY_KEY,1,11) FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_ID = :RESP_ID ) LIKE '%CTL%' ) UNION SELECT T.ORGANIZATION_ID FROM ( SELECT HOU.ORGANIZATION_ID FROM HR_ALL_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI, HR_ALL_ORGANIZATION_UNITS HOU2 WHERE HOI.ORGANIZATION_ID = HOU.ORGANIZATION_ID AND HOU2.ORGANIZATION_ID = :ORG_ID AND HOI.ORG_INFORMATION1 = TO_CHAR(HOU2.ORGANIZATION_ID) AND HOI.ORG_INFORMATION_CONTEXT = 'Exp Organization Defaults' ) T, HR_ALL_ORGANIZATION_UNITS H WHERE T.ORGANIZATION_ID = H.ORGANIZATION_ID AND H.TYPE='DEPARTMENT' AND ( SELECT INSTR(RESPONSIBILITY_KEY,'_',12) FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_ID = :RESP_ID ) != 0 AND ( SELECT SUBSTR(RESPONSIBILITY_KEY,1,11) FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_ID = :RESP_ID ) NOT LIKE '%CTL%' ) AND PRO.CARRYING_OUT_ORGANIZATION_ID = DPT.ORGANIZATION_ID AND ( ( ( ( ( TO_CHAR(PRO.CLOSED_DATE,'YY')>=SUBSTR(:AUDIT_PRE_P,4) AND :WANT_IS_CLOSED = 'N' ) ) OR ( ( :WANT_IS_CLOSED = 'Y' ) ) ) AND PRO.CLOSED_DATE IS NOT NULL ) OR PRO.CLOSED_DATE IS NULL ) ) GROUP BY ---------------------- LABELS DEAL_CODE ,DEAL_CODE_DESCRIPTION ,DEAL_CODE_TYPE ,DECODE(:WANT_BY_PROJECT_TYPE,'N',PROJECT_TYPE,'ALL') ,COMPANY ,AREA_UNIT ,SECTOR_UNIT ,PROFIT_CENTER ---------------------- MANUAL FIELDS ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' -- TODO ,'RES_IND_BUD_CUM + AL12 – AF12' ,'Y12 – First RES_IND_BUD_CUM' ,'=SOMME( AI12:AL12)' ORDER BY A12, B12, C12, D12 );