VIEW: APPS.RA_CUSTOMER_TRX_MRC_V

Source

View Text - Preformatted

SELECT PRIMARY.customer_trx_id, mc.set_of_books_id, mc.exchange_rate_type, mc.exchange_date, mc.exchange_rate, PRIMARY.last_update_date, PRIMARY.last_updated_by, PRIMARY.creation_date, PRIMARY.created_by, PRIMARY.last_update_login, PRIMARY.trx_number, PRIMARY.cust_trx_type_id, PRIMARY.trx_date, PRIMARY.bill_to_contact_id, PRIMARY.batch_id, PRIMARY.batch_source_id, PRIMARY.reason_code, PRIMARY.sold_to_customer_id, PRIMARY.sold_to_contact_id, PRIMARY.sold_to_site_use_id, PRIMARY.bill_to_customer_id, PRIMARY.bill_to_site_use_id, PRIMARY.ship_to_customer_id, PRIMARY.ship_to_contact_id, PRIMARY.ship_to_site_use_id, PRIMARY.shipment_id, PRIMARY.remit_to_address_id, PRIMARY.term_id, PRIMARY.term_due_date, PRIMARY.previous_customer_trx_id, PRIMARY.primary_salesrep_id, PRIMARY.printing_original_date, PRIMARY.printing_last_printed, PRIMARY.printing_option, PRIMARY.printing_count, PRIMARY.printing_pending, PRIMARY.purchase_order, PRIMARY.purchase_order_revision, PRIMARY.purchase_order_date, PRIMARY.customer_reference, PRIMARY.customer_reference_date, PRIMARY.comments, PRIMARY.internal_notes, PRIMARY.territory_id, PRIMARY.invoice_currency_code, PRIMARY.initial_customer_trx_id, PRIMARY.agreement_id, PRIMARY.end_date_commitment, PRIMARY.start_date_commitment, PRIMARY.last_printed_sequence_num, PRIMARY.attribute_category, PRIMARY.attribute1, PRIMARY.attribute2, PRIMARY.attribute3, PRIMARY.attribute4, PRIMARY.attribute5, PRIMARY.attribute6, PRIMARY.attribute7, PRIMARY.attribute8, PRIMARY.attribute9, PRIMARY.attribute10, PRIMARY.orig_system_batch_name, PRIMARY.post_request_id, PRIMARY.request_id, PRIMARY.program_application_id, PRIMARY.program_id, PRIMARY.program_update_date, PRIMARY.finance_charges, PRIMARY.complete_flag, PRIMARY.posting_control_id, PRIMARY.bill_to_address_id, PRIMARY.ra_post_loop_number, PRIMARY.ship_to_address_id, PRIMARY.credit_method_for_rules, PRIMARY.credit_method_for_installments, PRIMARY.receipt_method_id, PRIMARY.attribute11, PRIMARY.attribute12, PRIMARY.attribute13, PRIMARY.attribute14, PRIMARY.attribute15, PRIMARY.related_customer_trx_id, PRIMARY.invoicing_rule_id, PRIMARY.ship_via, PRIMARY.ship_date_actual, PRIMARY.waybill_number, PRIMARY.fob_point, PRIMARY.customer_bank_account_id, PRIMARY.interface_header_attribute1, PRIMARY.interface_header_attribute2, PRIMARY.interface_header_attribute3, PRIMARY.interface_header_attribute4, PRIMARY.interface_header_attribute5, PRIMARY.interface_header_attribute6, PRIMARY.interface_header_attribute7, PRIMARY.interface_header_attribute8, PRIMARY.interface_header_context, PRIMARY.default_ussgl_trx_code_context, PRIMARY.interface_header_attribute10, PRIMARY.interface_header_attribute11, PRIMARY.interface_header_attribute12, PRIMARY.interface_header_attribute13, PRIMARY.interface_header_attribute14, PRIMARY.interface_header_attribute15, PRIMARY.interface_header_attribute9, PRIMARY.default_ussgl_transaction_code, PRIMARY.recurred_from_trx_number, PRIMARY.status_trx, PRIMARY.doc_sequence_id, PRIMARY.doc_sequence_value, PRIMARY.paying_customer_id, PRIMARY.paying_site_use_id, PRIMARY.related_batch_source_id, PRIMARY.default_tax_exempt_flag, PRIMARY.created_from, PRIMARY.org_id, PRIMARY.wh_update_date, PRIMARY.global_attribute1, PRIMARY.global_attribute2, PRIMARY.global_attribute3, PRIMARY.global_attribute4, PRIMARY.global_attribute5, PRIMARY.global_attribute6, PRIMARY.global_attribute7, PRIMARY.global_attribute8, PRIMARY.global_attribute9, PRIMARY.global_attribute10, PRIMARY.global_attribute11, PRIMARY.global_attribute12, PRIMARY.global_attribute13, PRIMARY.global_attribute14, PRIMARY.global_attribute15, PRIMARY.global_attribute16, PRIMARY.global_attribute17, PRIMARY.global_attribute18, PRIMARY.global_attribute19, PRIMARY.global_attribute20, PRIMARY.global_attribute21, PRIMARY.global_attribute22, PRIMARY.global_attribute23, PRIMARY.global_attribute24, PRIMARY.global_attribute25, PRIMARY.global_attribute26, PRIMARY.global_attribute27, PRIMARY.global_attribute28, PRIMARY.global_attribute29, PRIMARY.global_attribute30, PRIMARY.global_attribute_category, PRIMARY.edi_processed_flag, PRIMARY.edi_processed_status, PRIMARY.address_verification_code, PRIMARY.approval_code, PRIMARY.old_trx_number, PRIMARY.payment_server_order_num, PRIMARY.br_amount, PRIMARY.br_unpaid_flag, PRIMARY.br_on_hold_flag, PRIMARY.drawee_id, PRIMARY.drawee_contact_id, PRIMARY.drawee_site_use_id, PRIMARY.drawee_bank_account_id, PRIMARY.remittance_bank_account_id, PRIMARY.override_remit_account_flag, PRIMARY.special_instructions, PRIMARY.remittance_batch_id, PRIMARY.prepayment_flag, PRIMARY.ct_reference, PRIMARY.contract_id, PRIMARY.bill_template_id, PRIMARY.REVERSED_CASH_RECEIPT_ID FROM ra_customer_trx_all PRIMARY, ra_mc_customer_trx mc WHERE PRIMARY.customer_trx_id = mc.customer_trx_id AND NVL(PRIMARY.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) AND mc.set_of_books_id = NVL (TO_NUMBER (RTRIM (SUBSTRB (USERENV ('CLIENT_INFO'), 45, 10), ' ')), -99)

View Text - HTML Formatted

SELECT PRIMARY.CUSTOMER_TRX_ID
, MC.SET_OF_BOOKS_ID
, MC.EXCHANGE_RATE_TYPE
, MC.EXCHANGE_DATE
, MC.EXCHANGE_RATE
, PRIMARY.LAST_UPDATE_DATE
, PRIMARY.LAST_UPDATED_BY
, PRIMARY.CREATION_DATE
, PRIMARY.CREATED_BY
, PRIMARY.LAST_UPDATE_LOGIN
, PRIMARY.TRX_NUMBER
, PRIMARY.CUST_TRX_TYPE_ID
, PRIMARY.TRX_DATE
, PRIMARY.BILL_TO_CONTACT_ID
, PRIMARY.BATCH_ID
, PRIMARY.BATCH_SOURCE_ID
, PRIMARY.REASON_CODE
, PRIMARY.SOLD_TO_CUSTOMER_ID
, PRIMARY.SOLD_TO_CONTACT_ID
, PRIMARY.SOLD_TO_SITE_USE_ID
, PRIMARY.BILL_TO_CUSTOMER_ID
, PRIMARY.BILL_TO_SITE_USE_ID
, PRIMARY.SHIP_TO_CUSTOMER_ID
, PRIMARY.SHIP_TO_CONTACT_ID
, PRIMARY.SHIP_TO_SITE_USE_ID
, PRIMARY.SHIPMENT_ID
, PRIMARY.REMIT_TO_ADDRESS_ID
, PRIMARY.TERM_ID
, PRIMARY.TERM_DUE_DATE
, PRIMARY.PREVIOUS_CUSTOMER_TRX_ID
, PRIMARY.PRIMARY_SALESREP_ID
, PRIMARY.PRINTING_ORIGINAL_DATE
, PRIMARY.PRINTING_LAST_PRINTED
, PRIMARY.PRINTING_OPTION
, PRIMARY.PRINTING_COUNT
, PRIMARY.PRINTING_PENDING
, PRIMARY.PURCHASE_ORDER
, PRIMARY.PURCHASE_ORDER_REVISION
, PRIMARY.PURCHASE_ORDER_DATE
, PRIMARY.CUSTOMER_REFERENCE
, PRIMARY.CUSTOMER_REFERENCE_DATE
, PRIMARY.COMMENTS
, PRIMARY.INTERNAL_NOTES
, PRIMARY.TERRITORY_ID
, PRIMARY.INVOICE_CURRENCY_CODE
, PRIMARY.INITIAL_CUSTOMER_TRX_ID
, PRIMARY.AGREEMENT_ID
, PRIMARY.END_DATE_COMMITMENT
, PRIMARY.START_DATE_COMMITMENT
, PRIMARY.LAST_PRINTED_SEQUENCE_NUM
, PRIMARY.ATTRIBUTE_CATEGORY
, PRIMARY.ATTRIBUTE1
, PRIMARY.ATTRIBUTE2
, PRIMARY.ATTRIBUTE3
, PRIMARY.ATTRIBUTE4
, PRIMARY.ATTRIBUTE5
, PRIMARY.ATTRIBUTE6
, PRIMARY.ATTRIBUTE7
, PRIMARY.ATTRIBUTE8
, PRIMARY.ATTRIBUTE9
, PRIMARY.ATTRIBUTE10
, PRIMARY.ORIG_SYSTEM_BATCH_NAME
, PRIMARY.POST_REQUEST_ID
, PRIMARY.REQUEST_ID
, PRIMARY.PROGRAM_APPLICATION_ID
, PRIMARY.PROGRAM_ID
, PRIMARY.PROGRAM_UPDATE_DATE
, PRIMARY.FINANCE_CHARGES
, PRIMARY.COMPLETE_FLAG
, PRIMARY.POSTING_CONTROL_ID
, PRIMARY.BILL_TO_ADDRESS_ID
, PRIMARY.RA_POST_LOOP_NUMBER
, PRIMARY.SHIP_TO_ADDRESS_ID
, PRIMARY.CREDIT_METHOD_FOR_RULES
, PRIMARY.CREDIT_METHOD_FOR_INSTALLMENTS
, PRIMARY.RECEIPT_METHOD_ID
, PRIMARY.ATTRIBUTE11
, PRIMARY.ATTRIBUTE12
, PRIMARY.ATTRIBUTE13
, PRIMARY.ATTRIBUTE14
, PRIMARY.ATTRIBUTE15
, PRIMARY.RELATED_CUSTOMER_TRX_ID
, PRIMARY.INVOICING_RULE_ID
, PRIMARY.SHIP_VIA
, PRIMARY.SHIP_DATE_ACTUAL
, PRIMARY.WAYBILL_NUMBER
, PRIMARY.FOB_POINT
, PRIMARY.CUSTOMER_BANK_ACCOUNT_ID
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE1
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE2
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE3
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE4
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE5
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE6
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE7
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE8
, PRIMARY.INTERFACE_HEADER_CONTEXT
, PRIMARY.DEFAULT_USSGL_TRX_CODE_CONTEXT
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE10
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE11
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE12
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE13
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE14
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE15
, PRIMARY.INTERFACE_HEADER_ATTRIBUTE9
, PRIMARY.DEFAULT_USSGL_TRANSACTION_CODE
, PRIMARY.RECURRED_FROM_TRX_NUMBER
, PRIMARY.STATUS_TRX
, PRIMARY.DOC_SEQUENCE_ID
, PRIMARY.DOC_SEQUENCE_VALUE
, PRIMARY.PAYING_CUSTOMER_ID
, PRIMARY.PAYING_SITE_USE_ID
, PRIMARY.RELATED_BATCH_SOURCE_ID
, PRIMARY.DEFAULT_TAX_EXEMPT_FLAG
, PRIMARY.CREATED_FROM
, PRIMARY.ORG_ID
, PRIMARY.WH_UPDATE_DATE
, PRIMARY.GLOBAL_ATTRIBUTE1
, PRIMARY.GLOBAL_ATTRIBUTE2
, PRIMARY.GLOBAL_ATTRIBUTE3
, PRIMARY.GLOBAL_ATTRIBUTE4
, PRIMARY.GLOBAL_ATTRIBUTE5
, PRIMARY.GLOBAL_ATTRIBUTE6
, PRIMARY.GLOBAL_ATTRIBUTE7
, PRIMARY.GLOBAL_ATTRIBUTE8
, PRIMARY.GLOBAL_ATTRIBUTE9
, PRIMARY.GLOBAL_ATTRIBUTE10
, PRIMARY.GLOBAL_ATTRIBUTE11
, PRIMARY.GLOBAL_ATTRIBUTE12
, PRIMARY.GLOBAL_ATTRIBUTE13
, PRIMARY.GLOBAL_ATTRIBUTE14
, PRIMARY.GLOBAL_ATTRIBUTE15
, PRIMARY.GLOBAL_ATTRIBUTE16
, PRIMARY.GLOBAL_ATTRIBUTE17
, PRIMARY.GLOBAL_ATTRIBUTE18
, PRIMARY.GLOBAL_ATTRIBUTE19
, PRIMARY.GLOBAL_ATTRIBUTE20
, PRIMARY.GLOBAL_ATTRIBUTE21
, PRIMARY.GLOBAL_ATTRIBUTE22
, PRIMARY.GLOBAL_ATTRIBUTE23
, PRIMARY.GLOBAL_ATTRIBUTE24
, PRIMARY.GLOBAL_ATTRIBUTE25
, PRIMARY.GLOBAL_ATTRIBUTE26
, PRIMARY.GLOBAL_ATTRIBUTE27
, PRIMARY.GLOBAL_ATTRIBUTE28
, PRIMARY.GLOBAL_ATTRIBUTE29
, PRIMARY.GLOBAL_ATTRIBUTE30
, PRIMARY.GLOBAL_ATTRIBUTE_CATEGORY
, PRIMARY.EDI_PROCESSED_FLAG
, PRIMARY.EDI_PROCESSED_STATUS
, PRIMARY.ADDRESS_VERIFICATION_CODE
, PRIMARY.APPROVAL_CODE
, PRIMARY.OLD_TRX_NUMBER
, PRIMARY.PAYMENT_SERVER_ORDER_NUM
, PRIMARY.BR_AMOUNT
, PRIMARY.BR_UNPAID_FLAG
, PRIMARY.BR_ON_HOLD_FLAG
, PRIMARY.DRAWEE_ID
, PRIMARY.DRAWEE_CONTACT_ID
, PRIMARY.DRAWEE_SITE_USE_ID
, PRIMARY.DRAWEE_BANK_ACCOUNT_ID
, PRIMARY.REMITTANCE_BANK_ACCOUNT_ID
, PRIMARY.OVERRIDE_REMIT_ACCOUNT_FLAG
, PRIMARY.SPECIAL_INSTRUCTIONS
, PRIMARY.REMITTANCE_BATCH_ID
, PRIMARY.PREPAYMENT_FLAG
, PRIMARY.CT_REFERENCE
, PRIMARY.CONTRACT_ID
, PRIMARY.BILL_TEMPLATE_ID
, PRIMARY.REVERSED_CASH_RECEIPT_ID
FROM RA_CUSTOMER_TRX_ALL PRIMARY
, RA_MC_CUSTOMER_TRX MC
WHERE PRIMARY.CUSTOMER_TRX_ID = MC.CUSTOMER_TRX_ID
AND NVL(PRIMARY.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND MC.SET_OF_BOOKS_ID = NVL (TO_NUMBER (RTRIM (SUBSTRB (USERENV ('CLIENT_INFO')
, 45
, 10)
, ' '))
, -99)