EXM_CREDIT_CARD_TRXNS
This table contains the details of corporate card transactions and summary details as provided by the card issuer. .
Details
-
Schema: FUSION
-
Object owner: EXM
-
Object type: TABLE
-
Tablespace: APPS_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
EXM_CREDIT_CARD_TRXNS_PK |
CREDIT_CARD_TRXN_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| CREDIT_CARD_TRXN_ID | NUMBER | 18 | Yes | Unique identifier of the corporate card transaction. | |
| MC_ADJUSTMENT_REASON_CODE | VARCHAR2 | 80 | Adjustment reason code provided by MasterCard. | ||
| REQ_CNTL_ACCT_NUMBER | VARCHAR2 | 20 | Requesting control account number provided by AMEX for each of the transactions. | ||
| VALIDATE_CODE | VARCHAR2 | 25 | Yes | Status indicating whether the corporate card transaction is validated and the error codes for invalid transactions. | |
| TRANSACTION_STATUS | VARCHAR2 | 25 | Status of a corporate card transaction after validation, such as Valid and Invalid. | ||
| CARD_PROGRAM_ID | NUMBER | 18 | Yes | Identifier of a corporate card program associated with the corporate card. | |
| COMPANY_ACCOUNT_ID | NUMBER | 18 | Identifier of the company account associated with the corporate card. | ||
| CARD_ID | NUMBER | 18 | Identifier of the corporate card in Oracle Fusion Expenses. | ||
| CC_NETWORK_REFERENCE_NUMBER | VARCHAR2 | 240 | Unique identifier generated by the payment network for each corporate card authorization request. | ||
| CARD_NUMBER | VARCHAR2 | 80 | Corporate card number. Stored only when card number is invalid. | ||
| REFERENCE_NUMBER | VARCHAR2 | 240 | Yes | Corporate card transaction reference number provided by the card issuer in the charge file. | |
| AUTH_TRXN_NUMBER | VARCHAR2 | 240 | Authorization identifier provided by the corporate card issuer. | ||
| EMPLOYEE_NUMBER | VARCHAR2 | 20 | Employee number in the corporate card transaction file for a transaction charged to a company card. | ||
| TRANSACTION_TYPE | VARCHAR2 | 80 | Type of transaction, such as Purchase, Payment, and Finance Charges. | ||
| TRANSACTION_DATE | DATE | Date when the transaction occurred. | |||
| TRANSACTION_AMOUNT | NUMBER | Amount charged at the time of transaction. | |||
| DEBIT_FLAG | VARCHAR2 | 1 | Indicator of whether this transaction is a debit or credit. | ||
| DESCRIPTION | VARCHAR2 | 240 | Description of the transaction. | ||
| BILLED_DATE | DATE | Date of the statement on which the transaction appears. | |||
| BILLED_AMOUNT | NUMBER | Amount billed to the corporate card account. | |||
| ORIGINAL_BILLED_AMOUNT | NUMBER | Billed amount provided by the card issuer in the feed file. | |||
| BILLED_DECIMAL | NUMBER | 1 | Number of decimal places used to display the billed amount. | ||
| BILLED_CURRENCY_CODE | VARCHAR2 | 15 | Currency of the amount billed to the corporate card account. | ||
| ORIGINAL_BILLED_CURRENCY_CODE | VARCHAR2 | 15 | Billed currency provided by the card issuer in the feed file. | ||
| POSTED_DATE | DATE | Date the transaction was posted to the statement. | |||
| POSTED_AMOUNT | NUMBER | Amount of the transaction posted to the statement. | |||
| POSTED_DECIMAL | NUMBER | 1 | Number of decimal places used to display the posted amount. | ||
| POSTED_CURRENCY_CODE | VARCHAR2 | 15 | Currency of the transaction amount. | ||
| CURRENCY_CONVERSION_EXPONENT | NUMBER | 1 | Number of decimal places used to display currency conversion rate. | ||
| CURRENCY_CONVERSION_RATE | NUMBER | Rate used to convert the amount in billed currency to the payment currency. | |||
| ORIGINAL_CURR_CONV_RATE | NUMBER | Rate used for converting transaction amount or posted amount to billed amount. | |||
| MIS_INDUSTRY_CODE | VARCHAR2 | 80 | Management Information System code that is specific to American Express, which is provided by the card issuer and identifies the type of transaction. | ||
| SIC_CODE | NUMBER | 18 | Standard Industrial Classification is a United States government system for classifying industries by a four-digit code. | ||
| MERCHANT_TAX_ID | VARCHAR2 | 80 | Taxpayer identifier of the merchant who accepted the corporate card for this transaction. | ||
| MERCHANT_REFERENCE | VARCHAR2 | 80 | Purchase identification of a transaction provided by the merchant at the point of sale. | ||
| MERCHANT_NAME1 | VARCHAR2 | 80 | Name of merchant or establishment. | ||
| MERCHANT_NAME2 | VARCHAR2 | 80 | Name of merchant or establishment. | ||
| MERCHANT_ADDRESS1 | VARCHAR2 | 80 | Address of merchant or establishment. | ||
| MERCHANT_ADDRESS2 | VARCHAR2 | 80 | Address of merchant or establishment. | ||
| MERCHANT_ADDRESS3 | VARCHAR2 | 80 | Address of merchant or establishment. | ||
| MERCHANT_ADDRESS4 | VARCHAR2 | 80 | Address of merchant or establishment. | ||
| MERCHANT_CITY | VARCHAR2 | 80 | City where merchant or establishment is located. | ||
| MERCHANT_PROVINCE_STATE | VARCHAR2 | 80 | Province where merchant or establishment is located. | ||
| MERCHANT_POSTAL_CODE | VARCHAR2 | 80 | Zip or Postal code where merchant or establishment is located. | ||
| MERCHANT_COUNTRY | VARCHAR2 | 80 | Country where merchant or establishment is located. | ||
| TOTAL_TAX | NUMBER | Total tax charged for the transaction. | |||
| LOCAL_TAX | NUMBER | Local tax charged for the transaction. | |||
| NATIONAL_TAX | NUMBER | National tax charged for the transaction. | |||
| OTHER_TAX | NUMBER | Total of other taxes charged for the transaction. | |||
| FOLIO_TYPE | VARCHAR2 | 80 | Corporate card expense types that are assigned to the transaction by the upload process. | ||
| ATM_CASH_ADVANCE | NUMBER | Amount of cash withdrawn from an Automatic Teller Machine with a card. | |||
| ATM_TRANSACTION_DATE | DATE | Date when the Automatic Teller Machine transaction occurred. | |||
| ATM_FEE_AMOUNT | NUMBER | Fee charged by the card issuer for the Automatic Teller Machine transaction. | |||
| ATM_TYPE | VARCHAR2 | 80 | Type of Automatic Teller Machine. | ||
| ATM_ID | VARCHAR2 | 80 | Identifier of the Automatic Teller Machine provided by the bank. | ||
| ATM_NETWORK_ID | VARCHAR2 | 80 | Identifier of the Automatic Teller Machine network provided by the bank. | ||
| RESTAURANT_FOOD_AMOUNT | NUMBER | Total restaurant meal charges in a card transaction for a hotel stay. | |||
| RESTAURANT_BEVERAGE_AMOUNT | NUMBER | Total restaurant beverage charges in a card transaction for a hotel stay. | |||
| RESTAURANT_TIP_AMOUNT | NUMBER | Total restaurant tip amount in a card transaction for a hotel stay. | |||
| CAR_RENTAL_DATE | DATE | Date the car was first rented. | |||
| CAR_RETURN_DATE | DATE | Date the car was returned. | |||
| CAR_RENTAL_LOCATION | VARCHAR2 | 80 | Location where the car was rented. | ||
| CAR_RENTAL_STATE | VARCHAR2 | 80 | State where the car was rented. | ||
| CAR_RETURN_LOCATION | VARCHAR2 | 80 | Location where the car was returned. | ||
| CAR_RETURN_STATE | VARCHAR2 | 80 | State where the car was returned. | ||
| CAR_RENTER_NAME | VARCHAR2 | 80 | Name of the person renting the car. | ||
| CAR_RENTAL_DAYS | VARCHAR2 | 80 | Number of days for which the car was rented. | ||
| CAR_RENTAL_AGREEMENT_NUMBER | VARCHAR2 | 80 | Number of the rental agreement as provided by the car rental company. | ||
| CAR_CLASS | VARCHAR2 | 80 | Class of car as provided by the car rental company. | ||
| CAR_TOTAL_MILEAGE | NUMBER | Total number of miles driven by the renter. | |||
| CAR_GAS_AMOUNT | NUMBER | Fuel charges charged by the car rental company. | |||
| CAR_INSURANCE_AMOUNT | NUMBER | Insurance charges charged by the car rental company. | |||
| CAR_MILEAGE_AMOUNT | NUMBER | Excess mileage charges charged by the car rental company. | |||
| CAR_DAILY_RATE | NUMBER | Car rental rate per day. | |||
| HOTEL_ARRIVAL_DATE | DATE | Checkin date at the hotel or the scheduled arrival date in case of a no show. | |||
| HOTEL_DEPART_DATE | DATE | Departure date from the hotel. | |||
| HOTEL_CHARGE_DESC | VARCHAR2 | 80 | Description of hotel charge as provided by the merchant. | ||
| HOTEL_GUEST_NAME | VARCHAR2 | 80 | Name of the person on the hotel reservation. | ||
| HOTEL_STAY_DURATION | NUMBER | Number of days stayed at the hotel. | |||
| HOTEL_ROOM_RATE | NUMBER | Daily room rate for the hotel. | |||
| HOTEL_NO_SHOW_FLAG | VARCHAR2 | 1 | Indicator of a transaction due to the person not arriving at the hotel. | ||
| HOTEL_ROOM_AMOUNT | NUMBER | Total room charges excluding room taxes in a card transaction for a hotel stay. | |||
| HOTEL_TELEPHONE_AMOUNT | NUMBER | Total telephone charges in a card transaction for a hotel stay. | |||
| HOTEL_ROOM_TAX | NUMBER | Total room tax in a card transaction for a hotel stay. | |||
| HOTEL_BAR_AMOUNT | NUMBER | Total bar charges excluding mini bar charges in a card transaction for a hotel stay. | |||
| HOTEL_MOVIE_AMOUNT | NUMBER | Total movie charges for in room movies in a card transaction for a hotel stay. | |||
| HOTEL_GIFT_SHOP_AMOUNT | NUMBER | Total gift shop charges in a card transaction for a hotel stay. | |||
| HOTEL_LAUNDRY_AMOUNT | NUMBER | Total laundry charges in a card transaction for a hotel stay. | |||
| HOTEL_HEALTH_AMOUNT | NUMBER | Total health club charges including pool charges, equipment rental pool in a card transaction for a hotel stay. | |||
| HOTEL_RESTAURANT_AMOUNT | NUMBER | Total restaurant charges in a card transaction for a hotel stay. | |||
| HOTEL_BUSINESS_AMOUNT | NUMBER | Total business center charges in a card transaction for a hotel stay. | |||
| HOTEL_PARKING_AMOUNT | NUMBER | Total parking charges in a card transaction for a hotel stay. | |||
| HOTEL_ROOM_SERVICE_AMOUNT | NUMBER | Total room service charges in a card transaction for a hotel stay. | |||
| HOTEL_TIP_AMOUNT | NUMBER | Total tip amount charged to the card for a hotel stay. | |||
| HOTEL_MISC_AMOUNT | NUMBER | Total miscellaneous charges in a card transaction for a hotel stay. | |||
| HOTEL_CITY | VARCHAR2 | 80 | City where the hotel is located. | ||
| HOTEL_STATE | VARCHAR2 | 80 | State where the hotel is located. | ||
| HOTEL_FOLIO_NUMBER | VARCHAR2 | 80 | Reference number assigned by the hotel to uniquely identify the reservation or stay. | ||
| HOTEL_ROOM_TYPE | VARCHAR2 | 240 | Room type assigned by the hotel. | ||
| AIR_DEPARTURE_DATE | DATE | Date of departure or the departure date on the first leg of the trip for a multiple leg trip. | |||
| AIR_DEPARTURE_CITY | VARCHAR2 | 80 | Departure city of the flight. | ||
| AIR_ROUTING | VARCHAR2 | 80 | Airport code for each leg of the trip. | ||
| AIR_ARRIVAL_CITY | VARCHAR2 | 80 | Arrival city of the flight. | ||
| AIR_STOPOVER_FLAG | VARCHAR2 | 1 | Indicator of whether a destination is a connection point or a final destination. | ||
| AIR_BASE_FARE_AMOUNT | NUMBER | Base air fare without any taxes. | |||
| AIR_FARE_BASIS_CODE | VARCHAR2 | 80 | Fare class for the airline ticket. | ||
| AIR_SERVICE_CLASS | VARCHAR2 | 80 | Passenger class for leg of the trip. | ||
| AIR_CARRIER_ABBREVIATION | VARCHAR2 | 80 | Alphabetic code that uniquely identifies an airline. | ||
| AIR_CARRIER_CODE | VARCHAR2 | 80 | Code for the airline carrier for each leg of the trip. | ||
| AIR_TICKET_ISSUER | VARCHAR2 | 80 | Name of airline or travel agency issuing the ticket. | ||
| AIR_ISSUER_CITY | VARCHAR2 | 80 | City of the airline or travel agency issuing the ticket. | ||
| AIR_PASSENGER_NAME | VARCHAR2 | 80 | Name of the passenger as printed on the ticket. | ||
| AIR_REFUND_TICKET_NUMBER | VARCHAR2 | 80 | Ticket number of a refunded ticket. | ||
| AIR_EXCHANGED_TICKET_NUMBER | VARCHAR2 | 80 | Ticket number for which this ticket was exchanged. | ||
| AIR_AGENCY_NUMBER | VARCHAR2 | 80 | Identification code of the airline or travel agency issuing the ticket. | ||
| AIR_TICKET_NUMBER | VARCHAR2 | 80 | Ticket number on the airline ticket. | ||
| FINANCIAL_CATEGORY | VARCHAR2 | 80 | Category of the transaction as provide by the card issuer, such as New Charges and Payment. | ||
| PAYMENT_FLAG | VARCHAR2 | 1 | Indicator of whether this transaction is a payment. | ||
| RECORD_TYPE | VARCHAR2 | 80 | Type of record, such as header, trailer, or detail billing. | ||
| MERCHANT_ACTIVITY | VARCHAR2 | 80 | Source of the transaction, such as a transaction by a merchant and an adjustment by a card issuer. | ||
| PAYMENT_DUE_FROM_CODE | VARCHAR2 | 30 | Denotes the liability owner of the transaction. | ||
| REQUEST_ID | NUMBER | 18 | Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row. | ||
| DISPUTE_DATE | DATE | Date when the individual placed this transaction on dispute. | |||
| TRXN_AVAILABLE_DATE | DATE | Date when this corporate card transaction is available for the individual for submission. | |||
| CARD_ACCEPTOR_ID | NUMBER | 18 | Identifier of the merchant who provides detail transactions for this transaction. | ||
| TRXN_DETAIL_FLAG | VARCHAR2 | 1 | Indicator of whether the transaction has detail transactions. | ||
| REVIEWED_FLAG | VARCHAR2 | 1 | Indicator of whether an invalid transaction was reviewed by an administrator. | ||
| MINI_BAR_AMOUNT | NUMBER | Total mini bar charges in a card transaction for a hotel stay. | |||
| EARLY_DEPARTURE_CHARGES | NUMBER | Early departure charges in a card transaction for a hotel stay. | |||
| CONFERENCE_ROOM_CHARGES | NUMBER | Total conference room charges in a card transaction for a hotel stay. | |||
| TRANSPORTATION_CHARGES | NUMBER | Total transportation charges in a card transaction for a hotel stay. | |||
| AUDIO_VISUAL_CHARGES | NUMBER | Total audio visual charges in a card transaction for a hotel stay. | |||
| BANQUET_CHARGES | NUMBER | Total banquet charges in a card transaction for a hotel stay. | |||
| INTERNET_ACCESS_CHARGES | NUMBER | Total internet charges in a card transaction for a hotel stay. | |||
| CASH_ADVANCES | NUMBER | Total cash advance in a card transaction for a hotel stay. | |||
| MARKET_CODE | VARCHAR2 | 30 | Market code as provided by the card issuer in the charge file. | ||
| BILLING_CNTL_ACCT_NUMBER | VARCHAR2 | 30 | Billing control account number as provided by the card issuer in the charge file. | ||
| COMPANY_NUMBER | VARCHAR2 | 30 | Company number as provided by the card issuer in the charge file. | ||
| INTERBANK_CARDASSOC_NUMBER | VARCHAR2 | 30 | Unique number assigned by the processor to bank issuing corporate cards. | ||
| CARD_ISSUER_NUMBER | VARCHAR2 | 30 | Number assigned by the payment processor that identifies a card issuer in the payment processor's system. | ||
| AIR_ARRIVAL_DATE | DATE | Arrival date of the flight. | |||
| AIR_TOTAL_FEES_AMOUNT | NUMBER | Total fees charged for airline ticket booking. | |||
| HOTEL_CASH_DISB_AMOUNT | NUMBER | Total cash disbursement in a card transaction for a hotel stay. | |||
| HOTEL_TRANSPORTATION_AMOUNT | NUMBER | Total transportation charges in a card transaction for a hotel stay. | |||
| MERCHANT_CATEGORY_CODE | VARCHAR2 | 30 | Code assigned to the merchant by the credit card company that classifies the type of business. | ||
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. | ||
| BUSINESS_PROCESS_DATE | DATE | Date when a corporate card transaction was processed by American Express. | |||
| CORPORATION_NUM | VARCHAR2 | 10 | Corporation identifier assigned by American Express for remittance and reconciliation. | ||
| GENESIS_DETAIL_INDUSTRY_CODE | VARCHAR2 | 7 | A sub-classification of Genesis Major industry codes for corporate card transactions. | ||
| GENESIS_MAJOR_INDUSTRY_CODE | VARCHAR2 | 7 | American Express industry classification code for corporate card transactions. | ||
| MERCHANT_BRAND_CODE | VARCHAR2 | 50 | The parent company code for the merchant where the charge was incurred. | ||
| MERCHANT_CHAIN_NAME | VARCHAR2 | 50 | The parent company name for the merchant where the charge was incurred. | ||
| MERCHANT_NUM | VARCHAR2 | 15 | The account number for the merchant where the charge was incurred. | ||
| PROCESS_FILE_ID | NUMBER | 18 | Unique identifier of the upload process run on the transaction file. | ||
| CC_AUTHORIZATION_IDENTIFIER | VARCHAR2 | 240 | Unique identifier that maps authorization requests to credit card transactions in the transaction files when the files are uploaded to Expenses. The identifier links the original authorization data to all related transactions, such as reversals, adjustments, confirmations and chargebacks. | ||
| EMAIL_NOTIFICATION_SENT_DATE | TIMESTAMP | The date and time when the email notification is sent for this charge. |
Foreign Keys
| Table | Foreign Table | Foreign Key Column |
|---|---|---|
| EXM_CREDIT_CARD_TRXNS | exm_cc_acceptors | CARD_ACCEPTOR_ID |
| EXM_CREDIT_CARD_TRXNS | exm_cards | CARD_ID |
| EXM_CREDIT_CARD_TRXNS | exm_cc_company_accounts | COMPANY_ACCOUNT_ID |
| exm_expenses | exm_credit_card_trxns | CREDIT_CARD_TRXN_ID |
| ap_invoice_lines_interface | exm_credit_card_trxns | CREDIT_CARD_TRX_ID |
| exm_cc_trxn_details | exm_credit_card_trxns | CREDIT_CARD_TRXN_ID |
| ap_invoice_distributions_all | exm_credit_card_trxns | CREDIT_CARD_TRX_ID |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| EXM_CREDIT_CARD_TRXNS_N1 | Non Unique | Default | REQUEST_ID |
| EXM_CREDIT_CARD_TRXNS_N2 | Non Unique | Default | CARD_ID |
| EXM_CREDIT_CARD_TRXNS_N3 | Non Unique | Default | CARD_PROGRAM_ID, REQUEST_ID, TRANSACTION_STATUS |
| EXM_CREDIT_CARD_TRXNS_N4 | Non Unique | Default | VALIDATE_CODE |
| EXM_CREDIT_CARD_TRXNS_N5 | Non Unique | Default | TRANSACTION_STATUS |
| EXM_CREDIT_CARD_TRXNS_N6 | Non Unique | Default | REFERENCE_NUMBER |
| EXM_CREDIT_CARD_TRXNS_N7 | Non Unique | Default | REVIEWED_FLAG, TRANSACTION_STATUS |
| EXM_CREDIT_CARD_TRXNS_N8 | Non Unique | Default | AUTH_TRXN_NUMBER |
| EXM_CREDIT_CARD_TRXNS_PK | Unique | Default | CREDIT_CARD_TRXN_ID |
| EXM_CREDIT_CARD_TRXNS_U2 | Unique | Default | CARD_PROGRAM_ID, COMPANY_ACCOUNT_ID, CARD_NUMBER, REFERENCE_NUMBER |
| EXM_CREDIT_CARD_TRXNS_U3 | Unique | Default | CARD_PROGRAM_ID, COMPANY_ACCOUNT_ID, CARD_ID, REFERENCE_NUMBER |