AR_XLA_CTLGD_LINES

Details

Schema: FUSION
Object owner: AR
Object type: VIEW

Columns

Name
CUST_TRX_LINE_GL_DIST_ID CUSTOMER_TRX_LINE_ID GL_SL_LINK_ID GL_SL_LINK_TABLE CODE_COMBINATION_ID SET_OF_BOOKS_ID PERCENT AMOUNT GL_DATE GL_POSTED_DATE CUST_TRX_LINE_SALESREP_ID COMMENTS ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 CONCATENATED_SEGMENTS ORIGINAL_GL_DATE POST_REQUEST_ID POSTING_CONTROL_ID ACCOUNT_CLASS RA_POST_LOOP_NUMBER CUSTOMER_TRX_ID ACCOUNT_SET_FLAG ACCTD_AMOUNT ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 LATEST_REC_FLAG ORG_ID COLLECTED_TAX_CCID COLLECTED_TAX_CONCAT_SEG REVENUE_ADJUSTMENT_ID REV_ADJ_CLASS_TEMP REC_OFFSET_FLAG EVENT_ID AE_HEADER_ID LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY REQUEST_ID

Name

CUST_TRX_LINE_GL_DIST_ID

CUSTOMER_TRX_LINE_ID

GL_SL_LINK_ID

GL_SL_LINK_TABLE

CODE_COMBINATION_ID

SET_OF_BOOKS_ID

PERCENT

AMOUNT

GL_DATE

GL_POSTED_DATE

CUST_TRX_LINE_SALESREP_ID

COMMENTS

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

CONCATENATED_SEGMENTS

ORIGINAL_GL_DATE

POST_REQUEST_ID

POSTING_CONTROL_ID

ACCOUNT_CLASS

RA_POST_LOOP_NUMBER

CUSTOMER_TRX_ID

ACCOUNT_SET_FLAG

ACCTD_AMOUNT

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

LATEST_REC_FLAG

ORG_ID

COLLECTED_TAX_CCID

COLLECTED_TAX_CONCAT_SEG

REVENUE_ADJUSTMENT_ID

REV_ADJ_CLASS_TEMP

REC_OFFSET_FLAG

EVENT_ID

AE_HEADER_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

REQUEST_ID

Query

SQL_Statement
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)) 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 , DECODE(ae.accounting_class_code, 'RECEIVABLE','REC', 'FV_REC_DR' ,'REC', 'FV_REC_CR' ,'REC', ctlgd.ACCOUNT_CLASS) 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)) ACCTD_AMOUNT, ctlgd.ATTRIBUTE11 , ctlgd.ATTRIBUTE12 , ctlgd.ATTRIBUTE13 , ctlgd.ATTRIBUTE14 , ctlgd.ATTRIBUTE15 , ctlgd.LATEST_REC_FLAG , ctlgd.ORG_ID , 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 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.event_type_code <> 'MANUAL' 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 ALL SELECT ctlgd.CUST_TRX_LINE_GL_DIST_ID , ctlgd.CUSTOMER_TRX_LINE_ID , null GL_SL_LINK_ID, null GL_SL_LINK_TABLE, 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.ATTRIBUTE11 , ctlgd.ATTRIBUTE12 , ctlgd.ATTRIBUTE13 , ctlgd.ATTRIBUTE14 , ctlgd.ATTRIBUTE15 , ctlgd.LATEST_REC_FLAG , ctlgd.ORG_ID , 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 FROM ra_cust_trx_line_gl_dist_all ctlgd WHERE ctlgd.posting_control_id <> -3 AND ctlgd.event_id IS NULL

SQL_Statement

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)) 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 ,

DECODE(ae.accounting_class_code,

'RECEIVABLE','REC', 'FV_REC_DR' ,'REC',

'FV_REC_CR' ,'REC', ctlgd.ACCOUNT_CLASS) 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)) ACCTD_AMOUNT,

ctlgd.ATTRIBUTE11 ,

ctlgd.ATTRIBUTE12 ,

ctlgd.ATTRIBUTE13 ,

ctlgd.ATTRIBUTE14 ,

ctlgd.ATTRIBUTE15 ,

ctlgd.LATEST_REC_FLAG ,

ctlgd.ORG_ID ,

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

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.event_type_code <> 'MANUAL'

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 ALL