VIEW: APPS.PA_UBR_UER_PROJ_DTLS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'