be happy and make others to be happy: AP Invoice Interface

AP Invoice Interface

This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.

Interface tables:

1] AP_INVOICES_INTERFACE

This is the open interface table for importing AP Invoices from external sources and stores header information about invoices. Invoice data comes from sources including:

EDI invoices from suppliers that are loaded through Oracle e-Commerce Gateway
Supplier invoices that are transferred through the Oracle XML Gateway
Invoices that are loaded using Oracle SQL*Loader
Lease invoices from Oracle Property Manager
Lease payments from Oracle Assets
Credit card transaction data that are loaded using the Credit Card Invoice Interface Summary
Expense Report invoices from Oracle Internet Expenses
Payment Requests from Receivables
Invoices that are entered through the Invoice Gateway.

There is one row for each invoice you import. Oracle Payables application uses this information to create invoice header information when Payables Open Interface program is submitted.

Data in the AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE table to create Payables Invoice, Invoice lines, Distributions and Schedule payments. Data in this table can be viewed and edited using ‘Open Interface Invoices’ window. The Payables Open Interface program validates each record in this interface table selected for import, and if the record contains valid data then the program creates a Payables Invoice.

Important columns:

INVOICE_ID (Required) : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.

INVOICE_NUM (Required) : Enter the invoice number that needs to be assigned to the invoice created in Payables from this record.

INVOICE_TYPE_LOOKUP_CODE (Optional) : Type of invoice: Credit or Standard.

INVOICE DATE (Optional) : Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date.

PO_NUMBER (Optional) : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order.

VENDOR_ID & VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.

VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER.

INVOICE_AMOUNT (Required) : Amount of the invoice.

INVOICE_CURRENCY_CODE (Optional) : Currency code for the invoice. If you want to create foreign currency invoices, enter a currency code that is different from your functional currency.

EXCHANGE_RATE (Optional) : This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE.

TERMS_ID (Optional) : Internal identifier for the payment terms.

DESCRIPTION (Optional) : Enter the description that you want to assign to the invoice created from this record.

SOURCE (Required) : Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables.

2] AP_INVOICE_LINES_INTERFACE

This is the lines interface table for the AP Invoice Open Interface and it is used in conjunction with AP_INVOICE_INTERFACE table. AP_INVOICE_LINES_INTERFACE stores information used to create one or more invoice distributions. Note that one row in this table may create, during the import process, more than one invoice distribution.

Important columns:

INVOICE_ID (Required) :Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table.

INVOICE_LINE_ID : This value is not required. You can enter a unique number for each invoice line of an invoice.

LINE_NUMBER (Optional) : You can enter a unique number to identify the line.

LINE_TYPE_LOOKUP_CODE (Required) : Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table.

AMOUNT (Required) : The invoice distribution amount. If you are matching to a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice that has the same INVOICE_ID, then Payables Open Interface Import will reject the invoice.

Concurrent program:

Payables Open Interface Import

Parameters:

Source: Choose the source of the invoices from the list of values. Use EDI Gateway, Credit Card, or a Source type QuickCode you defined in the Payables QuickCodes window.

Group: To limit the import to invoices with a particular Group ID, enter the Group ID. The Group must exactly match the GROUP_ID in the Payables Open Interface tables.

Batch Name: Payables groups the invoices created from the invoices you import and creates an invoice batch with the batch name you enter. You can enter a batch name only if you have enabled the Use Batch Control Payables option, and if you have enabled the Use Batch Control Payables option, you must enter a batch name. If you use a batch name and some invoices are rejected during the import process, you can import the corrected invoices into the same batch if you enter the exact batch name during the subsequent import.

Hold Name: If you want to place all invoices on hold at the time of import, enter an Invoice Hold Reason. You can define your own hold reasons in the Invoice Approvals window.

Hold Reason: Payables displays the Invoice Hold Reason Description.

GL Date: If you want to assign a specific GL Date to all invoices, enter a GL Date. If you do not enter a value here, the system will assign a GL Date based on the GL Date Payables option.

Purge: Enter Yes if you want Payables to delete all successfully imported invoice records that match the Source and Group ID of this import. Payables does not delete any invoice data for which it has not yet created invoices. If you want to purge later, you can use the Payables Open Interface Purge Program.

Steps:

1] Firstly, let’s get a unique number to be used as INVOICE_ID to the invoice to be imported. This method ensures that each invoice has a unique INVOICE_ID assignment.

select ap_invoices_interface_s.nextval from dual;
NEXTVAL
-------------
132277


2] Then, create records in the Invoice Open Interface tables through SQL queries.

insert into AP_INVOICES_INTERFACE (
            invoice_id,
            invoice_num,
            vendor_id,
            vendor_site_id,
            invoice_amount,
            INVOICE_CURRENCY_CODE,
            invoice_date,
            DESCRIPTION,
            PAY_GROUP_LOOKUP_CODE,
            source,
            org_id
                )
values (
            132277,
            'INV100',
            '7124',
            '11792',
            1200.00,
            'USD',
            to_date('01-31-2010','mm-dd-yyyy'),
            'This Invoice is created for test purpose',
            'WUFS SUPPLIER',
            'Manual Invoice Entry',
            81
);

insert into AP_INVOICE_LINES_INTERFACE (
            invoice_id,
            line_number,
            line_type_lookup_code,
            amount
            )
values     (
            132277,
            1,
            'ITEM',
            1200.00
);

3] You can go to Payables > Invoices > Entry > Open Interface Invoices to check the details of Invoice and Invoice Lines from the front end. If required you can do any modifications here. Alternatively you can use these forms to put invoice data in AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables.

4] Go to the front end and run the concurrent program “Payables Open Interface Import”  to submit a request for Invoice Import.



Output:





output of Payables Open Interface Import


5] The imported invoice becomes available for review in Invoices Workbench.





Interface Invoice through Invoice Banch

====

Payables Open Interface Import

You can use the Payables Open Interface Import program to create Payables invoices from invoice data in the Payables Open Interface Tables. You can populate the Payables Open Interface Tables with invoice data from the following sources:

Supplier EDI invoices (ASC X12 810/EDIFACT INVOIC) transferred through Oracle EDI Gateway

Invoices you load from other accounting systems with a custom SQL*Loader program See: Using SQL*Loader to Populate the Payables Open Interface Tables

Credit card transactions you have transferred using the Credit Card Invoice Interface Summary. See: Credit Card Invoice Interface Summary.

Note: In Payables Release 11, use Payables Invoice Import to import expense report data from the Payables Invoice Interface tables. See: Payables Invoice Import Program.

This section includes the following documents:
==================

Understanding the Payables Open Interface Tables

Use Oracle EDI Gateway, the Credit Card Invoice Interface Summary, or SQL*Loader to load invoice information into the interface tables, AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE. When you initiate importing invoices, the import program validates all required invoice data in these tables. After it validates the information, Payables Open Interface Import creates invoices from the information in the Payables Open Interface tables. Payables also creates scheduled payments based on the payment terms. Records in the AP_INVOICE_LINES_INTERFACE table create one or more invoice distributions. Note that one row may create more than one distribution. For example, if you enter a Tax line in this table and prorate it across three Item lines, during Open Interface Import the system will create three Tax invoice distributions based on the single Tax line in this table.
The interface tables are organized by columns that Payables uses to categorize and store specific invoice information. For example, invoice source information is stored in the column called SOURCE in AP_INVOICES_INTERFACE.

NOT NULL Columns

You must enter values for all NOT NULL columns in the interface tables to successfully save your invoice data in the interface tables.

NULL Columns

A column in the interface tables that must not have a value in order for Payables Open Interface Import to successfully create invoices from the records in the tables.

Required Columns

In addition to the NOT NULL columns, there are columns in the interface tables that require a value for import. If you do not enter a valid value in a Required field, the record will be rejected during import.

Conditionally Required Columns

Payables requires you to enter values in a conditionally required column only if you have entered a value in another column on which the column is dependent.

Optional Columns

You can use some columns in the interface tables to import additional invoice information for the invoices created by Payables Open Interface Import. Payables Open Interface Import imports the data that you load into these optional columns, provided that the information passes the validation checks during Payables Open Interface Import.

Columns Not Used for Import

Payables Open Interface Import does not use the STATUS or REQUEST_ID columns to create invoices. Payables does not support importing any data from these columns and you should leave these columns null for the records you load into the table.

Internal ID Columns

These columns contain values that Payables uses internally and the user never sees. You can obtain these values only by looking inside Payables tables. These ID columns correspond to other columns in the interface table. You do not need to enter a value for any of these ID columns if you enter a value in the corresponding column. For example, if you enter the TERMS_NAME you do not need to enter the TERMS_ID. If you enter values for both columns and the values do not match, the record will be rejected during import. For example, if you enter a TERMS_NAME and a TERMS_ID and the TERMS_ID corresponds to a different TERMS_NAME, the record will be rejected during import.

Suggestion: The import process will work faster if you provide values for the internal ID columns instead of the corresponding columns.

========================================

Understanding the Payables Open Interface Tables

NOT NULL Columns

You must enter values for all NOT NULL columns in the interface tables to successfully save your invoice data in the interface tables.

NULL Columns

A column in the interface tables that must not have a value in order for Payables Open Interface Import to successfully create invoices from the records in the tables.

Required Columns

Conditionally Required Columns

Payables requires you to enter values in a conditionally required column only if you have entered a value in another column on which the column is dependent.

Optional Columns

Columns Not Used for Import

Internal ID Columns

Suggestion: The import process will work faster if you provide values for the internal ID columns instead of the corresponding columns.

be happy and make others to be happy

Sunday, 20 May 2012

AP Invoice Interface

AP Invoice Interface

AP Invoice Interface

Interface tables:

Concurrent program:

Steps:

Output:

Payables Open Interface Import

Understanding the Payables Open Interface Tables

NOT NULL Columns

NULL Columns

Required Columns

Conditionally Required Columns

Optional Columns

Columns Not Used for Import

Internal ID Columns

Understanding the Payables Open Interface Tables

NOT NULL Columns

NULL Columns

Required Columns

Conditionally Required Columns

Optional Columns

Columns Not Used for Import

Internal ID Columns

2 comments: