ECC Projects, Projects Billing: Revenue, Invoice, and Distributions

select
x.*
from
(
select * from (
	SELECT
    stg.RECORD_TYPE	                    ,
    stg.ECC_SPEC_ID	                    ,
    stg.PROJECT_ID	                    ,
    stg.PROJECT_NAME                    ,
    stg.PROJECT_NUMBER                  ,
    stg.FUNDING_TASK_ID                 ,
    stg.PROJFUNC_CURRENCY_CODE          ,
    stg.PROJECT_CURRENCY_CODE           ,
    stg.OPERATING_UNIT                  ,
    stg.ORG_ID                          ,
    stg.REV_GL_PERIOD_NAME                  ,
    stg.REV_PA_PERIOD_NAME                  ,
    stg.REV_CUSTOMER_NAME                   ,
    stg.REV_CUSTOMER_BILL_SPLIT             ,
    stg.REV_AGREEMENT_NUM                   ,
    stg.REV_RELEASED_DATE                   ,
    stg.REV_TRANSFERRED_DATE                ,
    stg.REV_XFER_REJ_REASON_DESC            ,
    stg.REV_TRANSFER_STATUS_CODE            ,
    stg.REV_XFER_REJECTION_REASON       ,
    stg.REV_PROJFUNC_UBR                    ,
    stg.REV_PROJFUNC_UER                    ,
    stg.REV_FUNDING_CURRENCY_CODE           ,
    stg.DRAFT_REVENUE_NUM               ,
    stg.DRAFT_REVENUE_NUM_CREDITED      ,
    stg.ACCRUE_THROUGH_DATE             ,
    stg.REVENUE_STATUS                  ,
    stg.REVENUE_STATUS_CODE             ,
    stg.PROJFUNC_REVENUE_AMOUNT         ,
    stg.FUNDING_REVENUE_AMOUNT          ,
    stg.PROJECT_REVENUE_AMOUNT          ,
    stg.REVTRANS_CURRENCY_CODE          ,
    stg.REVTRANS_AMOUNT                 ,
    stg.INV_GL_PERIOD_NAME                  ,
    stg.INV_PA_PERIOD_NAME                  ,
    stg.INV_CUSTOMER_NAME                   ,
    stg.INV_CUSTOMER_BILL_SPLIT             ,
    stg.INV_AGREEMENT_NUM                   ,
    stg.INV_RELEASED_DATE                   ,
    stg.INV_TRANSFERRED_DATE                ,
    stg.INV_TRANSFER_STATUS_CODE            ,
    stg.INV_XFER_REJECTION_REASON       ,
    stg.INV_PROJFUNC_UBR                    ,
    stg.INV_PROJFUNC_UER                    ,
    stg.INV_FUNDING_CURRENCY_CODE           ,
    stg.DRAFT_INVOICE_NUM               ,
    stg.DRAFT_INVOICE_NUM_CREDITED      ,
    stg.APPROVED_DATE                   ,
    stg.BILL_THROUGH_DATE               ,
    stg.INVOICE_DATE                    ,
    stg.PROJFUNC_BILL_AMOUNT            ,
    stg.FUNDING_BILL_AMOUNT             ,
    stg.PROJECT_BILL_AMOUNT             ,
    stg.RA_INVOICE_NUMBER               ,
    stg.BILL_TO_CUSTOMER                ,
    stg.SHIP_TO_CUSTOMER                ,
    stg.BILL_TO_CONTACT                 ,
    stg.SHIP_TO_CONTACT                 ,
    stg.CONCESSION_FLAG                 ,
    stg.WRITE_OFF_FLAG                  ,
    stg.CANCEL_CREDIT_MEMO_FLAG         ,
    stg.TAX_AMOUNT                      ,
    stg.INVOICE_STATUS                  ,
    stg.INVOICE_STATUS_CODE             ,
    stg.PFC_RETENTION_AMOUNT            ,
    stg.CREDIT_MEMO_REASON              ,
    stg.INVPROC_CURRENCY_CODE           ,
    stg.INVPROC_BILL_AMOUNT             ,
    stg.INV_CURRENCY_CODE               ,
    stg.INV_AMOUNT                      ,
    stg.BILL_GROUP                      ,
	stg.CONS_BILL_GROUP_NAME            ,
	stg.CONSOLIDATED_INV_NUM            ,
	stg.INVOICE_DUE_AMT                 ,
    stg.ACCURACY_INDEX_NUMRTOR          ,
	stg.RECEIPT_DATE                    ,
    stg.RECEIPT_AMOUNT                  ,
    stg.RECEIPT_WEIGHTED_DAYS           ,
	NVL(trunc(stg.receipt_date) - trunc(stg.invoice_date), 0) INVOICE_TO_CASH_DAYS,
    stg.pfc_invitem_bill_amt            ,
    stg.EI_EVENT_DIST_LINE_TYPE         ,
    stg.EI_EVENT_PFC_REV_AMT            ,
    stg.EI_EVENT_PFC_BILL_AMT           ,
    stg.EI_EVENT_PC_REV_AMT             ,
	stg.EI_EVENT_PC_BILL_AMT            ,
	stg.EI_EVENT_FC_REV_AMT             ,
	stg.EI_EVENT_FC_BILL_AMT            ,
    stg.EI_EVENT_REV_POBG_LINE_NUM      ,
    stg.EI_EVENT_INV_POBG_LINE_NUM      ,
    stg.EI_EVENT_SOURCE                 ,
    stg.EI_EVENT_REV_ITEM_LINE_NUM      ,
    stg.EI_EVENT_INV_ITEM_LINE_NUM      ,
    stg.EI_EVENT_DIST_LINE_NUM          ,
    stg.EI_EVENT_ORGANIZATION              ,
    stg.EI_EVENT_TYPE                      ,
    stg.EI_EVENT_DATE                      ,
    stg.EI_EVENT_REV_CATEGORY              ,
	stg.EI_EVENT_REVTRANS_AMOUNT           ,
	stg.EI_EVENT_INVPROC_AMOUNT            ,
	stg.EI_EVENT_BILL_GROUP                ,
    stg.EI_EVENT_TASK_ID                         ,
    stg.EI_EVENT_TASK_NUMBER                     ,
    stg.EI_EVENT_TASK_NUMBER FUNDING_TASK_NUMBER ,
    stg.EI_EVENT_TASK_NAME                       ,
    stg.EI_LOWEST_TASK_ID                         ,
    stg.EI_LOWEST_TASK_NUMBER                     ,
    stg.EI_LOWEST_TASK_NAME                       ,
    stg.EI_ID                           ,
	stg.EVENT_ID                        ,
	stg.EVENT_NUM                       ,
    stg.EI_EVENT_ID                     ,
    stg.chart_display_flag              ,
    stg.gl_period_start_date            ,
    stg.PROJECT_ID||':'||stg.FUNDING_TASK_ID DS_ASSOC_PRJ_TSK_ATTR,
    stg.PROJECT_ID||':'||stg.FUNDING_TASK_ID||':'||stg.EI_EVENT_ID DS_ASSOC_PRJ_TSK_EE_ATTR,
    stg.PROJECT_ID||':'||stg.FUNDING_TASK_ID||':'||nvl(stg.INV_GL_PERIOD_NAME, stg.REV_GL_PERIOD_NAME) DS_ASSOC_PRJ_TSK_PER_ATTR,
    stg.PROJECT_ID||':'||stg.FUNDING_TASK_ID||':'||stg.EI_EVENT_ID||':'||stg.INV_GL_PERIOD_NAME DS_ASSOC_PRJ_TSK_EE_PER_ATTR,
	stg.revenue,
	stg.chart_gl_period,
	stg.expenditure,
	(nvl(stg.revenue, 0) - nvl(stg.expenditure, 0)) cash_flow,
	chart_gl_period_fmt,
	chart_period_year,
	chart_period_quarter,
    stg.LANGUAGE
FROM PA_ECC_BILL_REV_INV_DETAIL stg,
     PA_DRAFT_INVOICES_ALL pdi
 
where stg.project_id = pdi.project_id(+)
  and stg.draft_invoice_num = pdi.draft_invoice_num(+)
 
) PIVOT	(
MAX(OPERATING_UNIT) AS OPERATING_UNIT,
MAX(EI_EVENT_REV_CATEGORY) AS EI_EVENT_REV_CATEGORY,
MAX(EI_EVENT_SOURCE) AS EI_EVENT_SOURCE,
MAX(EI_EVENT_ORGANIZATION) AS EI_EVENT_ORGANIZATION,
MAX(EI_EVENT_TYPE) AS EI_EVENT_TYPE,
MAX(CONCESSION_FLAG) AS CONCESSION_FLAG,
MAX(WRITE_OFF_FLAG) AS WRITE_OFF_FLAG,
MAX(CANCEL_CREDIT_MEMO_FLAG) AS CANCEL_CREDIT_MEMO_FLAG,
MAX(CREDIT_MEMO_REASON) AS CREDIT_MEMO_REASON,
MAX(INVOICE_STATUS) AS INVOICE_STATUS,
MAX(REVENUE_STATUS) AS REVENUE_STATUS,
MAX(REV_XFER_REJ_REASON_DESC) AS REV_XFER_REJ_REASON_DESC
FOR LANGUAGE IN ('US' "US"))
) x
where
2=2