How to setup Oracle iExpenses

Here is a summary of steps to set up Oracle Internet Expenses. iExpenses is basically an extension Oracle Payables. Employee and Continget Worker expense reports become supplier invoices and get paid from Payables. You will need following responsibilties to set up Internet Expenses: Payables Manager, Internet Expenses Setup and Administration, System Administration, Application Developer, and AX Developer. If you are also planning on charging expense reports to projects, you will also need Project Billing Super User and General Ledger Super User responsibilities. You will also need access to Oracle Workflow Builder to customize the Expenses workflow and Project Expense Reports Account Generator.

Oracle Internet Expenses Setup Steps:

Step 1: PA: Enable Project Expenditure Types for Expense Report Entry.

Navigation: Project Billing Super User: Setup > Expenditures > Expenditure Types. Enable expenditure types to be used on project-related expense reports. Enable selected expenditure types with an Expenditure Type Class ‘Expense Reports’. You need to associate these expenditure types with Expense Type you define in the Expesen Report Template (next step).

Step 2: AP: Define Expense Report Templates

Navigation: Payables Manager: Setup > Invoice > Expense Report Template. You must define at least one expense report template with the Enable for Internet Expenses Users check box selected. Only expense report templates with this option enabled can be used in Internet Expenses. Use the Oracle Payables Expense Report Templates window to define your expense report templates. Default default natural account for non-project expenses. For project-related expenses, associate your expense types with project expenditure types.

Step 3: AP: Define Financials Options > Accounting

Navigation: Payables Manager: Setup > Options > Financials. You define the Expense Clearing Account in the Oracle Payables Financials Options window. This will be a default liability account for iExpenses expenses reports imported into Oracle Payables. The Expense Clearing Account field is also available in the Card Program window. If you define the Expense Clearing Account field in the Card Program window, the value you define there will take precedence over the value in the Oracle Payables Financial Options window.

Step 4: AP: Define Financials Options > Human Resources

Navigation: Payables Manager: Setup > Options > Financials. Use the Payables Financials Options window to define the Expense Report Reimbursement Address and Employee Numbering Method.

Step 5: AP: Establish Multiple Currencies Setup

Navigation: Payables Manager: Setup > Options > Payables > Currency. The currency in which an expense report is paid is known as the reimbursement currency. Internet Expenses users can specify a reimbursement currency that is different from your company functional currency only ifOracle Payables is set up for multiple currencies.

Step 6: AP: Defining Expense Report Options

Navigation: Payables Manager: Setup > Options > Payables > Expense Reports. Define the fields below:

* Default Template. The default expense report template that you want to use in the Payables Expense Reports window. You can override this value in the Expense Reports window. A default expense report template appears in the Expense Reports window only if the expense report template is active.

* Payment Terms. Payment terms you want to assign to any suppliers that you create from employees during Expense Report Import. Define and assign immediate payment terms for your employee suppliers.

* Pay Group. Pay Group you want to assign to employee expense reports, e.g. EMPLOYEES. You must define this pay group in the Purchasing Lookups window.

* Payment Priority. Payment priority for employee expense reports. Choose a number between 1 (high) and 99 (low) to be the priority of employee payments.

* Apply Advances. If you enable this option, Payables applies advances to employee expense reports if the employee has any outstanding, available advances. You can override this default during expense report entry.

* Automatically Create Employee as Supplier. You must enable this option, if you want to import employee expense reports and automatically create a supplier for any expense report where an employee does not already exist as a supplier.

* Hold Unmatched Expense Reports. This option defaults to the Hold Unmatched Invoices option for the supplier and supplier site for any suppliers Payables creates during Expense Report Import.

Step 7: AP: Assign Signing Limits

Navigation: Payables Manager: Employees > Signing Limits. Managers can approve an expense report only if the total amount of the report does not exceed their signing limit defined in Accounts Payable. When you assign signing limits to a manager, you specify a cost center to which the signing limit applies. You have to give managers signing limits for multiple cost centers, if employees from multiple cost centers submit expense reports to him/her.

Step 8: OIE: Define iExpense Policies

Navigation: Internet Expenses Setup and Administration: Internet Expenses Setup > Policy > Expense Fields. Use the pages in the Policy region to set up online policy compliance and perdiem and mileage rates.

* Schedules. Create rate and policy schedules for your employees to use when they submit expense reports.

* Expense Fields. Set up expense fields to capture additional information on expense reports.

* Exchange Rates. Set up exchange rate definitions to validate the exchange rates that employees enter on their expense reports for foreign currency receipts.

Step 9: OIE: Enable Expense Allocations

Navigation: Internet Expenses Setup and Administration: Internet Expenses Setup > Accounting > Define. There are two tasks to complete for setting up expense allocations:

* Use the Internet Expenses Setup responsibility to define which segments of the accounting flexfield segments are visible and updatable by the user.

* Use the OIE: Enable Expense Allocations profile option to enable expense allocations according to your requirements. You can set the display of accounting flexfieldsegments and online validation as user-defineable or automatic.

Step 10: OIE: Define Receipt Notification Rule Set

Navigation: Internet Expenses Setup and Administration: Internet Expenses Setup > Audit > Notification Rules. Create one or more notification rule sets to determine when to send notifications to users for overdue or missing receipts.

Step 11: OIE: Assign Receipt Notification Rule Set

Navigation: Internet Expenses Setup and Administration: Internet Expenses Setup > Audit > Notification Rule Assignments. Use the Notification rule set assignments pages to assign the notification rule sets that you created to the operating units that you want.

Step 12: OIE: Define Mileage Rate Schedule.

Navigation: Internet Expenses Setup and Administration: Internet Expenses Setup > Policy > Schedules > Mileage. A mileage rate schedule can take into account distance traveled, type and category of vehicle, type of fuel, and the number of passengers. Set up one or more mileage rate schedules and schedule periods that you require for employee expense reporting.

Step 13: AP: Complete Mileage Expense Type Definition in Payables

Navigation: Payables Manager > Setup > Invoice > Expense Report Templates. Find the expenses template, find the mileage expense type, and assign the Mileage Schedule.

Step 14: SA: Define New iExpenses Responsibilities

Navigation: System Administration: Security > Responsibility. Create a new iExpenses responsibility.

Step 15: SA: Define OIE Profile Options

Navigation: System Administration: Profile > System. Set Internet Expenses related profile options according to your business requirements. Below is a brief description of each profile.

OIE: Allow Credit Lines. Set the profile option to Yes to enable users to enter negative receipts (credit lines). Users enter negative receipts to report the refund of a previously reimbursed expense, for example, an unused airline ticket. The default value is Yes.

OIE: Enable Credit Card. Set the profile option to Yes to enable the credit card functionality to allow users with corporate credit cards to select and add credit card transactions to their expense reports.

OIE: Allow Non-Base Pay. Set the profile option to Yes to enable users to choose the reimbursement currency for their expense reports. You must set up Payables to use multiple currencies before you can enable this profile option.

OIE: CC Approver Req profile option indicates whether users must enter an alternate approver when they charge their expense reports to a cost center different from their own. Set the profile option to Yes to require employees to enter the Alternate Approver field when employees enter a cost center other than their default cost center. If you set this profile option to Yes, you must also set the OIE: Enable Approver profile option to Yes.

OIE: CC Payment Notify. Use the profile option to specify whether a notification is sent to employees when payment is created in Oracle Payables for corporate credit card transactions. The default value is No.

OIE: Enable DescFlex profile option enables Internet Expenses to display descriptive flexfields. You must set up descriptive flexfields specifically for use in Internet Expenses before you can enable this option.

OIE: Enable Projects profile option enables users to enter project-related information on expense reports. You must set up Internet Expenses to integrate with Oracle Projects before you can enable this option.

OIE: Enable Tax profile option enables the availability of tax-related elements on expense reports.

OIE: Enable Approver profile option enables the Alternate Approver field in Internet Expenses. When this profile option is set to Yes, the Alternate Approver field is available for users to specify a different employee to approve their expense report. When this profile option is set to No, the Alternate Approver field is hidden.

OIE: Approver Required profile option indicates whether users must designate an approver for their expense reports. If you set it to Yes, Internet Expenses requires that users always enter an alternate approver as defined in Oracle HRMS.

OIE: Purpose Required profile option controls whether users must enter a purpose when creating an expense report. A purpose is a brief description of the business activities that justify the expenses in a report

OIE: Report Number Prefix profile option specifies a prefix value for expense report numbers, e.g. EXP-. The expense report number becomes the corresponding invoice number when the expense report is converted into an invoice via the Expense Report Import program.

OIE: Grace Period profile option specifies the number of grace period days beyond an end date that certain OIE setup items remain available for use. The default value is 30.

OIE: Enable Policy profile option controls the behavior of Internet Expenses in relation to reports that contain policy violations.

OIE: Enable Expense Allocations profile option determines whether an end user can update the cost center segment value on an expense line.

PA: Allow Project Time and Expense Entry profile option enables users to enter project-related information on expense reports. If you set this option to Yes, then you must set the OIE: Enable Projects profile option to Yes as well.

PA: AutoApprove Expense Reports profile option permits automatic approval of project-related expense reports.

Journals: Display Inverse Rate profile option determines how the reimbursable amount is calculated when users enter foreign currency receipts. When this profile option is set to No, the receipt amount is multiplied by the exchange rate to determine the reimbursable amount. When it is set to Yes, the receipt amount is divided by the exchange rate. The default value is No.

AME: Installed profile option enables the integration between Internet Expenses and Oracle Approvals Management. Enabling this profile disables Oracle Workflow expense report approvals!

WF: Notification Reassign Mode profile option determines the forwarding functionality that is available to employees. See Do You Want to Delegate or Transfer That Oracle Notification? article.

WF: Mailer Cancellation Email profile option enables the functionality that sends the cancellation notifications when time outs are reached for a notification and a new notification is sent because of resend setup.

Step 16: WF: Customize Project Expense Report Account Generator.

This step will be described in detail in a separate IAF article.

Step 17: GL: Assign Your Customized Project Expense Report Account Generator to your chart of accounts

Navigation: General Ledger Super User: Setup > Financials > Flexfields > Key > Accounts. Select your accounting structure to which you want to assign the process. Find the Project Expense Report Account Generator Item Type. Select a Process Name you define in the previous step. Save your changes.

Step 18: SA: Define a new OIEADMIN Role

Navigation: System Administrator: Security > Users. Create a new Oracle OIEADMIN user. Run the Synchronize Local WF tables process every time you make changes to user setup.

Step 19: WF: Define Workflow Notification Performers.

Perform the steps in Oracle Workflow Builder to set up expense report performers. This step will be documented in detail later on.

Step 20: Personalize Expense Report Submission Instructions

* As System Administrator: Set profile option ‘Personalize Self-Service Defn’ to Yes.

* Navigate to Expenses Home page in your iExpenses responsibility

* Create and submit an expense report

* In the Confirmation page, click the Personalize Submission Instructions Header link in the Submission Instructions region.

* In the Choose Personalization Context page, enter Your Business Group in the Organization field and click Apply.

* In the Personalize Region page, click Personalize for the Raw Text item.

* In the Personalize Raw Text page, select False for the Rendered row at the Site level, then click Apply.

* In the Personalize Region page, click Create Item for the Header: Submission Instructions item.

* In the Create Item page, select the “Raw Text” value from the Item Style list.

* Complete the page according to your business requirements: ID = XYZ_SUBMISSION_INSTRUCTIONS Text: Include the text message. Add Your Company’s Submission Instructions Here. Click Apply.

* In the Personalize Region page, click Personalize for the message you created.

* In the Personalize Raw Text page, enter a message in the Text field for the appropriate level, then click Apply.

* In the Personalize Region page, click Return to Application.

* As System Administrator: Set profile option ‘Personalize Self-Service Defn’ to No.

Step 21: AD: Compile the Expense Types Descriptive flexfield.

Navigation: Application Developer: Application > Validation > Set

* Query value set name ‘OIE_EXPENSE_TYPES’.

* Click Edit Information. In the Table Columns section, for the ID column, change the Size to 30 and Save.

* Navigate: Flexfield > Descriptive > Segments

* Query the Title ‘Expense Report Line’

* Freeze and compile the Expense Report Line Flexfield.

Step 22: Enable the Display of Project and Task

Navigation: AK Developer responsibility. Navigate to the Define Regions window. Use the Region Items window to enable the display of project and task information. You need to perform this step in order to view projects and tasks in View Expense Report History:

* Query the region ICX_AP_EXP_LINES_D.

* Choose Region Items to navigate to the Region Items window.

* Query the region items ICX_PROJECT_NUMBER item (ATTRIBUTE_NAME).

* Check the Node Display box for these region items.

* Query the region items ICX_TASK_NUMBER item (ATTRIBUTE_NAME).

* Check the Node Display box for these region items.

* Save your work.

Other Configuration Considerations

* Make sure all expense approving managers are set up as Oracle users.

* Make sure every employee is assigned to one Oracle user only!

* Submit the Synchronize WF LOCAL tables process regurarly to update the Workflow resource information

R12 Oracle E-Business Suite

Move Order Source Types

Move Order Source Types

Oracle Inventory provides three types of move orders:

·         Move Order Requisitions

·         Replenishment Move Orders

·         Pick Wave Move Orders

The move order type refers to the entity that created the move order. For all move orders, the final result is one of the two supported transactions: subinventory or move order issue.

Move Order Requisition

The requisition is a manually generated request for a move order. You can generate requests for subinventory transfers or move order issues. The requisition can optionally go through an Oracle Workflow approval process before it becomes a move order. If no approval process is used, the requisition becomes a move order immediately.

Replenishment Move Order

These move orders are generated automatically. For example, kanban cards can create a replenishment move order where the pull sequence calls for a subinventory transfer
(intra-organization kanbans), or by min-max planned items where the item’s replenishment source (set at the item-subinventory level) is another subinventory. In this case, the min-max or replenishment count report automatically generates a move order. Replenishment move orders are pre-approved and ready to be transacted.

The type of move order generated depends on the replenishment source:

·         If the replenishment source is a supplier, then a move order requisition is created.

·         If the replenishment source is another inventory organization, then an internal requisition is created.

·         If the replenishment source is a subinventory then a replenishment move order is created. Note that the source subinventory must be set up at the item subinventory level. For the min-max report to generate a move order, the report must be generated at the subinventory level.

For kanban to generate a move order, the pull sequence source type must be set to Intra-org.

Pick Wave Move Order

The pick release process generates move orders to bring the material from its source location in stores to a staging location, which you define as a subinventory in Oracle Inventory. This transaction is a subinventory transfer.

These move orders are generated automatically by the Oracle Shipping Execution pick release process.

These orders are pre-approved and ready to transact. Pick slips and other shipping and inventory reports will also be available with Oracle.

WIP Issue Move Orders

An optional process in Oracle WIP allows you to run pick release for the components of a range of jobs or schedules. This process creates move orders to either directly issue the material to WIP, or to move the material to the backflush locator. These orders are automatically approved, the picking rules are sued to allocated material for the job, and pick slips can be printed for warehouse users.

Move Order transaction from multiple sub-inventories
Before doing multiple sub-inventory Move Order transaction the on hand availabilities are below.

 
Navigation: MCR INV > Move Orders > Move Orders

Enter the values as per the below screen shot and save the changes.

Click on Approve and status become as Approved.

Now Allocate the Move order go to below navigation

Navigation: MCR INV > Move Orders > Transact Move Orders

Give the Move Order number as 116 and click on Find button.

Check the select check box and click on Allocate. When the latest transacted sub-inventory does not have in-sufficient stock, system will get the stock from other sub-inventory to full fill the required quantity at that time allocation field will display as Multiple. 

Click on View/Update Allocation and we can observe that system Split the 600 Qty into 300 qty from MIH-S1 and MIH-S2 sub-inventories like below screen shot. At MIH-S1 the on hand availability was only 300. For rest of 300 quantities system will allocate from MIH-S2 based on latest transacted sub-inventory.

Now if we generate Move Order Pick Slip report through Toolbar View > Request > Submit View a New Request with below screen shot details and click on Submit.

 
Enter the Parameters as Move Order Number From and to 118.
If we require sending notification to source warehouse/sub-inventory in-charger/Manager we can specify the in-charger/Manager at below navigation.
At the time of generating Move Order Pick Slip report we have button called Options and click on Options and enter the name of source warehouse/sub-inventory in-charger/Manager to send the pick slip notification.

Click on OK button and submit the request.

The Task status of the move order will be pending in Report output.

Now Check the check box and click on Transact button.

Now if we generate Move Order Pick Slip report through Toolbar View à Request à Submit View a New Request with below screen shot details with Notify the Name as CHARAN and click on Submit. The Task status of the move order will be completed in Report output.

CHARAN has got Move Order Pick Slip notification.

If we click on notification link we will get the details like below and click on OK.

Backend Table for Move Order Transactions:

SELECT * FROM MTL_TXN_REQUEST_HEADERS WHERE REQUEST_NUMBER='7146234' 

SELECT * FROM MTL_TXN_REQUEST_LINES WHERE HEADER_ID=' 7146234'

How to Enable Personalization link for OAF Pages

Set the values of following profiles to enable Personalization Page link in OAF Pages
 

Profile Name	Value
FND: Personalization Region Link Enabled	Yes
Personalize Self-Service Defn	Yes
Disable Self-Service Personal	No

 
After Setting the Values of above profiles
 

Current Running SQL for a Concurrent Program

SELECT C.sql_text
,C.module
FROM APPS.fnd_concurrent_requests A
,V$SESSION B
,V$SQLAREA C
WHERE A.oracle_session_id = B.audsid
AND B.sql_hash_value = C.hash_value
AND A.request_id = <p_request_id>;
Pass the Request ID for the above Query.
SELECT SQLT.hash_value
,SQLT.sql_text
,VSES.username
,VSES.module
,VSES.command
FROM v$sqltext SQLT
,v$session VSES
,APPS.fnd_concurrent_requests FCONC
WHERE SQLT.hash_value = VSES.sql_hash_value
AND FCONC.oracle_session_id = VSES.audsid
AND FCONC.request_id = <p_request_id>
ORDER BY SQLT.piece

AR (Receivables) Key Table in Oracle APPS R12

AR (Receivables) Key Table in Oracle APPS R12

1. What is TCA? Tables?

A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.

HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:

Organization   for example, Oracle Corporation
Person            for example, Jane Doe
Group              for example, World Wide Web Consortium
Relationship    for example, Jane Doe at Oracle Corporation.

HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.

HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.

HZ_CUST_ACCT_SITES_ALL

HZ_CUST_SITE_USES_ALL

HZ_CUST_CONTACT_POINTS

2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?

A) Customer Conversion:

Interface Tables       : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,

RA_CONTACT_PHONES_INT_ALL,

RA_CUSTOMER_BANKS_INT_ALL,

RA_CUST_PAY_METHOD_INT_ALL

    Base Tables             : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,

                                    RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc

B) Auto Invoice:

    Interface Tables       : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL

RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL

    Base Tables             : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,

RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL

C) AutoLockBox:

    Interface Tables       : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)

    Interim tables                       : AR_INTERIM_CASH_RECEIPTS_ALL (All  Populated by Submit Validation)

           : AR_INTERIM_CASH_RCPT_LINES_ALL,

            AR_INTERIM_POSTING

    Base Tables             : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,

                                       AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)

3. What are the tables in which Invoices/transactions information is stored?

A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.

RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).

RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.

The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.

4. What are the tables In which Receipt information is stored?

A) AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.

Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.

AR_CASH_RECEIPTS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.

AR_RECEIVABLE_APPLICATIONS_ALL, The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter. Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.

5. What are the tables in which Accounts information is stored?

RA_CUST_TRX_LINE_GL_DIST_ALL

AR Invoice Interface

AR Invoice Interface

AR INVOICE INTERFACE
************************

Interface Tables are :-
-----------------------
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_errors_all

Concurrent Program to import data from interface tables :-
------------------------------------------------------------
Autoinvoice Import Program

Script to Migrate data from Staging table to Interface Table
------------------------------------------------------------------------CREATE OR REPLACE PROCEDURE xxx_ar_invoice_api (errbuf out varchar2, rectcode out varchar2)
AS

l_org_id hr_operating_units.organization_id%type;
l_sob_id hr_operating_units.set_of_books_id%type;
l_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%type;
l_gl_id_rev ra_cust_trx_types_all.gl_id_rev%type;
l_cust_trx_type_name ra_cust_trx_types_all.name%type;
l_currency_code fnd_currencies.currency_code%type;
l_term_id ra_terms_tl.term_id%type;
l_term_name ra_terms_tl.name%type;
l_address_id hz_cust_acct_sites_all.cust_acct_site_id%type;
l_customer_id hz_cust_accounts.cust_account_id%type;
l_verify_flag char(1);
l_error_message varchar2(2500);


BEGIN

BEGIN
SELECT organization_id, SET_OF_BOOKS_ID
INTO l_org_id, l_sob_id
FROM hr_operating_units
WHERE name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Operating Unit...';

END;


BEGIN
SELECT cust_trx_type_id,name,gl_id_rev
INTO l_cust_trx_type_id,l_cust_trx_type_name, l_gl_id_rev
FROM ra_cust_trx_types_all
WHERE set_of_books_id = l_sob_id
AND org_id = l_org_id
AND name = 'xxx-Spares-Inv';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Invoice Type...';
END;


BEGIN
select currency_code
into l_currency_code
from fnd_currencies
where currency_code = 'INR';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Currency Code...';
END;
BEGIN
SELECT term_id,name
into l_term_id,l_term_name
FROM ra_terms_tl
WHERE upper(name) = upper('IMMEDIATE');
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Terms Name...';
END;


BEGIN
SELECT DISTINCT HCAS.cust_acct_site_id,HCA.cust_account_id
INTO l_address_id,l_customer_id
FROM hz_parties HP
,hz_party_sites HPS
,hz_cust_accounts HCA
,hz_cust_acct_sites_all HCAS
,hz_cust_site_uses_all HCSU
WHERE HCA.party_id = HP.party_id
AND HP.party_id = HPS.party_id
AND HCA.cust_account_id = HCAS.cust_account_id
AND HCAS.cust_acct_site_id = HCSU.cust_acct_site_id
AND HCSU.site_use_code = 'BILL_TO'
AND HCSU.primary_flag = 'Y'
AND upper (ltrim (rtrim (HP.party_name))) = upper (ltrim (rtrim ('Customer Name')))
AND HCAs.org_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Customer Name...';
END;


INSERT INTO ra_interface_lines_all(
INTERFACE_LINE_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
CUST_TRX_TYPE_ID,
cust_trx_type_name,
TRX_DATE,
GL_DATE,
CURRENCY_CODE,
term_id,
term_name,
orig_system_bill_customer_id,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
orig_system_bill_address_id,
ORIG_SYSTEM_BILL_ADDRESS_REF,
--orig_system_ship_customer_id,
--orig_system_ship_address_id,
orig_system_sold_customer_id,
QUANTITY,
--unit_selling_price
AMOUNT,
DESCRIPTION,
conversion_type,
conversion_rate,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
org_id
)
Values
(
RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
'Invoice Migration',
'LINE',
l_cust_trx_type_id,
l_cust_trx_type_name,
sysdate,
sysdate,
l_currency_code,
l_term_id,
l_term_name,
l_customer_id,
l_customer_id,
l_address_id,
l_address_id,
--85222,
--87978,
l_customer_id,
1,
--40000
4000,
'Test Invoice2 12JUN08',
'User',
1,
'Invoice Migration',
'RINV_000000002',
l_org_id
);



INSERT INTO ra_interface_distributions_all
(
INTERFACE_LINE_ID
,account_class
,amount
,code_combination_id
,percent
,interface_line_context
,interface_line_attribute1
,org_id
)
VALUES
(
RA_CUSTOMER_TRX_LINES_S.CURRVAL,
'REV'
,4000
,l_gl_id_rev
,100
,'Invoice Migration',
'RINV_000000002',
l_org_id
);

Commit;
END xxx_ar_invoice_api;
/