What is Global Temporary Tables???

Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

• Creation of Temporary Global Tables
• Miscellaneous Features

Creation of Global Temporary Tables

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT DELETE ROWS;

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (

  column1  NUMBER,

  column2  NUMBER

) ON COMMIT PRESERVE ROWS;

Miscellaneous Features

• If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
• Data in temporary tables is automatically delete at the end of the database session, even if it ends abnormally.
• Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
• Views can be created against temporary tables and combinations of temporary and permanent tables.
• Temporary tables can have triggers associated with them.
• Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
• There are a number of restrictions related to temporary tables but these are version specific.

What is FNDLOAD?

What is FNDLOAD: when we are working in oracle application development/implementation project? The equally important AOL data Migration takes place necessary to synchronize the data across databases instance during installation and upgarde.

Using FNDLOAD can download data from an application entity into an editable text file, which can be uploaded to another database.The Conversion between database format and text file format is specified by a configuration file.

It can be done following list
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Concurrent Manager Schedules
 
Advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. No learning curve. this is relief for developer/dbas
3. Fully supported and recommended by Oracle
4. Capture the migrations in a file and use it during installations(log file).
5. Pin-point when something happened and where (database) easily
6. AOL data migration process is now simplified!

Disadvantages
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. No validation against migrating database/instance sensitive data
 
The Syntax
To use FNDLOAD, the following syntax is needed.
FNDLOAD apps/appspwd 0 Y mode configfile datafile entity [parameter1.....]

· The mode is either DOWNLOAD or UPLOAD.
· The configfile is the file that Fndload needs to download on upload data.
· T he data file is the output file, in which the downloaded data is written
· The entity is the entity you want to download

Example of download:
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=application_short_name
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt – CUSTOM_MODE=FORCE undocumented parameter

Where is Config File Located

· Configuration files with extension .lct
o On Unix – all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
·Data files with extension .ldt
· The configfiles (.lct) are delivered and maintained by Oracle
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME
oDownloading a parent automatically downloads all children – (Example) Concurrent Program download

Sample Script Code for these Objects:
1 – Printer Styles
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=printer style name
2 – Lookups
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=prod LOOKUP_TYPE=lookup name
3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=prod DESCRIPTIVE_FLEXFIELD_NAME=desc flex name P_CONTEXT_CODE=context name
4 – Key Flexfield Structures
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=prod ID_FLEX_CODE=key flex code P_STRUCTURE_CODE=structure name
5 – Concurrent Programs
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=prod CONCURRENT_PROGRAM_NAME=concurrent name
6 – Value Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=value set name
7 – Value Sets with values
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=value set name
8 – Profile Options
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=profile option APPLICATION_SHORT_NAME=prod
9 – Requset Group
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=request group APPLICATION_SHORT_NAME=prod
10 – Request Sets
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=prod REQUEST_SET_NAME=request set
11 – Responsibilities
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=responsibility
12 – Menus
FNDLOAD apps/apps@seed115 O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=menu_name
13 Forms/Functions
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES The Upload syntax for all styles: FNDLOAD apps/apps@seed115 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

14. User/Responsibilities
FNDLOAD apps/apps@seed115 0 Y DOWNLOAD @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER Then UPLOAD FNDLOAD apps/apps@seed115 0 Y UPLOAD [UPLOAD_PARTIAL] @FND:patch/115/import/afscursp.lct file_name.ldt FND_USER []

Oracle Order Management Overview

Oracle Order Management Overview::

High-Quality Customer Service

You can set up customer defaults for shipping and billing preferences. Sales orders and returns defaults expedite the entry process as well as the order copy. Users are allowed to check product availability and optionally reserve inventory during Order Entry/Shipping.

Efficient Sales Order Management

You can define flexible order cycles that meet the routing, approval revenue, and inventory requirements of all your sales channels. Sales orders enter your system immediately and are visible to every branch of your organization.

Effective Order Controls

You can control the order processing by defining when to hold order lines for approval and who can apply or release each hold. Oracle Order Entry/Shipping automatically checks a customer’s credit status against the credit profile. It also automatically prices your orders as you enter them, using the pricing and discounting rules you define.

Flexible Shipping

You can partially or completely ship a delivery to a specific customer. You can also create a departure, assign deliveries to the departure, and ship or backorder the departure.

For Alcoa AEP, shipping functionality will be handled by Oracle and Clear Orbit Software applications.  

Key Features of Order Management::

Order Processing

·           Create order cycles with only the steps needed to control order processing.

·           Use any number of order cycles to meet the needs of your sales channels.

·           Increase control with approval steps.

Sales Orders

·           Speed Order Entry/Shipping with user-defined defaults and order templates.

·           Automate item pricing and discounts.

·           Create a single order for multiple shipments and different dates, Ship To addresses, shipping methods, and shipping sites.

·           Control order updates with security rules.

Pricing and Discounting

·           Create flexible, multilevel, currency-based price lists.

·           Create automatic and manual discounts based on item, item category, customer, customer classes, customer agreement, purchase order, or order type.

·           Create fixed-amount, percentage, dollar, or unit volume discounts.

·           Create rule-based pricing structures.

Key Features of Order Management

Delivery-Based Shipping

·           Ship a delivery partially or completely to a specific customer.

·           Create a departure, assign deliveries to the departure, and ship or backorder the departure.

·           Control the confirmation of partial shipments.

·           Record multiple freight costs for a pick slip.

·           Enter individual and ranges of serial numbers for item control.

Drop Shipments

·           Determine the item to be fulfilled from warehouse or sourced externally at entry time or scheduling.

·           Reduce order fulfillment time by streamlining the process to create purchase orders, releases, or requisitions for the drop shipment order.

·           Track the purchase orders status for the drop shipment order.

·           Invoice your customers after the products have been shipped.

·           Process drop shipment return to supplier or receive products into your inventory.

Order Holds

·           Apply holds to items, customers, sites, or orders.

·           Hold orders at any step of the order process.

·           Control hold updates by user.

·           Track holds that you place and remove.

Key Features of Order Management

Credit Checking

·           Check credit automatically during Order Entry/Shipping and Pick Release.

·           Set customer limits per order and for all orders.

·           Assign tolerances to single-order and total-order limits.

Returns

·           Enter returns by item, order, purchase order, or invoice.

·           Create replacement orders.

·           Automatically create applied credit memos or on-account credits.

Open Interfaces

·           Import sales orders and order changes from other systems (Order Import).

·           Import shipment confirmation and update shipping details from data collection devices or shipping systems (Update Shipping).

Concurrent Processing APIs for PL/SQL Procedures and other Concurrent programs

Concurrent Processing APIs for PL/SQL Procedures and other Concurrent programs ::

§FND_CONCURRENT: Information on Submitted Requests

§FND_FILE: PL/SQL: File I/O

§FND_PROGRAM: Concurrent Program Loaders

§FND_SET: Request Set Creation

§FND_REQUEST: Concurrent Program Submission

§FND_REQUEST_INFO: Request Information

§FND_SUBMIT: Request Set Submission

§FND_PROGRAM: Concurrent Program Loaders

 

§Concurrent Processing APIs for PL/SQL Procedures

Calling any Concurrent programs from concurrent PL/SQL procedure.

function FND_REQUEST.SUBMIT_REQUEST

( application IN varchar2 default NULL,

program IN varchar2 default NULL,

description IN varchar2 default NULL,

start_time IN varchar2 default NULL,

sub_request IN boolean default FALSE

argument1,

argument2, ..., argument99,

argument100) return number;

Submits a concurrent request for processing by a concurrent manager.         If the request completes successfully, this function returns the concurrent request ID; otherwise, it returns 0.

  

§FND_REQUEST.SUBMIT_REQUEST

•Arguments:

-Application:  Short name of the application associated with the concurrent request to be submitted.

-Application Program: Name of the concurrent program for  which the request should be submitted.

-Description : Description of the request that is displayed in the Concurrent Requests form (Optional.)

-Sub_request: Set to TRUE if the request is submitted from another request and should be treated as a sub–request. Starting with Release 11, this parameter can be used if  you are submitting requests from within a PL/SQL stored procedure concurrent program.

•

 

 

§Attention: FND_REQUEST must know information about the user and responsibility from which the request is submitted. Therefore, this function only works from concurrent programs or forms within Oracle Applications.                                        Use FND_client_info.setup_client_info procedure if testing from sql*Plus environment.

§

§Your code should retrieve and handle the error message generated if there is a submission problem (the concurrent request ID returned is 0).

§

  

§You must call FND_REQUEST.SET_MODE before calling FND_REQUEST.SUBMIT_REQUEST from a database trigger.

§

§If FND_REQUEST.SUBMIT_REQUEST fails from anywhere but a database trigger, database changes are rolled back up to the point of the function call.

§

§After a call to the FND_REQUEST.SUBMIT_REQUEST function, all setup parameters are reset to their default values.

 

 

§FND_CONCURRENT.SET_COMPLETION_STATUS (Server)

§

§Call SET_COMPLETION_STATUS from a concurrent program to set its self completion status. The function returns TRUE on success, otherwise FALSE.

function FND_CONCURRENT.SET_COMPLETION_STATUS(status IN varchar2, message IN varchar2) return boolean;

§The status to set the concurrent program to. Either NORMAL, WARNING, or ERROR.

  

§FND_REQUEST.SET_OPTIONS (Client or Server)

•Optionally call before submitting a concurrent request to set request options. Returns TRUE on successful completion, and FALSE otherwise.

 

§FND_CLIENT_INFO.setup_client_info

•(application_id in number,

-Responsibility_id in number,

-User_id in number,

-Security_group_id in number )

-By calling this program in sql*Plus or Reports with correct parameters, user can achieve concurrent program environment for testing. 

 

§FND_PROFILE.get_specific( profile_name varchar2,

User_id  in number,

Responsibility_id in number

Application_id in number,

 value  out varchar2,

Defined out boolean)

-Get profile value for a specific user, responsibility in an application

  

§request_id: The request ID of the request to wait on.

§

§max_wait : The maximum time in seconds to wait for the request’s completion.

§

§interval : Number of seconds to wait between checks (i.e., number of seconds to sleep.)

§

§

 

 

§FND_FILE: PL/SQL File I/O

•The FND_FILE package contains procedures to write text to log    and output files. In Release 11i, these procedures are supported    in all types of concurrent programs.

•FND_FILE.PUT_LINE

procedure FND_FILE.PUT_LINE

(which IN NUMBER,

buff IN VARCHAR2); 

 

ØUse this procedure to write a line of text to a file (followed by a new line character). You will use this utility most often.

Ø

fnd_file.put_line(FND_FILE.LOG,’Warning: Employee ’||

l_log_employee_name ) ;

 

 FND_GLOBAL::

§On the client side, most of the procedures in the FND_GLOBAL package are replaced by a user profile option with the same (or a similar) name. You should use FND_PROFILE routines in your forms instead. 

 

§FND_GLOBAL.USER_ID (Server)

•function FND_GLOBAL.USER_ID

•return number;

•

§Returns the user ID.

  

§FND_GLOBAL.APPS_INITIALIZE (Server)

procedure APPS_INITIALIZE(user_id in number,

resp_id in number,

resp_appl_id in number);

•This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session.

•You can use it for routines such as Java, PL/SQL, or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session).

 

 

§You can also use this procedure to set up a database session for manually testing application  code using SQL*Plus.

§This routine should only be used when the session must be established outside of a normal form or concurrent program connection.

§Example

USER_ID : The USER_ID number

RESP_ID : The ID number of the responsibility

RESP_APPL_ID: The ID number of the application to which the responsibility belongs

fnd_global.APPS_INITIALIZE (1010, 20417, 201);

  

 

 

 

Required fields for Payables Open Interface Import

Overview of Required fields for Payables Open Interface Import Records

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

TEST CASE FOR SIMPLE INVOICES - NOT PO MATCHED OR PROJECT RELATED 

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

  AP_INVOICE_INTERFACE

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

    Required Columns

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE_S.NEXTVAL)

    INVOICE_NUM                            (Must be unique to the supplier)

    VENDOR_ID or VENDOR_NUM or VENDOR_NAME (An active vendor. Validated against PO_VENDORS)

    VENDOR_SITE_ID or VENDOR_SITE_CODE     (An active pay site. Validated against PO_VENDOR_SITES)

    INVOICE_AMOUNT                         (Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type)

    ORG_ID                                 (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID)

    SOURCE                                 (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')

    Optional Columns

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

    INVOICE_DATE                           (Defaulted to SYSDATE)

    INVOICE_TYPE_LOOKUP_CODE               (Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT') 

    INVOICE_CURRENCY_CODE                  (Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)

    EXCHANGE_RATE_TYPE                     (Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE)

    TERMS_ID or TERMS_NAME                 (Defaulted from PO_VENDOR_SITES.TERMS_ID)

    DOC_CATEGORY_CODE                      (Only populated if using automatic voucher number)    

    PAYMENT_METHOD_LOOKUP_CODE             (Defaulted from PO_VENDOR_SITES.PAYMENT_METHOD_LOOKUP_CODE)

    PAY_GROUP_LOOKUP_CODE                  (Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE)

    ACCTS_PAY_CODE_COMBINATION_ID          (Defaulted from PO_VENDOR_SITES.ACCTS_PAY_CODE_COMBINAITON_ID)

    GROUP_ID                               (Group identifier. Suggest to use it)

    STATUS                                 (DO NOT POPULATE IT)  

  AP_INVOICE_LINES_INTERFACE

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

    Required Columns for LINE_TYPE_LOOKUP_CODE = 'ITEM'

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)

    INVOICE_LINE_ID                        (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)

    LINE_NUMBER                            (A unique number to the invoice)

    LINE_TYPE_LOOKUP_CODE                  ('ITEM')

    AMOUNT                              

    ACCOUNTING_DATE                        (Optional. Defaulted from INVOICE_DATE or SYSDATE)   

    DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID

                                           (Validated against

                                            - 'Parent' must be 'No' for All the segments values

                                            - DIST_CODE_CONCATENATED needs to meet the security rules

                                            - DIST_CODE_COMBINATION_ID must be in

                                              select code_combination_id from gl_code_combinations

                                              where account_type = 'E' and enabled_flag = 'Y' and summary_flag = 'N' 

                                              and sysdate between nvl(start_date_active, sysdate-1)

                                              and nvl(end_date_active, sysdate+1))

    Required Columns for LINE_TYPE_LOOKUP_CODE = 'TAX'

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)

    INVOICE_LINE_ID                        (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)

    LINE_NUMBER                            (A unique number to the invoice)

    LINE_TYPE_LOOKUP_CODE                  ('TAX')

    TAX_CODE or TAX_CODE_ID                (Validated against AP_TAX_CODES_ALL)

    AMOUNT                               

    ACCOUNTING_DATE                        (Optional. Defaulted from INVOICE_DATE or SYSDATE)

    DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID 

                                           (Optional. 

                                            Defaulted from AP_TAX_CODES.TAX_CODE_COMBINATION_ID.

                                            If one of them is populated, then validated against

                                            - 'Parent' must be 'No' for All the segments values

                                            - DIST_CODE_CONCATENATED needs to meet the security rules

                                            - DIST_CODE_COMBINATION_ID must be in

                                              select code_combination_id from gl_code_combinations

                                              where account_type = 'E' and enabled_flag = 'Y' and summary_flag = 'N' 

                                              and sysdate between nvl(start_date_active, sysdate-1)

                                              and nvl(end_date_active, sysdate+1))

    Required Columns for LINE_TYPE_LOOKUP_CODE = 'FREIGHT'

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)

    INVOICE_LINE_ID                        (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)

    LINE_NUMBER                            (A unique number to the invoice)

    LINE_TYPE_LOOKUP_CODE                  ('FREIGHT')

    AMOUNT                               

    ACCOUNTING_DATE                        (Optional. Defaulted from INVOICE_DATE or SYSDATE)

    DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID 

                                           (Optional. 

                                            Defaulted from AP_SYSTEM_PARAMETERS.FREIGHT_CODE_COMBINATION_ID.

                                            If one of them is populated, then validated against

                                            - 'Parent' must be 'No' for All the segments values

                                            - DIST_CODE_CONCATENATED needs to meet the security rules

                                            - DIST_CODE_COMBINATION_ID must be in

                                              select code_combination_id from gl_code_combinations

                                              where account_type = 'E' and enabled_flag = 'Y' and summary_flag = 'N' 

                                              and sysdate between nvl(start_date_active, sysdate-1)

                                              and nvl(end_date_active, sysdate+1))

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

TEST CASE FOR PO MATCHED INVOICES 

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

  AP_INVOICE_INTERFACE

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

    Required Columns

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE_S.NEXTVAL)

    INVOICE_NUM                            (Must be unique to the supplier)

    PO_NUMBER                              (An approved, not cancelled, not closed or final closed PO.

                                            Validated against PO_HEADERS)

    INVOICE_AMOUNT                         (Positive Amount)

    SOURCE                                 (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')

    ORG_ID                                 (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS_ALL.ORG_ID)

    Optional Columns

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

    INVOICE_DATE                           (Defaulted to SYSDATE)

    INVOICE_TYPE_LOOKUP_CODE               (Defaulted to 'STANDARD')

    INVOICE_CURRENCY_CODE                  (Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)

    EXCHANGE_RATE_TYPE                     (Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE)

    TERMS_ID or TERMS_NAME                 (Defaulted from PO_VENDOR_SITES.TERMS_ID)

    DOC_CATEGORY_CODE                      (Only populated if using automatic voucher number)    

    PAYMENT_METHOD_LOOKUP_CODE             (Defaulted from PO_VENDOR_SITES.PAYMENT_METHOD_LOOKUP_CODE)

    PAY_GROUP_LOOKUP_CODE                  (Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE)

    ACCTS_PAY_CODE_COMBINATION_ID          (Defaulted from PO_VENDOR_SITES.ACCTS_PAY_CODE_COMBINAITON_ID)

    GROUP_ID                               (Group identifier. Suggest to use it)

    STATUS                                 (DO NOT POPULATE IT)  

  AP_INVOICE_LINES_INTERFACE

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

    Required Columns for PO Matched Lines

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)

    INVOICE_LINE_ID                        (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)

    LINE_NUMBER                            (A unqiue number to the invoice)

    LINE_TYPE_LOOKUP_CODE                  ('ITEM')

    AMOUNT                                 (Should be QUANTITY_INVOICED * UNIT_PRICE)

    If MATCH_OPTION is 'P', then populate:

      RELEASE_NUM or PO_RELEASE_ID               (For Blanket Release only, validated against PO_RELEASES_ALL)

      PO_NUMBER or PO_HEADER_ID                  (Validated against PO_HEADER_ALL)

      PO_LINE_NUMBER or PO_LINE_ID               (Validated against PO_LINES_ALL)

      PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID     (Validated against PO_LINE_LOCATIONS_ALL)

    If MATCH_OPTION is 'R', then populate:

      RECEIPT_NUMBER                             (Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM)

      RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID  (Validated against RCV_TRANSACTIONS)

    Optional Columns for PO Matched Lines

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

    QUANTITY_INVOICED                      (Populated if different from PO shipment)

    UNIT_PRICE                             (Populated if different from PO shipment) 

    MATCH_OPTION                           ('P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION)

    ACCOUNTING_DATE                        (Defaulted from INVOICE_DATE or SYSDATE)

    FINAL_MATCH_FLAG                       (Populated 'Y' if it is final matching)

    INVENTORY_ITEM_ID                      (Validated against PO_LINES.INVENTORY_ITEM_ID)

    INVENTORY_DESCRIPTION                  (Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION)

    SHIP_TO_LOCATION_CODE                  (Populated if different from PO shipment)

    PRICE_CORRECTION_FLAG                  (Populated 'Y' if it is price correction)

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

TEST CASE FOR PROJECT RELATED INVOICES

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

  AP_INVOICE_INTERFACE

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

    Required Columns

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE_S.NEXTVAL)

    INVOICE_NUM                            (Must be unique to the supplier)

    VENDOR_ID or VENDOR_NUM or VENDOR_NAME (An active vendor. Validated against PO_VENDORS)

    VENDOR_SITE_ID or VENDOR_SITE_CODE     (An active pay site. Validated against PO_VENDOR_SITES)

    INVOICE_AMOUNT                         (Positive amount)

    ORG_ID                                 (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS_ALL.ORG_ID)

    SOURCE                                 (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')

    Optional Columns

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

    INVOICE_DATE                           (Defaulted to SYSDATE)

    INVOICE_TYPE_LOOKUP_CODE               (Defaulted to 'STANDARD')

    INVOICE_CURRENCY_CODE                  (Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)

    EXCHANGE_RATE_TYPE                     (Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE)

    TERMS_ID or TERMS_NAME                 (Defaulted from PO_VENDOR_SITES.TERMS_ID)

    DOC_CATEGORY_CODE                      (Only populated if using automatic voucher number)    

    PAYMENT_METHOD_LOOKUP_CODE             (Defaulted from PO_VENDOR_SITES.PAYMENT_METHOD_LOOKUP_CODE)

    PAY_GROUP_LOOKUP_CODE                  (Defaulted from PO_VENDOR_SITES.PAY_GROUP_LOOKUP_CODE)

    ACCTS_PAY_CODE_COMBINATION_ID          (Defaulted from PO_VENDOR_SITES.ACCTS_PAY_CODE_COMBINAITON_ID)

    GROUP_ID                               (Group identifier. Suggest to use it)

    STATUS                                 (DO NOT POPULATE IT)  

  AP_INVOICE_LINES_INTERFACE

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

    Required Columns for project related lines

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

    INVOICE_ID                             (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)

    INVOICE_LINE_ID                        (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)

    LINE_NUMBER                            (A unique number to the invoice)

    LINE_TYPE_LOOKUP_CODE                  ('ITEM')

    AMOUNT                                 

    PROJECT_ID                             (Validated against PA_PROJECTS_ALL.PROJECT_ID)

    TASK_ID                                (Validated against PA_TASKS.TASK_ID)

    EXPENDITURE_TYPE                       (Validated against PA_EXPENDITURE_TYPES.EXPENDITURE_TYPE)

    EXPENDITURE_ITEM_DATE                  (Needs to be between the task start date and end date)

    EXPENDITURE_ORGANIZATION_ID            (Validated against PA_EXP_ORGS_IT.ORGANIZATION_ID)

    PA_QUANTITY                              

    Optional Columns for project related lines

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

    ACCOUNTING_DATE                        (Defaulted from Invoice Date or SYSDATE)

    PA_ADDITION_FLAG                       ('Y' if the distribution has been transferred into Oracle Projects)