WITH DATES AS
(SELECT
TRUNC(VW_DIM_CALENDAR.CALENDAR_DATE) AS CURR_DATE,
CASE WHEN VW_DIM_CALENDAR.PERIOD = 1 THEN TO_NUMBER(VW_DIM_CALENDAR.FISCAL_YEAR)-1
ELSE TO_NUMBER(VW_DIM_CALENDAR.FISCAL_YEAR) END AS CURR_FY,
CASE WHEN VW_DIM_CALENDAR.PERIOD = 1 THEN 12
ELSE VW_DIM_CALENDAR.PERIOD-1 END AS CURR_PER,
(SELECT DISTINCT
MAX(VW_DIM_CALENDAR2.SUBPERIOD) OVER (PARTITION BY VW_DIM_CALENDAR2.FISCAL_YEAR||VW_DIM_CALENDAR2.PERIOD) AS SUB_P
FROM CPMTV.VW_DIM_CALENDAR VW_DIM_CALENDAR2
WHERE (TO_NUMBER(VW_DIM_CALENDAR2.FISCAL_YEAR) =
(SELECT
CASE WHEN VW_DIM_CALENDAR3.PERIOD = 1 THEN TO_NUMBER(VW_DIM_CALENDAR3.FISCAL_YEAR)-1
ELSE TO_NUMBER(VW_DIM_CALENDAR3.FISCAL_YEAR) END AS FY_VALUE
FROM CPMTV.VW_DIM_CALENDAR VW_DIM_CALENDAR3
WHERE VW_DIM_CALENDAR3.CALENDAR_DATE = TRUNC(SYSDATE)))
AND
(TO_NUMBER(VW_DIM_CALENDAR2.PERIOD) =
(SELECT
CASE WHEN VW_DIM_CALENDAR4.PERIOD = 1 THEN 12
ELSE VW_DIM_CALENDAR4.PERIOD-1 END AS PER_VALUE
FROM CPMTV.VW_DIM_CALENDAR VW_DIM_CALENDAR4
WHERE VW_DIM_CALENDAR4.CALENDAR_DATE = TRUNC(SYSDATE)))
AND VW_DIM_CALENDAR2.FISCAL_YEAR >= '2019') AS CURR_SUBP,
VW_DIM_CALENDAR.FISCAL_YEAR AS SUBSEQ_FY,
VW_DIM_CALENDAR.PERIOD AS SUBSEQ_PER,
1 AS SUBSEQ_SUBP
FROM CPMTV.VW_DIM_CALENDAR VW_DIM_CALENDAR
WHERE VW_DIM_CALENDAR.CALENDAR_DATE = TRUNC(SYSDATE)
AND VW_DIM_CALENDAR.FISCAL_YEAR >='2019'),


--GATHER DATA FROM INPUT TABLES FOR UAR

--AR PASS THRU
ARPASS AS
(SELECT VW_GENERAL_LEDGER_DETAIL.ACCOUNT_ID,
       VW_GENERAL_LEDGER_DETAIL.ACCOUNT_NAME,
       SUM (VW_GENERAL_LEDGER_DETAIL.USD_AMOUNT) AS AR_PASS_THRU_AMT,
       VW_PROJ.L2_PROJ_ID
FROM DATES, CPMTV.VW_GENERAL_LEDGER_DETAIL VW_GENERAL_LEDGER_DETAIL
     INNER JOIN CPMTV.VW_PROJ VW_PROJ
        ON (VW_GENERAL_LEDGER_DETAIL.PROJECT_ID = VW_PROJ.PROJ_ID)
WHERE     (VW_GENERAL_LEDGER_DETAIL.ACCOUNT_ID = '122-11-031-0000')
      AND (VW_GENERAL_LEDGER_DETAIL.COMPANY_ID = '1')
      AND ((VW_GENERAL_LEDGER_DETAIL.FISCAL_YEAR =TO_CHAR(DATES.CURR_FY) AND VW_GENERAL_LEDGER_DETAIL.PERIOD = DATES.CURR_PER AND VW_GENERAL_LEDGER_DETAIL.SUBPERIOD <=DATES.CURR_SUBP)
      OR (VW_GENERAL_LEDGER_DETAIL.FISCAL_YEAR = TO_CHAR(DATES.CURR_FY) AND VW_GENERAL_LEDGER_DETAIL.PERIOD < DATES.CURR_PER) OR (VW_GENERAL_LEDGER_DETAIL.FISCAL_YEAR < TO_CHAR(DATES.CURR_FY)))
GROUP BY VW_GENERAL_LEDGER_DETAIL.ACCOUNT_ID,
         VW_GENERAL_LEDGER_DETAIL.ACCOUNT_NAME,
         VW_PROJ.L2_PROJ_ID
HAVING (SUM (VW_GENERAL_LEDGER_DETAIL.USD_AMOUNT) != 0)
ORDER BY VW_PROJ.L2_PROJ_ID OFFSET 0 ROWS),

--BILL EDIT DETAIL
BEDIT AS (
SELECT 
ROW_NUMBER() OVER (PARTITION BY VW_PROJ.L2_PROJ_ID ORDER BY VW_PROJ.L2_PROJ_ID) as DUPCOUNT,
       SUM (VW_BILL_EDIT_DETL.OVER_CST_CEIL_AMT) AS SUM_OVER_CST_CEIL_AMT,
       SUM (VW_BILL_EDIT_DETL.OVER_FEE_CEIL_AMT) AS SUM_OVER_FEE_CEIL_AMT,
       SUM (VW_BILL_EDIT_DETL.OVER_TOT_CEIL_AMT) AS SUM_OVER_TOT_CEIL_AMT,
       NVL(SUM(VW_BILL_EDIT_INVC_HDR.INVC_AMT) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS PENDING_INVOICES,
       VW_BILL_EDIT_INVC_HDR.INVC_AMT,
       VW_BILL_EDIT_INVC_HDR.INVC_ID,
       VW_BILL_EDIT_INVC_HDR.POSTING_STATUS,
      MAX( VW_BILL_EDIT_INVC_HDR.INVC_ID) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID) AS L2_PROJ_MAX,
      CASE WHEN  MAX( VW_BILL_EDIT_INVC_HDR.INVC_ID) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID) = VW_BILL_EDIT_INVC_HDR.INVC_ID
      THEN 1 ELSE 0 END AS COMPUTED,
       VW_BILL_EDIT_DETL.INVC_PROJ_ID,
       VW_PROJ.L2_PROJ_ID
FROM ((COSTPOINT.VW_BILL_EDIT_DETL VW_BILL_EDIT_DETL INNER JOIN COSTPOINT.VW_BILL_EDIT_INVC_HDR
                                                                VW_BILL_EDIT_INVC_HDR ON (VW_BILL_EDIT_DETL.INVC_PROJ_ID =
                                                                                          VW_BILL_EDIT_INVC_HDR.PROJ_ID)) INNER JOIN COSTPOINT.VW_PROJ
                                                                                                                                     VW_PROJ ON (VW_BILL_EDIT_DETL.TRN_PROJ_ID =
                                                                                                                                                 VW_PROJ.PROJ_ID))
     INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG
        ON (VW_PROJ.ORG_ID = VW_DIM_ORG.ORG_ID)
WHERE (VW_BILL_EDIT_INVC_HDR.INVC_AMT != 0)
AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
GROUP BY VW_BILL_EDIT_DETL.INVC_PROJ_ID,
         VW_BILL_EDIT_INVC_HDR.INVC_AMT,
         VW_BILL_EDIT_INVC_HDR.INVC_ID,
         VW_BILL_EDIT_INVC_HDR.POSTING_STATUS,
         VW_PROJ.L2_PROJ_ID
         ),
  
  BEDIT2 AS
  (SELECT * FROM BEDIT
  WHERE DUPCOUNT = 1
  ORDER BY BEDIT.L2_PROJ_ID OFFSET 0 ROWS),
  
  --BILLING SUMMARY
 BILLSUM AS (
SELECT 
      ROW_NUMBER() OVER (PARTITION BY VW_PROJ.L2_PROJ_ID ORDER BY VW_PROJ.L2_PROJ_ID) as DUPCOUNT,
VW_BILLING_SUM.S_TRN_TYPE,
       SUM (VW_BILLING_SUM.BILLED_AMT) AS SUM_BILLED_AMT,
       SUM (VW_BILLING_SUM.BILLED_HRS) AS SUM_BILLED_HRS,
       CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'H' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END AS INV_LABOR,
       CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'H' THEN SUM(VW_BILLING_SUM.BILLED_HRS) ELSE 0 END AS INV_LABOR_HOURS,
       CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'C' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END AS INV_DIRECT_COST,
       CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'B' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END AS INV_INDIRECT_COST,
       CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'COM' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END AS INV_COM,
       CASE WHEN (VW_BILLING_SUM.S_TRN_TYPE = 'F' OR VW_BILLING_SUM.S_TRN_TYPE = 'FH') THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END AS INV_FEE,
       SUM (VW_BILLING_SUM.OVER_FEE_CEIL_AMT) AS INV_FEE_IN_EXCESS,
       SUM (VW_BILLING_SUM.OVER_CST_CEIL_AMT) AS INV_COST_IN_EXCESS,
       SUM (VW_BILLING_SUM.OVER_TOT_CEIL_AMT) AS INV_TOTAL_IN_EXCESS,
       CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'SCH' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END AS INV_FFP,
       NVL(SUM(CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'H' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_LABOR,
       NVL(SUM(CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'H' THEN SUM(VW_BILLING_SUM.BILLED_HRS) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_LABOR_HOURS,
       NVL(SUM(CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'C' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_DIRECT_COST,
       NVL(SUM(CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'B' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_INDIRECT_COST,
       NVL(SUM(CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'COM' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_COM,
       NVL(SUM(CASE WHEN (VW_BILLING_SUM.S_TRN_TYPE = 'F' OR VW_BILLING_SUM.S_TRN_TYPE =  'FH') THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_FEE,
       NVL(SUM(SUM (VW_BILLING_SUM.OVER_FEE_CEIL_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_FEE_IN_EXCESS,
       NVL(SUM(SUM (VW_BILLING_SUM.OVER_CST_CEIL_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_COST_IN_EXCESS,
       NVL(SUM(SUM (VW_BILLING_SUM.OVER_TOT_CEIL_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_TOTAL_IN_EXCESS,
       NVL(SUM(CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'SCH' THEN SUM(VW_BILLING_SUM.BILLED_AMT) ELSE 0 END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS XINV_FFP,
       VW_PROJ.L2_PROJ_ID,
       VW_PROJ.L1_PROJ_ID AS CRN
FROM (COSTPOINT.VW_BILLING_SUM VW_BILLING_SUM INNER JOIN COSTPOINT.VW_PROJ
                                                         VW_PROJ ON (VW_BILLING_SUM.TRN_PROJ_ID =
                                                                     VW_PROJ.PROJ_ID))
     INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG
        ON (VW_PROJ.ORG_ID = VW_DIM_ORG.ORG_ID)
WHERE (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
GROUP BY 
VW_BILLING_SUM.S_TRN_TYPE, 
VW_PROJ.L1_PROJ_ID,
CASE WHEN VW_BILLING_SUM.S_TRN_TYPE = 'H' THEN VW_BILLING_SUM.BILLED_AMT ELSE 0 END,
VW_PROJ.L2_PROJ_ID),

BILLSUM2 AS
(SELECT * FROM BILLSUM
WHERE DUPCOUNT = 1
ORDER BY BILLSUM.L2_PROJ_ID OFFSET 0 ROWS),

--OPEN BILL DETAIL
OBDETL AS 
(SELECT 
       ROW_NUMBER() OVER (PARTITION BY VW_PROJ.L2_PROJ_ID ORDER BY VW_PROJ.L2_PROJ_ID) as DUPCOUNT,
       CASE WHEN ( SUM (VW_OPEN_BILLING_DETL.HOLD_HRS)*NVL(VW_OPEN_BILLING_DETL.BILL_RT_AMT,0))=0 THEN SUM (VW_OPEN_BILLING_DETL.HOLD_AMT)
       ELSE (VW_OPEN_BILLING_DETL.BILL_RT_AMT*SUM (VW_OPEN_BILLING_DETL.HOLD_HRS)) END AS AOH_TM_BURDENED_CP_UNBURDENED,
       NVL(SUM(CASE WHEN (SUM (VW_OPEN_BILLING_DETL.HOLD_HRS)*NVL(VW_OPEN_BILLING_DETL.BILL_RT_AMT,0))=0 THEN SUM (VW_OPEN_BILLING_DETL.HOLD_AMT)
       ELSE (VW_OPEN_BILLING_DETL.BILL_RT_AMT*SUM (VW_OPEN_BILLING_DETL.HOLD_HRS)) END) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0)
       AS SUM_AOH_TM_BURDENED_CP_UNBURDENED,
       VW_OPEN_BILLING_DETL.TRN_PROJ_ID,
       VW_OPEN_BILLING_DETL.FY_CD,
       VW_OPEN_BILLING_DETL.PD_NO,
       VW_OPEN_BILLING_DETL.SUB_PD_NO,
       VW_OPEN_BILLING_DETL.ACCT_ID,
       SUM (VW_OPEN_BILLING_DETL.HOLD_AMT) AS SUM_HOLD_AMT,
       SUM (VW_OPEN_BILLING_DETL.HOLD_HRS) AS SUM_HOLD_HRS,
       VW_OPEN_BILLING_DETL.BILL_LAB_CAT_CD,
       VW_OPEN_BILLING_DETL.BILL_RT_AMT,
       VW_PROJ.L2_PROJ_ID,
       VW_PROJ.L1_PROJ_ID AS CRN,
       SYSDATE AS SYSDATE1
FROM (COSTPOINT.VW_OPEN_BILLING_DETL VW_OPEN_BILLING_DETL INNER JOIN COSTPOINT.VW_PROJ
                                                                     VW_PROJ ON (VW_OPEN_BILLING_DETL.TRN_PROJ_ID =
                                                                                 VW_PROJ.PROJ_ID))
     INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG
        ON (VW_PROJ.ORG_ID = VW_DIM_ORG.ORG_ID)
WHERE (VW_OPEN_BILLING_DETL.HOLD_AMT != 0)
AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
GROUP BY VW_OPEN_BILLING_DETL.ACCT_ID,
         VW_OPEN_BILLING_DETL.BILL_LAB_CAT_CD,
         VW_OPEN_BILLING_DETL.BILL_RT_AMT,
         VW_OPEN_BILLING_DETL.FY_CD,
         VW_OPEN_BILLING_DETL.PD_NO,
         VW_OPEN_BILLING_DETL.SUB_PD_NO,
         VW_OPEN_BILLING_DETL.TRN_PROJ_ID,
         VW_PROJ.L1_PROJ_ID,
         VW_PROJ.L2_PROJ_ID
         ),
         
 OBDETL2 AS         
 (SELECT * FROM OBDETL
 WHERE DUPCOUNT = 1
 ORDER BY OBDETL.L2_PROJ_ID OFFSET 0 ROWS),
 
 --PROJ BILL HISTORY
PBHS AS 
(SELECT 
       ROW_NUMBER() OVER (PARTITION BY VW_PROJ.L2_PROJ_ID ORDER BY VW_PROJ.L2_PROJ_ID) as DUPCOUNT,
       VW_PROJ.L2_PROJ_ID,
       VW_PROJ_BILL_INFO.BILL_GRP_CD AS BUG,
       SUM (VW_PROJ_BILL_HS.BILLED_AMT) AS SUM_BILLED_AMT,
       SUM (VW_PROJ_BILL_HS.RTNGE_AMT) AS SUM_RTNGE_AMT,
       SUM (VW_PROJ_BILL_HS.DEL_AMT) AS SUM_DEL_AMT,
       NVL(SUM(SUM (VW_PROJ_BILL_HS.BILLED_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) as XITD_BILLED,
       NVL(SUM(SUM (VW_PROJ_BILL_HS.RTNGE_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) as XRTNGE_AMT,
       VW_PROJ.L1_PROJ_NAME,
       VW_DIM_ORG.GROUP_NO,
       VW_DIM_ORG.GROUP_NAME,
       VW_DIM_ORG.BU_NO,
       VW_DIM_ORG.BU_NAME,
       VW_DIM_ORG.OPERATION_NO,
       VW_DIM_ORG.OPERATION_NAME,
       NVL(VW_DIM_EMPLOYEE.EMPLOYEE_NAME,' ') AS PROJECT_CONTROLLER,
       VW_DIM_PROJECT.CONTRACT_REC_NO,
       VW_PROJ.L1_PROJ_SEG_ID,
       VW_PROJ.L2_PROJ_SEG_ID
FROM DATES, COSTPOINT.VW_PROJ_BILL_HS VW_PROJ_BILL_HS 
INNER JOIN COSTPOINT.VW_PROJ
   VW_PROJ ON (VW_PROJ_BILL_HS.PROJ_ID =
    VW_PROJ.PROJ_ID) INNER JOIN CPMTV.VW_DIM_PROJECT
    VW_DIM_PROJECT ON (VW_PROJ_BILL_HS.PROJ_ID =
     VW_DIM_PROJECT.PROJECT_ID) LEFT OUTER JOIN CPMTV.VW_DIM_EMPLOYEE
    VW_DIM_EMPLOYEE ON (VW_DIM_PROJECT.PROJECT_CONTROLLER =
   VW_DIM_EMPLOYEE.EMPLOYEE_ID)
     INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG
        ON (VW_PROJ.ORG_ID = VW_DIM_ORG.ORG_ID)
     LEFT OUTER JOIN COSTPOINT.VW_PROJ_BILL_INFO VW_PROJ_BILL_INFO
        ON (VW_PROJ.L2_PROJ_ID = VW_PROJ_BILL_INFO.INVC_PROJ_ID)
WHERE (VW_PROJ.COMPANY_ID = '1')
      AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
      AND ((VW_PROJ_BILL_HS.FY_CD = TO_CHAR(DATES.CURR_FY) AND VW_PROJ_BILL_HS.PD_NO = DATES.CURR_PER AND VW_PROJ_BILL_HS.SUB_PD_NO <=DATES.CURR_SUBP)
      OR (VW_PROJ_BILL_HS.FY_CD = TO_CHAR(DATES.CURR_FY) AND VW_PROJ_BILL_HS.PD_NO < DATES.CURR_PER) OR (VW_PROJ_BILL_HS.FY_CD < TO_CHAR(DATES.CURR_FY)))
GROUP BY VW_DIM_ORG.BU_NAME,
         VW_DIM_ORG.BU_NO,
         VW_DIM_ORG.GROUP_NAME,
         VW_DIM_ORG.GROUP_NO,
         VW_DIM_ORG.OPERATION_NAME,
         VW_DIM_ORG.OPERATION_NO,
         VW_DIM_PROJECT.CONTRACT_REC_NO,
         VW_DIM_EMPLOYEE.EMPLOYEE_NAME,
         VW_PROJ_BILL_INFO.BILL_GRP_CD,
         VW_PROJ.L1_PROJ_NAME,
         VW_PROJ.L1_PROJ_SEG_ID,
         VW_PROJ.L2_PROJ_ID,
         VW_PROJ.L2_PROJ_SEG_ID),
         
 PBHS2 AS        
 (SELECT * FROM PBHS
 WHERE DUPCOUNT = 1
 ORDER BY PBHS.L2_PROJ_ID OFFSET 0 ROWS),
 
 --PROJ BILL HS RETAINAGE
PRJBILLHS AS
(SELECT 
ROW_NUMBER() OVER (PARTITION BY VW_PROJ.L2_PROJ_ID ORDER BY VW_PROJ.L2_PROJ_ID) as DUPCOUNT,
VW_PROJ.L2_PROJ_ID,
       SUM (VW_PROJ_BILL_HS.RTNGE_AMT) AS SUM_RTNGE_AMT,
       NVL(SUM(SUM (VW_PROJ_BILL_HS.RTNGE_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) as XRTNGE_AMT,
       VW_PROJ.L1_PROJ_ID AS CONTRACT_REC_NO
FROM DATES, COSTPOINT.VW_PROJ VW_PROJ 
INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG ON (VW_PROJ.ORG_ID = VW_DIM_ORG.ORG_ID)
     INNER JOIN COSTPOINT.VW_PROJ_BILL_HS VW_PROJ_BILL_HS
        ON (VW_PROJ_BILL_HS.PROJ_ID = VW_PROJ.PROJ_ID)
WHERE     (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
          AND ((VW_PROJ_BILL_HS.FY_CD = TO_CHAR(DATES.CURR_FY) AND VW_PROJ_BILL_HS.PD_NO = DATES.CURR_PER AND VW_PROJ_BILL_HS.SUB_PD_NO <= DATES.CURR_SUBP)
      OR (VW_PROJ_BILL_HS.FY_CD = TO_CHAR(DATES.CURR_FY) AND VW_PROJ_BILL_HS.PD_NO < DATES.CURR_PER) OR (VW_PROJ_BILL_HS.FY_CD < TO_CHAR(DATES.CURR_FY)))
GROUP BY VW_PROJ.L1_PROJ_ID, VW_PROJ.L2_PROJ_ID
),

PRJBILLHS2 AS
(SELECT * FROM PRJBILLHS
WHERE DUPCOUNT = 1 AND PRJBILLHS.SUM_RTNGE_AMT != 0
ORDER BY PRJBILLHS.L2_PROJ_ID OFFSET 0 ROWS),

--PROJ BILL SUB
PRJBILLSUB AS
(SELECT 
NVL(SUM(SUM (VW_PROJ_BILL_HS.BILLED_AMT)) OVER (PARTITION BY VW_PROJ.L2_PROJ_ID),0) AS BILLED_AMT,
SUM (VW_PROJ_BILL_HS.BILLED_AMT) AS SUM_BILLED_AMT,
       VW_PROJ_BILL_HS.PROJ_ID,
       VW_PROJ.L1_PROJ_SEG_ID AS CONTRACT_REC_NO,
       VW_PROJ.L2_PROJ_ID,
          VW_PROJ_BILL_HS.FY_CD
       || '.'
       || VW_PROJ_BILL_HS.PD_NO
       || '.'
       || VW_PROJ_BILL_HS.SUB_PD_NO AS FY_CAT
FROM DATES, COSTPOINT.VW_PROJ VW_PROJ INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG ON (VW_PROJ.ORG_ID =
                                                                           VW_DIM_ORG.ORG_ID)
     INNER JOIN COSTPOINT.VW_PROJ_BILL_HS VW_PROJ_BILL_HS
        ON (VW_PROJ_BILL_HS.PROJ_ID = VW_PROJ.PROJ_ID)
WHERE  (VW_PROJ_BILL_HS.FY_CD = TO_CHAR(DATES.SUBSEQ_FY))
      AND (VW_PROJ_BILL_HS.PD_NO = DATES.SUBSEQ_PER)
      AND (VW_PROJ_BILL_HS.SUB_PD_NO = DATES.SUBSEQ_SUBP)
      AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
GROUP BY VW_PROJ.L1_PROJ_SEG_ID,
         VW_PROJ.L2_PROJ_ID,
         VW_PROJ_BILL_HS.PROJ_ID,
            VW_PROJ_BILL_HS.FY_CD
         || '.'
         || VW_PROJ_BILL_HS.PD_NO
         || '.'
         || VW_PROJ_BILL_HS.SUB_PD_NO
ORDER BY VW_PROJ.L2_PROJ_ID OFFSET 0 ROWS),
         
--RPT REV SUM ITD
RPTREVSUM AS
(SELECT SUM (VW_RPT_REV_SUM.TGT_ITD_REV) AS SUM_TGT_ITD_REV,
       VW_RPT_REV_SUM.FY_CD,
       VW_RPT_REV_SUM.PD_NO,
       VW_PROJECT.LEVEL_1_PROJECT_ID,
       VW_PROJECT.L2_PROJ_SEG_ID,
       VW_PROJECT.L1_PROJ_SEG_ID||'.'||VW_PROJECT.L2_PROJ_SEG_ID AS L2_PROJ_ID,
       VW_RPT_REV_SUM.SUB_PD_NO,
       SUM (VW_RPT_REV_SUM.TGT_ITD_REV) AS ITD_Rev
FROM DATES, CPMTV.VW_DIM_PROJECT VW_DIM_PROJECT 
INNER JOIN CPMTV.VW_DIM_PROJECT VW_DIM_PROJECT_1 
 ON (VW_DIM_PROJECT.BILLING_PROJECT_ID = VW_DIM_PROJECT_1.PROJECT_ID) 
 INNER JOIN COSTPOINT.VW_RPT_REV_SUM VW_RPT_REV_SUM 
 ON (VW_DIM_PROJECT.PROJECT_ID = VW_RPT_REV_SUM.PROJ_ID) 
 INNER JOIN COSTPOINT.VW_PROJECT VW_PROJECT 
 ON (VW_RPT_REV_SUM.PROJ_ID = VW_PROJECT.PROJ_ID)
     INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG
        ON (VW_DIM_PROJECT_1.ORG_ID = VW_DIM_ORG.ORG_ID)
WHERE     (VW_RPT_REV_SUM.FY_CD =TO_CHAR(DATES.CURR_FY))
      AND (VW_RPT_REV_SUM.PD_NO = DATES.CURR_PER)
      AND (VW_RPT_REV_SUM.SUB_PD_NO = DATES.CURR_SUBP)
      AND  (VW_DIM_PROJECT.COMPANY_ID = '1')
            AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
            GROUP BY VW_PROJECT.L2_PROJ_SEG_ID,
         VW_PROJECT.LEVEL_1_PROJECT_ID,
         VW_RPT_REV_SUM.FY_CD,
         VW_RPT_REV_SUM.PD_NO,
         VW_RPT_REV_SUM.SUB_PD_NO,
         VW_PROJECT.LEVEL_2_PROJECT_ID
HAVING (SUM (VW_RPT_REV_SUM.TGT_ITD_REV) != 0)
ORDER BY VW_PROJECT.L1_PROJ_SEG_ID||'.'||VW_PROJECT.L2_PROJ_SEG_ID OFFSET 0 ROWS),

--STD BILL DETAIL
STDBILL AS
(SELECT VW_PROJ.L1_PROJ_SEG_ID AS CRN,
       VW_PROJ.L2_PROJ_ID,
       SUM (VW_STANDARD_BILL_DETAIL.BILL_AMT) AS SUM_BILL_AMT,
       SUM (VW_STANDARD_BILL_DETAIL.BILL_AMT) AS INVOICE_RV
FROM (COSTPOINT.VW_STANDARD_BILL_DETAIL VW_STANDARD_BILL_DETAIL 
INNER JOIN COSTPOINT.VW_PROJ VW_PROJ 
ON (VW_STANDARD_BILL_DETAIL.PROJ_ID = VW_PROJ.PROJ_ID))
     INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG
        ON (VW_PROJ.ORG_ID = VW_DIM_ORG.ORG_ID)
WHERE     (VW_STANDARD_BILL_DETAIL.BILL_AMT != 0)
AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
      AND (VW_STANDARD_BILL_DETAIL.BILL_FM_LN_LBL <> 'Fee')
      AND (VW_STANDARD_BILL_DETAIL.ACCT_ID LIKE '%-82-%')
      GROUP BY VW_PROJ.L1_PROJ_SEG_ID, VW_PROJ.L2_PROJ_ID
HAVING (SUM (VW_STANDARD_BILL_DETAIL.BILL_AMT) != 0)
ORDER BY VW_PROJ.L2_PROJ_ID OFFSET 0 ROWS),

--STD BILL LOOKUP
STDBLKP AS
(SELECT VW_PROJ.L1_PROJ_ID,
       VW_PROJ.L2_PROJ_ID,
       MAX (VW_STANDARD_BILL_LOOKUPS.INVC_ID) AS MAX_INVC_ID,
       'Y' AS PROJ_CONV
FROM COSTPOINT.VW_STANDARD_BILL_LOOKUPS VW_STANDARD_BILL_LOOKUPS
     INNER JOIN COSTPOINT.VW_PROJ VW_PROJ
        ON (VW_STANDARD_BILL_LOOKUPS.PROJ_ID = VW_PROJ.PROJ_ID)
GROUP BY VW_PROJ.L1_PROJ_ID, VW_PROJ.L2_PROJ_ID, 'Y'
ORDER BY VW_PROJ.L2_PROJ_ID OFFSET 0 ROWS),

--RPT REV WKS
REVWKS1 AS
(SELECT VW_DIM_PROJECT.LEVEL_1_PROJECT_SEGMENT_ID AS CRN,
       SUM (VW_RPT_REVENUE_WKS.ITD_TGT_AL_AMT) AS SUM_ITD_TGT_AL_AMT,
          VW_DIM_PROJECT.LEVEL_1_PROJECT_SEGMENT_ID
       || '.'
       || VW_DIM_PROJECT.LEVEL_2_PROJECT_SEGMENT_ID AS L2_PROJ_ID,
       VW_DIM_PROJECT.PROJECT_TYPE_DESCRIPTION AS PROJ_TYPE_DC,
       VW_RPT_REVENUE_WKS.SUB_TOT_TYPE_NO,
       SUM (VW_RPT_REVENUE_WKS.ITD_FEE_ON_HRS_AMT) AS SUM_ITD_FEE_ON_HRS_AMT,
       SUM (VW_RPT_REVENUE_WKS.ITD_OT_FEE_TGT_AMT) AS SUM_ITD_OT_FEE_TGT_AMT,
       SUM (VW_RPT_REVENUE_WKS.ITD_TGT_FEE_AMT) AS SUM_ITD_TGT_FEE_AMT,
       SUM (VW_RPT_REVENUE_WKS.ITD_OV_TGT_CST_AMT) AS SUM_ITD_OV_TGT_CST_AMT,
       SUM (VW_RPT_REVENUE_WKS.ITD_OV_TGT_FEE_AMT) AS SUM_ITD_OV_TGT_FEE_AMT,
       SUM (VW_RPT_REVENUE_WKS.ITD_OV_TGT_TOT_AMT) AS SUM_ITD_OV_TGT_TOT_AMT,
       SUM (VW_RPT_REVENUE_WKS.ITD_REV_ADJ_AMT) AS SUM_ITD_REV_ADJ_AMT,
       VW_RPT_REVENUE_WKS.ACCT_ID,
       SUM (VW_RPT_REVENUE_WKS.ITD_ALLOW_HRS) AS SUM_ITD_ALLOW_HRS,
       VW_RPT_REVENUE_WKS.REV_RT_AMT,
       VW_DIM_PROJECT.LEVEL_2_PROJECT_SEGMENT_ID,
       VW_PROJ_BILL_INFO.S_BILL_FORMULA_CD,
       SYSDATE AS SYSTEMDATE
FROM DATES, COSTPOINT.VW_RPT_REVENUE_WKS VW_RPT_REVENUE_WKS 
INNER JOIN CPMTV.VW_DIM_PROJECT VW_DIM_PROJECT 
ON (VW_RPT_REVENUE_WKS.PROJ_ID = VW_DIM_PROJECT.PROJECT_ID) 
INNER JOIN CPMTV.VW_DIM_ORG VW_DIM_ORG 
ON (VW_DIM_PROJECT.ORG_ID = VW_DIM_ORG.ORG_ID)
     LEFT OUTER JOIN COSTPOINT.VW_PROJ_BILL_INFO VW_PROJ_BILL_INFO
        ON (VW_DIM_PROJECT.BILLING_PROJECT_ID =
            VW_PROJ_BILL_INFO.INVC_PROJ_ID)
WHERE     (VW_RPT_REVENUE_WKS.SUB_TOT_TYPE_NO IN (1,
                                                  2,
                                                  3,
                                                  4))
      AND (VW_RPT_REVENUE_WKS.FY_CD = TO_CHAR(DATES.CURR_FY))
      AND (VW_RPT_REVENUE_WKS.PD_NO = DATES.CURR_PER)
      AND (VW_RPT_REVENUE_WKS.SUB_PD_NO = DATES.CURR_SUBP)
      AND (VW_DIM_ORG.GROUP_NO IN (6,7,8,9,10))
     GROUP BY VW_DIM_PROJECT.LEVEL_1_PROJECT_SEGMENT_ID,
         VW_DIM_PROJECT.LEVEL_2_PROJECT_SEGMENT_ID,
         VW_DIM_PROJECT.PROJECT_TYPE_DESCRIPTION,
         VW_PROJ_BILL_INFO.S_BILL_FORMULA_CD,
         VW_RPT_REVENUE_WKS.ACCT_ID,
         VW_RPT_REVENUE_WKS.REV_RT_AMT,
         VW_RPT_REVENUE_WKS.SUB_TOT_TYPE_NO,
            VW_DIM_PROJECT.LEVEL_1_PROJECT_SEGMENT_ID
         || '.'
         || VW_DIM_PROJECT.LEVEL_2_PROJECT_SEGMENT_ID,
         SYSDATE
     ORDER BY  VW_DIM_PROJECT.LEVEL_1_PROJECT_SEGMENT_ID
       || '.'
       || VW_DIM_PROJECT.LEVEL_2_PROJECT_SEGMENT_ID OFFSET 0 ROWS),
         
REVWKS2 AS
(SELECT
REVWKS1.L2_PROJ_ID,
REVWKS1.ACCT_ID,
CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO=2 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END AS RW_DIRECT_COSTS,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 3 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END AS RW_INDIRECT_COSTS,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 4 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END AS RW_COM,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN (REVWKS1.SUM_ITD_FEE_ON_HRS_AMT + REVWKS1.SUM_ITD_OT_FEE_TGT_AMT + REVWKS1.SUM_ITD_TGT_FEE_AMT)
       ELSE 0 END AS RW_FEE,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') = 'DIR-FFP' OR REVWKS1.LEVEL_2_PROJECT_SEGMENT_ID = '88'
       THEN (REVWKS1.SUM_ITD_TGT_AL_AMT + REVWKS1.SUM_ITD_FEE_ON_HRS_AMT + REVWKS1.SUM_ITD_TGT_FEE_AMT + REVWKS1.SUM_ITD_REV_ADJ_AMT + REVWKS1.SUM_ITD_OT_FEE_TGT_AMT
       - REVWKS1.SUM_ITD_OV_TGT_CST_AMT - REVWKS1.SUM_ITD_OV_TGT_FEE_AMT - REVWKS1.SUM_ITD_OV_TGT_TOT_AMT) ELSE 0.0 END AS RW_FFP,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND (SUBSTR(REVWKS1.ACCT_ID,1,6)='552-82' OR SUBSTR(REVWKS1.ACCT_ID,1,6)='592-82' )
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END AS RW_RV,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_FEE_AMT ELSE 0 END AS RW_FEE_IN_EXCESS,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_TOT_AMT ELSE 0 END AS RW_TOTAL_IN_EXCESS,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_CST_AMT ELSE 0 END AS RW_COST_IN_EXCESS,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_REV_ADJ_AMT ELSE 0 END AS REVENUE_ADJUSTMENT,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 2 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_DIRECT_COSTS,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 3 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_INDIRECT_COSTS,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 4 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_COM,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN (REVWKS1.SUM_ITD_FEE_ON_HRS_AMT + REVWKS1.SUM_ITD_OT_FEE_TGT_AMT + REVWKS1.SUM_ITD_TGT_FEE_AMT)
       ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_FEE,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END AS ODC_ACCRUALS,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN (REVWKS1.SUM_ITD_ALLOW_HRS*REVWKS1.REV_RT_AMT) ELSE 0 END AS RW_LABOR,
        CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_ALLOW_HRS ELSE 0 END AS RW_LABOR_HOURS,
        CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_FEE_AMT ELSE 0 END AS FEE_ON_ACCRUALS,
       SUBSTR(REVWKS1.ACCT_ID,4,4) AS COMPUTED,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN (REVWKS1.SUM_ITD_ALLOW_HRS * REVWKS1.REV_RT_AMT) ELSE 0 END AS LABOR_ACCRUALS,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') = 'UNIT1' AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN (REVWKS1.SUM_ITD_ALLOW_HRS * REVWKS1.REV_RT_AMT) ELSE 0 END AS LABOR_ACCRUALS_FFP,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') = 'UNIT1' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END AS RW_ODC_ACCRUALS_FFP,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC = 'DIR-FFP' OR REVWKS1.LEVEL_2_PROJECT_SEGMENT_ID = '88'
       THEN (REVWKS1.SUM_ITD_TGT_AL_AMT + REVWKS1.SUM_ITD_FEE_ON_HRS_AMT + REVWKS1.SUM_ITD_TGT_FEE_AMT + REVWKS1.SUM_ITD_REV_ADJ_AMT + REVWKS1.SUM_ITD_OT_FEE_TGT_AMT
       - REVWKS1.SUM_ITD_OV_TGT_CST_AMT - REVWKS1.SUM_ITD_OV_TGT_FEE_AMT - REVWKS1.SUM_ITD_OV_TGT_TOT_AMT) ELSE 0.0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0.0) AS xRW_FFP,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' AND (SUBSTR(REVWKS1.ACCT_ID,1,6)='552-82' OR SUBSTR(REVWKS1.ACCT_ID,1,6)='592-82' )
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_RV,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_FEE_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_FEE_IN_EXCESS,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_TOT_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_TOTAL_IN_EXCESS,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_CST_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_COST_IN_EXCESS,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN REVWKS1.SUM_ITD_REV_ADJ_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xREVENUE_ADJUSTMENT,
       NVL(SUM(CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xODC_ACCRUALS,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN (REVWKS1.SUM_ITD_ALLOW_HRS*REVWKS1.REV_RT_AMT) ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_LABOR,
       NVL(SUM(CASE WHEN REVWKS1.PROJ_TYPE_DC != 'DIR-FFP' THEN REVWKS1.SUM_ITD_ALLOW_HRS ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_LABOR_HOURS,
       NVL(SUM(CASE WHEN REVWKS1.S_BILL_FORMULA_CD != 'UNIT' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_FEE_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xFEE_ON_ACCRUALS,
       NVL(SUM(CASE WHEN REVWKS1.S_BILL_FORMULA_CD != 'UNIT' AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN (REVWKS1.SUM_ITD_ALLOW_HRS * REVWKS1.REV_RT_AMT) ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xLABOR_ACCRUALS,
       NVL(SUM(CASE WHEN REVWKS1.S_BILL_FORMULA_CD = 'UNIT1' AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN (REVWKS1.SUM_ITD_ALLOW_HRS * REVWKS1.REV_RT_AMT) ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xLABOR_ACCRUALS_FFP,
       NVL(SUM(CASE WHEN REVWKS1.S_BILL_FORMULA_CD = 'UNIT1' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END) OVER (PARTITION BY REVWKS1.L2_PROJ_ID),0) AS xRW_ODC_ACCRUALS_FFP
FROM REVWKS1
GROUP BY
REVWKS1.L2_PROJ_ID,
REVWKS1.ACCT_ID,
CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO=2 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 3 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND REVWKS1.SUB_TOT_TYPE_NO = 4 THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0  END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN (REVWKS1.SUM_ITD_FEE_ON_HRS_AMT + REVWKS1.SUM_ITD_OT_FEE_TGT_AMT + REVWKS1.SUM_ITD_TGT_FEE_AMT)
       ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') = 'DIR-FFP' OR REVWKS1.LEVEL_2_PROJECT_SEGMENT_ID = '88'
       THEN (REVWKS1.SUM_ITD_TGT_AL_AMT + REVWKS1.SUM_ITD_FEE_ON_HRS_AMT + REVWKS1.SUM_ITD_TGT_FEE_AMT + REVWKS1.SUM_ITD_REV_ADJ_AMT + REVWKS1.SUM_ITD_OT_FEE_TGT_AMT
       - REVWKS1.SUM_ITD_OV_TGT_CST_AMT - REVWKS1.SUM_ITD_OV_TGT_FEE_AMT - REVWKS1.SUM_ITD_OV_TGT_TOT_AMT) ELSE 0.0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' AND (SUBSTR(REVWKS1.ACCT_ID,1,6)='552-82' OR SUBSTR(REVWKS1.ACCT_ID,1,6)='592-82' )
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_FEE_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_TOT_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_OV_TGT_CST_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_REV_ADJ_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END,
       CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN (REVWKS1.SUM_ITD_ALLOW_HRS*REVWKS1.REV_RT_AMT) ELSE 0 END,
        CASE WHEN NVL(REVWKS1.PROJ_TYPE_DC,'X') != 'DIR-FFP' THEN REVWKS1.SUM_ITD_ALLOW_HRS ELSE 0 END,
        CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_FEE_AMT ELSE 0 END,
       SUBSTR(REVWKS1.ACCT_ID,4,4),
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') != 'UNIT' AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN (REVWKS1.SUM_ITD_ALLOW_HRS * REVWKS1.REV_RT_AMT) ELSE 0 END,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') = 'UNIT1' AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN (REVWKS1.SUM_ITD_ALLOW_HRS * REVWKS1.REV_RT_AMT) ELSE 0 END,
       CASE WHEN NVL(REVWKS1.S_BILL_FORMULA_CD,'X') = 'UNIT1' AND REVWKS1.SUB_TOT_TYPE_NO = 2 AND SUBSTR(REVWKS1.ACCT_ID,4,4) = '-79-' 
       THEN REVWKS1.SUM_ITD_TGT_AL_AMT ELSE 0 END,
       REVWKS1.PROJ_TYPE_DC,
       REVWKS1.SUB_TOT_TYPE_NO,
       REVWKS1.SUM_ITD_TGT_AL_AMT,
       REVWKS1.SUM_ITD_FEE_ON_HRS_AMT,
       REVWKS1.SUM_ITD_OT_FEE_TGT_AMT,
       REVWKS1.SUM_ITD_TGT_FEE_AMT,
       REVWKS1.LEVEL_2_PROJECT_SEGMENT_ID,
       REVWKS1.SUM_ITD_REV_ADJ_AMT,
       REVWKS1.SUM_ITD_OV_TGT_CST_AMT,
       REVWKS1.SUM_ITD_OV_TGT_FEE_AMT,
       REVWKS1.SUM_ITD_OV_TGT_TOT_AMT,
       REVWKS1.SUM_ITD_ALLOW_HRS,
       REVWKS1.REV_RT_AMT,
       REVWKS1.S_BILL_FORMULA_CD
ORDER BY REVWKS1.L2_PROJ_ID OFFSET 0 ROWS),
       
REVWKS3 AS
(SELECT 
REVWKS2.L2_PROJ_ID,
ROW_NUMBER() OVER (PARTITION BY REVWKS2.L2_PROJ_ID ORDER BY REVWKS2.L2_PROJ_ID) as L2_PROJECT_ID,
REVWKS2.XRW_DIRECT_COSTS,
REVWKS2.XRW_FEE_IN_EXCESS,
REVWKS2.XRW_TOTAL_IN_EXCESS,
REVWKS2.XRW_COST_IN_EXCESS,
REVWKS2.XREVENUE_ADJUSTMENT,
REVWKS2.XRW_INDIRECT_COSTS,
REVWKS2.XRW_COM,
REVWKS2.XRW_FEE,
REVWKS2.XRW_FFP,
REVWKS2.XRW_RV,
REVWKS2.XODC_ACCRUALS,
REVWKS2.XRW_LABOR,
REVWKS2.XRW_LABOR_HOURS,
REVWKS2.XFEE_ON_ACCRUALS,
REVWKS2.XLABOR_ACCRUALS,
REVWKS2.XLABOR_ACCRUALS_FFP,
REVWKS2.XRW_ODC_ACCRUALS_FFP
FROM REVWKS2
ORDER BY REVWKS2.L2_PROJ_ID OFFSET 0 ROWS),

REVWKSFINAL AS
(SELECT * FROM REVWKS3
WHERE L2_PROJECT_ID = 1
ORDER BY
REVWKS3.L2_PROJ_ID OFFSET 0 ROWS)

--CONSOLIDATE INPUT DATA
SELECT REVWKSFINAL.XRW_DIRECT_COSTS,
       REVWKSFINAL.XRW_INDIRECT_COSTS,
       REVWKSFINAL.XRW_COM,
       REVWKSFINAL.XRW_FFP,
       REVWKSFINAL.XRW_RV,
       REVWKSFINAL.XRW_FEE_IN_EXCESS,
       REVWKSFINAL.XRW_TOTAL_IN_EXCESS,
       REVWKSFINAL.XRW_COST_IN_EXCESS,
       REVWKSFINAL.XREVENUE_ADJUSTMENT,
       REVWKSFINAL.XODC_ACCRUALS,
       REVWKSFINAL.XRW_LABOR,
       REVWKSFINAL.XRW_LABOR_HOURS,
       REVWKSFINAL.XFEE_ON_ACCRUALS,
       REVWKSFINAL.XLABOR_ACCRUALS,
       REVWKSFINAL.XLABOR_ACCRUALS_FFP,
       REVWKSFINAL.XRW_ODC_ACCRUALS_FFP,
       REVWKSFINAL.XRW_FEE,
       BILLSUM2.XINV_LABOR,
       BILLSUM2.XINV_LABOR_HOURS,
       BILLSUM2.XINV_DIRECT_COST,
       BILLSUM2.XINV_INDIRECT_COST,
       BILLSUM2.XINV_COM,
       BILLSUM2.XINV_FEE,
       VW_CN_RECORD_MSTR.POP_END_DT,
       BILLSUM2.XINV_FEE_IN_EXCESS,
       BILLSUM2.XINV_COST_IN_EXCESS,
       BILLSUM2.XINV_TOTAL_IN_EXCESS,
       BILLSUM2.XINV_FFP,
       VW_CN_RECORD_MSTR.PRIMARY_CONTRACT_TYPE_CD,
       VW_CN_RECORD_MSTR.POP_START_DT,
       VW_CN_RECORD_MSTR.CLOSEOUT_DISTRIBUTED_DT,
       VW_CN_RECORD_MSTR.SOLD_TO_CUSTOMER_NAME,
       VW_CN_RECORD_MSTR.BILLING_FREQ_CD_OTHER_DESC,
       VW_CN_RECORD_MSTR.BILLING_FREQ_NAME,
       VW_CN_RECORD_MSTR.CONTRACT_NO,
       VW_CN_RECORD_MSTR.DELIVERY_ORDER_NO,
       VW_CN_RECORD_MSTR.MASTER_AGRMNT_NO,
       VW_CN_RECORD_MSTR.PRIME_CO_NO,
       VW_CN_RECORD_MSTR.PRIME_PROJ_NO,
       VW_CN_RECORD_MSTR.OWNER_CONTRACT_REC_NO,
       STDBILL.INVOICE_RV,
       PBHS2.L2_PROJ_ID,
       PBHS2.XITD_BILLED,
       PBHS2.GROUP_NO,
       PBHS2.GROUP_NAME,
       PBHS2.BU_NO,
       PBHS2.BU_NAME,
       PBHS2.OPERATION_NO,
       PBHS2.OPERATION_NAME,
       PBHS2.CONTRACT_REC_NO,
       PBHS2.PROJECT_CONTROLLER,
       RPTREVSUM.ITD_REV,
       PBHS2.L1_PROJ_NAME,
       PRJBILLSUB.BILLED_AMT,
       STDBLKP.PROJ_CONV,
       PRJBILLHS2.XRTNGE_AMT,
       PBHS2.L2_PROJ_SEG_ID,
       OBDETL2.SUM_AOH_TM_BURDENED_CP_UNBURDENED,
       BEDIT2.PENDING_INVOICES,
       ARPASS.AR_PASS_THRU_AMT,
       PBHS2.BUG
FROM ((((((((((PBHS2 LEFT OUTER JOIN CONTRACTS.VW_CN_RECORD_MSTR
                                                                 VW_CN_RECORD_MSTR ON (PBHS2.CONTRACT_REC_NO =
                                                                                       VW_CN_RECORD_MSTR.CONTRACT_REC_NO)) LEFT OUTER JOIN ARPASS ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                    ARPASS.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                      PRJBILLHS2 ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                     PRJBILLHS2.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                             OBDETL2 ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                          OBDETL2.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                                                                                                                STDBLKP ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                                                                                                               STDBLKP.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       PRJBILLSUB ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 PRJBILLSUB.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    BILLSUM2 ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             BILLSUM2.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               RPTREVSUM ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           RPTREVSUM.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        STDBILL ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       STDBILL.L2_PROJ_ID)) LEFT OUTER JOIN 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               REVWKSFINAL ON (PBHS2.L2_PROJ_ID =
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             REVWKSFINAL.L2_PROJ_ID))
     LEFT OUTER JOIN BEDIT2
        ON (PBHS2.L2_PROJ_ID =
            BEDIT2.L2_PROJ_ID)