Loading

Thursday, May 20, 2010

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

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';


0 Responses to “Procurement Card Processing in Oracle Apps (Credit Card Transactions to Invoices)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.