Wednesday, 27 April 2011

Period closing Process for Payables


Period closing Process for Payables

You cannot close a period in Payables if any of the following conditions exist:
o Outstanding payment batches. Confirm or cancel all incomplete payment batches.
o Future dated payments for which the Maturity Date is within the period but that still have a status of Issued.
o Unaccounted transactions. Submit the Payables Accounting Process to account for transactions, or submit the Unaccounted Transaction Sweep to move any remaining unaccounted transactions from one period to another.
o Accounted transactions that have not been transferred to general ledger. Submit the Payables Transfer to General Ledger process to transfer accounting entries.

To complete the close process in Payables:
1. Validate all invoices.
Run Invoice Validation Concurrent program.
2. Confirm or cancel all incomplete payment batches.

3. If you use future dated payments, submit the Update Matured Future Dated Payment Status Program. This will update the status of matured future dated payments to Negotiable so you can account for them.

4. Resolve all unaccounted transactions.
Submit the Payables Accounting Process to account for all unaccounted transactions. Review the Unaccounted Transactions Report. Review any unaccounted transactions and correct data as necessary.

Then resubmit the Payables Accounting Process to account for transactions you corrected. Or move any unresolved accounting transaction exceptions to another period (optional).
o Payables Accounting Process.
o Submit the Unaccounted Transactions Sweep Program.
5. Transfer invoices and payments to the General Ledger and resolve any problems you see on the output report:
o Payables Transfer to General Ledger Program.
6. In the Control Payables Periods window, close the period in Payables.
o Controlling the Status of Payables Periods.
7. Reconcile Payables activity for the period. You will need the following reports:
o Accounts Payable Trial Balance Report (this period and last period).
o Posted Invoice Register.
o Posted Payment Register.
8. If you use Oracle Purchasing, accrue uninvoiced receipts.

9. If you use Oracle Assets, run the Mass Additions Create Program transfer capital invoice line distributions from Oracle Payables to Oracle Assets.

10. Post journal entries to the general ledger and reconcile the trial balance to the General Ledger.

AR TRANSACTION MODEL (TABLE LINKS)


AR TRANSACTION MODEL (TABLE LINKS)


AP-SLA-GL LINK QUERY


AP-SLA-GL QUERY

SELECT
   aia.INVOICE_ID "Invoice Id",
   aia.INVOICE_NUM "Invoice Number",
   aia.INVOICE_DATE "Invoice Date",
   aia.INVOICE_AMOUNT "Amount",
   xal.ENTERED_DR "Entered DR in SLA",
   xal.ENTERED_CR "Entered CR in SLA",
   xal.ACCOUNTED_DR "Accounted DR in SLA",
   xal.ACCOUNTED_CR "Accounted CR in SLA",
      gjh.je_source ,
   gjl.ENTERED_DR "Entered DR in GL",
   gjl.ACCOUNTED_DR "Accounted DR in GL",
   xal.ACCOUNTING_CLASS_CODE "Accounting Class",
   gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
       ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
       ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
       ||gcc.SEGMENT7 "Code Combination",
   aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
   aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
   aia.GL_DATE "GL Date",
   xah.PERIOD_NAME "Period",
   aia.PAYMENT_METHOD_CODE "Payment Method",
   aia.VENDOR_ID "Vendor Id",
   aps.VENDOR_NAME "Vendor Name",
   xah.JE_CATEGORY_NAME "JE Category Name"
FROM
   apps.ap_invoices_all aia,
   xla.xla_transaction_entities XTE,
   apps.xla_events xev,
   apps.xla_ae_headers XAH,
   apps.xla_ae_lines XAL,
   apps.GL_IMPORT_REFERENCES gir,
   apps.gl_je_headers gjh,
   apps.gl_je_lines  gjl,
   apps.gl_code_combinations gcc,
   apps.ap_suppliers aps,
   (select aid1.invoice_id,
           pa.project_id,
           nvl(pa.segment1,'NO PROJECT') Project
   from    apps.ap_invoice_distributions_all aid1,
           apps.PA_PROJECTS_ALL pa
   where aid1.rowid in
       (select MAx(rowid)
       from apps.ap_invoice_distributions_all aid2
       where aid1.INvoice_ID=aid2.INvoice_ID
       group by aid1.invoice_id)
   and aid1.project_id=pa.project_id(+)) sql1,
   (select aid1.invoice_id,
           pt.task_id,
           nvl(pt.task_number,'NO TASK') Task
   from    apps.ap_invoice_distributions_all aid1,
           apps.PA_TASKS pt
   where aid1.rowid in
       (select MAx(rowid)
       from apps.ap_invoice_distributions_all aid2
       where aid1.INvoice_ID=aid2.INvoice_ID
       group by aid1.invoice_id)
   and aid1.task_id=pt.task_id(+)) sql2
WHERE
   aia.INVOICE_ID = xte.source_id_int_1
   and aia.INVOICE_ID=sql1.Invoice_ID
   and aia.INVOICE_ID=sql2.Invoice_ID
   and xev.entity_id= xte.entity_id
   and xah.entity_id= xte.entity_id
   and xah.event_id= xev.event_id
   and XAH.ae_header_id = XAL.ae_header_id
  -- and XAH.je_category_name = 'Purchase Invoices'
   and XAH.gl_transfer_status_code= 'Y'
   and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
   and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
   and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID
   and gjh.JE_HEADER_ID=gir.JE_HEADER_ID
   and gjl.JE_HEADER_ID=gir.JE_HEADER_ID
   and gir.JE_LINE_NUM=gjl.JE_LINE_NUM
   and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
   and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
   and aia.VENDOR_ID=aps.VENDOR_ID
   and gjh.STATUS='P'
   and gjh.Actual_flag='A'
   and gjh.CURRENCY_CODE='USD'
   and aia.Invoice_id=&Invoice_Id;

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


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