VIEW: APPS.AR_TRANSACTIONS_S_V

Source

View Text - Preformatted

SELECT /*+ INDEX(l AR_XLA_LINES_EXTRACT_N1) */ ctaf.attribute1 trx_attr1 ,ctaf.attribute10 trx_attr10 ,ctaf.attribute11 trx_attr11 ,ctaf.attribute12 trx_attr12 ,ctaf.attribute13 trx_attr13 ,ctaf.attribute14 trx_attr14 ,ctaf.attribute15 trx_attr15 ,ctaf.attribute2 trx_attr2 ,ctaf.attribute3 trx_attr3 ,ctaf.attribute4 trx_attr4 ,ctaf.attribute5 trx_attr5 ,ctaf.attribute6 trx_attr6 ,ctaf.attribute7 trx_attr7 ,ctaf.attribute8 trx_attr8 ,ctaf.attribute9 trx_attr9 ,ctaf.attribute_category trx_attr_cat ,ctaf.credit_method_for_installments trx_credit_method_for_install ,ctaf.credit_method_for_rules trx_credit_method_for_rules ,ctaf.customer_trx_id trx_customer_trx_id ,ctaf.doc_sequence_id trx_doc_sequence_id ,ctaf.doc_sequence_value trx_doc_sequence_value ,DECODE(ctaf.doc_sequence_value, NULL, NULL, cttaf.name) TRX_DOC_SEQUENCE_CATEGORY ,ctaf.fob_point trx_fob_point ,ctaf.global_attribute1 trx_global_attr1 ,ctaf.global_attribute10 trx_global_attr10 ,ctaf.global_attribute11 trx_global_attr11 ,ctaf.global_attribute12 trx_global_attr12 ,ctaf.global_attribute13 trx_global_attr13 ,ctaf.global_attribute14 trx_global_attr14 ,ctaf.global_attribute15 trx_global_attr15 ,ctaf.global_attribute16 trx_global_attr16 ,ctaf.global_attribute17 trx_global_attr17 ,ctaf.global_attribute18 trx_global_attr18 ,ctaf.global_attribute19 trx_global_attr19 ,ctaf.global_attribute2 trx_global_attr2 ,ctaf.global_attribute20 trx_global_attr20 ,ctaf.global_attribute3 trx_global_attr3 ,ctaf.global_attribute4 trx_global_attr4 ,ctaf.global_attribute5 trx_global_attr5 ,ctaf.global_attribute6 trx_global_attr6 ,ctaf.global_attribute7 trx_global_attr7 ,ctaf.global_attribute8 trx_global_attr8 ,ctaf.global_attribute9 trx_global_attr9 ,ctaf.global_attribute_category trx_global_attr_cat ,ctaf.interface_header_attribute1 trx_interface_header_attr1 ,ctaf.interface_header_attribute10 trx_interface_header_attr10 ,ctaf.interface_header_attribute11 trx_interface_header_attr11 ,ctaf.interface_header_attribute12 trx_interface_header_attr12 ,ctaf.interface_header_attribute13 trx_interface_header_attr13 ,ctaf.interface_header_attribute14 trx_interface_header_attr14 ,ctaf.interface_header_attribute15 trx_interface_header_attr15 ,ctaf.interface_header_attribute2 trx_interface_header_attr2 ,ctaf.interface_header_attribute3 trx_interface_header_attr3 ,ctaf.interface_header_attribute4 trx_interface_header_attr4 ,ctaf.interface_header_attribute5 trx_interface_header_attr5 ,ctaf.interface_header_attribute6 trx_interface_header_attr6 ,ctaf.interface_header_attribute7 trx_interface_header_attr7 ,ctaf.interface_header_attribute8 trx_interface_header_attr8 ,ctaf.interface_header_attribute9 trx_interface_header_attr9 ,ctaf.interface_header_context trx_interface_header_ct ,ctaf.invoice_currency_code trx_invoice_currency_code ,ctaf.invoicing_rule_id trx_invoicing_rule_id ,ctaf.printing_option trx_printing_option ,ctaf.purchase_order trx_purchase_order ,ctaf.purchase_order_date trx_purchase_order_date ,ctaf.purchase_order_revision trx_purchase_order_revision ,ctaf.reason_code trx_reason_code ,ctaf.ship_date_actual trx_ship_date_actual ,ctaf.status_trx trx_status_trx ,ctaf.term_due_date trx_term_due_date ,ctaf.trx_date trx_date ,ctaf.trx_number trx_number ,ctaf.waybill_number trx_waybill_number ,cttaf.cust_trx_type_id trx_type_cust_trx_type_id ,cttaf.description trx_type_description ,cttaf.drawee_issued_flag trx_type_drawee_issued_flag ,cttaf.global_attribute1 trx_type_global_attr1 ,cttaf.global_attribute10 trx_type_global_attr10 ,cttaf.global_attribute2 trx_type_global_attr2 ,cttaf.global_attribute3 trx_type_global_attr3 ,cttaf.global_attribute4 trx_type_global_attr4 ,cttaf.global_attribute5 trx_type_global_attr5 ,cttaf.global_attribute6 trx_type_global_attr6 ,cttaf.global_attribute7 trx_type_global_attr7 ,cttaf.global_attribute_category trx_type_global_attr_cat ,cttaf.gl_id_rev trx_type_dep_offset_ccid ,cttaf.gl_id_clearing trx_type_clearing_ccid ,cttaf.gl_id_factor trx_type_factor_ccid ,cttaf.gl_id_freight trx_type_freight_ccid ,cttaf.gl_id_rec trx_type_rec_ccid ,cttaf.gl_id_remittance trx_type_remittance_ccid ,cttaf.gl_id_rev trx_type_rev_ccid ,cttaf.gl_id_tax trx_type_tax_ccid ,cttaf.gl_id_unbilled trx_type_unbilled_ccid ,cttaf.gl_id_unearned trx_type_unearned_ccid ,cttaf.gl_id_unpaid_rec trx_type_unpaid_rec_ccid ,cttaf.magnetic_format_code trx_type_mag_format_code ,cttaf.name trx_type_name ,cttaf.post_to_gl trx_type_post_to_gl ,cttaf.type trx_type_class ,tf.description ter_description ,tf.name ter_name ,tf.segment1 ter_segment1 ,tf.segment10 ter_segment10 ,tf.segment11 ter_segment11 ,tf.segment12 ter_segment12 ,tf.segment13 ter_segment13 ,tf.segment14 ter_segment14 ,tf.segment15 ter_segment15 ,tf.segment16 ter_segment16 ,tf.segment17 ter_segment17 ,tf.segment18 ter_segment18 ,tf.segment19 ter_segment19 ,tf.segment2 ter_segment2 ,tf.segment20 ter_segment20 ,tf.segment3 ter_segment3 ,tf.segment4 ter_segment4 ,tf.segment5 ter_segment5 ,tf.segment6 ter_segment6 ,tf.segment7 ter_segment7 ,tf.segment8 ter_segment8 ,tf.segment9 ter_segment9 ,tf.summary_flag ter_summary_flag ,tf.territory_id ter_territory_id ,l.event_id event_id ,l.line_number line_number ,l.ledger_id ledger_id ,'C' xla_party_type ,ctaf.org_id trx_oper_unit ,l.paired_ccid trx_receivable_ccid ,NVL(ctaf.application_id,222) trx_xla_applied_to_app_id ,DECODE(l.event_class_code,'BILL', 'BILLS_RECEIVABLE', 'TRANSACTIONS') trx_entity_code ,ctaf.legal_entity_id ,ctaf.receipt_method_id trx_receipt_method_id ,arm.name trx_receipt_method_name FROM ra_customer_trx_all ctaf ,ra_cust_trx_types_all cttaf ,ra_territories tf ,ra_rules rarhf ,ar_xla_lines_extract l ,ar_receipt_methods arm WHERE ctaf.customer_trx_id = l.customer_trx_id AND ctaf.invoicing_rule_id = rarhf.rule_id (+) AND ctaf.cust_trx_type_id = cttaf.cust_trx_type_id (+) AND ctaf.org_id = cttaf.org_id (+) AND ctaf.territory_id = tf.territory_id (+) AND ctaf.receipt_method_id = arm.receipt_method_id (+) AND ((l.event_class_code IN ('INVOICE','DEBIT_MEMO','DEPOSIT', 'GUARANTEE','CHARGEBACK','ADJUSTMENT','BILL') AND l.level_flag = 'H') OR (l.event_class_code IN ('CREDIT_MEMO','RECEIPT') AND l.level_flag = 'L' AND l.from_to_flag = 'T'))

View Text - HTML Formatted

SELECT /*+ INDEX(L AR_XLA_LINES_EXTRACT_N1) */ CTAF.ATTRIBUTE1 TRX_ATTR1
, CTAF.ATTRIBUTE10 TRX_ATTR10
, CTAF.ATTRIBUTE11 TRX_ATTR11
, CTAF.ATTRIBUTE12 TRX_ATTR12
, CTAF.ATTRIBUTE13 TRX_ATTR13
, CTAF.ATTRIBUTE14 TRX_ATTR14
, CTAF.ATTRIBUTE15 TRX_ATTR15
, CTAF.ATTRIBUTE2 TRX_ATTR2
, CTAF.ATTRIBUTE3 TRX_ATTR3
, CTAF.ATTRIBUTE4 TRX_ATTR4
, CTAF.ATTRIBUTE5 TRX_ATTR5
, CTAF.ATTRIBUTE6 TRX_ATTR6
, CTAF.ATTRIBUTE7 TRX_ATTR7
, CTAF.ATTRIBUTE8 TRX_ATTR8
, CTAF.ATTRIBUTE9 TRX_ATTR9
, CTAF.ATTRIBUTE_CATEGORY TRX_ATTR_CAT
, CTAF.CREDIT_METHOD_FOR_INSTALLMENTS TRX_CREDIT_METHOD_FOR_INSTALL
, CTAF.CREDIT_METHOD_FOR_RULES TRX_CREDIT_METHOD_FOR_RULES
, CTAF.CUSTOMER_TRX_ID TRX_CUSTOMER_TRX_ID
, CTAF.DOC_SEQUENCE_ID TRX_DOC_SEQUENCE_ID
, CTAF.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
, DECODE(CTAF.DOC_SEQUENCE_VALUE
, NULL
, NULL
, CTTAF.NAME) TRX_DOC_SEQUENCE_CATEGORY
, CTAF.FOB_POINT TRX_FOB_POINT
, CTAF.GLOBAL_ATTRIBUTE1 TRX_GLOBAL_ATTR1
, CTAF.GLOBAL_ATTRIBUTE10 TRX_GLOBAL_ATTR10
, CTAF.GLOBAL_ATTRIBUTE11 TRX_GLOBAL_ATTR11
, CTAF.GLOBAL_ATTRIBUTE12 TRX_GLOBAL_ATTR12
, CTAF.GLOBAL_ATTRIBUTE13 TRX_GLOBAL_ATTR13
, CTAF.GLOBAL_ATTRIBUTE14 TRX_GLOBAL_ATTR14
, CTAF.GLOBAL_ATTRIBUTE15 TRX_GLOBAL_ATTR15
, CTAF.GLOBAL_ATTRIBUTE16 TRX_GLOBAL_ATTR16
, CTAF.GLOBAL_ATTRIBUTE17 TRX_GLOBAL_ATTR17
, CTAF.GLOBAL_ATTRIBUTE18 TRX_GLOBAL_ATTR18
, CTAF.GLOBAL_ATTRIBUTE19 TRX_GLOBAL_ATTR19
, CTAF.GLOBAL_ATTRIBUTE2 TRX_GLOBAL_ATTR2
, CTAF.GLOBAL_ATTRIBUTE20 TRX_GLOBAL_ATTR20
, CTAF.GLOBAL_ATTRIBUTE3 TRX_GLOBAL_ATTR3
, CTAF.GLOBAL_ATTRIBUTE4 TRX_GLOBAL_ATTR4
, CTAF.GLOBAL_ATTRIBUTE5 TRX_GLOBAL_ATTR5
, CTAF.GLOBAL_ATTRIBUTE6 TRX_GLOBAL_ATTR6
, CTAF.GLOBAL_ATTRIBUTE7 TRX_GLOBAL_ATTR7
, CTAF.GLOBAL_ATTRIBUTE8 TRX_GLOBAL_ATTR8
, CTAF.GLOBAL_ATTRIBUTE9 TRX_GLOBAL_ATTR9
, CTAF.GLOBAL_ATTRIBUTE_CATEGORY TRX_GLOBAL_ATTR_CAT
, CTAF.INTERFACE_HEADER_ATTRIBUTE1 TRX_INTERFACE_HEADER_ATTR1
, CTAF.INTERFACE_HEADER_ATTRIBUTE10 TRX_INTERFACE_HEADER_ATTR10
, CTAF.INTERFACE_HEADER_ATTRIBUTE11 TRX_INTERFACE_HEADER_ATTR11
, CTAF.INTERFACE_HEADER_ATTRIBUTE12 TRX_INTERFACE_HEADER_ATTR12
, CTAF.INTERFACE_HEADER_ATTRIBUTE13 TRX_INTERFACE_HEADER_ATTR13
, CTAF.INTERFACE_HEADER_ATTRIBUTE14 TRX_INTERFACE_HEADER_ATTR14
, CTAF.INTERFACE_HEADER_ATTRIBUTE15 TRX_INTERFACE_HEADER_ATTR15
, CTAF.INTERFACE_HEADER_ATTRIBUTE2 TRX_INTERFACE_HEADER_ATTR2
, CTAF.INTERFACE_HEADER_ATTRIBUTE3 TRX_INTERFACE_HEADER_ATTR3
, CTAF.INTERFACE_HEADER_ATTRIBUTE4 TRX_INTERFACE_HEADER_ATTR4
, CTAF.INTERFACE_HEADER_ATTRIBUTE5 TRX_INTERFACE_HEADER_ATTR5
, CTAF.INTERFACE_HEADER_ATTRIBUTE6 TRX_INTERFACE_HEADER_ATTR6
, CTAF.INTERFACE_HEADER_ATTRIBUTE7 TRX_INTERFACE_HEADER_ATTR7
, CTAF.INTERFACE_HEADER_ATTRIBUTE8 TRX_INTERFACE_HEADER_ATTR8
, CTAF.INTERFACE_HEADER_ATTRIBUTE9 TRX_INTERFACE_HEADER_ATTR9
, CTAF.INTERFACE_HEADER_CONTEXT TRX_INTERFACE_HEADER_CT
, CTAF.INVOICE_CURRENCY_CODE TRX_INVOICE_CURRENCY_CODE
, CTAF.INVOICING_RULE_ID TRX_INVOICING_RULE_ID
, CTAF.PRINTING_OPTION TRX_PRINTING_OPTION
, CTAF.PURCHASE_ORDER TRX_PURCHASE_ORDER
, CTAF.PURCHASE_ORDER_DATE TRX_PURCHASE_ORDER_DATE
, CTAF.PURCHASE_ORDER_REVISION TRX_PURCHASE_ORDER_REVISION
, CTAF.REASON_CODE TRX_REASON_CODE
, CTAF.SHIP_DATE_ACTUAL TRX_SHIP_DATE_ACTUAL
, CTAF.STATUS_TRX TRX_STATUS_TRX
, CTAF.TERM_DUE_DATE TRX_TERM_DUE_DATE
, CTAF.TRX_DATE TRX_DATE
, CTAF.TRX_NUMBER TRX_NUMBER
, CTAF.WAYBILL_NUMBER TRX_WAYBILL_NUMBER
, CTTAF.CUST_TRX_TYPE_ID TRX_TYPE_CUST_TRX_TYPE_ID
, CTTAF.DESCRIPTION TRX_TYPE_DESCRIPTION
, CTTAF.DRAWEE_ISSUED_FLAG TRX_TYPE_DRAWEE_ISSUED_FLAG
, CTTAF.GLOBAL_ATTRIBUTE1 TRX_TYPE_GLOBAL_ATTR1
, CTTAF.GLOBAL_ATTRIBUTE10 TRX_TYPE_GLOBAL_ATTR10
, CTTAF.GLOBAL_ATTRIBUTE2 TRX_TYPE_GLOBAL_ATTR2
, CTTAF.GLOBAL_ATTRIBUTE3 TRX_TYPE_GLOBAL_ATTR3
, CTTAF.GLOBAL_ATTRIBUTE4 TRX_TYPE_GLOBAL_ATTR4
, CTTAF.GLOBAL_ATTRIBUTE5 TRX_TYPE_GLOBAL_ATTR5
, CTTAF.GLOBAL_ATTRIBUTE6 TRX_TYPE_GLOBAL_ATTR6
, CTTAF.GLOBAL_ATTRIBUTE7 TRX_TYPE_GLOBAL_ATTR7
, CTTAF.GLOBAL_ATTRIBUTE_CATEGORY TRX_TYPE_GLOBAL_ATTR_CAT
, CTTAF.GL_ID_REV TRX_TYPE_DEP_OFFSET_CCID
, CTTAF.GL_ID_CLEARING TRX_TYPE_CLEARING_CCID
, CTTAF.GL_ID_FACTOR TRX_TYPE_FACTOR_CCID
, CTTAF.GL_ID_FREIGHT TRX_TYPE_FREIGHT_CCID
, CTTAF.GL_ID_REC TRX_TYPE_REC_CCID
, CTTAF.GL_ID_REMITTANCE TRX_TYPE_REMITTANCE_CCID
, CTTAF.GL_ID_REV TRX_TYPE_REV_CCID
, CTTAF.GL_ID_TAX TRX_TYPE_TAX_CCID
, CTTAF.GL_ID_UNBILLED TRX_TYPE_UNBILLED_CCID
, CTTAF.GL_ID_UNEARNED TRX_TYPE_UNEARNED_CCID
, CTTAF.GL_ID_UNPAID_REC TRX_TYPE_UNPAID_REC_CCID
, CTTAF.MAGNETIC_FORMAT_CODE TRX_TYPE_MAG_FORMAT_CODE
, CTTAF.NAME TRX_TYPE_NAME
, CTTAF.POST_TO_GL TRX_TYPE_POST_TO_GL
, CTTAF.TYPE TRX_TYPE_CLASS
, TF.DESCRIPTION TER_DESCRIPTION
, TF.NAME TER_NAME
, TF.SEGMENT1 TER_SEGMENT1
, TF.SEGMENT10 TER_SEGMENT10
, TF.SEGMENT11 TER_SEGMENT11
, TF.SEGMENT12 TER_SEGMENT12
, TF.SEGMENT13 TER_SEGMENT13
, TF.SEGMENT14 TER_SEGMENT14
, TF.SEGMENT15 TER_SEGMENT15
, TF.SEGMENT16 TER_SEGMENT16
, TF.SEGMENT17 TER_SEGMENT17
, TF.SEGMENT18 TER_SEGMENT18
, TF.SEGMENT19 TER_SEGMENT19
, TF.SEGMENT2 TER_SEGMENT2
, TF.SEGMENT20 TER_SEGMENT20
, TF.SEGMENT3 TER_SEGMENT3
, TF.SEGMENT4 TER_SEGMENT4
, TF.SEGMENT5 TER_SEGMENT5
, TF.SEGMENT6 TER_SEGMENT6
, TF.SEGMENT7 TER_SEGMENT7
, TF.SEGMENT8 TER_SEGMENT8
, TF.SEGMENT9 TER_SEGMENT9
, TF.SUMMARY_FLAG TER_SUMMARY_FLAG
, TF.TERRITORY_ID TER_TERRITORY_ID
, L.EVENT_ID EVENT_ID
, L.LINE_NUMBER LINE_NUMBER
, L.LEDGER_ID LEDGER_ID
, 'C' XLA_PARTY_TYPE
, CTAF.ORG_ID TRX_OPER_UNIT
, L.PAIRED_CCID TRX_RECEIVABLE_CCID
, NVL(CTAF.APPLICATION_ID
, 222) TRX_XLA_APPLIED_TO_APP_ID
, DECODE(L.EVENT_CLASS_CODE
, 'BILL'
, 'BILLS_RECEIVABLE'
, 'TRANSACTIONS') TRX_ENTITY_CODE
, CTAF.LEGAL_ENTITY_ID
, CTAF.RECEIPT_METHOD_ID TRX_RECEIPT_METHOD_ID
, ARM.NAME TRX_RECEIPT_METHOD_NAME
FROM RA_CUSTOMER_TRX_ALL CTAF
, RA_CUST_TRX_TYPES_ALL CTTAF
, RA_TERRITORIES TF
, RA_RULES RARHF
, AR_XLA_LINES_EXTRACT L
, AR_RECEIPT_METHODS ARM
WHERE CTAF.CUSTOMER_TRX_ID = L.CUSTOMER_TRX_ID
AND CTAF.INVOICING_RULE_ID = RARHF.RULE_ID (+)
AND CTAF.CUST_TRX_TYPE_ID = CTTAF.CUST_TRX_TYPE_ID (+)
AND CTAF.ORG_ID = CTTAF.ORG_ID (+)
AND CTAF.TERRITORY_ID = TF.TERRITORY_ID (+)
AND CTAF.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID (+)
AND ((L.EVENT_CLASS_CODE IN ('INVOICE'
, 'DEBIT_MEMO'
, 'DEPOSIT'
, 'GUARANTEE'
, 'CHARGEBACK'
, 'ADJUSTMENT'
, 'BILL')
AND L.LEVEL_FLAG = 'H') OR (L.EVENT_CLASS_CODE IN ('CREDIT_MEMO'
, 'RECEIPT')
AND L.LEVEL_FLAG = 'L'
AND L.FROM_TO_FLAG = 'T'))