eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
[Home] [Help]
MATERIALIZED VIEW: APPS.PJI_AC_CLS_F_MV

Source


select /* 12.0: bug#4526784 */
    acp.TIME_ID,
    acp.PROJECT_ORGANIZATION_ID,
    acp.CALENDAR_TYPE,
    acp.PERIOD_TYPE_ID,
    cls.PROJECT_CLASS_ID,
    acp.CURR_RECORD_TYPE_ID,
    acp.CURRENCY_CODE,
    acp.PROJECT_ORG_ID,
    count(*)                                    CNT,
    sum  (acp.REVENUE *
          cls.CODE_PERCENTAGE / 100)            REVENUE,
    count(acp.REVENUE *
          cls.CODE_PERCENTAGE / 100)            REVENUE_CNT,
    sum  (1)                                    PROJECT_COUNT,
    count(1)                                    PROJECT_COUNT_CNT,
    sum  (acp.INITIAL_FUNDING_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            INITIAL_FUNDING_AMOUNT,
    count(acp.INITIAL_FUNDING_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            INITIAL_FUNDING_AMOUNT_CNT,
    sum  (acp.INITIAL_FUNDING_COUNT)            INITIAL_FUNDING_COUNT,
    count(acp.INITIAL_FUNDING_COUNT)            INITIAL_FUNDING_COUNT_CNT,
    sum  (acp.ADDITIONAL_FUNDING_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            ADDITIONAL_FUNDING_AMOUNT,
    count(acp.ADDITIONAL_FUNDING_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            ADDITIONAL_FUNDING_AMOUNT_CNT,
    sum  (acp.ADDITIONAL_FUNDING_COUNT)         ADDITIONAL_FUNDING_COUNT,
    count(acp.ADDITIONAL_FUNDING_COUNT)         ADDITIONAL_FUNDING_COUNT_CNT,
    sum  (acp.CANCELLED_FUNDING_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            CANCELLED_FUNDING_AMOUNT,
    count(acp.CANCELLED_FUNDING_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            CANCELLED_FUNDING_AMOUNT_CNT,
    sum  (acp.CANCELLED_FUNDING_COUNT)          CANCELLED_FUNDING_COUNT,
    count(acp.CANCELLED_FUNDING_COUNT)          CANCELLED_FUNDING_COUNT_CNT,
    sum  (acp.FUNDING_ADJUSTMENT_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            FUNDING_ADJUSTMENT_AMOUNT,
    count(acp.FUNDING_ADJUSTMENT_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            FUNDING_ADJUSTMENT_AMOUNT_CNT,
    sum  (acp.FUNDING_ADJUSTMENT_COUNT)         FUNDING_ADJUSTMENT_COUNT,
    count(acp.FUNDING_ADJUSTMENT_COUNT)         FUNDING_ADJUSTMENT_COUNT_CNT,
    sum  (acp.REVENUE_WRITEOFF *
          cls.CODE_PERCENTAGE / 100)            REVENUE_WRITEOFF,
    count(acp.REVENUE_WRITEOFF *
          cls.CODE_PERCENTAGE / 100)            REVENUE_WRITEOFF_CNT,
    sum  (acp.AR_INVOICE_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_INVOICE_AMOUNT,
    count(acp.AR_INVOICE_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_INVOICE_AMOUNT_CNT,
    sum  (acp.AR_INVOICE_COUNT)                 AR_INVOICE_COUNT,
    count(acp.AR_INVOICE_COUNT)                 AR_INVOICE_COUNT_CNT,
    sum  (acp.AR_CASH_APPLIED_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_CASH_APPLIED_AMOUNT,
    count(acp.AR_CASH_APPLIED_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_CASH_APPLIED_AMOUNT_CNT,
    sum  (acp.AR_INVOICE_WRITEOFF_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_INVOICE_WRITEOFF_AMOUNT,
    count(acp.AR_INVOICE_WRITEOFF_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_INVOICE_WRITEOFF_AMOUNT_CNT,
    sum  (acp.AR_INVOICE_WRITEOFF_COUNT)        AR_INVOICE_WRITEOFF_COUNT,
    count(acp.AR_INVOICE_WRITEOFF_COUNT)        AR_INVOICE_WRITEOFF_COUNT_CNT,
    sum  (acp.AR_CREDIT_MEMO_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_CREDIT_MEMO_AMOUNT,
    count(acp.AR_CREDIT_MEMO_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_CREDIT_MEMO_AMOUNT_CNT,
    sum  (acp.AR_CREDIT_MEMO_COUNT)             AR_CREDIT_MEMO_COUNT,
    count(acp.AR_CREDIT_MEMO_COUNT)             AR_CREDIT_MEMO_COUNT_CNT,
    sum  (acp.UNBILLED_RECEIVABLES *
          cls.CODE_PERCENTAGE / 100)            UNBILLED_RECEIVABLES,
    count(acp.UNBILLED_RECEIVABLES *
          cls.CODE_PERCENTAGE / 100)            UNBILLED_RECEIVABLES_CNT,
    sum  (acp.UNEARNED_REVENUE *
          cls.CODE_PERCENTAGE / 100)            UNEARNED_REVENUE,
    count(acp.UNEARNED_REVENUE *
          cls.CODE_PERCENTAGE / 100)            UNEARNED_REVENUE_CNT,
    sum  (acp.AR_UNAPPR_INVOICE_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_UNAPPR_INVOICE_AMOUNT,
    count(acp.AR_UNAPPR_INVOICE_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_UNAPPR_INVOICE_AMOUNT_CNT,
    sum  (acp.AR_UNAPPR_INVOICE_COUNT)          AR_UNAPPR_INVOICE_COUNT,
    count(acp.AR_UNAPPR_INVOICE_COUNT)          AR_UNAPPR_INVOICE_COUNT_CNT,
    sum  (acp.AR_APPR_INVOICE_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_APPR_INVOICE_AMOUNT,
    count(acp.AR_APPR_INVOICE_AMOUNT *
          cls.CODE_PERCENTAGE / 100)            AR_APPR_INVOICE_AMOUNT_CNT,
    sum  (acp.AR_APPR_INVOICE_COUNT)            AR_APPR_INVOICE_COUNT,
    count(acp.AR_APPR_INVOICE_COUNT)            AR_APPR_INVOICE_COUNT_CNT,
    sum  (acp.AR_AMOUNT_DUE *
          cls.CODE_PERCENTAGE / 100)            AR_AMOUNT_DUE,
    count(acp.AR_AMOUNT_DUE *
          cls.CODE_PERCENTAGE / 100)            AR_AMOUNT_DUE_CNT,
    sum  (acp.AR_COUNT_DUE)                     AR_COUNT_DUE,
    count(acp.AR_COUNT_DUE)                     AR_COUNT_DUE_CNT,
    sum  (acp.AR_AMOUNT_OVERDUE *
          cls.CODE_PERCENTAGE / 100)            AR_AMOUNT_OVERDUE,
    count(acp.AR_AMOUNT_OVERDUE *
          cls.CODE_PERCENTAGE / 100)            AR_AMOUNT_OVERDUE_CNT,
    sum  (acp.AR_COUNT_OVERDUE)                 AR_COUNT_OVERDUE,
    count(acp.AR_COUNT_OVERDUE)                 AR_COUNT_OVERDUE_CNT,
    sum  (acp.DORMANT_BACKLOG_INACTIV *
          cls.CODE_PERCENTAGE / 100)            DORMANT_BACKLOG_INACTIV,
    count(acp.DORMANT_BACKLOG_INACTIV *
          cls.CODE_PERCENTAGE / 100)            DORMANT_BACKLOG_INACTIV_CNT,
    sum  (acp.DORMANT_BACKLOG_START *
          cls.CODE_PERCENTAGE / 100)            DORMANT_BACKLOG_START,
    count(acp.DORMANT_BACKLOG_START *
          cls.CODE_PERCENTAGE / 100)            DORMANT_BACKLOG_START_CNT,
    sum  (acp.LOST_BACKLOG *
          cls.CODE_PERCENTAGE / 100)            LOST_BACKLOG,
    count(acp.LOST_BACKLOG *
          cls.CODE_PERCENTAGE / 100)            LOST_BACKLOG_CNT,
    sum  (acp.ACTIVE_BACKLOG *
          cls.CODE_PERCENTAGE / 100)            ACTIVE_BACKLOG,
    count(acp.ACTIVE_BACKLOG *
          cls.CODE_PERCENTAGE / 100)            ACTIVE_BACKLOG_CNT,
    sum  (acp.REVENUE_AT_RISK *
          cls.CODE_PERCENTAGE / 100)            REVENUE_AT_RISK,
    count(acp.REVENUE_AT_RISK *
          cls.CODE_PERCENTAGE / 100)            REVENUE_AT_RISK_CNT
  from
    PJI.PJI_AC_PROJ_F       acp,
    PJI.PJI_PROJECT_CLASSES cls
  where
    acp.PROJECT_ID = cls.PROJECT_ID
  group by
    acp.TIME_ID,
    acp.PROJECT_ORGANIZATION_ID,
    acp.CALENDAR_TYPE,
    acp.PERIOD_TYPE_ID,
    cls.PROJECT_CLASS_ID,
    acp.CURR_RECORD_TYPE_ID,
    acp.CURRENCY_CODE,
    acp.PROJECT_ORG_ID