rendered paste bodySET 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;
/