Credit Card transaction import into Oracle Internet Expense (IE)

Purpose:

In this article we will see how to import credit card transaction sent by bank into Oracle Internet Expense application.

You need to work with bank to determine the format of the file.
There are 2 options available, either go with custom format or industry standard file format.

Let’s see the formats in detail:

Custom format:

You can define your own control file and load the data using sql loader utility. This process would take time as it involves defining the format, have bank follow the format, testing, etc.

Industry standard means:

Oracle has built-in credit card loader program that are accepted by most banks. The list of oracle loader program are given at the end of this article.

Things to note:

There is a loader program and loader & validate program. The difference between these 2 are discussed below:

Loader	Loader & validate
Just load the transaction in AP_CREDIT_CARD_TRXNS_ALL table	Will load transaction in AP_CREDIT_CARD_TRXNS_ALL table and perform validate.
Validation program need to run separately to validate the transaction	No need to run validate program as the transaction was already validated at the time of load
Loader program use sql loader utility to load the data	Java program is used to load and validate.
Easy to customize	As it use Java program is it difficult to customize

Demonstration on how to load the credit card data and import into IE:

Assumption: all the setup required for credit card transaction to load are setup properly. 
To know more about the setup please visit my article: 

http://oracleappssuren.blogspot.com/2014/12/credit-process-applicable-for-r12-setup.html

1. Import the file sent by bank and run loader program. 
2. For testing purpose i am going to run below insert statement 
3. Run 'Credit Card Transactions Validation Program'.
4. Import credit card transaction by login into IE.
5. Create new expense report
6. Submit IE.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

INSERT INTO AP_CREDIT_CARD_TRXNS_ALL (TRX_ID, VALIDATE_CODE, payment_flag, CARD_PROGRAM_ID, EXPENSED_AMOUNT, CARD_NUMBER, card_id, REFERENCE_NUMBER, TRANSACTION_TYPE, TRANSACTION_DATE, TRANSACTION_AMOUNT, DEBIT_FLAG, BILLED_DATE, BILLED_AMOUNT, BILLED_DECIMAL, BILLED_CURRENCY_CODE, POSTED_DATE, POSTED_AMOUNT, POSTED_DECIMAL, POSTED_CURRENCY_CODE, CURRENCY_CONVERSION_EXPONENT, CURRENCY_CONVERSION_RATE, MIS_INDUSTRY_CODE, SIC_CODE, MERCHANT_REFERENCE, MERCHANT_NAME1, MERCHANT_NAME2, MERCHANT_ADDRESS1, MERCHANT_ADDRESS2, MERCHANT_CITY, MERCHANT_PROVINCE_STATE, MERCHANT_POSTAL_CODE, MERCHANT_COUNTRY, TOTAL_TAX, LOCAL_TAX, org_id, CREATION_DATE) VALUES (ap_credit_card_trxns_s1.NEXTVAL, 'N', 'N', 10001, 0, NULL, 149391, -- card number: 123456789 '154132314259592SAUG06-5.6', '06', SYSDATE, 1, '+', SYSDATE, 1, 2, 'USD', SYSDATE, 1, 2, 'USD', 2, 1, 'FE', NULL, '00000000000', 'ABC CARD FEE', NULL, NULL, NULL, NULL, NULL, NULL, 'US', 0, 0, 83, SYSDATE);

Login to IE and create IE.
You will see if there are pending transaction to import.

You will see last 4 digit of your 16 digit credit card in the drop down. 

If you got multiple credit card then you can pick the credit card and import the transactions.

By default after importing the transactions will be categorized as Business (if you setup as both pay in the card program).

https://docs.oracle.com/cd/E18727_01/doc.121/e13454/T214904T214917.htm

Credit Cards

Credit Card Transactions Loader Preformat program converts US Bank Visa transaction data into the correct format for loading into the AP_CREDIT_CARD_TRXNS_ALL table. This program is a prerequisite for US Bank Visa data only.

US Bank Visa Transaction Loader program imports US Bank Visa transaction data into the AP_CREDIT_CARD_TRXNS_ALL table. You must run the Credit Card Transactions Loader Preformat program on US Bank Visa data first before running this program.

Bank of America Visa Transaction Loader program imports Bank of America Visa TS2 format transaction data into the AP_CREDIT_CARD_TRXNS_ALL table. You must run the Credit Card Transactions Loader Preformat program on Bank of America Visa data first before running this program.

Diner's Club Transaction Loader program imports Diner’s Club transaction data into the AP_CREDIT_CARD_TRXNS_ALL table.

American Express Transaction Loader program imports American Express transaction data into the AP_CREDIT_CARD_TRXNS_ALL table.

Create Credit Card Issuer Invoice creates invoices for your credit card issuers in Oracle Payables. You only use this program if your company uses the Company Pay reimbursement policy.

Credit Card Outstanding Transactions Management (Details) program and report generates detailed information about the outstanding transactions of a particular credit card program.

Credit Card Outstanding Transactions Summary Report prints summary transaction information of a particular credit card for one or more employees.

Credit Card Outstanding Transactions Management (Aging) program and report generates aging information on outstanding transactions of a particular credit card for one or more employees, and generates notifications to employees and managers.

MasterCard CDF Transaction Loader and Validation Program imports the MasterCard transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions. This program is for CDF version 2.0.

MasterCard CDF3 Transaction Loader and Validation Program imports the MasterCard transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions. The program also imports detail transaction data. This program is for CDF version 3.0.

Diner’s Club Transaction Loader and Validation Program imports the Diner’s Club transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions.

US Bank Visa Transaction Loader and Validation Program imports the US Bank Visa transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions.

Visa VCF4 Transaction Loader and Validation Program imports Visa transaction data directly from Visa into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions. The program also imports detail transaction data. This program is for Visa VCF3.0 and VCF4.0 formats.

American Express Transaction Loader and Validation Program imports the American Express transaction data into the AP_CREDIT_CARD_TRXNS_ALL table and validates the transactions.

Credit Card Transactions Validation Program validates a card issuer’s credit card transaction data that is loaded into the AP_CREDIT_CARD_TRXNS_ALL table.

Credit Card Transactions Inactive Employees Process generates the Inform Manager of Inactive Employee Transactions workflow process.

When employees are terminated and the system date is past the Final Process date, Internet Expenses will not process terminated employee credit card transactions. Specifically, the Credit Card Transactions Inactive Employees Process program, and the Credit Card Outstanding Transactions programs will not process transactions of terminated employees. See: Terminating Employees. For Human Resources shared installs, this check is not applicable.

Credit Card Historical Transactions Management Program program deactivates unused credit card transactions and categorizes them as Historical. Once deactivated, the transactions are only viewable by users from the credit card transactions history page.

Credit Card Transactions Deactivation Report provides details of credit transactions that were affected by the Credit Card Historical Transactions Management program.

Thanks for visiting my blog!!!