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 hd.application_id = 222 AND lk.ae_header_id = ae.ae_header_id AND lk.ae_line_num = ae.ae_line_num AND ae.ae_header_id = hd.ae_header_id AND hd.ledger_id = ctlgd.set_of_books_id AND hd.event_type_code <> 'MANUAL' 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' ) AND hd.application_id = 222 -- 10058328 and ctlgd.event_id = lk.event_id -- 10058328 UNION ALL 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 HD.APPLICATION_ID = 222
AND LK.AE_HEADER_ID = AE.AE_HEADER_ID
AND LK.AE_LINE_NUM = AE.AE_LINE_NUM
AND AE.AE_HEADER_ID = HD.AE_HEADER_ID
AND HD.LEDGER_ID = CTLGD.SET_OF_BOOKS_ID
AND HD.EVENT_TYPE_CODE <> 'MANUAL'
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' )
AND HD.APPLICATION_ID = 222 -- 10058328
AND CTLGD.EVENT_ID = LK.EVENT_ID -- 10058328 UNION ALL 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