VIEW: APPS.AR_XLA_CTLGD_LINES_V

Source

View Text - Preformatted

SELECT ctlgd.CUST_TRX_LINE_GL_DIST_ID , ctlgd.CUSTOMER_TRX_LINE_ID , ae.GL_SL_LINK_ID , ae.GL_SL_LINK_TABLE , ae.code_combination_id , ctlgd.SET_OF_BOOKS_ID , ctlgd.PERCENT , DECODE(ae.accounting_class_code, 'RECEIVABLE',NVL(lk.unrounded_entered_dr,0)-NVL(lk.unrounded_entered_cr,0), 'FV_REC_DR' ,NVL(lk.unrounded_entered_dr,0)-NVL(lk.unrounded_entered_cr,0), 'FV_REC_CR' ,NVL(lk.unrounded_entered_dr,0)-NVL(lk.unrounded_entered_cr,0), NVL(lk.unrounded_entered_cr,0)-NVL(lk.unrounded_entered_dr,0)) , ctlgd.GL_DATE , ctlgd.GL_POSTED_DATE , ctlgd.CUST_TRX_LINE_SALESREP_ID , ctlgd.COMMENTS , ctlgd.ATTRIBUTE_CATEGORY , ctlgd.ATTRIBUTE1 , ctlgd.ATTRIBUTE2 , ctlgd.ATTRIBUTE3 , ctlgd.ATTRIBUTE4 , ctlgd.ATTRIBUTE5 , ctlgd.ATTRIBUTE6 , ctlgd.ATTRIBUTE7 , ctlgd.ATTRIBUTE8 , ctlgd.ATTRIBUTE9 , ctlgd.ATTRIBUTE10 , ctlgd.CONCATENATED_SEGMENTS , ctlgd.ORIGINAL_GL_DATE , ctlgd.POST_REQUEST_ID , ctlgd.POSTING_CONTROL_ID , DECODE(ae.accounting_class_code, 'RECEIVABLE','REC', 'FV_REC_DR' ,'REC', 'FV_REC_CR' ,'REC', ctlgd.ACCOUNT_CLASS) , ctlgd.RA_POST_LOOP_NUMBER , ctlgd.CUSTOMER_TRX_ID , ctlgd.ACCOUNT_SET_FLAG , DECODE(ae.accounting_class_code, 'RECEIVABLE',NVL(lk.unrounded_accounted_dr,0)-NVL(lk.unrounded_accounted_cr,0), 'FV_REC_DR' ,NVL(lk.unrounded_accounted_dr,0)-NVL(lk.unrounded_accounted_cr,0), 'FV_REC_CR' ,NVL(lk.unrounded_accounted_dr,0)-NVL(lk.unrounded_accounted_cr,0), NVL(lk.unrounded_accounted_cr,0)-NVL(lk.unrounded_accounted_dr,0)) , ctlgd.USSGL_TRANSACTION_CODE , ctlgd.USSGL_TRANSACTION_CODE_CONTEXT , ctlgd.ATTRIBUTE11 , ctlgd.ATTRIBUTE12 , ctlgd.ATTRIBUTE13 , ctlgd.ATTRIBUTE14 , ctlgd.ATTRIBUTE15 , ctlgd.LATEST_REC_FLAG , ctlgd.ORG_ID , ctlgd.MRC_ACCOUNT_CLASS , ctlgd.MRC_CUSTOMER_TRX_ID , ctlgd.MRC_AMOUNT , ctlgd.MRC_GL_POSTED_DATE , ctlgd.MRC_POSTING_CONTROL_ID , ctlgd.MRC_ACCTD_AMOUNT , ctlgd.COLLECTED_TAX_CCID , ctlgd.COLLECTED_TAX_CONCAT_SEG , ctlgd.REVENUE_ADJUSTMENT_ID , ctlgd.REV_ADJ_CLASS_TEMP , ctlgd.REC_OFFSET_FLAG , ctlgd.EVENT_ID , ae.ae_header_id , ctlgd.LAST_UPDATE_DATE , ctlgd.LAST_UPDATED_BY , ctlgd.CREATION_DATE , ctlgd.CREATED_BY , ctlgd.request_id , ae.ae_line_num FROM ra_cust_trx_line_gl_dist_all ctlgd, xla_distribution_links lk, xla_ae_headers hd, xla_ae_lines ae WHERE ctlgd.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1 AND ae.application_id = 222 AND lk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL' AND lk.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND lk.ae_header_id = hd.ae_header_id AND hd.ledger_id = ctlgd.set_of_books_id AND ctlgd.event_id IS NOT NULL AND ae.accounting_class_code IN ( 'RECEIVABLE', 'REVENUE', 'REFUND', 'UNBILL', 'UNEARNED_REVENUE', 'TAX', 'CHARGES', 'FREIGHT', 'ROUNDING', 'FV_REC_DR', 'FV_REC_CR', 'ACCRUAL') UNION SELECT ctlgd.CUST_TRX_LINE_GL_DIST_ID , ctlgd.CUSTOMER_TRX_LINE_ID , NULL , NULL , ctlgd.code_combination_id , ctlgd.SET_OF_BOOKS_ID , ctlgd.PERCENT , ctlgd.AMOUNT , ctlgd.GL_DATE , ctlgd.GL_POSTED_DATE , ctlgd.CUST_TRX_LINE_SALESREP_ID , ctlgd.COMMENTS , ctlgd.ATTRIBUTE_CATEGORY , ctlgd.ATTRIBUTE1 , ctlgd.ATTRIBUTE2 , ctlgd.ATTRIBUTE3 , ctlgd.ATTRIBUTE4 , ctlgd.ATTRIBUTE5 , ctlgd.ATTRIBUTE6 , ctlgd.ATTRIBUTE7 , ctlgd.ATTRIBUTE8 , ctlgd.ATTRIBUTE9 , ctlgd.ATTRIBUTE10 , ctlgd.CONCATENATED_SEGMENTS , ctlgd.ORIGINAL_GL_DATE , ctlgd.POST_REQUEST_ID , ctlgd.POSTING_CONTROL_ID , ctlgd.ACCOUNT_CLASS , ctlgd.RA_POST_LOOP_NUMBER , ctlgd.CUSTOMER_TRX_ID , ctlgd.ACCOUNT_SET_FLAG , ctlgd.ACCTD_AMOUNT , ctlgd.USSGL_TRANSACTION_CODE , ctlgd.USSGL_TRANSACTION_CODE_CONTEXT , ctlgd.ATTRIBUTE11 , ctlgd.ATTRIBUTE12 , ctlgd.ATTRIBUTE13 , ctlgd.ATTRIBUTE14 , ctlgd.ATTRIBUTE15 , ctlgd.LATEST_REC_FLAG , ctlgd.ORG_ID , ctlgd.MRC_ACCOUNT_CLASS , ctlgd.MRC_CUSTOMER_TRX_ID , ctlgd.MRC_AMOUNT , ctlgd.MRC_GL_POSTED_DATE , ctlgd.MRC_POSTING_CONTROL_ID , ctlgd.MRC_ACCTD_AMOUNT , ctlgd.COLLECTED_TAX_CCID , ctlgd.COLLECTED_TAX_CONCAT_SEG , ctlgd.REVENUE_ADJUSTMENT_ID , ctlgd.REV_ADJ_CLASS_TEMP , ctlgd.REC_OFFSET_FLAG , ctlgd.EVENT_ID , NULL , ctlgd.LAST_UPDATE_DATE , ctlgd.LAST_UPDATED_BY , ctlgd.CREATION_DATE , ctlgd.CREATED_BY , ctlgd.request_id , null FROM ra_cust_trx_line_gl_dist_all ctlgd WHERE ctlgd.posting_control_id <> -3 AND ctlgd.event_id IS NULL

View Text - HTML Formatted

SELECT CTLGD.CUST_TRX_LINE_GL_DIST_ID
, CTLGD.CUSTOMER_TRX_LINE_ID
, AE.GL_SL_LINK_ID
, AE.GL_SL_LINK_TABLE
, AE.CODE_COMBINATION_ID
, CTLGD.SET_OF_BOOKS_ID
, CTLGD.PERCENT
, DECODE(AE.ACCOUNTING_CLASS_CODE
, 'RECEIVABLE'
, NVL(LK.UNROUNDED_ENTERED_DR
, 0)-NVL(LK.UNROUNDED_ENTERED_CR
, 0)
, 'FV_REC_DR'
, NVL(LK.UNROUNDED_ENTERED_DR
, 0)-NVL(LK.UNROUNDED_ENTERED_CR
, 0)
, 'FV_REC_CR'
, NVL(LK.UNROUNDED_ENTERED_DR
, 0)-NVL(LK.UNROUNDED_ENTERED_CR
, 0)
, NVL(LK.UNROUNDED_ENTERED_CR
, 0)-NVL(LK.UNROUNDED_ENTERED_DR
, 0))
, CTLGD.GL_DATE
, CTLGD.GL_POSTED_DATE
, CTLGD.CUST_TRX_LINE_SALESREP_ID
, CTLGD.COMMENTS
, CTLGD.ATTRIBUTE_CATEGORY
, CTLGD.ATTRIBUTE1
, CTLGD.ATTRIBUTE2
, CTLGD.ATTRIBUTE3
, CTLGD.ATTRIBUTE4
, CTLGD.ATTRIBUTE5
, CTLGD.ATTRIBUTE6
, CTLGD.ATTRIBUTE7
, CTLGD.ATTRIBUTE8
, CTLGD.ATTRIBUTE9
, CTLGD.ATTRIBUTE10
, CTLGD.CONCATENATED_SEGMENTS
, CTLGD.ORIGINAL_GL_DATE
, CTLGD.POST_REQUEST_ID
, CTLGD.POSTING_CONTROL_ID
, DECODE(AE.ACCOUNTING_CLASS_CODE
, 'RECEIVABLE'
, 'REC'
, 'FV_REC_DR'
, 'REC'
, 'FV_REC_CR'
, 'REC'
, CTLGD.ACCOUNT_CLASS)
, CTLGD.RA_POST_LOOP_NUMBER
, CTLGD.CUSTOMER_TRX_ID
, CTLGD.ACCOUNT_SET_FLAG
, DECODE(AE.ACCOUNTING_CLASS_CODE
, 'RECEIVABLE'
, NVL(LK.UNROUNDED_ACCOUNTED_DR
, 0)-NVL(LK.UNROUNDED_ACCOUNTED_CR
, 0)
, 'FV_REC_DR'
, NVL(LK.UNROUNDED_ACCOUNTED_DR
, 0)-NVL(LK.UNROUNDED_ACCOUNTED_CR
, 0)
, 'FV_REC_CR'
, NVL(LK.UNROUNDED_ACCOUNTED_DR
, 0)-NVL(LK.UNROUNDED_ACCOUNTED_CR
, 0)
, NVL(LK.UNROUNDED_ACCOUNTED_CR
, 0)-NVL(LK.UNROUNDED_ACCOUNTED_DR
, 0))
, CTLGD.USSGL_TRANSACTION_CODE
, CTLGD.USSGL_TRANSACTION_CODE_CONTEXT
, CTLGD.ATTRIBUTE11
, CTLGD.ATTRIBUTE12
, CTLGD.ATTRIBUTE13
, CTLGD.ATTRIBUTE14
, CTLGD.ATTRIBUTE15
, CTLGD.LATEST_REC_FLAG
, CTLGD.ORG_ID
, CTLGD.MRC_ACCOUNT_CLASS
, CTLGD.MRC_CUSTOMER_TRX_ID
, CTLGD.MRC_AMOUNT
, CTLGD.MRC_GL_POSTED_DATE
, CTLGD.MRC_POSTING_CONTROL_ID
, CTLGD.MRC_ACCTD_AMOUNT
, CTLGD.COLLECTED_TAX_CCID
, CTLGD.COLLECTED_TAX_CONCAT_SEG
, CTLGD.REVENUE_ADJUSTMENT_ID
, CTLGD.REV_ADJ_CLASS_TEMP
, CTLGD.REC_OFFSET_FLAG
, CTLGD.EVENT_ID
, AE.AE_HEADER_ID
, CTLGD.LAST_UPDATE_DATE
, CTLGD.LAST_UPDATED_BY
, CTLGD.CREATION_DATE
, CTLGD.CREATED_BY
, CTLGD.REQUEST_ID
, AE.AE_LINE_NUM
FROM RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
, XLA_DISTRIBUTION_LINKS LK
, XLA_AE_HEADERS HD
, XLA_AE_LINES AE
WHERE CTLGD.CUST_TRX_LINE_GL_DIST_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND AE.APPLICATION_ID = 222
AND LK.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND LK.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND LK.AE_HEADER_ID = HD.AE_HEADER_ID
AND HD.LEDGER_ID = CTLGD.SET_OF_BOOKS_ID
AND CTLGD.EVENT_ID IS NOT NULL
AND AE.ACCOUNTING_CLASS_CODE IN ( 'RECEIVABLE'
, 'REVENUE'
, 'REFUND'
, 'UNBILL'
, 'UNEARNED_REVENUE'
, 'TAX'
, 'CHARGES'
, 'FREIGHT'
, 'ROUNDING'
, 'FV_REC_DR'
, 'FV_REC_CR'
, 'ACCRUAL') UNION SELECT CTLGD.CUST_TRX_LINE_GL_DIST_ID
, CTLGD.CUSTOMER_TRX_LINE_ID
, NULL
, NULL
, CTLGD.CODE_COMBINATION_ID
, CTLGD.SET_OF_BOOKS_ID
, CTLGD.PERCENT
, CTLGD.AMOUNT
, CTLGD.GL_DATE
, CTLGD.GL_POSTED_DATE
, CTLGD.CUST_TRX_LINE_SALESREP_ID
, CTLGD.COMMENTS
, CTLGD.ATTRIBUTE_CATEGORY
, CTLGD.ATTRIBUTE1
, CTLGD.ATTRIBUTE2
, CTLGD.ATTRIBUTE3
, CTLGD.ATTRIBUTE4
, CTLGD.ATTRIBUTE5
, CTLGD.ATTRIBUTE6
, CTLGD.ATTRIBUTE7
, CTLGD.ATTRIBUTE8
, CTLGD.ATTRIBUTE9
, CTLGD.ATTRIBUTE10
, CTLGD.CONCATENATED_SEGMENTS
, CTLGD.ORIGINAL_GL_DATE
, CTLGD.POST_REQUEST_ID
, CTLGD.POSTING_CONTROL_ID
, CTLGD.ACCOUNT_CLASS
, CTLGD.RA_POST_LOOP_NUMBER
, CTLGD.CUSTOMER_TRX_ID
, CTLGD.ACCOUNT_SET_FLAG
, CTLGD.ACCTD_AMOUNT
, CTLGD.USSGL_TRANSACTION_CODE
, CTLGD.USSGL_TRANSACTION_CODE_CONTEXT
, CTLGD.ATTRIBUTE11
, CTLGD.ATTRIBUTE12
, CTLGD.ATTRIBUTE13
, CTLGD.ATTRIBUTE14
, CTLGD.ATTRIBUTE15
, CTLGD.LATEST_REC_FLAG
, CTLGD.ORG_ID
, CTLGD.MRC_ACCOUNT_CLASS
, CTLGD.MRC_CUSTOMER_TRX_ID
, CTLGD.MRC_AMOUNT
, CTLGD.MRC_GL_POSTED_DATE
, CTLGD.MRC_POSTING_CONTROL_ID
, CTLGD.MRC_ACCTD_AMOUNT
, CTLGD.COLLECTED_TAX_CCID
, CTLGD.COLLECTED_TAX_CONCAT_SEG
, CTLGD.REVENUE_ADJUSTMENT_ID
, CTLGD.REV_ADJ_CLASS_TEMP
, CTLGD.REC_OFFSET_FLAG
, CTLGD.EVENT_ID
, NULL
, CTLGD.LAST_UPDATE_DATE
, CTLGD.LAST_UPDATED_BY
, CTLGD.CREATION_DATE
, CTLGD.CREATED_BY
, CTLGD.REQUEST_ID
, NULL
FROM RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
WHERE CTLGD.POSTING_CONTROL_ID <> -3
AND CTLGD.EVENT_ID IS NULL