View: PA_UBR_UER_PROJ_DTLS_V

Product:	PA - Projects
Description:	This view is being used for reporting purpose in UBR and UER.This is UBR UER Project summary details view
Implementation/DBA Data:	APPS.PA_UBR_UER_PROJ_DTLS_V

View Text

SELECT PA.SEGMENT1 PROJECT_NUMBER
, PA.NAME PROJECT_NAME
, PA.PROJECT_ID PROJECT_ID
, PT.PROJECT_TYPE PROJECT_TYPE
, SU.COST_CENTER_SEGMENT COST_CENTER
, SU.ACCOUNT_SEGMENT ACCOUNT
, PALS.MEANING TYPE
, DI.RA_INVOICE_NUMBER INV_REV_NUM
, DII.LINE_NUM LINE_NUMBER
, DI.UBR_SUMMARY_ID UBR_SUMMARY_ID
, DI.UER_SUMMARY_ID UER_SUMMARY_ID
, SU.GL_PERIOD_NAME GL_PERIOD_NAME
, PA_CURRENCY.ROUND_CURRENCY_AMT(DECODE(SU.UBR_UER_SUMMARY_ID
, DI.UBR_SUMMARY_ID
, DECODE(DI.UBR_SUMMARY_ID
, DI.UER_SUMMARY_ID
, DII.ACCT_AMOUNT
, 0)
, DI.UER_SUMMARY_ID
, DECODE(DI.UER_SUMMARY_ID
, DI.UBR_SUMMARY_ID
, 0
, DII.ACCT_AMOUNT)
, 0) ) INV_REV_AMOUNT
, PA_CURRENCY.ROUND_CURRENCY_AMT(DECODE(SU.UBR_UER_SUMMARY_ID
, DI.UBR_SUMMARY_ID
, DII.UNBILLED_RECEIVABLE_DR
, 0)) UBR_AMOUNT
, PA_CURRENCY.ROUND_CURRENCY_AMT(DECODE(SU.UBR_UER_SUMMARY_ID
, DI.UER_SUMMARY_ID
, DII.UNEARNED_REVENUE_CR
, 0)) UER_AMOUNT
, TO_NUMBER((PA_UBR_UER_SUMM_PKG.GET_INV_GL_HEADER_ID_LINE_NUM('GL_HEADER_ID'
, DI.SYSTEM_REFERENCE
, DII.LINE_NUM
, DECODE(DII.UNBILLED_RECEIVABLE_DR
, 0
, DI.UNEARNED_CODE_COMBINATION_ID
, DI.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME))) GL_HEADER_ID
, TO_NUMBER((PA_UBR_UER_SUMM_PKG.GET_INV_GL_HEADER_ID_LINE_NUM('GL_LINE_NUM'
, DI.SYSTEM_REFERENCE
, DII.LINE_NUM
, DECODE(DII.UNBILLED_RECEIVABLE_DR
, 0
, DI.UNEARNED_CODE_COMBINATION_ID
, DI.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME))) GL_LINE_ID
, (PA_UBR_UER_SUMM_PKG.GET_INV_GL_HEADER_ID_LINE_NUM('GL_HEADER_NAME'
, DI.SYSTEM_REFERENCE
, DII.LINE_NUM
, DECODE(DII.UNBILLED_RECEIVABLE_DR
, 0
, DI.UNEARNED_CODE_COMBINATION_ID
, DI.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME)) GL_HEADER_NAME
, (PA_UBR_UER_SUMM_PKG.GET_INV_GL_HEADER_ID_LINE_NUM('GL_BATCH_NAME'
, DI.SYSTEM_REFERENCE
, DII.LINE_NUM
, DECODE(DII.UNBILLED_RECEIVABLE_DR
, 0
, DI.UNEARNED_CODE_COMBINATION_ID
, DI.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME)) GL_BATCH_NAME
, DII.TEXT
, SU.ZERO_BALANCE_FLAG
, SU.GL_PERIOD_START_DATE
, SU.REQUEST_ID
, SU.PROCESS_FLAG
, SU.MULTI_COST_CENTER_FLAG
, SU.UBR_UER_CODE
, SU.UBR_UER_SUMMARY_ID
, DII.LINE_NUM
, PIA.SET_OF_BOOKS_ID
, PIA.ORG_ID
FROM PA_UBR_UER_SUMM_ACCT SU
, PA_PROJECTS PA
, PA_PROJECT_TYPES_ALL PT
, PA_DRAFT_INVOICES_ALL DI
, PA_DRAFT_INVOICE_ITEMS DII
, PA_LOOKUPS PALS
, PA_IMPLEMENTATIONS_ALL PIA
WHERE PIA.ORG_ID = PT.ORG_ID
AND PT.PROJECT_TYPE = PA.PROJECT_TYPE
AND PT.ORG_ID = PA.ORG_ID
AND PA.PROJECT_ID = SU.PROJECT_ID
AND PA.PROJECT_ID = DI.PROJECT_ID
AND DII.PROJECT_ID = DI.PROJECT_ID
AND DII.DRAFT_INVOICE_NUM = DI.DRAFT_INVOICE_NUM
AND ( ( SU.UBR_UER_SUMMARY_ID = DI.UBR_SUMMARY_ID ) OR ( SU.UBR_UER_SUMMARY_ID = DI.UER_SUMMARY_ID
AND DI.UBR_SUMMARY_ID <> DI.UER_SUMMARY_ID ) )
AND PALS.LOOKUP_TYPE = 'PA_UBR_UER_TRANS_TYPE'
AND PALS.LOOKUP_CODE = 'PA_INVOICE' UNION SELECT PA.SEGMENT1 PROJECT_NUMBER
, PA.NAME PROJECT_NAME
, PA.PROJECT_ID PROJECT_ID
, PT.PROJECT_TYPE PROJECT_TYPE
, SU.COST_CENTER_SEGMENT COST_CENTER
, SU.ACCOUNT_SEGMENT ACCOUNT
, PALS.MEANING TYPE
, TO_CHAR(DR.DRAFT_REVENUE_NUM) INV_REV_NUM
, -1 LINE_NUMBER
, DR.UBR_SUMMARY_ID UBR_SUMMARY_ID
, DR.UER_SUMMARY_ID UER_SUMMARY_ID
, SU.GL_PERIOD_NAME GL_PERIOD_NAME
, PA_CURRENCY.ROUND_CURRENCY_AMT(DECODE(SU.UBR_UER_SUMMARY_ID
, DR.UBR_SUMMARY_ID
, DECODE(DR.UBR_SUMMARY_ID
, DR.UER_SUMMARY_ID
, (DR.UNBILLED_RECEIVABLE_DR - DR.UNEARNED_REVENUE_CR)
, 0)
, DR.UER_SUMMARY_ID
, DECODE(DR.UER_SUMMARY_ID
, DR.UBR_SUMMARY_ID
, 0
, (DR.UNBILLED_RECEIVABLE_DR - DR.UNEARNED_REVENUE_CR))
, 0)) INV_REV_AMOUNT
, PA_CURRENCY.ROUND_CURRENCY_AMT(DECODE(SU.UBR_UER_SUMMARY_ID
, DR.UBR_SUMMARY_ID
, DR.UNBILLED_RECEIVABLE_DR
, 0)) UBR_AMOUNT
, PA_CURRENCY.ROUND_CURRENCY_AMT(DECODE(SU.UBR_UER_SUMMARY_ID
, DR.UER_SUMMARY_ID
, DR.UNEARNED_REVENUE_CR
, 0)) UER_AMOUNT
, TO_NUMBER((PA_UBR_UER_SUMM_PKG.GET_REV_GL_HEADER_ID_LINE_NUM('GL_HEADER_ID'
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_CODE_COMBINATION_ID
, DR.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME))) GL_HEADER_ID
, TO_NUMBER((PA_UBR_UER_SUMM_PKG.GET_REV_GL_HEADER_ID_LINE_NUM('GL_LINE_NUM'
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_CODE_COMBINATION_ID
, DR.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME))) GL_LINE_ID
, (PA_UBR_UER_SUMM_PKG.GET_REV_GL_HEADER_ID_LINE_NUM('GL_HEADER_NAME'
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_CODE_COMBINATION_ID
, DR.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME)) GL_HEADER_NAME
, (PA_UBR_UER_SUMM_PKG.GET_REV_GL_HEADER_ID_LINE_NUM('GL_BATCH_NAME'
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(DR.UNBILLED_RECEIVABLE_DR
, 0
, DR.UNEARNED_CODE_COMBINATION_ID
, DR.UNBILLED_CODE_COMBINATION_ID)
, SU.GL_PERIOD_NAME)) GL_BATCH_NAME
, '' TEXT
, SU.ZERO_BALANCE_FLAG
, SU.GL_PERIOD_START_DATE
, SU.REQUEST_ID
, SU.PROCESS_FLAG
, SU.MULTI_COST_CENTER_FLAG
, SU.UBR_UER_CODE
, SU.UBR_UER_SUMMARY_ID
, TO_NUMBER(NULL)
, PIA.SET_OF_BOOKS_ID
, PIA.ORG_ID
FROM PA_UBR_UER_SUMM_ACCT SU
, PA_PROJECTS PA
, PA_PROJECT_TYPES_ALL PT
, PA_DRAFT_REVENUES_ALL DR
, PA_LOOKUPS PALS
, PA_IMPLEMENTATIONS_ALL PIA
WHERE PIA.ORG_ID = PT.ORG_ID
AND PT.PROJECT_TYPE = PA.PROJECT_TYPE
AND PT.ORG_ID = PA.ORG_ID
AND PA.PROJECT_ID = SU.PROJECT_ID
AND PA.PROJECT_ID = DR.PROJECT_ID
AND ( ( SU.UBR_UER_SUMMARY_ID = DR.UBR_SUMMARY_ID ) OR ( SU.UBR_UER_SUMMARY_ID = DR.UER_SUMMARY_ID
AND DR.UBR_SUMMARY_ID <> DR.UER_SUMMARY_ID ))
AND PALS.LOOKUP_TYPE = 'PA_UBR_UER_TRANS_TYPE'
AND PALS.LOOKUP_CODE = 'PA_REVENUE'

Columns