Saturday, March 19, 2016

AP Invoice Technical Details with Functional Inputs

AP Invoice Technical Details with Functional Inputs


Overview Of payable :
An invoice is an itemized list of goods shipped or services rendered, with an account of all costs. Oracle Payables lets you capture all the attributes of the real-life invoice documents you receive from your suppliers. When you enter an invoice in Payables, the invoice information is divided between the invoice header and the invoice lines.



The Invoice to Payment process takes you from entering an invoice to paying the vendor.
  •  Entering or Importing the Supplier Invoice
  • Entering Invoices with Matched Purchase Orders and Receipts in the Invoice Workbench
  • Validating the invoice
  • Resolving automatic invoice holds
  •  Running the invoice approval process and approving invoices
  • Creating and approving payment

Entering or importing the supplier invoice
Invoice Types
Payables provide the following invoice types:
• Standard
• Mixed
• PO Price Adjustment
• Credit Memo
• Debit Memo
• Prepayment
• Expense Report
You can use these invoice types to enter any type of invoice document you receive from a supplier. For example, you can enter basic invoices that are not matched to purchase orders or more complex invoices that matched to purchase orders at the header, lines, shipping or receipt levels. You can enter invoices in foreign currency or enter invoices for suppliers who are subject to income tax reporting requirements (1099). The characteristics of each invoice type are described below.
Standard Invoices
Standard Invoice is invoices from a supplier representing an amount due for goods or services purchased. Standard invoices can be either matched to a purchase order or not matched. Standard invoices must be positive amounts.
Mixed Invoices
Mixed Invoices can be matched to both purchase orders and invoices. Mixed invoices can have either positive or negative amounts.
PO Price Adjustment Invoices
PO Price Adjustment Invoices are for recording the difference in price between the original invoice and the new purchase order price. PO price adjustment invoices can be matched to both purchase orders and invoices.
Credit Memo
Credit Memos are memos from a supplier representing a credit amount toward goods or services. Credit memos are always negative amounts.
Debit Memo
Debit Memos are invoices you enter to record a credit for a supplier who does not send you a credit memo.
Prepayment
Prepayments are invoices you enter to record an advance payment for expenses to a supplier or employee.
Expense Reports
Expense Reports are invoices representing an amount due to an employee for business-related expenses.
Entering Invoices Overview
You can enter and import invoices into Payables in many ways:
Manual Invoice Entry
You usually enter supplier invoices in either the Invoice Workbench or the Quick Invoices window. You can match to purchase orders when entering these invoices. For information on the differences between these windows, see the following section,
Differences between Entering Invoices in the Quick Invoices Window and the Invoice Workbench.
• Invoice Workbench: (Invoice Batches window, Invoices window, Distributions window, and the associated windows). Used to enter any invoice directly into the Payables system. Use this instead of the Quick Invoices when entering complex invoices or invoices that require online validation and defaulting. Also used when entering an invoice that needs immediate action, such as payment.
• Quick Invoices window: Used for quick, high-volume invoice entry for invoices that does not require extensive validation and defaults. After entry, you import these into the Payables system. Validation and defaulting occur during import.
Automatic Invoice Creation
You can set up your system to automatically create periodic invoices, for example, rent invoices:
• Recurring Invoices.
• RTS Invoices. If you use Return to Supplier feature in Oracle Purchasing, the system creates these debit memos directly in Payables.
• Retroactive Price Adjustment Invoices. If Oracle Purchasing users use the Retroactive Pricing of Purchase Orders feature, the system automatically creates Adjustment and PO Price Adjustment invoices.
Imported Invoices
You can import invoices or invoice information to create invoices in Payables.
• Oracle Internet Expenses expense reports: Expense reports your employees enter using a Web browser.
• Payables expense reports: Expense reports entered in the Payables Expense reports window by the Payables department.
• Credit Card invoices: Invoices for employee credit card expenses. The credit card company sends you these invoices as a flat file.
• EDI invoices. Electronic invoices transferred from Oracle e-Commerce Gateway.
• XML invoices. Electronic invoices transferred from the Oracle XML Gateway.
• Invoices from external systems. Invoices, such as invoices from legacy systems, loaded using SQL*Loader.
• Oracle Property Manager invoices: Lease invoices transferred from Oracle
Property Manager.
• Oracle Assets lease payments: Lease payments transferred from Oracle Assets.

Entering Invoices with Matched Purchase Orders and Receipts in the Invoice Workbench

You can match Payables invoices to purchase orders to ensure that you pay only for the goods that you have ordered, or you can match to purchase order receipts to ensure that you pay only for goods that you have received. Purchase order matched invoicesare invoices that you match to any of the following:

• Purchase order shipments

• Purchase order receipts

• Purchase order receipt lines

• Purchase order distributions
You can set up controls in your system to require you to purchase order match each invoice. You can also set an option on a purchase order shipment that controls whether invoices should match to that shipment directly or to a receipt. You set tolerances to specify the range of variance you will allow if the amounts or quantities on the invoice are greater than the amounts or quantities on the purchase order or receipt.
When you enter an invoice and match it, Payables automatically creates distributions for you and checks that the match is within the tolerance you define.
After you save the match, Payables updates the quantity or amount billed for each matched shipment and its corresponding distribution(s) based on the amount you enter in the Quantity Invoiced field. Payables also update the amount billed on the purchase order distribution(s).
Receipt and purchase order matching setup and functionality is described in detail in Purchasing Integration. 

The following section describes how to enter and match invoices.

You can match an invoice to a purchase order in different ways:
• Receipt match. Matching to receipts allows you to pay only for goods or services you receive, and to pay for partial shipments without getting invoice holds. In addition, any exchange rate variance is likely to be smaller because the time between the receipt and invoice is less than the time between the purchase order and invoice.
If you use one of the Periodic Costing options available in Oracle Cost Management to record costs of goods that you order, it is critical that you always match to receipts to ensure accurate cost accounting. When you match an invoice for goods to a receipt, you can also link other charges, such as freight, tax, and miscellaneous to that receipt, so that costing can include those charges in the cost of the goods. If you match to a purchase order instead of a receipt, you will not have accurate costing data.
• Purchase order shipment match. Based on the Quantity Invoiced, Payables prorates the Match Amount across all non-fully billed purchase order distributions associated with the purchase order shipments you match to. Payables automatically create invoice distributions based on the purchase order distributions.
You can match to individual purchase order shipments. Payables automatically create invoice distributions based on the purchase order distributions.
• Purchase order distribution match. You can allocate the match amount to specific purchase order distributions. Payables automatically create invoice distributions based on the purchase order distributions you match to. See: Matching to Purchase
Order Shipments and Distributions from the Invoice Workbench.
• Price correction. Use a price correction to adjust the invoiced unit price of previously matched purchase order shipments, distributions, or receipts without adjusting the quantity billed.
When you are matching to a purchase order, open the View PO window to have easy access to purchase order information.
Prerequisites
• Enter a purchase order.
• Enter purchase order shipment information.
• Select a Match Approval Level and an Invoice Match Option for the purchase order.
• Enter the supplier and at least one pay site.


Validating the Invoice

Actions --> Validate --> select Validate check box --> OK.


  • Before you can pay or create accounting entries for any invoice, the Invoice Validation process must validate the invoice.
  • Invoice Validation checks the matching, tax, period status, exchange rate, and distribution information for invoices you enter. It checks the supplier site to determine which invoice tolerance template to use. If no invoice tolerance template is specified, tolerance checking is not performed. If an invoice tolerance template is specified,
  • Invoice Validation will check against the specific invoice tolerances template stored in the Invoice Tolerances window to determine if the invoice falls within the defined tolerances and automatically applies holds to exception invoices. If an invoice has a hold, you can release the hold by correcting the exception that caused Invoice Validation to apply the hold by updating the invoice or the purchase order, or changing the invoice tolerances. Then resubmit Invoice Validation to release the hold. In the Invoice Holds tab, you can manually release certain invoice holds, even if you have not resolved the matching error condition.




Note:

  • When the invoice is in a validated state, the Liability Account field cannot be updated.
  • Authorized users can always correct an invoice, even if you have validated, approved, paid, or created accounting entries for the invoice.
  • You can identify all invoices that Payables has not yet reviewed with Invoice Validation by submitting the Invoice Register and selecting the Invalidated Invoices Only parameter. You can review the validation status of an invoice online in the Invoice Overview window or the Invoices window.
  • Payables and Oracle Alert integrate to alert the appropriate accounts payable or purchasing staff when you or Payables place an invoice on hold. Oracle Alert also provides an integrated system of alerts, messages, and message distribution to focus attention on time-sensitive or critical information and streamline the validation process.
  • Exception reporting in Oracle Alert is accomplished using either electronic mail or paper reports.

You can submit an invoice for validation in one of the following ways:
• Online by using either the Validate or the Validate Related Invoices check box in the Invoice Actions window.

• Online by using the Validate button in the Invoice Batches window.

• Batch by submitting the Payables Invoice Validation program from the Submit Request window.
The order of the Invoice Validation process and Invoice Approval Workflow Program is based on the Approval Processing Sequence Payables option.

For example, you might want to validate before you approve if you enter invoices that require the Invoice Validation process to create tax distributions for you. If your approves need to review tax details before they approve an invoice, then we recommend that you select an option that validates before approving.
Validation Example
For example, Invoice Validation places three invoices on matching hold. The first invoice is on matching hold because the quantity billed exceeds the quantity received.

The second invoice is on matching hold because the invoice price exceeds the purchase order shipment price. The third invoice is on matching hold because the quantity billed exceeds the quantity ordered.
Invoice 1. Your receiving department receives the goods and records the receipt information. Your invoice now matches the receipt and Payables releases the hold when you submit Invoice Validation.
Invoice 2. A supplier sends a credit to correct the amount due on a previous invoice.
You match the credit to the purchase order as a price correction and associate it with the invoice during the match. The weighted average price of the invoice and credit memo now matches the purchase order shipment price. Payables release the hold when you resubmit Invoice Validation. Use the Validate Related Invoices option in the Invoice
Actions window to submit Invoice Validation for both the credit and the original invoice.
Invoice 3. You increase your tolerance levels using the Invoice Tolerances window.
Your invoice now matches your receipt and purchase order within your newly-defined tolerance levels and Payables releases the hold when you resubmit Invoice Validation.


Resolving Invoice Holds
When the validation process places an invoice on hold, you must either resolve the hold or release the hold before payment can be made.  Some holds can be released (like price and quantity holds) and other need to be resolved.  These holds are called system holds and are need to be resolved to make the invoice valid.
 Running the invoice approval process and approving invoices
After we have a valid invoice, you will need to run the invoice approval process.  This process can be tailor to your specific business needs with the Approval Management Engine (AME).  If the invoice meets the rules to require approval, AME will determine the approver(s) and send a notification.  The approver can then approve or reject the invoice online.
Creating and Approving payment
After we have an approved invoice, you are ready to pay the supplier.  You can create a manual payment for this invoice or you can batch all the invoices that are due for payment.  To create a payment batch, you use the Payments Process Request and select a template.  This template will identify the invoices to be paid and determines if approval is necessary.  If approval is not necessary, the payment documents will be formatted for printing (for checks) or electronic transmission.

When Invoice Booked and Saved
===========================
One row created in ap_invoices_all and its distribution lines created in ap_invoice_distributions_all

When Invoice Validated :

======================
ap_invoice_distributions_all.MATCH_STATUS_FLAG='A'
ap_invoice_distributions_all.ACCOUNTING_EVENT_ID=NOT NULL(Here 1370092)
one row created in ap_accounting_events_all with accounting_event_id=ap_invoice_distributions_all.ACCOUNTING_EVENT_ID ap_accounting_events_all.EVENT_STATUS_CODE='CREATED' ap_accounting_events_all.SOURCE_TABLE='AP_INVOICES'
ap_accounting_events_all.SOURCE_ID=ap_invoice_distributions_all.INVOICE_ID=
AP_INVOICE_ALL.INVOICE_ID

When Invoice Accounted :

=====================
ap_invoice_distributions_all.ACCRUAL_POSTED_FLAG='Y'
ap_invoice_distributions_all.POSTED_FLAG='Y' ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'
ONE ROW CREATED IN AP_AE_HEADERS_ALL where AP_AE_HEADERS_ALL. accounting_event_id=ap_accounting_events_all.accounting_event_id Rows created in ap_ae_lines_all
where AP_AE_HEADERS_ALL.ae_header_id=ap_ae_lines_all.ae_header_id as
below The number of rows generally created in ap_ae_lines_all counted as 1)
one row for invoice with ap_ae_lines_all.AE_LINE_TYPE_CODE='LIABILITY'
ap_ae_lines_all.SOURCE_TABLE='AP_INVOICES' ,ap_ae_lines_all.source_id=ap_invoices_all.invoice_id2)
Other rows are created for the invoice distribution lines (one line per invoice distribution line).ap_ae_lines_all.AE_LINE_TYPE_CODE='CHARGE',
SOURCE_TABLE='AP_INVOICE_DISTRIBUTIONS',
ap_ae_lines_all.SOURCE_ID=AP_INVOICE_DISTRIBUTIONS.INVOICE_ID

When Invoice Approved : 

========================
ap_invoices_all.WFAPPROVAL_STATUS='MANUALLY APPROVED', initially it was 'REQUIRED'

When Payment Created
=====================
when payment created the one record created in ap_checks_all table.

When Payment Accounted
=============================
When payment document accounted then one row is created in ap_accounting_events_all table.
AP_invoice_payments_all.ACCOUNTING_EVENT_ID=
ap_accounting_events_all.ACCOUNTING_EVENT_ID
ap_accounting_events_all.EVENT_STATUS_CODE='ACCOUNTED'. andap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id.

After Doing the Payment (paid) of invoice with Created Payment Document
=============================================================
ap_invoices_all.payment_status_flag='Y' BEFORE 'N'It creates the linKing between ap_invoices_all and ap_checks_all by AP_INVOICE_PAYMENTS_ALL.one row created in AP_INVOICE_PAYMENTS_ALL with reference of invoice id.
AP_INVOICE_PAYMENTS_ALL.ACCRUAL_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.CASH_POSTED_FLAG='Y'
AP_INVOICE_PAYMENTS_ALL.POSTED_FLAG='Y'and when get void the AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG='Y' unless it is 'N'
When payment got accounted the one row created in ap_accounting_events_all with ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'

After Clearing Check from cash management
=====================================
open payment document and create accounting for it, showing partial now.
after successfull accounting of the document:
one line is created in AP_PAYMENT_HISTORY_all with new accounting _event_id.
AP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_idone new line created in ap_accounting_events_all with EVENT_TYPE_CODE='PAYMENT CLEARING'and AP_INVOICE_PAYMENTS_ALL.source_table='AP_CHECKS'
ap_accounting_events_all.source_id=AP_INVOICE_PAYMENTS_ALL.check_id andAP_PAYMENT_HISTORY_all.accounting_event_id=
ap_accounting_events_all.accounting_event_id
one row created in AP_AE_HEADERS_ALL with new accounting_event_id and two rows in this case created in ap_ae_lines_all with AE_LINE_TYPE_CODE='CASH CLEARING ' AND 'CASH',SOURCE_TABLE='AP_CHECKS'.


 AP invoice to XLA link
 ======================
xla_distribution_links l  table
liked in AP_INVOIVES_ALL table and AP_INVOICE_DISTRIBUTIONS_ALL
  AND l.applied_to_source_id_num_1 = i.invoice_id
  AND l.source_distribution_id_num_1 = ad.invoice_distribution_id

 xla.xla_transaction_entities xte  table =   xte.entity_code = 'AP_INVOICES'

  XLA to GL 
 ===========

apps.gl_import_references r,
apps.xla_ae_lines al,

LINK
=====
 and al.gl_sl_link_id = r.gl_sl_link_id

When Posted in GL (GL_POSTING)
===============================
after running the request "Payables Transfer to General Ledger".The ap_ae_lines_all.GL_SL_LINK_ID populates.AP_AE_HEADERS_ALL.GL_TRANSFER_FLAG='Y'AP_AE_HEADERS_ALL.
GL_TRANSFER_RUN_ID IS NOT NULL AP_AE_HEADERS_ALL.TRIAL_BALANCE_FLAG='Y'

Query 
=========

/* Formatted on 12/26/2013 9:33:44 AM (QP5 v5.114.809.3010) */
SELECT   A.ORG_ID "ORG ID",
         E.VENDOR_NAME "VENDOR NAME",
         UPPER (E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         F.VENDOR_SITE_CODE "VENDOR SITE",
         F.ADDRESS_LINE1 "ADDRESS",
         F.CITY "CITY",
         F.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (D.CREATION_DATE)) "PO DATE",
         D.SEGMENT1 "PO NUMBER",
         D.TYPE_LOOKUP_CODE "PO TYPE",
         C.QUANTITY_ORDERED "QTY ORDERED",
         C.QUANTITY_CANCELLED "QTY CANCALLED",
         G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         G.UNIT_PRICE "UNIT PRICE",
         (NVL (C.QUANTITY_ORDERED, 0) - NVL (C.QUANTITY_CANCELLED, 0))
         * NVL (G.UNIT_PRICE, 0)
            "PO Line Amount",
         (SELECT   DECODE (PH.APPROVED_FLAG, 'Y', 'Approved')
            FROM   PO.PO_HEADERS_ALL PH
           WHERE   PH.PO_HEADER_ID = D.PO_HEADER_ID)
            "PO STATUS",
         A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         A.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (A.INVOICE_DATE)) "INVOICE DATE",
         A.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)
            "Invoice Approved?",
         A.AMOUNT_PAID,
         H.AMOUNT,
         I.CHECK_NUMBER "CHEQUE NUMBER",
         TO_CHAR (TRUNC (I.CHECK_DATE)) "PAYMENT DATE"
  FROM   AP.AP_INVOICES_ALL A,
         AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
         PO.PO_DISTRIBUTIONS_ALL C,
         PO.PO_HEADERS_ALL D,
         PO.PO_VENDORS E,
         PO.PO_VENDOR_SITES_ALL F,
         PO.PO_LINES_ALL G,
         AP.AP_INVOICE_PAYMENTS_ALL H,
         AP.AP_CHECKS_ALL I
 WHERE       A.INVOICE_ID = B.INVOICE_ID
         AND B.PO_DISTRIBUTION_ID = C.PO_DISTRIBUTION_ID(+)
         AND C.PO_HEADER_ID = D.PO_HEADER_ID(+)
         AND E.VENDOR_ID(+) = D.VENDOR_ID
         AND F.VENDOR_SITE_ID(+) = D.VENDOR_SITE_ID
         AND D.PO_HEADER_ID = G.PO_HEADER_ID
         AND C.PO_LINE_ID = G.PO_LINE_ID
         AND A.INVOICE_ID = H.INVOICE_ID
         AND H.CHECK_ID = I.CHECK_ID
         AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
         AND C.PO_HEADER_ID IS NOT NULL
         AND A.PAYMENT_STATUS_FLAG = 'Y'
         AND D.TYPE_LOOKUP_CODE != 'BLANKET';



account paybles(ap)module important tables in oracle apps 
===============================================

--ACCOUNT PAYBLES(AP) IMPORTANT TABLES

--INVOICES

Select * from   AP_INVOICES_ALL --IN 11I

Select * from   AP_INVOICE_DISTRIBUTIONS_ALL  --IN 11I

Select * from   AP_INVOICES_ALL

Select * from   AP_INVOICE_LINES_ALL

Select * from   AP_INVOICE_DISTRIBUTIONS_ALL

--ACCOUNTING

Select * from   AP_AE_HEADERS_ALL  
11I 

Select * from   AP_AE_LINES_ALL  
11I 

--JOURNALS


Select * from   GL_JE_HEADERS

Select * from   GL_JE_LINES

--PAYMENTS


Select * from   AP_INVOICE_PAYMENTS_ALL

Select * from   AP_CHECKS_ALL

--DISTRIBUTION SETS

Select * from   AP_DISTRIBUTION_SETS_ALL

Select * from   AP_DISTRIBUTION_SET_LINES_ALL

--PAYMENT TERMS

Select * from   AP_TERMS_TL

Select * from   AP_TERMS_LINES

--SCHEDULE PAYMENTS

Select * from   AP_PAYMENT_SCHEDULES_ALL

--HOLD

Select * from   AP_HOLDS_ALL

--SUPPLIERS

Select * from   PO_VENDORS

Select * from   PO_VENDOR_SITES_ALL

Select * from   PO_VENDOR_CONTACTS

Select * from AP_SUPPLIERS

Select * from AP_SUPPLIERS_SITS_ALL

--INVOICE TYPES

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='INVOICE_TYPE')

--INVOICE DIST TYPES

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='INVOICE_DISTRIBUTION_TYPE')

Select * from   AP_LOOKUP_CODES  --(LOOKUP_TYPE='SOURCE')

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...