SET SERVEROUTPUT ON SIZE 1000000 SET VERIFY OFF WHENEVER SQLERROR CONTINUE; SET ECHO OFF SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 2000 SET PAGESIZE 0 SET FEEDBACK OFF SET HEADING OFF SET TRIMSPOOL ON SET COLSEP ';' DECLARE -- primary/secondary pour filter-- ---------------------------------------------------------------------- -- Cursors ---------------------------------------------------------------------- -- 12-06 / 01-07 / '' / 82 / Y / Y / 50142 CURSOR CUR_MAIN( VV_AUDIT_P VARCHAR2, VV_AUDIT_PRE_P VARCHAR2, VV_FILTER_DMC VARCHAR2, VN_ORG_ID NUMBER, VV_WANT_IS_CLOSED VARCHAR2, VV_WANT_BY_PROJECT_TYPE VARCHAR2, VN_RESP_ID NUMBER) IS SELECT ---------------------- LABELS COMPANY ,AREA_UNIT ,SECTOR_UNIT ,PROFIT_CENTER ,DEAL_CODE AS A_ --Deal Management Code ,DEAL_CODE_DESCRIPTION AS B_ --Description of the DMC ,DEAL_CODE_TYPE AS C_ --Deal Code type ,DECODE(VV_WANT_BY_PROJECT_TYPE,'N',PROJECT_TYPE,'ALL') AS D_ --Project type ---------------------- DATA ,SUM(REV_GR_BUD_LP) AS E_--Revenue Budget (EGR) ,SUM(REV_GR_BUD_CUM) AS F_--Contract to Date ,SUM(AUTHORIZED_AMT_CUM) AS G_--Current Authorised Amount ,SUM(REV_GR_BUD_LP - REV_GR_ACT_LP ) AS H_--Backlog ,SUM(REV_GR_BUD_CUM - REV_GR_ACT_CUM) AS I_--Contract to Date ,SUM(ECSE_BUD_LP + ICSE_BUD_LP + SD_COST_BUD_LP) AS J_--Costs ,SUM(ECSE_BUD_CUM + ICSE_BUD_CUM + SD_COST_BUD_CUM) AS K_--Budget Contract to Date ,SUM(ECSE_ACT_CUM + ICSE_ACT_CUM + SD_COST_ACT_CUM ) AS L_--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 M_--OPA Estimate to Complete:J12 - K12 ,SUM(DECODE(REV_GR_BUD_LP,0,NULL,GMD_BUD_LP / REV_GR_BUD_LP)) AS P_--Budget GMD / EGR (%) ,SUM(DECODE(REV_GR_BUD_CUM,0,NULL,GMD_BUD_CUM / REV_GR_BUD_CUM)) AS Q_--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 R_--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 S_--Contract to Date ,SUM(DECODE(REV_GR_BUD_LP,0,NULL,RES_IND_BUD_LP / REV_GR_BUD_LP)) AS U_--Budget Operating Margin RI/EGR (%) ,SUM(DECODE(REV_GR_BUD_CUM,0,NULL,RES_IND_BUD_CUM / REV_GR_BUD_CUM))AS V_--Contract to Date ,SUM(RES_IND_BUD_LP) AS W_--Absolute Budget End Dec-2005 ,SUM(RES_IND_BUD_CUM) AS X_--Absolute Budget Contract to Date ,SUM(RES_IND_ACT_CUM) AS AA_--Marg. Taken totale MIS (K) ,SUM(DECODE(REV_GR_BUD_LP,0,NULL,RES_IND_BUD_LP / REV_GR_BUD_LP)) AS AC_-- % / GR :W12/E12 ,SUM(PROV_RISQ_1513_CUM) AS AJ_-- Risk Allowances ,SUM(CNTG_RES_CUM) AS AK_-- Reserve against Cost to Complete ,SUM(RSV_REV_RED_DISCOUNT_CUM) AS AL_-- RfRR-Discounts ,SUM(RSV_REV_RED_PENALTY_CUM) AS AM_-- RfRR-Penalties ,SUM(BILL_ADJ_EC_CUM) AS AP_-- Billing Adjustment (k) ,SUM(ECSE_BUD_CUM) ,SUM(ECSE_ACT_CUM) AS AS_-- Spent in MIS ,SUM(ICSE_BUD_CUM) AS AV_-- ICSE ,SUM(ICSE_ACT_CUM) AS AW_-- Spent in MIS ,SUM(SD_COST_BUD_CUM) AS AZ_-- Cost of Labour ,SUM(SD_COST_ACT_CUM) AS BA_-- Spent in MIS FROM ( SELECT 1 AS COMPANY,1 AS AREA_UNIT,1 AS SECTOR_UNIT,1 AS PROFIT_CENTER -- ( -- SELECT haou.NAME -- FROM HR_ALL_ORGANIZATION_UNITS haou -- WHERE ORGANIZATION_ID = VN_ORG_ID -- ) AS COMPANY -- ,( -- SELECT haou.NAME -- FROM HR_ALL_ORGANIZATION_UNITS haou -- WHERE ORGANIZATION_ID = T1.AU_ID -- ) AS AREA_UNIT -- ,( -- SELECT haou.NAME -- FROM HR_ALL_ORGANIZATION_UNITS haou -- WHERE ORGANIZATION_ID = T1.SU_ID -- ) AS SECTOR_UNIT -- ,( -- SELECT haou.NAME -- FROM HR_ALL_ORGANIZATION_UNITS haou -- 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 -- A ,NVL(PPCL.CLASS_CODE,'') AS DEAL_CODE -- B ,NVL(PCLC.DESCRIPTION,'') AS DEAL_CODE_DESCRIPTION -- C ,NVL(PRO.PROJECT_TYPE,'') AS PROJECT_TYPE -- D ,NVL(T2.PDCT_BRUT_BUDGET ,0) AS REV_GR_BUD_LP -- E ,NVL(T1.PDCT_BRUT_BUDGET ,0) AS REV_GR_BUD_CUM -- F ,NVL(T1.MT_AUTHOR_BUDGET ,0) AS AUTHORIZED_AMT_CUM -- G ,NVL(T2.PDCT_BRUT_REALISE ,0) AS REV_GR_ACT_LP -- H ,NVL(T1.PDCT_BRUT_REALISE ,0) AS REV_GR_ACT_CUM -- I ,NVL(T2.CRE_BUDGET ,0) AS ECSE_BUD_LP -- J ,NVL(T2.CRI_BUDGET ,0) AS ICSE_BUD_LP -- J ,NVL(T2.COUT_JS_BUDGET ,0) AS SD_COST_BUD_LP -- J ,NVL(T1.CRE_BUDGET ,0) AS ECSE_BUD_CUM -- K ,NVL(T1.CRI_BUDGET ,0) AS ICSE_BUD_CUM -- K ,NVL(T1.COUT_JS_BUDGET ,0) AS SD_COST_BUD_CUM -- k ,NVL(T1.CRE_REALISE ,0) AS ECSE_ACT_CUM -- L ,NVL(T1.CRI_REALISE ,0) AS ICSE_ACT_CUM -- L ,NVL(T1.COUT_JS_REALISE ,0) AS SD_COST_ACT_CUM -- L ,NVL(T2.GMD_BUDGET ,0) AS GMD_BUD_LP -- P ,NVL(T1.GMD_BUDGET ,0) AS GMD_BUD_CUM -- Q ,NVL(T2.CRE_REALISE ,0) AS ECSE_ACT_LP -- R ,NVL(T2.CRI_REALISE ,0) AS ICSE_ACT_LP -- R ,NVL(T2.COUT_JS_REALISE ,0) AS SD_COST_ACT_LP -- R ,NVL(T2.INDC_RESULT_BUDGET ,0) AS RES_IND_BUD_LP -- U ,NVL(T1.INDC_RESULT_BUDGET ,0) AS RES_IND_BUD_CUM -- V ,NVL(T1.INDC_RESULT_REALISE ,0) AS RES_IND_ACT_CUM -- AA ,NVL(T1.PROV_RISQ_1513_REALISE,0) AS PROV_RISQ_1513_CUM -- AJ ,NVL(T1.PROV_ALEAS_BUDGET ,0) AS CNTG_RES_CUM -- AK ,NVL(T1.PRCA_DISCOUNT_BUDGET ,0) AS RSV_REV_RED_DISCOUNT_CUM -- AL ,NVL(T1.PRCA_PENALTY_BUDGET ,0) AS RSV_REV_RED_PENALTY_CUM -- AM ,NVL(T1.FAE_FEA_EC_REALISE ,0) AS BILL_ADJ_EC_CUM -- AP 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 = VV_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 = VV_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 = VV_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 = VV_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(VV_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 = VN_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=VN_RESP_ID -- ) = 0 -- OR ( SELECT SUBSTR(RESPONSIBILITY_KEY,1,11) FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_ID = VN_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 = VN_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 = VN_RESP_ID -- ) != 0 --AND -- ( -- SELECT SUBSTR(RESPONSIBILITY_KEY,1,11) -- FROM FND_RESPONSIBILITY_VL -- WHERE RESPONSIBILITY_ID = VN_RESP_ID -- ) NOT LIKE '%CTL%' ) AND PRO.CARRYING_OUT_ORGANIZATION_ID = DPT.ORGANIZATION_ID --AND -- ( -- ( -- ( -- ( -- TO_CHAR(PRO.CLOSED_DATE,'YY')>=SUBSTR(VV_AUDIT_PRE_P,4) -- AND VV_WANT_IS_CLOSED = 'N' -- ) -- OR -- ( -- VV_WANT_IS_CLOSED = 'Y' -- ) -- ) -- AND PRO.CLOSED_DATE IS NOT NULL -- ) -- OR PRO.CLOSED_DATE IS NULL -- ) ) GROUP BY COMPANY ,AREA_UNIT ,SECTOR_UNIT ,PROFIT_CENTER ,DEAL_CODE ,DEAL_CODE_DESCRIPTION ,DEAL_CODE_TYPE ,DECODE(VV_WANT_BY_PROJECT_TYPE,'N',PROJECT_TYPE,'ALL') ORDER BY A_, B_, C_, D_ ; ---------------------------------------------------------------------- -- Cursors stuff ---------------------------------------------------------------------- VC_MAIN CUR_MAIN%ROWTYPE; VC_MAIN_P CUR_MAIN%ROWTYPE; ---------------------------------------------------------------------- -- Constants ---------------------------------------------------------------------- C_SEPARATOR CONSTANT VARCHAR2(1) :=';'; -- Better for further algorithm reading -- Beginning of data lines (-1) C_DATA_BEGIN CONSTANT NUMBER := 12 - 1; ---------------------------------------------------------------------- -- Variables ---------------------------------------------------------------------- -- excel table virtualization TYPE T_ROW is record ( A_ varchar2(200) , AA_ varchar2(200) , B_ varchar2(200) , AB_ varchar2(200) , C_ varchar2(200) , AC_ varchar2(200) , D_ varchar2(200) , AD_ varchar2(200) , E_ varchar2(200) , AE_ varchar2(200) , F_ varchar2(200) , AF_ varchar2(200) , G_ varchar2(200) , AG_ varchar2(200) , H_ varchar2(200) , AH_ varchar2(200) , I_ varchar2(200) , AI_ varchar2(200) , J_ varchar2(200) , AJ_ varchar2(200) , K_ varchar2(200) , AK_ varchar2(200) , M_ varchar2(200) , AM_ varchar2(200) , N_ varchar2(200) , AN_ varchar2(200) , O_ varchar2(200) , AO_ varchar2(200) , P_ varchar2(200) , AP_ varchar2(200) , Q_ varchar2(200) , AQ_ varchar2(200) , R_ varchar2(200) , AR_ varchar2(200) , S_ varchar2(200) , AS_ varchar2(200) , T_ varchar2(200) , AT_ varchar2(200) , U_ varchar2(200) , AU_ varchar2(200) , V_ varchar2(200) , AV_ varchar2(200) , W_ varchar2(200) , AW_ varchar2(200) , X_ varchar2(200) , AX_ varchar2(200) , Y_ varchar2(200) , AY_ varchar2(200) , Z_ varchar2(200) , AZ_ varchar2(200) , BA_ varchar2(200) , BB_ varchar2(200) , BC_ varchar2(200) ); TYPE T_ROWS is table of T_ROW index by binary_integer ; VT_ROWS T_ROWS; VT_TOTAL_AU T_ROW; VT_TOTAL_SU T_ROW; VT_TOTAL_PC T_ROW; VT_TOTAL_DMC T_ROW; -- input parameters VV_AUDIT_P VARCHAR2; -- Audit period VV_AUDIT_PRE_P VARCHAR2; -- Audit previous period VV_FILTER_DMC VARCHAR2; -- filter by DMC type VN_ORG_ID NUMBER; -- Starting Organisation VV_WANT_IS_CLOSED VARCHAR2; -- Projects closed Previous Year VV_WANT_BY_PROJECT_TYPE VARCHAR2; -- Report by Project type VN_RESP_ID NUMBER; -- User responsabilities -- program variables VN_DATA_POS NUMBER := C_DATA_BEGIN; -- current line to write in VN_TOTAL_NBLI NUMBER; -- total number of pages ---------------------------------------------------------------------- -- Functions ---------------------------------------------------------------------- --======================================================= -- Remettre un record a zero --======================================================= PROCEDURE F_REINIT_TABLE(vt_table T_ROW) IS BEGIN FOR i IN vt_table.first..vt_table.last LOOP vt_table(i)=NULL; END LOOP; END F_REINIT_TABLE; --======================================================= -- Enregistrement d'une ROW de curseur dans le tableau --======================================================= PROCEDURE F_RECORD_LINE(VN_ROW IN NUMBER,VC_MAIN_BUF IN CUR_MAIN%ROWTYPE) IS BEGIN VT_ROWS(VN_ROW).A_ := VC_MAIN_BUF.A_ ; VT_ROWS(VN_ROW).B_ := VC_MAIN_BUF.B_ ; VT_ROWS(VN_ROW).C_ := VC_MAIN_BUF.C_ ; VT_ROWS(VN_ROW).D_ := VC_MAIN_BUF.D_ ; VT_ROWS(VN_ROW).E_ := VC_MAIN_BUF.E_ ; VT_ROWS(VN_ROW).F_ := VC_MAIN_BUF.F_ ; VT_ROWS(VN_ROW).G_ := VC_MAIN_BUF.G_ ; VT_ROWS(VN_ROW).H_ := VC_MAIN_BUF.H_ ; VT_ROWS(VN_ROW).I_ := VC_MAIN_BUF.I_ ; VT_ROWS(VN_ROW).J_ := VC_MAIN_BUF.J_ ; VT_ROWS(VN_ROW).K_ := VC_MAIN_BUF.K_ ; VT_ROWS(VN_ROW).M_ := VC_MAIN_BUF.M_ ; VT_ROWS(VN_ROW).N_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).O_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).P_ := VC_MAIN_BUF.P_ ; VT_ROWS(VN_ROW).Q_ := VC_MAIN_BUF.Q_ ; VT_ROWS(VN_ROW).R_ := VC_MAIN_BUF.R_ ; VT_ROWS(VN_ROW).S_ := VC_MAIN_BUF.S_ ; VT_ROWS(VN_ROW).T_ := '' ; VT_ROWS(VN_ROW).U_ := VC_MAIN_BUF.U_ ; VT_ROWS(VN_ROW).V_ := VC_MAIN_BUF.V_ ; VT_ROWS(VN_ROW).W_ := VC_MAIN_BUF.W_ ; VT_ROWS(VN_ROW).X_ := VC_MAIN_BUF.X_ ; VT_ROWS(VN_ROW).Y_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).Z_ := 'RES_IND_BUD_CUM + AL12 – AF12';--Pot. Marg. M+P-R VT_ROWS(VN_ROW).AA_ := VC_MAIN_BUF.AA_ ; VT_ROWS(VN_ROW).AB_ := 'Y12 – First RES_IND_BUD_CUM'; VT_ROWS(VN_ROW).AC_ := VC_MAIN_BUF.AC_ ; VT_ROWS(VN_ROW).AD_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AE_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AF_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AG_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AH_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AI_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AJ_ := VC_MAIN_BUF.AJ_ ; VT_ROWS(VN_ROW).AK_ := VC_MAIN_BUF.AK_ ; VT_ROWS(VN_ROW).AM_ := VC_MAIN_BUF.AM_ ; VT_ROWS(VN_ROW).AN_ := '=SOMME( AI12:AL12)' ; -- Total (P) VT_ROWS(VN_ROW).AO_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AP_ := VC_MAIN_BUF.AP_ ; VT_ROWS(VN_ROW).AQ_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AR_ := VC_MAIN_BUF.AR_ ; VT_ROWS(VN_ROW).AS_ := VC_MAIN_BUF.AS_ ; VT_ROWS(VN_ROW).AT_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AU_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AV_ := VC_MAIN_BUF.AV_ ; VT_ROWS(VN_ROW).AW_ := VC_MAIN_BUF.AW_ ; VT_ROWS(VN_ROW).AX_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AY_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).AZ_ := VC_MAIN_BUF.AZ_ ; VT_ROWS(VN_ROW).BA_ := VC_MAIN_BUF.BA_ ; VT_ROWS(VN_ROW).BB_ := '' ; -- MANUAL FIELD VT_ROWS(VN_ROW).BC_ := '' ; -- MANUAL FIELD END F_RECORD_LINE; --======================================================= -- Increment amout in a table --======================================================= PROCEDURE F_ADD_ROW(VT_T IN T_ROW,VT_L IN T_ROW) IS BEGIN VT_T.A_ := VT_T.A_ + VT_L.A_; VT_T.B_ := VT_T.B_ + VT_L.B_; VT_T.C_ := VT_T.C_ + VT_L.C_; VT_T.D_ := VT_T.D_ + VT_L.D_; VT_T.E_ := VT_T.E_ + VT_L.E_; VT_T.F_ := VT_T.F_ + VT_L.F_; VT_T.G_ := VT_T.G_ + VT_L.G_; VT_T.H_ := VT_T.H_ + VT_L.H_; VT_T.I_ := VT_T.I_ + VT_L.I_; VT_T.J_ := VT_T.J_ + VT_L.J_; VT_T.K_ := VT_T.K_ + VT_L.K_; VT_T.M_ := VT_T.M_ + VT_L.M_; VT_T.N_ := VT_T.N_ + VT_L.N_; VT_T.O_ := VT_T.O_ + VT_L.O_; VT_T.P_ := VT_T.P_ + VT_L.P_; VT_T.Q_ := VT_T.Q_ + VT_L.Q_; VT_T.T_ := VT_T.T_ + VT_L.T_; VT_T.U_ := VT_T.U_ + VT_L.U_; VT_T.V_ := VT_T.V_ + VT_L.V_; VT_T.AD_ := VT_T.AD_ + VT_L.AD_; VT_T.AE_ := VT_T.AE_ + VT_L.AE_; VT_T.AF_ := VT_T.AE_ + VT_L.AF_; VT_T.AG_ := VT_T.AE_ + VT_L.AG_; VT_T.AH_ := VT_T.AE_ + VT_L.AH_; VT_T.AI_ := VT_T.AE_ + VT_L.AI_; VT_T.AJ_ := VT_T.AE_ + VT_L.AJ_; VT_T.AK_ := VT_T.AE_ + VT_L.AK_; VT_T.AL_ := VT_T.AE_ + VT_L.AL_; VT_T.AM_ := VT_T.AE_ + VT_L.AM_; VT_T.AN_ := VT_T.AE_ + VT_L.AN_; VT_T.AO_ := VT_T.AE_ + VT_L.AO_; VT_T.AP_ := VT_T.AE_ + VT_L.AP_; END F_INCREMENT_TABLE; --======================================================= -- Ecriture d'une ROW --======================================================= PROCEDURE F_PUTLINE(VN_ROW IN NUMBER) IS buf varchar2(5000); BEGIN FOR i in VN_ROW.first..VN_ROW.last LOOP buf:= buf||VT_ROWS(VN_ROW)(i)||C_SEPARATOR; END LOOP; DBMS_OUTPUT.PUT_LINE(buf); -- VT_ROWS(VN_ROW).A_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).B_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).C_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).D_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).E_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).F_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).G_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).H_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).I_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).J_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).K_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).M_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).N_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).O_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).P_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).Q_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).R_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).S_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).T_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).U_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).V_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).W_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).X_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).Y_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).Z_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AA_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AB_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AC_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AD_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AE_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AF_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AG_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AH_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AI_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AJ_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AK_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AM_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AN_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AO_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AP_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AQ_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AR_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AS_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AT_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AU_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AV_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AW_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AX_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AY_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).AZ_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).BA_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).BB_ || C_SEPARATOR || -- VT_ROWS(VN_ROW).BC_ || C_SEPARATOR ; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Fonction F_PUTLINE : OTHERS'); END F_PUTLINE; --======================================================= -- PROGRAMME PRINCIPAL --======================================================= BEGIN ---------------------------- PARAMETERS INITIALIZATION VV_AUDIT_P := &1; VV_AUDIT_PRE_P := &2; VV_FILTER_DMC := &3; VN_ORG_ID := &4; VV_WANT_IS_CLOSED := &5; VV_WANT_BY_PROJECT_TYPE := &6; VN_RESP_ID := &7; ---------------------------- HEADER VN_DATA_POS:=0; -- 1st line VN_DATA_POS:=VN_DATA_POS+1; -- 2nd line -> EMPTY VN_DATA_POS:=VN_DATA_POS+1; -- 3th line VN_DATA_POS:=VN_DATA_POS+1; VT_ROWS(VN_DATA_POS).E_ := 'Revenue Budget (EGR)'; VT_ROWS(VN_DATA_POS).H_ := 'Backlog'; VT_ROWS(VN_DATA_POS).J_ := 'Costs'; VT_ROWS(VN_DATA_POS).P_ := 'Budget GMD / EGR (%)'; VT_ROWS(VN_DATA_POS).R_ := 'Budget GMD on Backlog'; VT_ROWS(VN_DATA_POS).T_ := 'GMD'; VT_ROWS(VN_DATA_POS).U_ := 'Budget Operating Margin RI/EGR (%)'; VT_ROWS(VN_DATA_POS).AB_ := 'D(M+P-R) vs init'; VT_ROWS(VN_DATA_POS).AD_ := 'Risk Exposure'; VT_ROWS(VN_DATA_POS).AJ_ := 'Risk Allowances'; VT_ROWS(VN_DATA_POS).AO_ := 'Gap'||chr(10)||'(P-R)'||chr(10)||'(k)'; VT_ROWS(VN_DATA_POS).AP_ := 'Billing Adjustment (k)'; VT_ROWS(VN_DATA_POS).AQ_ := 'Comments and actions (completed, to perform)'; VT_ROWS(VN_DATA_POS).AR_ := 'ECSE'; VT_ROWS(VN_DATA_POS).AV_ := 'ICSE'; VT_ROWS(VN_DATA_POS).AZ_ := 'Cost of Labour'; -- 4th line VN_DATA_POS:=VN_DATA_POS+1; VT_ROWS(VN_DATA_POS).A_ := 'Contract Ref. (DMC)'; VT_ROWS(VN_DATA_POS).B_ := 'Client - Contract Description'; VT_ROWS(VN_DATA_POS).C_ := 'Deal Code Type'; VT_ROWS(VN_DATA_POS).D_ := 'Project Type'; VT_ROWS(VN_DATA_POS).F_ := 'Contract to Date'; VT_ROWS(VN_DATA_POS).G_ := 'Current Authorised Amount'; VT_ROWS(VN_DATA_POS).I_ := 'Contract to Date'; VT_ROWS(VN_DATA_POS).K_ := 'Budget Contract to Date'; VT_ROWS(VN_DATA_POS).L_ := 'Actual Contract to Date'; VT_ROWS(VN_DATA_POS).M_ := 'OPA Estimate to Complete'; VT_ROWS(VN_DATA_POS).N_ := 'Estimate to Complete'; VT_ROWS(VN_DATA_POS).Q_ := 'Contract to Date'; VT_ROWS(VN_DATA_POS).S_ := 'Contract to Date'; VT_ROWS(VN_DATA_POS).T_ := 'Annual Target'; VT_ROWS(VN_DATA_POS).V_ := 'Contract to Date'; VT_ROWS(VN_DATA_POS).X_ := 'Absolute Budget Contract to Date'; VT_ROWS(VN_DATA_POS).Y_ := 'RI Annual Target'; VT_ROWS(VN_DATA_POS).Z_ := 'Pot. Marg. M+P-R'; VT_ROWS(VN_DATA_POS).AA_ := 'Marg. Taken total MIS (K)'; VT_ROWS(VN_DATA_POS).AB_ := '(K)'; VT_ROWS(VN_DATA_POS).AC_ := '% / GR'; VT_ROWS(VN_DATA_POS).AD_ := 'Entity level exposure'; VT_ROWS(VN_DATA_POS).AE_ := 'Exposure on Cost to Complete'; VT_ROWS(VN_DATA_POS).AF_ := 'Exposure on Revenue'; VT_ROWS(VN_DATA_POS).AG_ := 'Total (R)'; VT_ROWS(VN_DATA_POS).AH_ := '% / GR'; VT_ROWS(VN_DATA_POS).AI_ := 'Trend'; VT_ROWS(VN_DATA_POS).AJ_ := 'Entity'; VT_ROWS(VN_DATA_POS).AK_ := 'Reserve against Cost to Complete'; VT_ROWS(VN_DATA_POS).AL_ := 'RfRR-Discounts'; VT_ROWS(VN_DATA_POS).AM_ := 'RfRR-Penalties'; VT_ROWS(VN_DATA_POS).AN_ := 'Total (P)'; VT_ROWS(VN_DATA_POS).AR_ := 'Budget in MIS'; VT_ROWS(VN_DATA_POS).AS_ := 'Spent in MIS'; VT_ROWS(VN_DATA_POS).AT_ := 'Est. to compl.'; VT_ROWS(VN_DATA_POS).AU_ := 'Gap'; VT_ROWS(VN_DATA_POS).AV_ := 'Budget in MIS'; VT_ROWS(VN_DATA_POS).AW_ := 'Spent in MIS'; VT_ROWS(VN_DATA_POS).AX_ := 'Est. to compl.'; VT_ROWS(VN_DATA_POS).AY_ := 'Gap'; VT_ROWS(VN_DATA_POS).AZ_ := 'Budget in MIS'; VT_ROWS(VN_DATA_POS).BA_ := 'Spent in MIS'; VT_ROWS(VN_DATA_POS).BB_ := 'Est. to compl.'; VT_ROWS(VN_DATA_POS).BC_ := 'Gap'; -- 5th line -> EMPTY VN_DATA_POS:=VN_DATA_POS+1; ---------------------------- MAIN LOOP OPEN CUR_MAIN( VV_AUDIT_P, VV_AUDIT_PRE_P, VV_FILTER_DMC, VN_ORG_ID, VV_WANT_IS_CLOSED, VV_WANT_BY_PROJECT_TYPE, VN_RESP_ID ); FETCH CUR_MAIN INTO VC_MAIN; WHILE CUR_MAIN%FOUND LOOP WHILE CUR_MAIN%FOUND -- Group on AREA_UNIT AND VC_MAIN.AREA_UNIT = VC_MAIN_P.AREA_UNIT LOOP F_REINIT_TABLE(VT_TOTAL_AU); WHILE CUR_MAIN%FOUND -- Group on SECTOR_UNIT AND VC_MAIN.SECTOR_UNIT = VC_MAIN_P.SECTOR_UNIT AND VC_MAIN.AREA_UNIT = VC_MAIN_P.AREA_UNIT LOOP F_REINIT_TABLE(VT_TOTAL_SU); WHILE CUR_MAIN%FOUND -- Group on PROFIT_CENTER AND VC_MAIN.PROFIT_CENTER = VC_MAIN_P.PROFIT_CENTER AND VC_MAIN.SECTOR_UNIT = VC_MAIN_P.SECTOR_UNIT AND VC_MAIN.AREA_UNIT = VC_MAIN_P.AREA_UNIT LOOP F_REINIT_TABLE(VT_TOTAL_PC); WHILE CUR_MAIN%FOUND -- Group on DEAL_CODE AND VC_MAIN.A_ = VC_MAIN_P.A_ -- DMC AND VC_MAIN.PROFIT_CENTER = VC_MAIN_P.PROFIT_CENTER AND VC_MAIN.SECTOR_UNIT = VC_MAIN_P.SECTOR_UNIT AND VC_MAIN.AREA_UNIT = VC_MAIN_P.AREA_UNIT LOOP F_REINIT_TABLE(VT_TOTAL_DMC); WHILE CUR_MAIN%FOUND -- Group on PROJECT_TYPE AND VC_MAIN.PROJECT_TYPE = VC_MAIN_P.PROJECT_TYPE AND VC_MAIN.A_ = VC_MAIN_P.A_ -- DMC AND VC_MAIN.PROFIT_CENTER = VC_MAIN_P.PROFIT_CENTER AND VC_MAIN.SECTOR_UNIT = VC_MAIN_P.SECTOR_UNIT AND VC_MAIN.AREA_UNIT = VC_MAIN_P.AREA_UNIT LOOP VC_MAIN_P:=VC_MAIN; VN_DATA_POS := VN_DATA_POS+1; F_RECORD_LINE(VN_DATA_POS,VC_MAIN); FETCH CUR_MAIN INTO VC_MAIN; END LOOP; -- END Group on DEAL_CODE F_ADD_ROWS(VT_TOTAL_DMC,T_ROWS(VN_DATA_POS)); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : PROJECT_TYPE'); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : TOTAL:'||T_ROWS(VN_DATA_POS)); END LOOP; -- END Group on DEAL_CODE F_ADD_ROWS(VT_TOTAL_PC,VT_TOTAL_DMC); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : DMC'); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : TOTAL:'||VT_TOTAL_DMC); END LOOP; -- END Group on PROFIT_CENTER F_ADD_ROWS(VT_TOTAL_SU,VT_TOTAL_PC); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : PC'); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : TOTAL:'||VT_TOTAL_PC); END LOOP; -- END Group on SECTOR_UNIT F_ADD_ROWS(VT_TOTAL_AU,VT_TOTAL_SU); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : SU'); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : TOTAL:'||VT_TOTAL_SU); END LOOP; -- END Group on AREA_UNIT DBMS_OUTPUT.PUT_LINE('Fonction MAIN : AU'); DBMS_OUTPUT.PUT_LINE('Fonction MAIN : TOTAL:'||VT_TOTAL_AU); END LOOP; CLOSE CUR_MAIN; ---------------------------- AFFICHAGE FOR i IN VT_ROWS.first..VT_ROWS.last LOOP F_PUTLINE(i); END LOOP; END; /