ECC Projects, Projects Billing: Revenue, Invoice, and Distributions
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: pa-ds-bill-revenueinvoice
Query Procedure: PA_ECC_BILL_LOAD_PKG.LOAD_FULL_REV_INV_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
Dataset Key: pa-ds-bill-revenueinvoice
Query Procedure: PA_ECC_BILL_LOAD_PKG.LOAD_FULL_REV_INV_DATA
Security Procedure: PA_ECC_DATASECURITY_PKG.GetFilterAttributeValues
Run
ECC Projects, Projects Billing: Revenue, Invoice, and Distributions and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |