View: PA_UBR_UER_ACCT_DTLS_V

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

View Text

SELECT PA.SEGMENT1
, PA.NAME
, PA.PROJECT_ID
, PT.PROJECT_TYPE
, SU.COST_CENTER_SEGMENT
, SU.ACCOUNT_SEGMENT
, PALS.MEANING TYPE
, DI.RA_INVOICE_NUMBER INV_REV_NUM
, DI.UBR_SUMMARY_ID
, DI.UER_SUMMARY_ID
, SU.GL_PERIOD_NAME
, PA_CURRENCY.ROUND_CURRENCY_AMT( DECODE(SU.UBR_UER_SUMMARY_ID
, DI.UBR_SUMMARY_ID
, DECODE(SIGN(DI.UNBILLED_RECEIVABLE_DR)
, -1
, -JE.ACCOUNTED_CR
, -JE.ACCOUNTED_DR)
, DECODE(SIGN(DI.UNEARNED_REVENUE_CR)
, -1
, JE.ACCOUNTED_DR
, JE.ACCOUNTED_CR))) AMOUNT
, JE.JE_HEADER_ID GL_HEADER_ID
, JE.JE_LINE_NUM GL_LINE_NUM
, JH.NAME GL_HEADER_NAME
, JB.NAME GL_BATCH_NAME
, DII.TEXT
, SU.ZERO_BALANCE_FLAG
, SU.GL_PERIOD_START_DATE
, 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
, GL_JE_LINES JE
, RA_CUSTOMER_TRX_LINES_ALL RCTLA
, RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA
, GL_JE_HEADERS JH
, GL_JE_BATCHES JB
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 DI.PROJECT_ID = DII.PROJECT_ID
AND DI.DRAFT_INVOICE_NUM = DII.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'
AND JE.REFERENCE_2 = TO_CHAR(RCTLGDA.CUSTOMER_TRX_ID)
AND JE.REFERENCE_3 = TO_CHAR(RCTLGDA.CUST_TRX_LINE_GL_DIST_ID)
AND JE.CODE_COMBINATION_ID = RCTLGDA.CODE_COMBINATION_ID
AND JE.PERIOD_NAME = SU.GL_PERIOD_NAME
AND RCTLGDA.CUSTOMER_TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID
AND (( SU.UBR_UER_CODE = 'UER'
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNEARNED_CODE_COMBINATION_ID ) OR ( SU.UBR_UER_CODE = 'UBR'
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNBILLED_CODE_COMBINATION_ID ) OR ( SU.UBR_UER_CODE = 'UBR_UER'
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNEARNED_CODE_COMBINATION_ID
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNBILLED_CODE_COMBINATION_ID ) )
AND RCTLA.CUSTOMER_TRX_ID = DI.SYSTEM_REFERENCE
AND RCTLA.INTERFACE_LINE_ATTRIBUTE6 = DII.LINE_NUM
AND JE.JE_HEADER_ID = JH.JE_HEADER_ID
AND JH.JE_BATCH_ID = JB.JE_BATCH_ID(+) UNION SELECT PA.SEGMENT1
, PA.NAME
, PA.PROJECT_ID
, PT.PROJECT_TYPE
, SU.COST_CENTER_SEGMENT
, SU.ACCOUNT_SEGMENT
, PALS.MEANING TYPE
, DI.RA_INVOICE_NUMBER INV_REV_NUM
, DI.UBR_SUMMARY_ID
, DI.UER_SUMMARY_ID
, SU.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.UNBILLED_RECEIVABLE_DR - DII.UNEARNED_REVENUE_CR)
, DII.UNBILLED_RECEIVABLE_DR))
, DII.UNEARNED_REVENUE_CR)) AMOUNT
, NULL GL_HEADER_ID
, NULL GL_LINE_NUM
, NULL GL_HEADER_NAME
, NULL GL_BATCH_NAME
, DII.TEXT
, SU.ZERO_BALANCE_FLAG
, SU.GL_PERIOD_START_DATE
, 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 DI.PROJECT_ID = DII.PROJECT_ID
AND DI.DRAFT_INVOICE_NUM = DII.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'
AND NOT EXISTS ( SELECT 1
FROM GL_JE_LINES JE
, RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA
, RA_CUSTOMER_TRX_LINES_ALL RCTLA
WHERE JE.REFERENCE_2 = TO_CHAR(RCTLGDA.CUSTOMER_TRX_ID)
AND JE.REFERENCE_3 = TO_CHAR(RCTLGDA.CUST_TRX_LINE_GL_DIST_ID)
AND JE.CODE_COMBINATION_ID = RCTLGDA.CODE_COMBINATION_ID
AND JE.PERIOD_NAME = SU.GL_PERIOD_NAME
AND RCTLGDA.CUSTOMER_TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID
AND (( SU.UBR_UER_CODE = 'UER'
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNEARNED_CODE_COMBINATION_ID ) OR ( SU.UBR_UER_CODE = 'UBR'
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNBILLED_CODE_COMBINATION_ID ) OR ( SU.UBR_UER_CODE = 'UBR_UER'
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNEARNED_CODE_COMBINATION_ID
AND RCTLGDA.CODE_COMBINATION_ID = DI.UNBILLED_CODE_COMBINATION_ID ) )
AND RCTLA.CUSTOMER_TRX_ID = DI.SYSTEM_REFERENCE
AND RCTLA.INTERFACE_LINE_ATTRIBUTE6 = DII.LINE_NUM ) UNION SELECT PA.SEGMENT1
, PA.NAME
, PA.PROJECT_ID
, PT.PROJECT_TYPE
, SU.COST_CENTER_SEGMENT
, SU.ACCOUNT_SEGMENT
, PALS.MEANING TYPE
, TO_CHAR(DR.DRAFT_REVENUE_NUM) INV_REV_NUM
, DR.UBR_SUMMARY_ID
, DR.UER_SUMMARY_ID
, SU.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)
, DR.UNBILLED_RECEIVABLE_DR))
, DR.UNEARNED_REVENUE_CR)) AMOUNT
, TO_NUMBER(PA_UBR_UER_SUMM_PKG.GET_REV_GL_HEADER_ID_LINE_NUM('GL_HEADER_ID'
, DECODE(SU.UBR_UER_CODE
, 'UER'
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 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(SU.UBR_UER_CODE
, 'UER'
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 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(SU.UBR_UER_CODE
, 'UER'
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 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(SU.UBR_UER_CODE
, 'UER'
, DR.UNEARNED_BATCH_NAME
, DR.UNBILLED_BATCH_NAME)
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 'REVENUE - UER'
, 'REVENUE - UBR')
, DECODE(SU.UBR_UER_CODE
, 'UER'
, 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
, -1 LINE_NUMBER
, 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