Someone
public text v1 · immutableCURSOR 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
);