Procurement Card Processing in Oracle Apps (Credit Card Transactions to Invoices)

1. Overview

The main objective of the Procurement card transaction interface to load the transaction data from your credit card issuers and create invoices to pay them

This will help you reduce transaction costs and eliminate low-amount invoices.

2. Setup

1. Enter your card issuer as a supplier
2. Set up your employees who will be card holders
3. Credit Card Code Sets window,  Create credit card code sets. Enter card codes, such as Standard Industry Classification (SIC) codes, or Merchant Category Codes (MCC).  Assign a default GL account to a card code
4. Credit Card Programs window, Define your credit card program, including the card issuer, card type, and credit card code set. Specify transaction statuses for which you will not create invoices
5. Credit Card GL Sets window, define GL account sets. Card holders can one of the accounts listed here to change accounts during transaction verification
6. Credit Card Profiles window,  Define credit card profiles that you assign to credit cards. We can setup various attributes like credit card program, gl account set, default gl account, exception clearing account, employee verification options, and manager approval options.  You can record restrictions for credit card codes also.
7. Credit Cards window, assign a card to a card holder and assign a credit card profile to the card.
8. Set up the Credit Card Transaction Employee Workflow and Credit Card Transaction Manager Workflow
9. Configure Web Employees credit card functions
10. In the Users window, assign a Credit Cards responsibility and the Workflow responsibility to employees

3. Graphical Representation of the Process Flow:

Detailed Explanation of about each step mentioned above

Import credit card transactions

The card issuer sends you a file with the card transactions and charges. Use BPEL or SQL Loader to Load this file into the AP_EXPENSE_FEED_LINES.

Validate imported credit card transactions

• This program identifies exceptions.
• This program also builds the default GL accounts for the transactions based on your setup. 
• This program populates all foreign keys and validates foreign key values in the table.
• The program creates a report that lists all transactions that could not be validated
• It creates default accounting distributions for transactions whose CREATE_DISTRIBUTION_FLAG is set to ‘Y’

Employee verification

·         This initiates the Credit Card Transaction Employee Workflow

·         If verification is required, an employee can verify transactions directly from a workflow notification

·         If verification is not required, an employee will receive a notification indicating that transactions posted to the employee's credit card account

Manager approval or notification

• This initiates the Credit Card Transaction Manager Workflow
• If approval is required from the manager, a manager can approve an employee's credit card transactions directly from a workflow notification
• If approval is not required, a manager will receive a notification that lists all credit card transactions incurred by the manager's direct reports

Adjust transaction distributions

·                     Credit Card Transactions window is mainly used to review and update credit card transaction distributions.

·         We can use this window to split a transaction distribution into multiple distributions which you can then process separately

·         The Window contains the field named status and it can contain one of the below values

Ø      Approved. All approvals are complete and ready for import.

Ø      Disputed. A card holder or manager assigns this status to indicate a dispute over the distribution of a transaction.

Ø      Hold. A card holder assigns this status to hold the distribution of a transaction.

Ø      Personal. Personal Transaction.

Ø      Rejected. Workflow assigns this status to a transaction if the manager denies approval for the transaction.

Ø      Validated. The Credit Card Transaction Validation and Exception Report assign this status to a transaction if it was successfully validated.

Ø      Verified. Either in the Credit Card Transaction Verification page or by using workflow, the card holder has verified the transaction.

Create data in Payables Open Invoice interface tables

·                     This program creates invoices for your credit card issuers in the Payables Open Interface tables

·                     This program selects all records for a given date range in AP_EXPENSE_FEED_DISTS with a status of at least Validated

·         It will summarize the transactions to create a single invoice for each unique CCID and Tax Name combination based on the setup.

Creation of Invoices

You can see detail information about this parting my other posts

Payables Open Interface Import Program

4. Query involving important tables associated with

“PAYABLES PROCUREMENT CARD TRANSACTION INTERFACE”

SELECT EFD.FEED_LINE_ID

      ,EFD.FEED_DISTRIBUTION_ID

      ,EFD.INVOICE_ID

      ,EFD.INVOICE_LINE_ID

      ,EFD.AMOUNT

      ,EFD.DIST_CODE_COMBINATION_ID  

      ,EFD.INVOICED_FLAG  

      ,EFD.TAX_CODE

      ,EFD.MANAGER_APPROVAL_ID

      ,EFD.EMPLOYEE_VERIFICATION_ID

      ,EFD.ACCOUNT_SEGMENT_VALUE

      ,EFD.COST_CENTER  

      ,EFL.EMPLOYEE_ID

      ,EFL.CARD_ID

      ,EFL.CARD_PROGRAM_ID

      ,IBY.CARD_NUMBER

      ,EFL.REFERENCE_NUMBER  

      ,EFL.CUSTOMER_CODE

      ,EFL.AMOUNT LINE_AMOUNT

      ,EFL.ORIGINAL_CURRENCY_AMOUNT

      ,EFL.ORIGINAL_CURRENCY_CODE

      ,EFL.POSTED_CURRENCY_CODE  

      ,EFL.POSTED_DATE  

      ,EFL.CREATE_DISTRIBUTION_FLAG

      ,EFL.MERCHANT_NAME    

      ,EFL.TAX_AMOUNT

      ,EFL.TAX_RATE  

      ,EFL.FREIGHT_AMOUNT

      ,EFL.DUTY_AMOUNT  

      ,EFL.PRODUCT_CODE  

      ,EFL.EXTENDED_ITEM_AMOUNT  

      ,EFL.DISCOUNT_AMOUNT

      ,EFL.EMPLOYEE_VERIFICATION_ID LINE_EMP_VERIFICATION_ID

      ,EFL.DESCRIPTION LINE_DESCRIPTION

      ,EFL.REJECT_CODE

      ,IBY.CARD_NUMBER SET_UP_CARD_NUMBER

      ,C.EMPLOYEE_ID CARD_EMPLOYEE_ID

      ,C.DEPARTMENT_NAME

      ,CP.CARD_PROGRAM_NAME

      ,CP.CARD_TYPE_LOOKUP_CODE

      ,CPR.PROFILE_NAME

      ,APL.DISPLAYED_FIELD DIST_STATUS

      ,HREMP.FULL_NAME

      ,EFD.DESCRIPTION

      ,HREMP.SUPERVISOR_ID

      ,CP.ADMIN_EMPLOYEE_ID PROGRAM_ADMIN_EMPLOYEE_ID

      ,CPR.ADMIN_EMPLOYEE_ID PROFILE_ADMIN_EMPLOYEE_ID

      ,EFD.CONC_REQUEST_ID

      ,TO_CHAR(EFD.AMOUNT

                ,FND_CURRENCY.

                     GET_FORMAT_MASK(EFL.POSTED_CURRENCY_CODE30)

                    ) DISPLAYED_AMOUNT

      ,IBY.CARD_NUMBER

FROM AP_EXPENSE_FEED_DISTS EFD

    ,AP_EXPENSE_FEED_LINES EFL

    ,AP_CARDS C

    ,AP_CARD_PROGRAMS CP

    ,AP_CARD_PROFILES CPR

    ,AP_LOOKUP_CODES APL

    ,(SELECT p.full_name

            ,p.person_id employee_id

            ,a.supervisor_id

      FROM   PER_PEOPLE_F P,

            ,PER_ASSIGNMENTS_F A

      WHERE  A.PERSON_ID  = P.PERSON_ID

      AND A.PRIMARY_FLAG = 'Y'

      AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE

      AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE

      AND (   NVL(CURRENT_EMPLOYEE_FLAG,'N') = 'Y'

           OR NVL(CURRENT_NPW_FLAG,'N')      = 'Y'

              )

      AND A.ASSIGNMENT_TYPE IN ('E','C')

       )HREMP

    ,IBY_FNDCPT_PAYER_ALL_INSTRS_V IBY

WHERE EFD.FEED_LINE_ID     = EFL.FEED_LINE_ID

AND   EFL.EMPLOYEE_ID        = HREMP.EMPLOYEE_ID

AND   EFL.CARD_ID            = C.CARD_ID

AND   C.CARD_REFERENCE_ID    = IBY.INSTRUMENT_ID

AND   EFL.CARD_PROGRAM_ID    = CP.CARD_PROGRAM_ID

AND   C.PROFILE_ID           = CPR.PROFILE_ID

AND   EFD.STATUS_LOOKUP_CODE = APL.LOOKUP_CODE (+)

AND   APL.LOOKUP_TYPE (+)    = 'PCARD TRX STATUS';