Oracle EBS Blog

Chart of Account - Overview

2012-01-21T11:41:00.000+05:30

ERP manages to achieve accounting requirements by having different segments and combining them into a COA Structure. The Chart of Accounts (COA) is an account structure composed of various dimensions capturing relevant information related to underlying transaction. This structure helps in capturing appropriate level of details while recording transactions which helps in deriving the financial account balances at the desired segment level.
A typical structure in ERP will have segments like below:

Legal Entity Capturing the Company Information
Cost Center Capturing Department or Cost Center Information
Natural Account Synonym with the tradition account and captures Asset, Liability, Income or Expense
Sub – Account Captures granular details of the Natural Account

ERP is the importance of the account type - while defining the Natural Account; the Natural Account value must have only one Account Type. The valid account types are primarily four – Asset, Liabilities, Income and Expense and some packages may include Share Holder Equity as another classification. Defining an account type as Income or Expense tells to the system that this is a revenue account and the opening balance of this account must be zero at the beginning of the financial yea.

Account Receivables Profiles

2011-12-19T10:12:00.000+05:30

AR: Allow Update of Existing Sales Credits
This profile option determines whether a user can update existing sales credits or if additional sales credit records need to be created to maintain an audit trail.

AR: Application GL Date Default
This profile option determines how Receivables determines the default GL Date when you apply receipts. Choose one of the following values:

Later of Receipt GL date and Invoice GL date:
Choose this value to use either the receipt GL date or the invoice GL date, whichever is later, as the default GL date for your receipt applications. This is the default value.

Later of Invoice GL date, and Current date:
Choose this value to use the receipt GL date, the invoice GL date, or the current date, whichever is later, as the default GL date for your receipt applications.This profile option can be set by the system administrator at the site, application, and responsibility levels but cannot be updated by the user.

AR: Automatic Contact Numbering
If you want to require users to provide a value for the Contact Number field, then enter No.If you enter Yes or leave this profile option blank, then Receivables automatically assigns a number to the field, and users cannot change it.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels.

AR: Bills Receivable Batch Source
This profile option determines the default bills receivable transaction batch source to use for bills receivable transactions. Enter a bills receivable batch source that you previously defined to default to the Bills Receivable window and the Bills Receivable Transaction Batches window.This profile option can be set only at the user level and can be updated by the user.

AR: Bills Receivable Batch Source
This profile option determines the default bills receivable transaction batch source to use for bills receivable transactions. Enter a bills receivable batch source that you previously defined to default to the Bills Receivable window and the Bills Receivable Transaction Batches window.This profile option can be set only at the user level and can be updated by the user.

AR: Change Customer on Transaction
This profile option determines whether you can update customer names in the Transaction windows. If this option is Yes, you can update the bill–to and ship–to names of customers on your transactions.Note: You cannot update the customer bill–to or ship–to information if the transaction is a chargeback, has activity against it, or has been posted, even if this profile option is set to Yes.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels, but cannot be updated by the user.

AR: Change Customer Name
This profile option allows you to change the party name in the customer standard form when set to Yes. When this option is set to no you cannot change the party name in the customer standard form once it is entered.

AR: Commit Between Validations in Lockbox
This profile option determines whether the AutoLockbox program will commit (save) after validating each record in a Lockbox transmission. If you set this profile option to No, AutoLockbox will only commit after validating all of the records in the transmission. This profile option can be set by the system administrator at the site, application, and responsibility levels.

AR: Create Bank Charges
This profile option determines whether users can create bank charges when entering receipts in Receivables. Other operations that this profile can affect include the following:

QuickCashPostBatch
The value for this option can be set by the system administrator at the site, application, and responsibility levels, but cannot be updated by the user.

AR: Credit Limit Selection
Use this profile option to enable cross currency credit check. By default, this profile option is set to Single. If this profile option is set to Single, your credit exposure will include only those transactions with currency that match the currency of the central credit limit. If this profile option is set to Multi, you can set up a central credit limit in one currency and calculate the exposure for all transactions.

AR: Customer Merge Commit Size
This profile option determines how many site uses are included in a merge set when you run the Customer Merge program from the Standard Request Submission windows. This profile option is not used when you submit the program from the Customers Merge window. A merge set contains one or more pairs of customer accounts to merge and is created based on these two rules, with the first rule taking precedence: Site uses for a customer account cannot be split between two merge sets.

AR: Customer Text Last Successful
This profile option stores the last successful run date of the customer text data creation and indexing process . You must run this customer text data creation and indexing process to acquire good search results in the Text tabbed region of the Find/Enter Customers window.

AR: Customers – Enter Alternate Fields
This profile option controls whether users can enter information in the Alternate Name field in the Customers and Customer Addresses windows. This field lets you enter a phonetic representation of a customer name. Alternate names are used primarily in Japan to enter and sort customer information using Kana characters.Receivables also uses this information when you choose Customer Name Sort as a parameter when printing certain Receivables reports (for example, the Customer Listing – Detail or Summary report). Refer to the AR: Sort Customer Reports by Alternate Fields profile option in this section.

AR: Debug Level for PostBatch
This profile option determines whether PostBatch is run in debug mode. If this option is set to Yes, enter one of the following values to indicate how extensive the debugging information saved to the log file will be:-Save only the most severe messages and errors (default setting).-Save messages that indicate entering and exiting various functions.–Save any useful and informative messages.-Save all other debug messages, including printing values of several important variables.

AR: Default Exchange Rate Type
This option determines the default exchange rate to use when converting foreign currency transactions to your functional currency. Valid values are:

Corporate Exchange Rate –
An exchange rate you define to standardize rates for your company. This rate is usually a standard market rate determined by senior financial management for use throughout the organization.

Spot Exchange Rate – An exchange rate you enter to perform a conversion based on the rate on a specific date.

User Specified Rate – An exchange rate you specify when entering a foreign currency transaction.

AR: Deposit Offset Account Source
This option indicates which accounting source to use for a deposit’s offset account. Receivables can use either AutoAccounting or the deposit’s transaction type as the accounting source for the offset account.

AR: Disable Receivable Activity Balancing Segment Substitution
This profile option lets you disable balancing segment substitution for receivable activities. This profile option does not affect the gain, loss, and rounding accounts that you define at the system options level.The default value is No. If you set this profile option to Yes, then you must define a suspense account in the event that your activities and original transactions do not post to the same balancing segment value.This profile option affects these activities:

Activity Applications These options are receipt write–off activity, short term debt, and claims investigation. See: Oracle Receivables Doc Update RELEASE 11i - Using the Account Generator in Oracle Receivables.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels but
cannot be updated by the user.

AR: Enable Debug Message Output
Use this profile option to help resolve errors that can occur when generating iReceivables pages, such as web pages that appear with missing fields or data that displays incorrectly. This profile option activates a debugging program that inserts comments into your HTML source files to help your system administrator determine the cause of any errors. This profile option can be set only at the user level and can be updated by the user.

AR: Enable SQL Trace
Use this profile option to activate SQL Trace, a program that creates a log file of each SQL procedure performed while executing Oracle Receivables functions. If this profile option is enabled and a SQL–related error occurs, Oracle Applications developers and your system administrator can use the log file the program creates to identify the cause. Accept the default setting of No for optimum performance during normal day–to–day processing. Enable this profile option before reproducing and researching SQL errors.

AR: Enable Debug Message Output
Use this profile option to help resolve errors that can occur when generating iReceivables pages, such as web pages that appear with missing fields or data that displays incorrectly. This profile option activates a debugging program that inserts comments into your HTML source files to help your system administrator determine the cause of any errors. This profile option can be set only at the user level and can be updated by the user.

AR: GL Transfer Balance Test
This profile option controls whether the General Ledger Interface program will reject debit and credit balances that are not equal before posting to the general ledger. Available values are:Yes: Receivables will reject unbalanced debits and credits before posting to the general ledger. These rejected unbalanced debits and credits will be listed in the Unposted Items Report which runs automatically during the posting program.No: Receivables will not reject unbalanced debits and credits before posting to general ledger.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels but cannot be updated by the user.

AR: Include Receipts at Risk in Customer Balance
This profile option controls whether Receivables displays items at risk and includes them when calculating customer account balances in the Account Details, Account Overview, and Customer Account windows.Set this option to No if you do not want to view items at risk in these windows or include them when calculating the balance due.

AR: Invoices with Unconfirmed Receipts
This profile option lets you choose whether to adjust or credit invoices selected for automatic receipt. You can adjust transactions that have been approved, but not confirmed. The options available are:Adjust:^(Further explanation forthcoming)Adjust and Credit:^(Further explanation forthcoming)Credit:^(Further explanation forthcoming)None:^(Further explanation forthcoming)The value for this option can be set by the system administrator at the site, application, responsibility, and user levels, but cannot be updated by the user.

AR: Item Flexfield Mode
This profile option lets you choose the format to use for entering flexfield information for Line Items for commitments in the Transactions and Item Tax Rate Exceptions windows. Available values are:Always Pop a Flexfield Window: The item flexfield always pops up as you navigate through the field.Concatenated Segment Entry: The item flexfield never pops up as you navigate through the field. Instead, you can type the item flexfield information directly into the field.No Window for a Single Segment Flexfield: The item flexfield will only pop if it has more than one segment enabled.The value you specify for this profile option at the user level takes precedence over the value that is set for this profile option by the system administrator at the site, application, responsibility, or user level.

AR: Mask Bank Account Numbers
Use this profile option to control the display of bank account numbers in Receivables windows. If you accept credit cards as payment for open debit items, Receivables displays the credit card number in the Bank Account field of the Customers, Transactions, and Receipts windows. You can limit access to this information by displaying only a portion of the number.No Masking: Do not mask any portion of the number.Mask – First Four Digits Visible: Show only the first four digits of the number. Display the remaining digits as asterisks(*).Mask – Last Four Digits Visible: Show only the last four digits of the number. Display the preceding digits as asterisks (*).The value you specify for this profile option at the user level takes precedence over the value that is set for this profile option by the system administrator at the site, application, responsibility, or user level.

AR: Maximum Lines per AutoInvoice Worker
This profile option lets you set a maximum number of lines per AutoInvoice worker.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels, but cannot be updated by the user. This profile option has no default value.

AR: Oracle Exchange Database Link Name
This profile option lets you enter the name of the database link between Oracle Exchange and Receivables when they are on two separate database instances. Set this profile option in Receivables after you create the database link to Exchange. You must set this profile option if you want to integrate your Exchange billing data with Receivables.The value for this option can be set by the system administrator at the site level only, and cannot be updated by the user. This profile option has no default value. Please refer to the Oracle Exchange and Oracle Sourcing System Operator Implementation Guide, Release 6.2.2 and above, for complete information on the Oracle Exchange Billing integration with Receivables.

AR: Override Adjustment Activity Account Option
This profile option lets you choose whether you can override the default adjustment account when entering or updating manual adjustments and when updating automatic adjustments. These changes must be made before posting to the general ledger.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels, but cannot be updated by the user.

AR: Receipt Batch Source
This is the default receipt source for the Batch field in the Receipts and Receipt Batches windows. Only Receipt sources with a Receipt Source Type of ’Manual’ will be displayed in the list of values. The value that you specify for this profile option at the user level takes precedence over the value that is set for this profile option by the system administrator at the site, application, responsibility, or user level.

AR: Sort Customer Reports by Alternate Fields
This profile option controls whether Receivables will sort information using the value of the Alternate Name field in the Customers window when you run certain reports. To use this option, the AR: Customers Enter Alternate Fields profile option must also be set to Yes. Reports that can use this option include: the Customer Profiles Report, Receipt History Report, Applied Receipts Register, Unapplied Receipts Register, Automatic Receipts Awaiting Confirmation, Print Statements, Transaction Register, and the Customer Listing Detail and Summary reports.

AR: Transaction Batch Source
This profile option determines the default invoice source that will appear in the Batch field of the Transaction and Credit Memo windows. Receivables only displays batch sources with a Transaction Source Type of ’Manual’ in the list of values for this option.The value you specify for this profile option at the user level in this window takes precedence over the value set for this profile option by the system administrator at the site, application, responsibility, or user level.

AR: Update Due Date
This profile option determines whether you can update the due date (payment schedule) of a transaction in the Installments and Account Details windows. Available values are:Yes: Receivables lets you update the due date of a debit item to any date that is greater than the transaction date of this item. If you change the due date of your debit item.

AR: Use Invoice Accounting For Credit Memos
This profile option determines whether to assign your credit memo to the same accounts that are assigned to the invoice you are crediting.The value for this option can be set by the system administrator at the site, application, responsibility, and user levels, but cannot be updated by the user.

Know ICM Status

2011-12-10T19:54:00.002+05:30

Method 1 : Using OAM (Oracle Application Manager)

Because 1 process is running that means the ICM is running. In this Form we can see if the other Concurrent Managers (CM) are running as well.

METHOD 2 : OS level (on UNIX)

ps -ef | grep FNDLIBR

ICM is not running

[oracle@APPS VIS_apps]$ ps -ef | grep FNDLIBR
oracle 17846 7256 0 01:20 pts/2 00:00:00 grep FNDLIBR
[oracle@APPS VIS_apps]$

ICM is running

[oracle@APPS VIS_apps]$ ps -ef | grep FNDLIBR
oracle 18069 18064 0 01:22 pts/2 00:00:00 FNDLIBR
oracle 18297 18137 0 01:22 ? 00:00:00 FNDLIBR
oracle 18298 18137 0 01:22 ? 00:00:00 FNDLIBR
oracle 18299 18137 0 01:22 ? 00:00:00 FNDLIBR
oracle 18303 18137 0 01:22 ? 00:00:00 FNDLIBR
oracle 18304 18137 0 01:22 ? 00:00:00 FNDLIBR
oracle 18623 7256 0 01:23 pts/2 00:00:00 grep FNDLIBR

Differences Between Standard PO, Blanket PO, Planned PO & Contract PO

2011-11-30T11:55:00.000+05:30

	Standard Purchase Order	Planned Purchase Order	Blanket Purchase Agreement	Contract Purchase Agreement
Terms and Conditions Known	Yes	Yes	Yes	Yes
Goods or Services Known	Yes	Yes	Yes	No
Pricing Known	Yes	Yes	May Be	No
Quantity Known	Yes	Yes	No	No
Account Distributions Known	Yes	Yes	No	No
Delivery Schedule Known	Yes	May Be	No	No
Can be Encumbered	Yes	Yes	Yes	No
Can Encumber Releases	N/A	Yes	Yes	N/A

APIs for Concurrent Processing

2011-11-20T12:05:00.000+05:30

FND_CONCURRENT.GET_REQUEST_STATUS

This API Returns the Status of a concurrent request. It also returns the completion text if the request is already completed. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

function get_request_status

(
request_id     IN OUT NOCOPY number,
appl_shortname IN varchar2 default NULL,
program        IN varchar2 default NULL,
phase          OUT NOCOPY varchar2,
status         OUT NOCOPY varchar2,
dev_phase      OUT NOCOPY varchar2,
dev_status     OUT NOCOPY varchar2,
message        OUT NOCOPY varchar2
) return boolean;

PARAMETERS

REQUEST_ID	Request ID of the program to be checked.
APPL_SHORTNAME	Short name of the application associated with the program. Default is NULL
PROGRAM	Short name of the concurrent program
PHASE	Request phase
STATUS	Request Status
DEV_PHASE	Request phase as a string constant
DEV_STATUS	Request status as a string constant
MESSAGE	Request completion message

FND_CONCURRENT.WAIT_FOR_REQUEST

This API waits for the request completion, then returns the request phase/status and completion message to the caller. It goes to sleep between checks for the request completion. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

function wait_for_request
(
request_id   IN number default NULL,
interval     IN number default 60,
max_wait     IN number default 0,
phase        OUT NOCOPY varchar2,
status       OUT NOCOPY varchar2,
dev_phase    OUT NOCOPY varchar2,
dev_status   OUT NOCOPY varchar2,
message      OUT NOCOPY varchar2
) return boolean;

PARAMETERS

REQUEST_ID	Request ID of the request to wait on. The default is NULL
INTERVAL	Number of seconds to wait between checks. Default is 60 seconds.
MAX_WAIT	Maximum number of seconds to wait for the request completion. Default is 00 seconds
PHASE	Request phase
STATUS	Request Status
DEV_PHASE	Request phase as a string constant
DEV_STATUS	Request status as a string constant
MESSAGE	Request completion message

USEFUL APIs

FND_CONCURRENT.SET_COMPLETION_STATUS	Called from a concurrent request to set its completion status and message
FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS	Returns the print options for a concurrent request
FND_CONCURRENT.GET_SUB_REQUESTS	Get all sub-requests for a given request id. For each sub-request it provides request_id, phase,status, developer phase , developer status and completion text
FND_CONCURRENT.CANCEL_REQUEST	It cancels a given Concurrent Request
FND_CONCURRENT.AF_ROLLBACK	It is used by a concurrent program that use a particular rollback segment and has no arguments.

Fixed Assets in R12

2011-11-02T12:32:00.000+05:30

Sub Ledger Accounting Architecture and Inquiries

1. Oracle Assets is fully integrated with SLA, which is a common accounting platform for Sub Ledgers.

2. Customers can use the seeded Account Derivation definitions or modify them as required.

3. Continue to support Account Generator functionality for existing Asset Books.

4. New SLA Accounting report and online account inquiry.

Enhanced Mass Additions for Legacy conversions

1. Enhancements have been made to the FA_MASS_ADDITIONS table. New Attributes have been added viz,

Asset Life
Depreciation Method
Prorate Convention
Bonus Rule Ceiling Name
Depreciation Limit Plus others

2. Customers can populate the values for the new attributes directly in the FA Mass Additions interface table rather than accepting default values from the asset category.

3. Legacy conversion can be completely automated

4. Web ADI has also been upgraded to include the new Attributes

New Automatic Preparation of Mass Additions

1. This new feature consists of default rules and Public APIs that can be used by customers to complete the preparation of mass addition lines automatically.

2. Auto populate required fields such as Expense Account, Asset Category etc.

3. Avoid manual intervention during the Mass Additions prepare process

4. Avoid customization and use public APIs to effect custom business logic

Assets now uses Flexible Reporting using XML publisher

1. Major Asset Transaction reports have been modified to support XML publisher.

2. Users can modify or use new templates to view report output.

3. XML reporting is available for the following thirteen asset reports:

Asset Transfers Report
Transaction History Report
Asset Reclassification Report
Mass Additions Create Report
Cost Adjustment Report
Cost Summary Report
CIP Summary Report
Reserve Summary
Journal Entry Reserve Ledger
Asset Additions Report
CIP Capitalization Report
Mass Additions Posting
Asset Retirements

Automatic Depreciation Rollback

1. Depreciation is rolled back automatically by the system when any transaction is performed on an asset if the following conditions are met:

Depreciation has been processed in that period
The period is not closed

2. It is no longer required to run depreciation rollback program manually.

3. Depreciation rollback is executed only on select assets as required and not on the entire Asset Book this enhances performance of the program.

4. Since release 11i, users have been able to run depreciation for an asset book without closing the period. If additional adjustments are required in the current period, then the user submits a process to roll back depreciation for the entire book, performs the necessary adjustment(s) and then resubmits the depreciation program.

5. In Release 12 the intermediate manual step of rolling back depreciation
for the entire book in order to process further adjustments on selected assets is no longer necessary. As before users may submit depreciation for the entire book prior to closing the period. If it becomes necessary to process financial
adjustments on one or more assets, the user may proceed with the transaction
normally via the asset workbench or mass transactions.

6. Oracle Assets automatically rolls back the depreciation on just the selected assets(instead of the whole book) and allows the transaction(s) to be processed normally. The asset(s) for which depreciation was rolled back is automatically picked up during the next depreciation run or at the time that the depreciation period is finally closed.

Enhanced functionality for Energy Industry

1. Energy Units of Production Method for Group Assets

Allow Production method for Group Assets
Allow Production upload to Group Assets

2. Energy Straight line Method

Net Book Value / Remaining Life

Enhanced Asset Tracking in Payables

1. Enhanced Asset Tracking in Payables

Item Description
Manufacturer
Model
Serial Number
Warranty Number

2. Enhanced Mass Additions

Asset Book
Asset Category
IPV/ERV distributions

3. With Release 12, Payables is introducing invoice lines into the invoice model. This entity has been added between the header and distributions and allows Payables to capture a third level of information. This new layer of data relates to the actual goods and services listed on the physical (or electronic) invoice document, including quantity, unit of measure, unit price, item description, manufacturer, model, serial number, and warranty number. Now that this information is stored for the invoice line, Oracle Payables can transfer it over to Oracle Assets where the information is an essential part of asset tracking. (For example, a single invoice line might be for a quantity of three computers, but each of these computers belongs to a different department represented by a cost center, so the one line consists of three distributions. The line can be distributed across the three departments, and each distribution represents the expense charged to each of those departments.)

4. The Create Mass Additions process has been enhanced to transfer these new fields into the interface table, FA_MASS_ADDITIONS. Invoice distributions are still the entity that gets transferred from Payables to Assets, but now the new asset tracking fields are included. Two additional changes to the Create Mass Additions process concern the Asset Book and Asset Category. In prior releases, these two values were available for PO/RCV matched distributions however the fields were not exposed in Payables, so no update was allowed in the Purchasing-Payables-Assets flow. In Release 12, Asset Book and Asset Category are new fields at the line and distribution levels (Asset Book only, not Asset Category) of an invoice.

5. Finally, Create Mass Additions has been enhanced to group the Invoice Price Variance and Exchange Rate Variance distributions to their corresponding item distributions since, in Release 12, these two expenses are tracked as separate distributions rather than being stored as columns on the item distribution, as in prior releases.

API to Purge an person from Oracle HRMS

2011-10-15T16:42:00.000+05:30

DECLARE
   l_person_org_manager_warning VARCHAR2 (200);
BEGIN
   hr_person_api.delete_person(p_validate => FALSE,
   p_effective_date => SYSDATE,
   p_person_id => :person_id,
   p_perform_predel_validation => FALSE,
   p_person_org_manager_warning => l_person_org_manager_warning
   );
   COMMIT;
END;

Before purging the person from Oracle HRMS we need to make sure that the employee and fnd_user link is been deleted and also the person should not have an active payroll.

If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from 'Employee' to 'Applicant' and then use the above API again to purge the record.

Request Group and Responsibility by Concurrent Program

2011-10-11T12:18:00.000+05:30

To debug an issue of any concurrent program which fails with errors we need to relogin to reproduce the error. It would be useful if we know in which request group and in which responsibility the concurrent program is available. The below SQL query will help us to get this information.

SELECT

frg.request_group_name,

frg.description,

cp.concurrent_program_id,

cp.concurrent_program_name,

cpt.user_concurrent_program_name,

frv.responsibility_name

FROM

fnd_request_groups frg,

fnd_request_group_units frgu,

fnd_concurrent_programs cp,

fnd_concurrent_programs_tl cpt,

fnd_responsibility_vl frv

WHERE

frg.request_group_id = frgu.request_group_id

AND frgu.request_unit_id = cp.concurrent_program_id

AND cp.concurrent_program_id = cpt.concurrent_program_id

AND frv.request_group_id = frg.request_group_id(+)

AND frg.request_group_name LIKE 'All Reports'

AND frg.description = 'All Purchasing SRS Reports'

AND cpt.user_concurrent_program_name LIKE 'Account Analysis Report%'

Oracle Receivables frequently used Tables

2011-09-17T22:21:00.001+05:30

AR Master Tables

• HZ_PARTIES
• HZ_CUST_ACCOUNTS
• HZ_PARTY_SITES
• HZ_CUST_ACCT_SITES_ALL
• HZ_CUST_SITE_USES_ALL
• HZ_LOCATIONS
• HZ_PARTY_RELATIONSHIPS
• HZ_ORGANIZATION_PROFILES
• HZ_CONTACT_POINTS
• HZ_CUST_ACCOUNT_ROLES
• HZ_PERSON_PROFILES
• HZ_ORG_CONTACTS

AR Transaction Tables

• RA_CUSTOMER_TRX_ALL
• RA_CUSTOMER_TRX_LINES_ALL
• RA_CUST_TRX_LINE_GL_DIST_ALL
• AR_PAYMENT_SCHEDULES_ALL
• AR_CASH_RECEIPTS_ALL
• AR_CASH_RECEIPT_HISTORY_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL

HZ_PARTIES
A party is an entity that can enter into a business relationship. This table stores basic information about parties, which is true regardless of this relationship to the deploying company. Entities are modeled only once in HZ_PARTIES, regardless of how many roles they play. For example, if an organization is a customer, a distributor, and a partner, there is still only one record for them in HZ_PARTIES.

HZ_LOCATIONS
A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.
• This table stores information about an address such as: street address and postal code.
• This table provides physical location information about parties (organizations and people) and customer accounts.
• Records in HZ_LOCATIONS can store delivery and postal code information about a location, store latitude and longitude, and can be used to determine the appropriate calculations and tax rates for sales tax and VAT calculations.

HZ_PARTY_SITES
This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
• One party can point to one or more party sites.
• One location can point to one or more party site.
• Party sites serve as the intersection between parties and locations, allowing for a many-to-many relationship between the two.

HZ_RELATIONSHIPS
This table stores information about relationships between two entities, for example, one party and another party.
• The SUBJECT_ID and OBJECT_ID columns specify the relationship that exists between two parties. For example, if the party relationship type is “Parent Of,” then a holding company could be the “SUBJECT_ID” in the relationship while one of its subsidiaries could be the OBJECT_ID. Creating a party contact causes a party relationship to be created.
• A party can have different relationships with one or more other parties that can change over time.

HZ_ORGANIZATION_PROFILES
This table stores a variety of information about a party of type Organization.
• This table gets populated when a party of type ORGANIZATION is created.
• Historical data is also stored in this table.

HZ_PERSON_PROFILES
This table stores a variety of information about a party of type Person.
• For example, this table could contain the correct spelling and phonetic pronunciation of the person’s name.
• Some information in this table may also be entered into the HZ_PARTIES table.

HZ_ORG_CONTACTS
This table stores a variety of information about an organization contact.
• The records in this table provide information about a contact position such as job title, rank, and department.
• This table is not used to store information about a specific person or organization. For example, this table may include a record for the position of Vice President of Manufacturing that indicates that the contact is a senior executive, but it would not include the name of the person in that position.

HZ_CUST_ACCOUNTS
This table stores information about customer/financial relationships established between a Party and the deploying company.
• Because a party can have multiple customer accounts, this table may contain several records for a single party. For example, an individual person may establish a personal account, a family account, and a professional account for a consulting practice.

HZ_CUST_ACCT_SITES_ALL
This table stores information about customer/financial account sites information.
Stores information about customer sites. One customer account can have multiple sites.

HZ_CUST_SITE_USES_ALL
This table stores information about the business purposes assigned to a customer account site.
• A customer account site can have multiple purposes, but each record in this table only specifies one purpose for a customer account site. For example, a customer account site may be assigned as a ship-to site in one record and as a bill-to site in another record.

HZ_CUST_ACCOUNT_ROLES
This table stores information about a role or function that a party performs as related to a customer account. For example, Jane Doe might be the Legal Contact for a specific customer account of Corporation ABC. Note that account ownership such as financial responsibility for an account is determined by the single party ID that is stored directly on the HZ_CUST_ACCOUNTS table.

HZ_CONTACT_POINTS
This table stores information about how to communicate with parties or party sites using electronic media or methods such as Electronic Data Interchange (EDI), e-mail, telephone, telex, and the Internet.
• Each medium or method should be stored as a separate method in this table. For example, the attributes of a complete telephone number connection should be stored in a record, while EDI information should be stored in a different record.

RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commit-ment, chargeback, bills receivable, and credit memo header information.
• Each row includes general invoice information such as customer, transaction type, and printing instructions.
• You need one row for each invoice, debit memo, commitment, and credit memo you create in Oracle Receivables and these are all distinguished by their transaction types stored in RA_CUST_ TRX_ TYPES_ALL.

RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines. It describes to the customer the charges that appear on these documents.

AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except adjust-ments and miscellaneous cash receipts. A miscellaneous cash receipt is one that is not connected to a customer.
• All customer-related activity is logged in this table.
• This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable, receipt, or commitments.

RA_CUST_TRX_TYPES_ALL
This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
• Each row includes Auto Accounting information as well as standard defaults for the resulting invoices. The primary key for this table is CUST_TRX_TYPE_ID.

AR_TRANSACTION_HISTORY_ALL
This table is a Bills Receivable-specific table containing the history of a transaction’s lifecycle.
• A new row is created each time there is activity on the transaction or the status of the transaction has changed.
• This table stores the header for the Receivables posting information.

AR_DISTRIBUTIONS_ALL
This table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions.

AR_CASH_RECEIPTS_ALL
This table stores one record for each receipt entry.
• All cash receipts are logged in this table.
• Oracle Receivables creates records concurrently in the AR_CASH _RECEIPT_ HISTORY_ ALL, AR_PAYMENT_ SCHEDULES_ ALL, AR_DISTRI-BUTIONS_ALL, and AR_ RECEIVABLE_ APPLICA-TIONS_ALL tables for invoice-related receipts.
• For receipts that are not related to invoices, records are created in the AR_MISC_CASH_ DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ ALL table.

AR_CASH_RECEIPT_HISTORY_ALL
This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.

AR_RECEIVABLE_APPLICATIONS_ALL
This table stores all accounting entries for cash and credit memo applications.
• Each row includes the amount applied, status, and accounting flex field information.

AR_MISC_CASH_DISTRIBUTIONS_ALL
This table stores all accounting entries for miscellaneous cash applications.
• Miscellaneous cash cannot be invoiced, such as stock revenue, interest income, and investment income.
• AR_CASH_RECEIPTS_ALL stores one record for each payment, and this table stores one record for each distribution of the receipt.

AR_RECEIPT_CLASSES
This table stores the different receipt classes that you define.
• Receipt classes determine whether the receipt[s] belonging to this class are created manually or automatically, and whether the receipts go through the different steps in a receipt’s life-cycle.

AR_RECEIPT_METHODS
This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications.
• For automatically created receipts, a Payment Method defines the rules for creating these receipts.
• For manually created receipts, a Payment Method defines a user-definable type for the receipt.
• Each Payment Method is associated with a set of bank accounts, which forms the set of bank accounts you can assign to your receipt.

AR_ADJUSTMENTS_ALL
This table stores information about the adjustment applied to the Invoices.

Weekly Oracle Jobs - September 1, 2011

2011-09-04T14:01:00.003+05:30

Oracle Projects - TransSys Solutions for onsite Geneva

TransSys Solutions is looking for Oracle EBS Projects (billing, budget,costing) technical/techno-functional consultant with 3 plus relevant experience in Projects module for immediate onsite location for Geneva for duration of 3 months. Freelancers are welcome. Please send your CV at mohammed.rafi@transsyssolutions.com

Oracle BRM Trainer Required For Europe

I am currently recruiting for a consultant who has solid experience of Oracle communications. The successful candidate will have the ability to deliver training on the following subject matter in a classroom environment. I am able to provide the training material and an environment to prepare in. The course that requires delivering is:

Billing and Revenue Management Billing and Finance 7.4 . This course is for 3 days, however there are two other courses that also need delivering to the same client all of which are based around Oracle communications. Please get in touch with Stuart Douglas for more details regarding this opportunity as well as the two other Oracle Communication courses that need delivering for further details about this role and as well as the number of other training opportunities we have available. I can be reached on +44-1689-669-280 or email at stuart@discovererinternational.com

Oracle Financial Functional Lead - New York, USA

We need Oracle Financial Functional Lead at NY for 6+ months with 3 implementations in modules AP, AR, GL, CM, FA and iExpenses, please forward your resume to dhar@aesinc.us.com

Oracle EBS R12 Experts - Chennai, India

We are in search of talented Oracle Apps R 12 Experts in Chennai for Doyen Systems Pvt. Ltd. In India. Mail your profile to gshubashree@hotmail.com and can be reached at 9965584464/9444084464.

Oracle EBS Developer and Functional Lead - London, UK

Looking for really strong Oracle E-Business Developers & Functional Analysts for roles based in London. Send a CV to daniel.campbell@rothstein.co.uk to be considered.

Great opportunity For " Oracle Apps P2P / iProcurement Techno Functional Consultant " at "Atlanta" Send resumes to resumes@enterprisepals.com

Duration: Long term
Location: Atlanta, GA
Rate: Market rate
Start Date: Immediate

APPS Forms developer - Mumbai, India

Oracle apps forms with 3-5yrs of candidates for a top notch product dev company at mumbai. please share any referrals for this position at jagadeesh.m@solix.com

APPS DBA - Bangalore/Hyderabad, India for Oracle Corporation

Looking for "Oracle apps DBA" with 5-14 yrs of exp. work location : Hyderabad & Bangalore ..if you anyone is interested plz send your profile on shweta.bnema@oracle.com or can reach me on +91-40-66054584

Oracle EBS Quiz 1

2011-08-28T12:15:00.001+05:30

In which year Oracle EBS first release happened ?

Rules @ Regulation :
-----------------------------
Currently open to India or NRI holding account in India.

Send the answer via email at rafi.aamiri@gmail.com or through SMS at +919841278679.

While sending via email mention the subject as OEQ1 and while sending through SMS send as OEQ1

Quiz is open till 2nd September (Friday) till 5 PM IST.

The first winner will get cash reward of INR 100 credit to his account.

Disable Password prompt for Diagnostics

2011-06-21T11:26:00.007+05:30

To disable prompting the APPS password for Diagnostics which includes Examine, Properties, Forms Personalization.

Set the value as Yes to the profile option "Utilities: Diagnostics" at any level.

Note : Please ensure that you Logoff and Login again to see the effect.

Sales Order – Flow of Line Status

2011-06-05T16:08:00.005+05:30

Entered [Order Management] Order is saved but not booked

Booked [Order Management] Order is Booked.

Awaiting Shipping [Order Management] Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

Not Ready to Release [Shipping Transactions] A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

Released to Warehouse [Shipping Transactions] Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

Ready to Release [Shipping Transactions] Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

Backordered [Shipping Transactions] The status of Backorderd is assigned to a line under the following circumstances.

●…The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available.
....In this case the system automatically backorders the discrepant quantity.

●…At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.

●…The user manually Backorders the entire delivery.

Shipped [Shipping Transactions] The delivery line is shipped confirmed.

Confirmed [Shipping Transactions] The delivery line is shipped or backordered and the trip stops are open.

Picked [Order Management] Pick release is complete, both allocations and pick confirm

Picked Partial [Order Management] This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

Interfaced [Shipping Transactions] The delivery line is shipped and Inventory interface concurrent process is complete.

Awaiting Fulfillment [Order Management] When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

Fulfilled [Order Management] All lines in a fulfillment set are fulfilled.

Interfaced to Receivables [Order Management] The order is linked with Receivables and the invoice is created.

Partially Interfaced to Receivables [Order Management] This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

Closed [Order Management] Closed indicates that the line is closed.

Canceled [Order Management] Indicates that the line has been completely canceled. No further processing will occur for this line.

R12 User Management Proxy User Functionality

2011-05-15T16:14:00.001+05:30

Setting up this functionality is actually very simple. To illustrate, I'll refer to two types of E-Business suite Users:

1) MANAGER - This is the User who will delegate responsibility to perform transactions on their behalf to the Administrator user.
……………………….In my examples, this user name is TESTMGR.

2) ADMINISTRATOR - This is the user who will sign on to the E-Business suite and assume the responsibilities of the Manager user.
……………………….…………In my examples, this user name is TESTADMIN.

STEP 1
- Connect to the R12 E-Business Suite as ANY User (e.g. SYSADMIN) with the responsibility 'User Management' and open the 'Users' form.
- Search for the user name of the MANAGER. In this case, the user name is TESTMGR.
- Assign the role 'Manage Proxies' to this user name e.g.

STEP 2
Now connect as the MANAGER user e.g. TESTMGR and navigate to the 'Preferences' form:

Click on the 'Manage Proxies' option and then, the 'Add People' button.

This will allow you to add the User Names of those Users that you wish to perform actions on your behalf:

STEP 3
Now, add the user names of all Users that you wish to assume your responsibilities.
In my example, I add the user name of my ADMINISTRATOR TESTADMIN:
Apply the changes and exit the E-Business suite.

STEP 4
OK - now comes the fun stuff.

Connect as the ADMINISTRATOR TESTADMIN:

Note - the new options 'Switch User' and 'Return to Self' available:

As its name suggest 'Switch User' is the magic button that allows you to 'become' the other User without having to enter their password i.e. TESTADMIN can become TESTMGR and access their responsibility list.

STEP 5
Select the 'People Icon' to switch your user to that of TESTMGR:

STEP 6
As if by magic, you are navigated to the home page of the proxied user, can access any of their current responsibilities and can perform action on their behalf in the E-Business that they themself are allowed to perform:

Note - the label 'Logged in As TESTADMIN Proxy For TESTMGR':

STEP 7
OK - to return to the original user session, select the option 'Return to Self'

Oracle Site Hub - Overview

2011-05-02T17:46:00.000+05:30

Oracle Site Hub is a location mastering solution that enables organizations to centralize site and location specific information from heterogeneous systems, creating a single view of site information that can be leveraged across all functional departments and analytical systems. Site Hub has integrations to Property Manager, Assets Management and few other modules. Oracle Site Hub module is available from R12.1.1 version.

Site Hub Objects

Site - A site is defined as a place of business interest.
Hierarchy - Hierarchy provides a classification of relationships needed for the business.
Cluster - A group of Sites forms the Cluster.
Trade Area

Site Hub has two new responsibilites

Setup (Site Management Administrator)
The Site Administrator responsibility is mainly used for setting up the Attributes for all the Site Hub objects.Other set ups include Associating the UDA with Purpose and Pages, creating Results Format,Search Criteria,Value Sets for Attributes , Import/Export formats etc.

For capturing any data for Site/Hierarchy/Cluster the first step is to create the UDA ( User Defined Attributes). The UDA can be created by using the Site Administrator responsibility.Attribute groups can be single row or multi row. Every UDA needs to be assigned to a Purpose. Purpose can be of type Mail Box, Restaurant etc. It is an important step to be done before using it in Site or hierarchy.

Every AG will be associated to a Page. It can be added to a existing page or create a new page and associate the AG to the page.

Site Home (Site Management User)

Site Management User responsibility is used for creating the Site Hub objects, Perform Advanced Search and manage the objects.After the UDA set up for the objects, the UDA's can be viewed while creating or viewing or updating the Site or any other object. The objects needs to be added to the same purpose where the AG is. Only then the UDA's are visible for the user.

Overview of XML Publisher

2011-04-21T15:24:00.000+05:30

Oracle XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. At runtime, XML Publisher merges the custom templates with the concurrent request data extracts to generate output in PDF, HTML, RTF, EXCEL (HTML), or even TEXT for use with EFT and EDI transmissions.

Need for XML: Consider the following scenarios,

We have a RDF report with tabular layout which prints in English

New Requirements:

User1 wants the same Report needs to be printed in Spanish
User2 wants the Same Report needs to be printed in chart format
User3 wants the Same Report output in Excel
User4 wants the Same Report output to be published on intranet or internet
User5 wants the Same Report output eliminating few columns and adding few other.

A new RDF needs to be created for each requirement stated above or an existing RDF needs to be modified with huge amount of effort but whereas with XML Publisher it can be done very easily.

XML Publisher separates a reports data, layout and translation components into three manageable pieces at design time; at runtime all the three pieces are brought back together by XML Publisher to generate the final formatted, translated outputs like PDF, HTML, XLS and RTF. In future, if any there is any change in layout we just need to add/modify the Layout file

Data Logic Data extracted from database and converted into an XML string.

Layout - The layout templates to be used for the final output are stored and managed in the Template Manager.

Translation -The translation handler will manage the translation that is required at runtime

Flow from Order to Invoice

2011-04-11T14:23:00.001+05:30

Order Entry
This is first stage, When the order is entered in the system, it basically creates a record in order headers and Order Lines table.

oe_order_headers_all (Here the flow_status_code as entered)
oe_order_lines_all (flow_status_code as entered) ( order number is generated)

Order Booking
This is next stage , when Order is booked then the Flow status changed from Entered to Booked. At this stage ,these below table get affected.

oe_order_headers_all (flow_status_code as booked ,booked_flag updated)
oe_order_lines_all (flow_status_code as awaiting shipping,booked_flag updated)
wsh_new_deliveries (status_code OP open)
wsh_delivery_details (released_status ‘R’ ready to release)
Same time, Demand interface program runs in background And insert into inventory tables mtl_demand

Reservation
This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved.Once this program get successfully get completed , the mtl_reservations table get updated.

Pick Release
Ideally pick release is the process which is defined in which the items on the sales order are taken out from inventory. Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

oe_order_lines_all (flow_status_code ‘PICKED’ )
wsh_delivery_details (released_status ‘S’ ‘submitted for release’ )
mtl_txn_request_headers
mtl_txn_request_lines
(move order tables.Here request is generated to move item from saleble to staging sub inventory)
Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

Pick Confirm
Items are transferred from saleble to staging Subinventory.

mtl_material_transactions
mtl_transaction_accounts
wsh_delivery_details (released_status ‘Y’‘Released’ )
wsh_delivery_assignments

Ship Confirm
Here ship confirm interface program runs in background . Data removed from wsh_new_deliveries

oe_order_lines_all (flow_status_code ‘shipped’)
wsh_delivery_details (released_status ‘C’ ‘Shipped’)
mtl_transaction_interface
mtl_material_transactions(linked through Transaction source header id)
mtl_transaction_accounts
Data deleted from mtl_demand,mtl_reservations
Item deducted from mtl_onhand_quantities

Enter Invoice
This is also called Receivables interface, that mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables.

ra_interface_lines_all (interface table into which the data is transferred from order management)Then Autoinvoice program imports data from this table which get affected into this stage are recievables base table.

ra_customer_trx_all (cust_trx_id is primary key to link it to trx_lines table and trx_number is the invoice number)
ra_customer_trx_lines_all (line_attribute_1 and line_attribute_6 are linked to header_id (or order number) and line_id of the orders)

Complete Line
In this stage order line leval table get updated with Flow status and open flag.

oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)

Close Order
This is last step of Order Processing . In this stage only oe_order_lines_all table get updated. These are the table get affected in this step.

oe_order_lines_all (flow_status_code ‘closed’,open_flag “N”)

These are the typically data flow of a order to cash model for a standard order.

Delete Netting Batch from Backend

2011-03-08T17:11:00.002+05:30

Whenever there is an issue with Netting Batch where the batch is in error and the user is unable to Update or Delete the Netting batch, one can use the following SQL Statements to delete the Batch from the Backend.

1. Select CHECKRUN_ID, Batch_id from Fun_net_batches_all where Batch_Name = '&Your_batch_name'

2. Delete from fun_net_batches_all where Batch_Name = '&Your_batch_name'

3. Delete from FUN_NET_AR_TXNS_ALL where Batch_id= &Batch_id_from_step1

4. Delete from FUN_NET_AP_INVS_ALL where Batch_id= &Batch_id_from_step1

5. To remove the transactions from AP that was picked up in the Netting Batch, you may please run the following script.

select * from AP_PAYMENT_SCHEDULES_ALL where checkrun_id= &Checkrun_Id_from_step1;

If this returns any rows, then use the below statement, else no action is required

UPDATE AP_PAYMENT_SCHEDULES_ALL
SET checkrun_id     = NULL
WHERE invoice_id   = &invoice_id_from_prev_sel_stmnt and
checkrun_id          = &Checkrun_Id_from_step1;

Once this is done, then issue a COMMIT.

Recurring Journals

2011-02-21T23:32:00.000+05:30

Skeleton Journal Entries Skeleton journals have varying amounts in each period. You define a recurring journal entry with out amounts, and then enter the appropriate amounts each accounting period. There are no formulas to enter, only account combinations.

For example, you can record temporary labor expenses in the same account combination every month with varying amount due to fluctuations in hours..

Standard Recurring Journal Entries Standard recurring journal entries use the same accounts and amounts each period.
For Example: Record monthly lease expenses with constant amounts charged to the same account.

Recurring Journal Formula Entries Formula entries use formulas to calculate journal amounts that vary from period to period.

For example, calculate commotion to sales representative based on the sales of the month.

General Ledger - Period Status

2011-02-19T16:19:00.002+05:30

Open In the Open status you can enter and post Journals.

Closed In this status Journal entry and posting not allowed until accounting period is reopened. Reporting and inquiry allowed.

Permanently Closed In this status Journal entry and posting not allowed. You cannot change this period status. Reporting and inquiry allowed. You can change the status.

Never Opened Journal entry and posting are not allowed. General Ledger assigns this status to any period preceding the first period ever opened in your
calendar, or to any period that has been defined, but is not yet future-enterable. You cannot change this period status.

Future-Entry Journal entry is allowed, but posting is not. Your period is not yet open, but falls within the range of future-enterable periods you designated in the Set of Books window. You cannot change this period status without using the concurrent process to open the period.

R11 Analyze Network Performance

2011-02-15T10:24:00.000+05:30

Connect to the E-Business Home Page as sysadmin

Choose System Administrator -> Application -> Network Test

Click on "Run Test" button

The Latency and Bandwith results are displayed as shown below.

NOTES:

1) The latency = the time a single data packet spend to go to the server and after that go back (RTT, calculated in milliseconds).

2) The bandwidth = data transfer rate between the client and the server (Data Rate, calculated in Bytes / sec).

Concurrent Program (CP) Phases

2011-02-14T15:22:00.001+05:30

Phase	Phase Description	Phase Status	Phase Status Description
Pending	The Concurrent Program (CP) is waiting to be run.	Normal	The CP is waiting for a concurrent manager (CM)
		Waiting	A child request is waiting for the parent process to let it run.
		Standby	The CP1 (in Standby) is waiting for another CP2 to finish. The CP1 may run only after CP2 complete.
		Scheduled	The execution time for the CP has not been reached.

Running	The CP is running.	Normal	The CP process is running.
		Paused	The parent CP is paused because is waiting for a child process to complete.
		Resuming	After the "Paused" Status when the parent CP restart working.
		Terminating	The CP is being terminated.

Completed	The CP has been finished the execution.	Normal	The CP completed successfully.
		Warning	The CP completed successfully with a warning message.
		Error	The CP failed to complete due to an error.
		Terminated	The CP was stopped before completion.
		Canceled	The CP was canceled before it started Example: The CP was scheduled for tomorrow but today I cancel the CP.

Inactive	Describes a CP that cannot be run immediately.	Disabled	The CP has not been enabled for execution.
		On Hold	The CP has been placed in on hold.
		No Manager	There is no managers for that type of request.

Oracle Project Accounting Tables

2011-01-30T13:36:00.000+05:30

A_PROJECTS_ALL Information about projects

PA_AGREEMENTS_ALL Customer contracts that serve as the basis for work authorization

PA_BILL_RATES_ALL Information about bill rates and markups of standard bill rate schedules

PA_BILLING_ASSIGNMENTS_ALL Assignments of billing extensions to a project type, project, or task

PA_COST_DISTRIBUTION_LINES_ALL Information about expenditure item cost distribution

PA_CUST_REV_DIST_LINES_ALL Information about expenditure item revenue distribution

PA_DRAFT_INVOICE_DETAILS_ALL Intercompany invoice details for cross charged transactions

PA_DRAFT_INVOICES_ALL Information about draft invoices generated for projects

PA_DRAFT_REVENUES_ALL Information about draft revenue generated for projects

PA_EXPENDITURE_COST_RATES_ALL Cost rates for non-labor expenditure types

PA_EXPENDITURE_GROUPS_ALL Groups of pre-approved expenditures

PA_EXPENDITURE_ITEMS_ALL The smallest units of expenditure charged to projects and tasks

PA_EXPENDITURES_ALL Groups of expenditure items incurred by employees or organizations for an expenditure period

PA_PERIODS_ALL Implementation-defined periods against which project performance is measured

PA_PROJECT_ASSET_LINES_ALL Summarized project CIP costs

PA_PROJECT_ASSETS_ALL Assets defined for capital projects

PA_EXPENDITURE_TYPES Implementation-defined classifications of expenditures charged to projects and tasks

PA_EXPENDITURE_CATEGORIES Implementation-defined groupings of expenditure types by type of cost

PA_CLASS_CATEGORIES Implementation-defined categories for classifying projects

PA_CLASS_CODES Implementation-defined values within class categories that can be used to classify projects

PA_PROJECT_CLASSES Class codes of class categories that are used to classify projects

PA_BUDGET_VERSIONS Versions of project budgets

PA_BUDGET_LINES Detail lines of project and task budgets

Enhanced Features in Oracle APEX 4.1

2011-01-24T19:46:00.005+05:30

Development for Mobile Applications – Include themes and HTML templates suitable for smart phones and mobile devices.

Charting - Provide for chart rendering without using Flash (to enable display on mobile devices).

Error Handling - Improve error handling and user-defined exception processing.

Interactive Reporting – Allow multiple reports on one page and support pivot queries.

Tabular Forms – Allow multiple tabular forms on one page and continue to expand tabular forms validations.

Master-Detail-Detail – Allow the generation of pages to support master-detail-detail relationships.

Dynamic Actions – Enhance conditional processing and allow dynamic actions to be defined for tabular forms, reports, and buttons.

Plug-Ins - Add plug-in support for additional components and enhance plug-in definitions.

Use of ROWID – Allow usage of ROWID for Automatic DML processing (as an alternative to identifying the PK columns).

Modal Dialogue - Add ability to display a dialog on top of a page (the rest of the page will be grayed out).

Websheets – Allow for greater control over user interface, new page section types, and enhanced spreadsheet / datagrid integration.

Data Upload - Enable end-users to upload data into an existing table (within an application).

Accessibility – Improve accessibility in existing themes and HTML templates.

BI Publisher Overview

2011-01-19T20:39:00.000+05:30

Oracle Business Intelligence Publisher is a reporting and publishing application that enables you to extract data from multiple data sources, create a template to lay out the data in a report, and publish the report to numerous output formats. BI Publisher also enables you to schedule reports and deliver the reports to any delivery channel required by your business.

Administering BI Publisher requires setting up and maintaining the following system components:

• BI Publisher security
• Data source connections
• Integrations with other Oracle products
• Report delivery options
• Runtime configuration settings
• BI Publisher Scheduler configurations
• System maintenance options

Integration
Oracle BI Publisher enables you to integrate with the following products:
• Oracle BI Presentation Services - This integration enables you to use Oracle BI Answers requests as data sources for your reports.

• Oracle BI Discoverer - This integration enables you to use Oracle BI Discoverer Worksheets as data sources for your reports.

• Hyperion Workspace and Shared Services - This integration enables you to access BI Publisher from within the Oracle Enterprise Performance Management Workspace.

• Oracle Smart Space Client - This integration enables you to set up a link to the Oracle Smart Space download page. This will enable all BI Publisher users to easily access the Oracle Smart Space client download page from their BI Publisher session.

• Oracle E-Business Suite – This integration can be done by uploading a DBC file to recognize your Oracle E-Business Suite users.

• Oracle BI Server – This can be integrated by setting up the BI Publisher roles in your BI Server Administration tool then configure BI Publisher to integrate with it.

Integrating with Oracle E-Business Suite
1. In the Oracle E-Business Suite, log in as a System Administrator and create the following responsibilities to correspond to the BI Publisher functional roles:

• XMLP_ADMIN – this is the administrator role for the BI Publisher server.
• XMLP_DEVELOPER – allows users to build reports in the system.
• XMLP_SCHEDULER – allows users to schedule reports.
• XMLP_ANALYZER_EXCEL – allows users to use the Excel analysis feature.
• XMLP_ANALYZER_ONLINE – allows users to use the online analysis feature.
• XMLP_TEMPLATE_DESIGNER - allows users to connect to the BI Publisher
server from the Template Builder and to upload and download templates.

2. Add the new BI Publisher responsibilities to the appropriate Users.

3. Log in to BI Publisher Enterprise. From the Admin tab, select Security Configuration.

4. In the Security Model section of the page, select Oracle E-Business Suite from the list.

5. Load your dbc file from the E-Business Suite instance. This is typically located under the $FND_SECURE directory. If you do not have access to this file, contact your E-Business Suite system administrator. This file specifies how BI Publisher should access the E-Business Suite instance.

6. It is recommended that you create a local super user for the system to allow you to access the Administrator pages once the changes take effect. Select the Enable Local Superuser check box and enter a username and password for the user under the Local Superuser section of the Security Configuration tab.

7. Restart the BI Publisher server for the security changes to take effect.
Once you restart the system, all your E-Business Suite responsibilities will be visible as roles in the BI Publisher security center. Add folders to the E-Business Suite roles.

Integrating with Oracle BI Server Security
This is done by defining BI Publisher functional roles within the Oracle BI Server Administration tool, assign users to these groups, and then specify Oracle BI Security as your security model in the BI Publisher Admin interface.

1. In the BI Server Administration tool, create the following groups to correspond to the BI Publisher functional roles:
• XMLP_ADMIN – this is the administrator role for the BI Publisher server.
• XMLP_DEVELOPER – allows users to build reports in the system.
• XMLP_SCHEDULER – allows users to schedule reports.
• XMLP_ANALYZER_EXCEL – allows users to use the Excel analysis feature.
• XMLP_ANALYZER_ONLINE – allows users to use the online analysis feature.
• XMLP_TEMPLATE_DESIGNER - allows users to connect to the BI Publisher
server from the Template Builder and to upload and download templates.

2. Add the appropriate users to the BI Publisher groups in the BI Server Administration tool.

3. In the BI Publisher Enterprise application, log in with Administration privileges. From the Admin tab select Security Configuration.

4. In the Security Model section of the page, select Oracle BI Server from the list. Provide the following connection information for the BI Server:
• JDBC Connection String - example: jdbc:oraclebi://host:port/
• Administrator Username and Administrator Password
• Database Driver Class - example: oracle.bi.jdbc.AnaJdbcDriver

5. It is recommended that you create a local super user for the system to allow you to access the Administrator pages once the changes take effect. Select the Enable Local Superuser check box and enter a username and password for the user under the Local Superuser section of the Security Configuration tab.

6. Restart the BI Publisher server for the security changes to take effect.

Logging into Business Intelligence Publisher

1. Navigate to the URL provided by your system administrator. The URL is of the format http://hostname:port/xmlpserver/

2. Select the language you prefer for the user interface.

3. Enter your credentials to log in to BI Publisher.

4. Select Accessibility Mode if you wish to render the Reports home page in an accessible tree structure.

5. Select Sign In.

Web ADI Setup in R11

2011-01-08T11:51:00.000+05:30

This setup is with respect to Oracle Applications 11.5.10.2 instance.

First apply patch - 6898666. This patch is available via Metalink. As always, please review the prereqs. If you are patched up to 11.5.10.2, you should be fine.

In order to upload budgets with Excel, you will need to create the profile option, "GL: Default Desktop Viewer". You can find more information pertaining to the steps for setting up this profile option in Metalink note 556280.1.

When trying to upload a budget for the first time, you may encounter the error: "You do not have permission to access this functionality". This error is normal. It can be corrected by setting the profile option: "BNE Allow No Security Rule" to Yes at the Responsibility level. You can find more information pertaining to this error in Metalink note 559924.1.

With the three steps above you will be uploading budgets via WebADI in no time.

Setup for for multiple sets of books

First create a new responsibility for WebADI, similar to the WebADI responsibility seeded. (Copy the menu name and data group seeded in the responsibilities setup form.)

Associate the new responsibility with the SOB via the "GL: Set of Books Name" profile option at the responsibility level.

Assign the responsibility to a user.

Best Practices for Designing Accounting Flexfield

2010-12-25T13:08:00.007+05:30

Common Segments used across Enterprises

Given below are some of the common segment titles that many organizations define with separate account segments:

Company A segment that indicates legal entities for commercial, for–profit organizations.

Fund A segment that indicates a fiscal and accounting entity with a self–balancing set of accounts for governmental or not–for–profit organizations.

Cost Center or Department A segment that indicates functional areas of your business or agency, such as Accounting, Facilities, Shipping, and so on.

Account A segment that indicates traditional “Natural Account”, such as Cash, Current Asset, Accounts Payable, or Salary Expense.

Product A segment that indicates products, such as disk drives, printer cables or magnetic tapes manufactured by a commercial, for–profit organization.

Program A segment that indicates programs, such as, for a university, scholarship program, endowment program, or annual giving program.

Project A segment that indicates projects such as work orders, contracts, grants, or other entities for which you want to track revenues and expenses.

State/District/Geography A segment that indicates geographical locations, such as Northern California, Central Florida or Western New York.

Distribution Channel A segment that indicates the method by which your product reaches your customer, such as Wholesale, Retail, OEM, and so on.

Intercompany A segment that indicates intercompany entities. If your subsidiaries share the same set of books, you can use an intercompany segment to identify subsidiaries involved in intercompany-transactions. This segment usually mirrors your company segment (balancing legal entity segment).

Best Practices for defining Segments

* Determine the segment that captures the natural account, such as assets, liabilities, expenses, and validate whether you need sub-accounts. Use child-parent relationships to define your account hierarchy.

* Define a separate Accounting Flexfield segment for each dimension of your organization on which you want detailed reporting, such as regions, products, services, programs, and projects. For example, you may want to record and report on expenses by project. To do this, your account must categorize expenses by project. Define your account to include a “Project” segment. By doing this, you automatically categorize all your accounting information by project as you enter it, and you can easily report on project information.

* Group similar business dimensions into one segment. This allows a more simplified and flexible accounting structure. For example, you only need one segment to record and report on both districts and regions, as illustrated below. Because regions are simply groups of districts, you can easily create regions within your district segment by defining a parent for each region with the relevant districts as children. Use these parents when defining summary accounts to maintain account balances and reporting hierarchies to perform regional reporting.

* Consider information you track in other accounting information systems. You may not need to capture certain organizational dimensions if another system already records and reports on this information. For example, if you need to report on sales by product and your sales tracking system already provides this information, General Ledger account structure does not need to categorize information by product. If you are a government or not–for–profit agency using a labor costing system which captures work breakdown structure for reimbursable billing, you may not need to capture this in your account structure.

* Identify segments that you might need in the future. Consider future expansion and possible changes in your organization and reporting needs. For example, you may not need a region segment now, but eventually you plan to expand you organization to cover multiple regions. Determine the length of each segment. Consider the structure of values you plan to maintain within the segment. For example, you might use a 3 character segment to capture project information, and classify your projects so that all administrative projects are in the 100 to 199 range, all the facilities projects are in the 200 to 299 range, and so on. If you develop more than 10 classifications of projects, you would run out of values within this segment. You might want to add an extra character to the size of each segment to anticipate future needs.

* If you want to perform multi–company or fund accounting within a set of books, choose a balancing segment. You must define one and only one balancing segment in your account. General Ledger automatically balances all journal entries for each value of this balancing segment and performs any necessary intercompany or interfund posting to the intercompany or interfund account you specify when you define your set of books.

* If you plan to maintain and consolidate multiple set of books, think of common elements among your separate account structures. Consider which segments can share value sets, or where opportunities for rolling up segments from a subsidiary set of books into a parent set of books exist.

Plan your value sets. To reduce maintenance and to maintain consistency between sets of books, you can use value sets when defining multiple charts of accounts. Using the same value sets allow two different sets of books to reference the same segment values and descriptions for a specified segment.

* For example, the values in your natural account segment, such as Cash, Accounts Payable, and so on, may be equally applicable to each of your sets of books. Ideally, when you set up a new set of books you should consider how you would map your new Accounting Flexfield segments for consolidation. When a common natural account segment is used between sets of books, it is easier to map account balances from your subsidiary sets of books to a consolidating entity.

Oracle AIM and ABF Methodology

2010-12-18T18:50:00.003+05:30

Traditional AIM methodology is known as Ask and Do as it is requirements driven and solution is derived based on the requirements during the project. It is more or less based on the traditional Waterfall Approach, prescribes a way to do the fit gap analysis and assists you to define customizations where standard Oracle functionality does not meet the business requirements. The approach is taken on a modular basis.

AIM For Business Flows (ABF) also called as Show and Tell is solution driven and Solution Flow is defined before the start of project. It is based on iterative approach based where multiple runs of CRPs are done. In the essence it aims at avoiding customizations and prioritizes all changes. There is a strong emphasis on integrated view and focus is on cross module process flows. Some of the advantages of ABF are:

ABF is most recommended approach for green-field projects where there are no legacy systems in place and the customer is willing adopt to the standard processes prescribed by the Oracle ERP.
More rigor to use the standard business processes defined by Oracle
Minimize numbers of Customizations (extensions)
Reduced cycle time

CRP’s in ABF

CRP in terms of ABF is a series of workshops where “Flow Teams” assigned to the implementation project go through the business flows iteratively during the project. The flows in a Flow Family are grouped into logical “Flow Batches” that can be refined and tested in a series of workshops conducted during a given CRP cycle. The Flow Families being implemented are also tested in parallel by separate Flow Teams during the CRP. Composition of a Flow Team will generally include at-least one consultant, Business Process Owner, and some super or key users or SME’s. The main objectives of the CRP are Familiarization, Initial mapping, Refine Mapping, Validate COA, Multi-Org and TCA, Scope Freezing, and Business System Test.

Key Deliverables in ABF

BT.070 Project Management Framework

BF.015 Future Process Model

BF.040 Change Catalog

BF.045 High Level Solution document

BF.016 Application Setup Documents

BF.080 Reporting Requirements Listing

BF.170 Security Profiles

BF.100 Phase end review

BF.035 CRP1 Strategy Plan

TE.065 CRP2 Strategy Plan

MD.050 Application Extensions Functional Design

MD.070 Application Extension Technical Design

TE.040 System Test Script

DO.070 User Guide

CV.040 Conversion Data Mapping

MD.120 Installation Instructions

TA.030 Preliminary Conceptual Architecture

TA.150 System Management Procedures

Oracle has announced retirement dates for AIM and ABF of January 2011 and all of Oracle’s existing methods are being replaced by the Oracle Unified Method (OUM) aligning to their long term strategy post- acquisition of PeopleSoft, Siebel, BEA and other solutions.

EBS 11i extended support starts from December 2010

2010-12-11T21:17:00.003+05:30

Premier Support for Oracle E-Business Suite Release 11i ends on November 30, 2010 and Extended Support for EBS 11i begins from December 1st, 2010.

Extended Support comes at an additional cost, but these are waived for Oracle E-Business Suite Release 11i through November 2011. Contact your Oracle account manager with questions about Extended Support costs.

Here is a comparison of the different support levels:

A new set of minimum technical requirements for Apps 11i goes into effect when Extended Support begins in December 2010. These new minimum baseline patch requirements are listed in:

Minimum Baseline Patch Requirements for Extended Support on Oracle E-Business Suite 11.5.10 (Note 883202.1)

What are the impacts of this new baseline?

As always, Oracle Support will assist with diagnosis, triage, and debugging of any issues that you report for your E-Business Suite environment. You will continue to have access to any existing online support tools, knowledge base documentation, and pre-existing fixes.

If you report a new issue that requires a new bug fix, you should be aware that new patches will be released only for the new baseline patches listed in the Minimum Baseline Patch Requirements Note. You should plan to apply the baseline patches (at minimum) listed in Note 883202.1 by November 2010 in order to receive any new bug fixes.

Minimum Baseline Patch Requirements

Oracle E-Business Suite Release 11.5.10 Consolidated Update 2 plus additional patches listed in Note 883202.1
ATG Rollup Patchset 6 (11i.ATG_PF.H.delta.6, Patch 5903765)
10.2.0.4 Database or 11.1.0.7 Database
Forms6i Patchset 19 (version 6.0.8.28, Patch 6194129)
Oracle HTTP Server MLR Patchset 4393827
Sun Java Runtime Engine (JRE) 1.6.0_03 or higher

The baseline versions listed here (and in the Note) are current as of the document's release date but may change in the future. Any changes will be communicated via Note 883202.1. If you've found this article as the result of a web search, I would strongly encourage you to check the latest version of the Note for the current information.

What EBS 11i products are affected by this new baseline?

In addition to the major technology stack components shown above, Note 883202.1 contains additional minimum baseline requirements for:

Applications Technology

Common Applications Calendar

Contracts

Core Contracts
Procurement Contracts
Project Contracts
Sales Contracts
Service Contracts

Customer Data Management

Customers Online
Trading Community

Financials

Assets
iAssets
Advanced Collections
Bill Presentment Architecture
Cash Management
E-Business Tax
Financials for the Americas
Financials for Asia/Pacific
Financials Common Country
Financials for EMEA
Financials for India
Internet Expenses
Lease Management
Loans
Payables
Payments
Property Manager
Public Sector Budgeting
Public Sector Financials
Public Sector Financials (International)
Receivables
iReceivables
Treasury
U.S. Federal Financials

Human Resources

Advanced Benefits
Approvals Management
HR Intelligence
Human Resources
Labor Distribution
Learning Management
Payroll (All Localizations)
Payroll (International/Custom)
iRecruitment
Self-Service Human Resources
Time and Labor
US Federal Human Resources

Intelligence

Balanced Scorecard
E-Business Intelligence

Interaction Center

Customer Interaction History

Logistics

Inventory Management
Warehouse Management

Manufacturing

Cost Management
Quality
Supply Chain Globalization
Work in Process

Marketing and Sales

Incentive Compensation
Marketing
Partner Management
Quoting
Sales
Sales for Handheld
Sales Offline
Territory Management
Trade Management

Order Management

Advanced Pricing
Configurator
Order Capture
Order Management

Procurement

iProcurement
Purchasing
Sourcing
iSupplier Portal
Supplier Scheduling

Product Lifecycle Management

Advanced Product Catalog
Bills of Material
Document Management and Collaboration
Engineering
Item Master

Projects

Grants Accounting
Project Billing
Project Collaboration
Project Costing
Project Portfolio Analysis
Project Foundation
Project Resource Management
Projects

Service

Advanced Scheduler
Complex Maintenance, Repair & Overhaul
Customer Care
Depot Repair
Field Service
iSupport
Teleservice

Supply Chain Planning

Demand Planning
Production Scheduling
Strategic Network Optimization

Publishing Modes of UPK

2010-12-02T00:01:00.020+05:30

Enables a user to learn by watching an animated demonstration of the steps for a task being performed in a simulated environment. All the required activities, such as moving the mouse and entering data, are completed automatically.

Enables a user to learn interactively in a simulated environment. The user is prompted for mouse clicks and/or keystrokes to complete the task.

Enables a user to learn interactively using live data. In Do It! – Manual Advance mode, a user is presented with a small window on top of the live target application that displays each step for a particular task. As the user completes each step, he/she can click a button or use a hot-key combination to display the next step in the process. Within the Do It! – Manual Advance window, a thumbnail graphic of the screen is available with a highlight indicating the area where the action should take place. Do It! – Manual Advance is the default mode for custom applications. It is the only Do It! mode available in browser-based Player deployment.

An assessment-type playback mode in which users are instructed to complete a particular task. Users do not receive step-by-step instructions for completing a task. Instead, they complete the steps on their own in a simulated environment, and they are scored on how accurately they complete them.

Overview of User Productivity Kit (UPK)

2010-11-30T23:57:00.009+05:30

The User Productivity Kit shortly known as UPK is a comprehensive, cost-effective, customizable solution that helps your organization quickly create the critical documentation, training, and support materials needed to drive project team and user productivity throughout the lifecycle of your software.

Oracle UPK Professional is the leading solution for creating, deploying, and accessing content for enterprise application deployments. Consisting of Oracle UPK and the Knowledge Pathways designer, manager, and player modules, Oracle UPK Professional turns knowledge into productivity and ensures ongoing end-user competency.

The User Productivity Kit provides system process documentation, user acceptance test scripts, comprehensive instructor led training materials, web based training materials, role-based performance support and complete documentation.

Also provided is the UPK Developer, which serves as a single source development and customization tool to enable rapid content creation and customization.

UPK is a package that develops content for all of the major software suites that Oracle now controls, including: Oracle E-Business Suite, PeopleSoft Enterprise, JD Edwards EnterpriseOne, Siebel CRM, JD Edwards World, Oracle Transportation Management and Retek. UPK also works with the following: Accential, Acrobat, Application Designer, Configuration Manager, Crystal, Excel, Outlook, PowerPoint, PSnVision, Query Analyzer, Tutor and Word. Although initially developed by Global Knowledge as OnDemand Developer, Oracle has designed object recognition into UPK.

BENEFITS
--------------------------------------------------------------------------------------------------------------

Enable business users gain good knowledge of the functionality of an application by learning how to use a program in both a simulated environment.
Create documentation, training materials, capture transaction and performance support across the organization.
Provides training and support personnel to create interactive software simulations and other instructional materials. These simulations allow users to see and interact with an application even before they have access to it.
Provides process documentation, UAT scripts, comprehensive instructor led training materials, web based training materials.

Allows to edit the process flow captured and reproduce the same process both in animated format and document format.
With the addition of prebuilt content for UPK, the effectiveness and quality of that development is greatly enhanced.
Utilized in any software application development or deployment methodology.

Develop central repository to improve competency skills by developing CBTs, BPDs, Test Scripts etc.

Utilize while rolling out a new application.
Reduce content creation time — Rapid content creation tools produce multiple assets through a single authoring session saving content development time and cost.
Reduce time to deployment—System process documentation for fit-gap analysis provides time and cost savings, jump-starting your implementation or upgrade project.
Manage project assets—Central repository stores project assets for stakeholders and deploys training materials to user community.
Ensure organizational readiness—Performance testing monitors end-user competency prior to go-live as well as tracks ongoing proficiency.

Integration of Oracle Apex with APPS

2010-11-10T08:22:00.000+05:30

Oracle Application Express (Oracle Apex) is a rapid web application development tool for the Oracle database. Using only a web browser and limited programming experience, you can develop and deploy professional applications. We wanted to extend the E-Business Suite functionality with Apex features. Apex functions need to be available from the EBS user menu. Users have to be able to move seamlessly from EBS to Apex, without logging in twice. The context of the EBS user needs to specify which data the user is allowed to see in Apex. Our goal was to develop a calendar in Apex which shows a team’s absences for the logged in supervisor. In EBS the “supervisor -> employee” relation has a hierarchical structure, which has to be available in the calendar.

The first challenge we have to deal with is authentication. Apex users have to log in to access an application. We used the EBS security to allow users to logon to Apex using their EBS username and password. In Apex we created a custom authentication scheme which calls an external function to validate the username and password. The authentication scheme calls our plsql function “apex_authorise” which uses the EBS “fnd_web_sec.validate_login” to validate the username and password. At this point we are able to log in to an Apex application using any valid EBS username and password combination.

Now we want to link Apex forms from the EBS menu. The first problem is that Apex requires a valid password for the user and the EBS database does not contain passwords, it holds a hash of the username/password combination. You can handle this by creating an alternative password using the DBMS_OBFUSCATION_TOOLKIT md5 function. The password will be generated by hashing the username, a time component and a key held within the database. This mechanism will be handled in two functions: “apex_generate_hash” and “apex_validate_hash”. To check if the password is valid, we extend the already created function “apex_authorise”.

Now we want to login in Apex automatically. We use a cookie to achieve this. The form function we use in EBS, sets a cookie containing the username and password and redirects to the Apex URL to launch the Apex application. In EBS we have to create a web enabled mod_plsql procedure to achieve this. This form function can then be added to the EBS menu. In Apex we use a “before header” process to read the cookie and log the user in.

The cookie we used:

OWA_COOKIE.send
(name=>’APEX_APPS_’||application,
value=>FND_GLOBAL.user_name||’:’||
apex_generate_hash(FND_GLOBAL.user_name)||’:’||
FND_GLOBAL.user_id||’:’||
FND_GLOBAL.resp_id||’:’||
FND_GLOBAL.resp_appl_id
);

We store the FND_GLOBAL values in global variables in Apex. We use these variables in the page query of an Apex application to only show the results for this specific user. In our case we used the standard calendar feature of Apex. In this calendar you can specify a sql query which selects the data to publish.

Ideally we want to save the context of the environment an APPS user is working in and seamlessly use this context in our Apex application. This way we don’t have to care about which data is visible for the user, the APPS security mechanism will take care of that. This is something which has to be investigated. In our project we send the user information in the cookie, and use this information for our data selection.