RA_CUSTOMER_TRX_RM_V

Details

• Schema: FUSION

• Object owner: VRM

• Object type: VIEW

Columns

Name

CUSTOMER_TRX_ID DOCUMENT_TYPE_CODE TRX_DATE TRX_NUMBER TYPE CREATION_DATE LAST_UPDATE_DATE INVOICE_CURRENCY_CODE PRIMARY_RESOURCE_SALESREP_ID ORG_ID SET_OF_BOOKS_ID LEGAL_ENTITY_ID PURCHASE_ORDER EXCHANGE_RATE EXCHANGE_RATE_TYPE EXCHANGE_DATE TERM_ID INVOICING_RULE_ID BILL_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID CONTRACT_ID AGREEMENT_ID ACCOUNTING_EFFECT_FLAG SOURCE_TYPE_CODE ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE_CATEGORY INTERFACE_HEADER_CONTEXT INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE15 BATCH_SOURCE_SEQ_ID CUST_TRX_TYPE_SEQ_ID TRX_CLASS PREVIOUS_CUSTOMER_TRX_ID

Query

SQL_Statement

SELECT ct.CUSTOMER_TRX_ID, 'TRANSACTIONS' DOCUMENT_TYPE_CODE, ct.TRX_DATE, ct.TRX_NUMBER, DECODE(ctt.TYPE,'INV','Invoice', 'CM','Credit Memo' ) TYPE, ct.CREATION_DATE, ct.LAST_UPDATE_DATE, ct.INVOICE_CURRENCY_CODE, ct.PRIMARY_RESOURCE_SALESREP_ID, ct.ORG_ID, ct.SET_OF_BOOKS_ID, ct.LEGAL_ENTITY_ID, ct.PURCHASE_ORDER, ct.EXCHANGE_RATE, ct.EXCHANGE_RATE_TYPE, ct.EXCHANGE_DATE, ct.TERM_ID, ct.INVOICING_RULE_ID INVOICING_RULE_ID, ct.BILL_TO_SITE_USE_ID, ct.SHIP_TO_SITE_USE_ID, ct.CONTRACT_ID, ct.AGREEMENT_ID, 'N' ACCOUNTING_EFFECT_FLAG, 'EXTERNAL' SOURCE_TYPE_CODE, ct.ATTRIBUTE1, ct.ATTRIBUTE2, ct.ATTRIBUTE3, ct.ATTRIBUTE4, ct.ATTRIBUTE5, ct.ATTRIBUTE6, ct.ATTRIBUTE7, ct.ATTRIBUTE8, ct.ATTRIBUTE9, ct.ATTRIBUTE10, ct.ATTRIBUTE11, ct.ATTRIBUTE12, ct.ATTRIBUTE13, ct.ATTRIBUTE14, ct.ATTRIBUTE15, ct.ATTRIBUTE_CATEGORY, ct.INTERFACE_HEADER_CONTEXT, ct.INTERFACE_HEADER_ATTRIBUTE1, ct.INTERFACE_HEADER_ATTRIBUTE2, ct.INTERFACE_HEADER_ATTRIBUTE3, ct.INTERFACE_HEADER_ATTRIBUTE4, ct.INTERFACE_HEADER_ATTRIBUTE5, ct.INTERFACE_HEADER_ATTRIBUTE6, ct.INTERFACE_HEADER_ATTRIBUTE7, ct.INTERFACE_HEADER_ATTRIBUTE8, ct.INTERFACE_HEADER_ATTRIBUTE9, ct.INTERFACE_HEADER_ATTRIBUTE10, ct.INTERFACE_HEADER_ATTRIBUTE11, ct.INTERFACE_HEADER_ATTRIBUTE12, ct.INTERFACE_HEADER_ATTRIBUTE13, ct.INTERFACE_HEADER_ATTRIBUTE14, ct.INTERFACE_HEADER_ATTRIBUTE15, ct.BATCH_SOURCE_SEQ_ID, ct.CUST_TRX_TYPE_SEQ_ID, ct.trx_class, ct.PREVIOUS_CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL ct, RA_CUST_TRX_TYPES_ALL ctt, RA_CUST_TRX_LINE_GL_DIST_ALL gld WHERE ct.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id AND ct.complete_flag = 'Y' AND ctt.post_to_gl = 'Y' AND ctt.accounting_affect_flag = 'Y' AND ct.trx_class IN ('INV','ONACC') AND gld.customer_trx_id = ct.customer_trx_id AND gld.account_class = 'REC' AND gld.account_set_flag = 'N' AND gld.posting_control_id <> -3 AND gld.latest_rec_flag = 'Y' UNION ALL SELECT ct.CUSTOMER_TRX_ID, 'TRANSACTIONS' DOCUMENT_TYPE_CODE, ct.TRX_DATE, ct.TRX_NUMBER, DECODE(ctt.TYPE,'INV','Invoice', 'CM','Credit Memo' ) TYPE, ct.CREATION_DATE, ct.LAST_UPDATE_DATE, ct.INVOICE_CURRENCY_CODE, ct.PRIMARY_RESOURCE_SALESREP_ID, ct.ORG_ID, ct.SET_OF_BOOKS_ID, ct.LEGAL_ENTITY_ID, ct.PURCHASE_ORDER, ct.EXCHANGE_RATE, ct.EXCHANGE_RATE_TYPE, ct.EXCHANGE_DATE, ct.TERM_ID, ct.INVOICING_RULE_ID INVOICING_RULE_ID, ct.BILL_TO_SITE_USE_ID, ct.SHIP_TO_SITE_USE_ID, ct.CONTRACT_ID, ct.AGREEMENT_ID, 'N' ACCOUNTING_EFFECT_FLAG, 'EXTERNAL' SOURCE_TYPE_CODE, ct.ATTRIBUTE1, ct.ATTRIBUTE2, ct.ATTRIBUTE3, ct.ATTRIBUTE4, ct.ATTRIBUTE5, ct.ATTRIBUTE6, ct.ATTRIBUTE7, ct.ATTRIBUTE8, ct.ATTRIBUTE9, ct.ATTRIBUTE10, ct.ATTRIBUTE11, ct.ATTRIBUTE12, ct.ATTRIBUTE13, ct.ATTRIBUTE14, ct.ATTRIBUTE15, ct.ATTRIBUTE_CATEGORY, ct.INTERFACE_HEADER_CONTEXT, ct.INTERFACE_HEADER_ATTRIBUTE1, ct.INTERFACE_HEADER_ATTRIBUTE2, ct.INTERFACE_HEADER_ATTRIBUTE3, ct.INTERFACE_HEADER_ATTRIBUTE4, ct.INTERFACE_HEADER_ATTRIBUTE5, ct.INTERFACE_HEADER_ATTRIBUTE6, ct.INTERFACE_HEADER_ATTRIBUTE7, ct.INTERFACE_HEADER_ATTRIBUTE8, ct.INTERFACE_HEADER_ATTRIBUTE9, ct.INTERFACE_HEADER_ATTRIBUTE10, ct.INTERFACE_HEADER_ATTRIBUTE11, ct.INTERFACE_HEADER_ATTRIBUTE12, ct.INTERFACE_HEADER_ATTRIBUTE13, ct.INTERFACE_HEADER_ATTRIBUTE14, ct.INTERFACE_HEADER_ATTRIBUTE15, ct.BATCH_SOURCE_SEQ_ID, ct.CUST_TRX_TYPE_SEQ_ID, ct.trx_class, ct.PREVIOUS_CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_ALL ct, RA_CUSTOMER_TRX_ALL ct_inv, RA_CUST_TRX_TYPES_ALL ctt, RA_CUST_TRX_LINE_GL_DIST_ALL gld WHERE ct.cust_trx_type_seq_id = ctt.cust_trx_type_seq_id AND ct.complete_flag = 'Y' AND ctt.post_to_gl = 'Y' AND ctt.accounting_affect_flag = 'Y' AND ct.trx_class = 'CM' AND ct.previous_customer_trx_id = ct_inv.customer_trx_id AND ct_inv.rev_rec_application in ('VRM_BILLING_TRANSITION','VRM_BILLING') AND gld.customer_trx_id = ct.customer_trx_id AND gld.account_class = 'REC' AND gld.account_set_flag = 'N' AND gld.posting_control_id <> -3 AND gld.latest_rec_flag = 'Y'