Search

OracleDollars

Happy New year- 2009

*Wishing you a New Year filled with good health, new hope and new beginnings!

* May Y ear 2009 open up for you more opportunities, lead you onto the path of continued success, happiness and prosperity!

* I wish ... that the coming year showers on you all that you ever wished for!

* Good luck and smiles, good cheer and ...



Deleting a Netting batch from backend


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.

AP/AR Netting


The Payables and Receivables Netting feature enables the automatic netting of Payable
and Receivable transactions within a business enterprise. You can predefine a netting
agreement that incorporates the netting business rules and transaction criteria needed
to run your tailored netting process. The netting process automatically creates the
Payables payments and Receivables receipts required to clear a selected number of
Payables and Receivables transactions.
You can view the receipts that the Netting process creates by querying the netted
receipts in the Receipts workbench. To view additional details about the netting batch,
select AP/AR Netting from the Actions menu.

Note: You cannot update netted receipts in the Receipts workbench.

1 Tables

1.1 New Tables

Table Name

Feature Area

FUN_NET_AR_TRX_TYPES_ALL

Netting Agreements

FUN_NET_AP_INV_TYPES_ALL

Netting Agreements

FUN_NET_AGREEMENTS_ALL

Netting Agreements

FUN_NET_SUPPLIERS_ALL

Netting Agreements

FUN_NET_CUSTOMERS_ALL

Netting Agreements

FUN_NET_BATCHES_ALL

Netting Batches

FUN_NET_AR_TXNS_ALL

Netting Batches

FUN_NET_AP_INVS_ALL

Netting Batches

1.2 Views

1.2.1 New Views

View Name

Feature Area

FUN_NET_RELATED_CUSTOMERS_V

Netting Related Customers


2. Implementation Considerations

This section identifies the new/changed setup steps for Oracle Netting that were introduced in Release 12. For detailed step-by-step implementation instructions, please refer to one of the Oracle Finanicials Implementation Guide, Oracle Financials User Guide or the Oracle Applications Upgrade Guide.

  1. New Setups

2.1 New Setup Steps

  1. Netting Bank Account
  2. Receivables System Options
  3. Netting Batch Approver
  4. Chargeable Subcontracting
  5. Netting Agreement

2.1.1 Netting Bank Account

Set up a Bank Account in Oracle Cash Management which can be used by the Netting process. Set the "Netting Account" flag to "Yes" for the bank account. Assign this bank account to the seeded Receivables Receipt Class "AP/AR Netting".

2.1.2 Receivables System Options

Check the 'Allow payment of Unrelated Transactions' checkbox in Receivables System Options if you want to create a netting agreement across multiple unrelated customers.

2.1.3 Netting Batch Approver

If approval is required for a netting agreement, the approver must be set up as a contact for the trading partner (customer or supplier). The contact must be set up with an email address.

2.1.4 Profile: Chargeable Subcontracting Enabled

Setting the profile "Chargeable Subcontracting Enabled" to "Yes" would mean only invoices that were matched to purchase orders with outsourced assemblies would be selected by the netting process. If the profile option is set to "No", only invoices that were matched to purchase orders without outsourced assemblies would be selected by the netting process. If the profile option is set to "D", all eligible invoices will be selected for netting irrespective of whether or not they are matched to purchase orders.

2.1.5 Netting Agreement

Netting agreements control how payables and receivables transactions are netted for a group of trading partners. Create netting agreements to set up the rules that will decide which transactions are selected for netting and how they are ordered and processed once selected.


3. Performance & Sizing: Considerations & Optimizations

3.1 Upgrade

Netting does not upgrade any transactional data. It upgrades only setup data which will not be of high volume. Therefore no special consideration needs to be given for the table sizes during upgrade.


4. Minimum Technical Requirements

The minimum technical requirements for Netting are included in the Release 12 Rapid Install.


5. Diagnostics

5.1 FND Logging

To check log messages added by the Netting processes, set the following profile options at user level
FND: Debug Log Enabled – Yes
FND: Debug Log Level – Statement
FND: Debug Log Module –FUN%NET

Then execute query
SELECT *
FROM fnd_log_messages
WHERE module like 'FUN%NET%'
AND trunc(timestamp) = trunc(sysdate)


6. Technical Architecture Considerations

  1. Netting Batch Status
  2. Netting Batch Creation Process
  3. Netting Batch Submission Process
  4. Netting Batch Settlement Process
  5. Customer Merge
  6. Supplier Merge

6.1 Netting Batch Status

Following is the sequence of events and the corresponding status for the netting batch.

Event

Netting Batch Status

User creates the netting batch

RUNNING

"Review Batch" option is set to "Yes"

SELECTED

Error encountered while validating, selecting transactions

ERROR

Current date has gone past batch settlement date

SUSPENDED

"Review Batch" option is set to "No" but no approval is required

CLEARING

"Review Batch" option is set to "No" and approval is required

SUBMITTED

User rejects batch requiring approval

REJECTED

User approves batch requiring approval

APPROVED

Netting process completed for transactions

COMPLETE

User chooses to reverse batch

REVERSING

Batch has been reversed

REVERSED

6.2 Netting Batch Creation Process

Criteria used for selecting AP invoices
Dates

AP Payment schedule due date < = FUN_NET_BATCHES.transaction_due_date

AP Payment schedule due date between Agreement Start Date and Agreement End Date ( if null ,Sysdate)

Only Invoices for supplier and supplier sites ( if specified) in the agreement are selected.
Only invoices with the selected invoice types in the agreement are included.
Operating Unit of the Invoices must equal Operating Unit of the batch
Invoices must be approved.
Invoices must not be on hold ( Payment schedule.hold_flag = ‘N’)
Invoices that have already been included in a batch that is not yet in status ‘COMPLETE’ are not included.
Shikyu Processing :

Additional processing is done for Shikyu Invoices based on the Shikyu Rule setup in the Netting Agreement.

Check Shikyu rule code on the Agreement

Y - "Yes" - Only invoices lines that were matched to PO lines with the OSA flag checked are selected for Netting

N - "No" -Only invoices lines that were matched to PO lines with the OSA flag not checked are selected for Netting

D - "Disregard"- All AP invoices selected for Netting; no filtering, therefore all eligible invoices are selected, as if the profile is "Off"


Lock AP Invoices

Selected AP invoices are locked by populating AP_PAYMENT_SCHEDULES.checkrun_id column for the
selected invoices.
The same checkrun_id value is populated in FUN_NET_BATCHES.CHECKRUN_ID for the given batch.

Criteria used for selecting AR Transactions
Dates

AR Payment Schedule due date <= FUN_NET_BATCHES.transaction_due_date

AR Payment Schedule date should be between Agreement Start Date and end date ( if null,Sysdate)

Transactions must be Complete.
Only transactions for customer and customer sites ( if specified) in the agreement are selected.
Only transactions with the selected transaction types in the agreement are included.
Operating Unit of the Transactions must be equal to the Operating Unit of the batch
Transactions that have already been included in a batch that is not yet in status ‘COMPLETE’ will not be included in the new batch.
The Receipt method associated with the transaction should not have a Payment_type_code = ‘CREDIT_CARD’
Transaction must not be in dispute.
Payment schedule status must be open (payment schedule.status = ‘OP’)
Transaction must not be a prepayment (ra_customer_trx.prepayment_flag = ‘N’)

Criteria for ordering the transactions
Invoices and transactions are first ordered by supplier and customer priority associated in the agreement.They are then ordered by the Netting Order Rule selected in the agreement. The priority is important as this decides which transaction / invoice will be netted first.

Netting Currency Rule
The Netting Currency Rule is checked to see if multiple batches need to be created.
If NET_CURRENCY_RULE_CODE = ‘SINGLE_CURRENCY’ then Invoices & transactions are filtered based on Invoice/transaction currency code = FUN_NET_AGREEMENTS.CURRENCY_CODE

If NET_CURRENCY_RULE_CODE = ‘WITHIN_CURRENCY’ then the invoices/transactions are grouped based on their invoice/transaction currency code .
For every group of transactions a new batch is created.
The netting currency of the batch = invoice currency code of the grouped transactions.

IF NET_CURRENCY_CODE = ‘CONVERT_TO_ACCOUNTING’ then the open balance on each transaction is converted to the the Accounting currency.
The netting currency of the batch = Accounting currency

Validate AP and AR Balances
For every batch that has been created.
AP balance is calculated as (sum of the OPEN_AMT in FUN_NET_AP_INVS_ALL for the given batch)
AR balance is calculated as (sum of the OPEN_AMT in FUN_NET_AR_TXNS_ALL for the given batch) .

When the Netting balance Rule = NET_PAYABLES ,
AP balance should be > the AR balance to continue the netting process. If not the current batch
status is set to ‘ERROR’.
If the AP balance or the AR balance <= 0 then the current batch is set to ‘ERROR’. If AP balance < amount =" AP" amount =" AR">

6.3 Netting Batch Submission Process

The following validations are performed when a netting batch as submitted as there might be a time lag between when the batch is created and when the batch is submitted. It is also possible that the batch might have been modified.
If the current date has gone past the batch settlement date ( sysdate > settlement_date) then the batch is set to status ‘SUSPENDED’ and the AP invoices are unlocked.
The AP and AR balances for the transactions are validated again. If the validation fails the batch is set status ‘ERROR’and the AP invoices are unlocked.
If the batch has passed all validations, it is sent for approval if approval is required else the Settle Netting Batch process is initiated.

6.4 Netting Batch Settlement Process

Validation
The process starts off with the validation of the AR transactions. This is necessary as the AR transactions are not locked when the netting batch is created and therefore there is a possibility that the AR transasction may be have been modified since it was included in the netting batch.
If any of the validations fail , then the batch is set to status ‘CANCELLED’.
Validations are also performed to check whether the GL, AP and AR periods are open for the batch settlement date. If not the batch is set to status ‘ERROR’ and an error message is displayed in the Report Log.

Derive Netting Bank Details
The netting bank details like bank account name, bank account owner and number derived based on the FUN_AGREEMENTS.bank_account_id for the given batch.

Settle AP Invoices
Invoices in each batch are grouped by vendor , vendor site and invoice currency code. The AP Payment APIs are called to process the payments. These API return the check id which is updated in FUN_NET_AP_INVS_ALL.CHECK_ID.

Settle AR Transactions
AR Receipt APIs are called to create and apply the receipts. These APIs return the receipt id which is updated in FUN_NET_AR_TXNS.RECEIPT_ID

If all the processing goes through successfully, the batch status is set to COMPLETE and the AP invoices and netting agreements are unlocked.

6.5 Customer Merge

The TCA Account Merge process calls a netting API to propogate the customer merge updates to the Netting tables.
If the merge results in duplicate accounts and /or account site uses for a netting agreement , then the priority of these records is updated to the highest priority among the records.
All duplicate records for a given agreement are deleted except for one record.

6.6 Supplier Merge

The Payables Supplier Merge process calls a netting API to propagate the supplier merge updates to the Netting tables.
If the merge results in duplicate supplier and /or supplier site for a netting agreement, then the priority of these records is updated to the highest priority among the records.
All duplicate records for a given agreement are deleted except for one record

More on netting in future posts.

Your comments and queries are most welcome.

AutoInvoice

What is AutoInvoice?

Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

What Module data can be integrated?
Oracle Order Management
Oracle Project Accounting
Oracle services

To make fully functional what else required?
Loader program
Validation program

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program
Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called
Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

Working of Auto invoice

Validates data
Inserts records
Deletes interface data
Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program

Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’
•Ra_interface_lines
•Ra_interface_distributions
•Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works

Normally, Auto Invoice can be divided into three major phases

Pre-grouping: here the validates all of the line level data takes place
Grouping: groups lines based on the grouping rules and validates header level data
Transfer :validates information that exists in Receivables tables


What happen when AutoInvoice run?

Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?

Select, insert and update and delete take place on certain tables once it is logged out.

Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert

– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling

Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL
Errors can be corrected in the Exception Handling window
Once corrections are made, Auto invoice must be resubmitted
Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column



Courtesy: www.asoracle.blogspot.com

Accounts Receivables


Following is a list of few terms which are quintessential for any Accounts Receivables(AR) Functional Consultant.


The following terms relate to Transactional Documents which will be used when dealing with AR Transactions.

Invoice:
Invoice can be defined as a bill for the purchase in a laymans terms. Technically Invoice in AR is defined as a Bill or document showing the amount owed, the item(s) purchased, the price and quantity, and the billing date, and requesting payment for a credit order. In many businesses, particularly direct marketing, invoices are accompanied by a letter and a reply envelope.

Credit Memo: A Credit Memo (short for "credit memorandum") is a commercial document issued by a seller to a buyer, listing the products, quantities and agreed prices for products or services the seller provided the buyer, but the buyer did not receive or returned. It may be issued in the case of damaged goods, errors or allowances. In respect of the previously issued invoice, a Credit Memo will reduce or eliminate the amount the buyer has to pay.

Chargeback: (In terms of a Credit card) A chargeback is a reversal of a credit-card transaction, as viewed from the perspective of the merchant. It usually occurs when a consumer files a complaint with their bank or credit/debit card provider. This usually happens when a consumer discovers fraudulent transactions on their statement.

Deposit: It can be termed as a Caution deposit paid by a customer to do a business with the organization.

Guarantee: This is similar to that of deposit but sometimes a bank comes into the picture. A bank provides a guarantee to the organization for a customer. Such scenarios are applicable when a very good customer of a bank approaches the bank to provide the guarantee on his behalf to the organization to do any kind of business.

Debit Memo: In situations where there is a need for raising an invoice without an order, we use Debit memos. These are similar to that of Invoices but do not have any underlying order.

Adjustments: Adjustments are made whenever there is an error while creating an invoice. Instead of scratching the entire invoice, the erred amount is adjusted using the adjustment account.

The following terms will be used predominantly while using the Receipts workbench in AR.

Receipts:

Receipts in a broader term can be defined as The amount the customer pays to the organization in lieu of an invoice or a transaction that he has performed with the organization.

There are two types of receipts which are in use and they are

Standard Receipt: It is the payment that the organization receives either in cash or cheques for the services or goods provided. This type of receipt is also called as Cash Receipt.

Miscellaneous Receipt: These kinds of receipts are issued whenever there is no direct customer involved for the money coming into the organization. Money received in the form of Refunds from IT dept. investments, interest and stock sales and many more other sources are normally accounted by creating a Miscellaneous receipt.

Lets have some more details about receipts like the receipt Status.

Receipt Status:

A receipt can have the following status:

Approved: This receipt has been approved for automatic receipt creation. This status is only valid for Automatic Receipts.

Confirmed: The customer has approved the application of this receipt and their account balances have been updated within Receivables. This status is only valid for automatic receipts.

Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.

Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.

Reversed: This receipt has been reversed. You can reverse a receipt when your customer stops payment on a receipt, if a receipt comes from an account with non-sufficient funds or if you want to re-enter and reapply it in Receivables. You can reverse cash/Standard receipts and miscellaneous transactions.

More updates on Accounts Receivables to follow. Please feel free to post your comments and queries if any.


Forms Personalization and Custom.PLL


What is this?

Oracle Supplied forms are the seeded forms and have a standard functionality attached to them. Whenever there is a need to change the functionality or change the look and feel of the forms, we use Forms Personalization or Custom.PLL.

Custom.PLL or Forms personalization is used to alter the standard functionality of an Oracle Seeded form without affecting the base form and without altering its coding. In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.


Advantages of Forms Personalization over Custom.PLL

Forms personalization can be used by an user with limited PL/SQL knowledge.
Changes take place immediately on reopening the form.
Anything which can be done using Custom.PLL can be done using Forms Personalization also.
Personalizations are stored in base tables related to Form Personalization.


Restrictions of Custom.PLL and Forms Personalization:

It is not possible to create any new item in the Standard form using Custom.PLL or Forms Personalization.


How to

Normally a user with some basic knowledge of Oracle Forms and PL/SQL is allowed to modify a standard form. This can be achieved by setting the profile Option

FND: Diagnostics (Set to Yes at User Level)
Hide Diagnostics (Set to NO by default)

This enables the diagnostics menu when the particular user logs in and provides access to the personalization form. Once the form is open, the user can make any modifications for the specific form and then save and check the effect.
Custom.PLL is mainly used by the developers who have a vast knowledge of PL/SQL and can attach any logic using PL/SQL which in-turn will implement the business logic when the form opens. To achieve this, the developer has to first edit Custom.PLL and then add the code in it, compile and save it in the UNIX box and then deploy it in the applications. The changes will be reflected only the next time when the application is restarted.


Some typical uses


  • Changing the property of an Item. It can be any property associated with the item.
  • Calling a different form and passing a value from the calling form to the called form.
  • Addition of special menus apart from the standard menu.
  • Displaying custom messages for debugging purposes.

Feel free to post your comments and your queries.


$FLEX$ and $PROFILES$


What is $FLEX$ and $PROFILES$?

$FLEX$ and $PROFILES$ are Special Variables in oracle Apps environment and are used to hold values at runtime.
Every Oracle Apps technical consultant will be familiar with the term $FLEX$ and $PROFILES$. Whenever we are working with value sets, we will be using both of these for modifying the data fetched, for basing the values of a parameter on the other parameter and also fetching the Profile Option values.
To segregate this based on the functionality
$FLEX$: Used for basing the value of a parameter on another parameter.
$PROFILES$: used for fetching the data stored in the specified profile option value which is currently active.

Where is it used?

Both these variables are used in the Parameter form of a Concurrent Program and are used at the Where Clause window in the value set of Table type.

Syntax:

:$FLEX$.previous_value_set_name
Important:

  • $FLEX$ must always be in capitals.
  • A ‘:’ must precede the declaration of $FLEX$.
  • The previous value set name must have already been assigned and saved on a different parameter.
:$PROFILES$.Profile_option_name

Important:
  • $PROFILES$ must be always in capitals.
  • ‘:’ must always precede the declaration.
  • Profile option name is the Profile Name and not to be confused with the User profile Name.

Some use of the Special Variables are as below:

Pre-Requisites:

Created an Executable and a concurrent program which is registered in the Application Object Library. The Query for the report is as below:

SELECT e.ename, e.empno, e.sal, e.hiredate, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE d.deptno = :x_deptno;
The name of the concurrent program is taken as “XX_Checking_flex_and_profile_use”


Scenario 1:

Changing the value of Parameter B based on the Parameter A:
In this, we created two value sets and they are: XX_SCENARIO_1 and XX_Sub1

Steps:

Create the value set XX_SCENARIO_1 using the validation type as ‘Table’.
Create the second value set XX_Sub1 using the validation type as ‘Table’ and in the where clause field specify the following code:
where deptno <= :$FLEX$.XX_SCENARIO_1

Working:

To check the working of this concurrent program, lets submit this


In the picture here, the First parameter contains no value, so the second parameter is disabled as the WHERE clause in value set is equated to NULL, making the field disabled.



When a value is selected in the first parameter, the second parameter gets enabled and when the LOV is clicked, it shows the departments which are in department number 20 and below it, as we have specified <= in the where clause.



Scenario 2:

Use of :$FLEX$ in the default type option of a Parameter form

Steps:

The query used in the Default Value test field is as below: Select dname from dept where deptno =:$FLEX$.XX_SCENARIO_1
Ensure that the default value for the field Department Number is given as SQL Statement.
Select deptno from dept where deptno=20

Working:

Lets submit the concurrent program and check.

Since the default value was given as a SQL statement, the result of that is shown in the first parameter and the second parameter has the value based on the first parameter as specified in the SQL statement.


Scenario 3:

Use of $PROFILES$ in where clause and Default Value. It is done in the same way as is used in $FLEX$.



Modifying the where clause using $PROFILES$ returns Successful upon validation.





Scenario 4:

Use of Parameter name instead of a value set name in the $FLEX$ Special Variable.
Where Clause of the value set of table type using the parameter name.

Reason:
When we provide the name of the value set in $FLEX$ then we will be able to change the value of the parameter which is dependent on it. If the value set is used by more than one parameter then it becomes difficult to get the exact value in the dependent parameter as it will fetch the values based on the last used value of the value set. It could have been last used by the first parameter or any other parameter in the form. To avoid this kind of confusion, the alternative is to provide the name of the Parameter itself in the $FLEX$ variable. No matter how many times the value set is used, if we provide the parameter name, we will get the dependent parameter value based on its parent parameter only. Let me explain this using the pictures below:

Where Clause of the value set of table type using the parameter
name.



The first and third parameter are based on value set names and the second and fourth parameter are based on parameter name. Since the second parameter is not initialized, the third parameter (value set based )is also not initialized.




Since the latest value is from Second parameter, hence the value in third is based on the second parameter. The third value is based on the value of the second parameter and is irrespective of the value set. Shown in the next picture.





Here the third parameter takes the latest value of the value set, hence shows the department name corresponding to that and the fourth parameter values are dependent on the value of the second parameter.







These were some of the basic uses of $FLEX$ and $PROFILES$. Feel free to post your queries and comments on this topic.

Enabling Descriptive Flexfield in Oracle Applications


Summary:
The key for enabling a Descriptive Flexfield is to identify the Base table of the related form and then use the table name to query the Descriptive Flexfield Title. Once the title of the Descriptive Flexfield is obtained, then we just have to Query the name in the Application Developer responsibility and then add the desired columns. This will enable the Descriptive Flexfield.

Getting Started:
Lets enable the Descriptive Flexfield of Purchase Order form. The initial screen looks as shown below and the DFF field is Disabled.



Get the name of the view from which this form is created using the Tools=>Record History option




Use the utility TOAD to find the base table of this view. We will find that the base table involved here is PO_HEADERS_ALL.

Now, switch to Application Developer Responsibility and open the form for registering the Flexfields.

Navigation:

Application Developer => Flexfields => Descriptive => Register

The window which opens is as shown below:





Now, query this form by providing the values for Application as ‘Purchasing’ and the Table Name as ‘PO_HEADERS_ALL’. The result will be as shown below:



Once the window is queried, then select the Title ‘PO Headers’ from this window and then navigate to
Application Developer => Flexfield=> Descriptive => Segments



Now, query this window using the title and the application name. The window is as shown below:




Now, uncheck the freeze flexfield definition and then add the context field values as per the need. This is illustrated in the screen below.





Here, I have added A and B as the code and ‘Global Data Elements’ is default and is always enabled.

Now, select the code and then add columns to it. It is shown as below



When clicked on Segments, we will get the window as below. We then specify the columns which will get displayed when this code is selected.



If desired, we can attach the value set also to restrict the user from entering any garbage value.

Now, save this and follow the same steps for enabling the other code(B) also and also ensure that to show the difference between code A and code B, enable three columns in Code B.

Now, the last step is to specify a default value to the Context Field. I am providing here a reference field as TYPE_LOOKUP_CODE. Ensure that the checkboxes are checked. The screen is as below.



Once this is done, we then have to compile the flexfield and save the window. For compiling the Flexfield definition, we need to first save the window and then check the freeze flexfield checkbox and then click on Compile.



Now, Login to Purchasing responsibility and then open the Purchase Order form.



This window shows that the DFF field is now enabled. When we click on the DFF window, we will be getting a screen as below



When the LOV is clicked, we get the values populated by the reference column which we gave as ‘TYPE_LOOKUP_CODE'.It is shown below




Now, based on the value selected we will get the corresponding columns which is shown in the subsequent screen.



Hence, this is how a flexfield works. When the data is saved, it will be saved in the base table on the corresponding Attribute columns as specified in the Application developer responsibility.

Your comments and queries are always appreciated.




Flexfields in Oracle Applications


What is a Flexfield?
As the name suggests, a Flexfield is a ‘flexible field’. A flexfield is a field made up of segments. Segments are nothing but columns of the base table. Each segment has a name that can be assigned and a set of valid values. There are two types of Flexfields in Oracle Applications and they are called as
• Key Flexfield
• Descriptive Flexfield
With both of these flexfields, users get the ability to customize their application to match their business needs without programming.


When and where are these flexfields used?
Descriptive Flexfields are used in places whenever the user feels a need of extra information apart from the Oracle supplied standard information in the forms.
Key Flexfields are used whenever there is a need for storing Key information related to the organization.

What is the difference between Key and Descriptive flexfields?

  • Key flexfield is used to store the key information about an organization whereas Descriptive flexfields are used to store any additional related information.
  • Key Flexfield values are stored in Segment columns in the base table whereas descriptive flexfield values are stored in the Attribute columns of the base table.
  • Key Flexfields are non-updatable and mostly have only restricted values as input (Attached Value sets) wheras a the fields in Descriptive Flexfield are updatable.
  • Key flexfields do not have any context sensitive segments whereas Descriptive flexfields can be made into context sensitive fields.
  • Key Flexfields are the mandatory fields whereas descriptive flexfields are not mandatory by default but can be made mandatory.

What are the Base Tables involved in Flexfields creation?
The tables involved are
FND_FLEX_VALUES_TL, FND_ID_FLEXS, FND_FLEX_VALUES, FND_FLEX_VALUE_SETS, FND_ID_FLEX_STRUCTURES

Query to find the number of oracle Supplied key flexfields in each application

SELECT fa.application_name application, COUNT (fif.application_id) flexfield_count
FROM fnd_id_flexs fif, fnd_application_tl fa
WHERE fif.application_id = fa.application_id
GROUP BY fa.application_name

A sample output of this query is as below:

These were some of the basics involved with Flexfields in Oracle Applications. The next post will be on how to enable a Descriptive Flexfield in Oracle Applications.

Please feel free to post your comments or queries




Analytical Functions in SQL


Analytical Functions:
Introduced in Oracle 8i and are used for various purposes like calculating a running total, computing a percentage within a group, compute a moving average and many more in SELECT statements. Analytical functions add extensions to SQL language and not only make it easier to code but also makes the query execute faster.

How Analytic Functions Work?
It works on a group of records and it actually computes the aggregate value on a group of rows and returns the result. The main difference between Aggregate functions and Analytical functions is that, analytical functions return multiple rows for each group.

Important:
  • Analytical functions can only be used in a SELECT clause and ORDER BY Clause.
  • Difference between Analytical Function and Aggregate Function is that Aggregate functions return one row from a group of data’s whereas Analytical functions return multiple rows in a group of data.

Syntax:

Analytic-Function(Argument,Argument,...)
OVER (
Query-Partition-Clause
Order-By-Clause
Windowing-Clause
)

Some of the Analytical functions are as below:

AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

Some examples of Analytical functions in SQL Queries

a) Calculating a Running total:

Query:

SELECT ename, deptno, sal, SUM (sal) OVER (ORDER BY sal) "Running Total"
FROM emp

Output:


Note: One thing to note here is that, whenever ‘SAL’ is same for the same dept, the running total displayed here is a sum of both the salaries.

b) Retrieving the first and Last values in a group:

Query:
SELECT ename, sal, deptno, first_value(ename) over(Partition by deptno) FIRST, Last_value(ename) over(Partition by deptno) Last FROM emp

Output:



The above query gives the first and last values in a group.


c) Calculating a running average:

Query:

SELECT deptno, sal, AVG (sal) OVER (ORDER BY sal DESC) "Avg Sal"
FROM emp

Output:




d) TOP ‘n’ Analysis:

Query:

SELECT * FROM (SELECT ename, sal, ROW_NUMBER () OVER (ORDER BY sal DESC) RANK FROM emp) WHERE RANK <= 3

Output:



e) Accessing rows around a current row:

Query:

SELECT deptno, ename, sal, LAG (sal, 1, 0) OVER (ORDER BY hiredate, ename) "LAG",
LEAD (sal, 1, 0) OVER (ORDER BY hiredate, ename) "LEAD"
FROM emp

Output:



This query gives the LAG and LEAD values of an organization. LAG means the value previous to the current value and LEAD means the value next to the current value.
The syntax can be explained as below:


LAG(Column_name, Offset, Default_value)

LEAD(Column_name, Offset, Default_value)

Where:
Column_name is the name of the column
Offset defines on how many rows need to be skipped
Default value places a user defined value when there are now values in the offset range.

Windowing Clauses:
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. The default window is an anchored window that simply starts at the first row of a group and continues to the current row.

The various keywords used here are:
ROWS n PRECEDING, RANGE n PRECEDING

Note: RANGE option can only be used for number and Date datatypes.

Example:

1) RANGE window clause:

Query:

SELECT empno, sal, COUNT (*) OVER (ORDER BY sal ASC RANGE 100 PRECEDING) "counter"
FROM emp
ORDER BY empno

Output:



Explanation:
To understand the above query, we need to first look at the clause ‘RANGE 100 PRECEDING’, This creates a range from the current row for sal which is in the range of current value to (current value-100) and counts the salaries which falls within this range.
If we take the first row(1600) the count for this is 2. This is because there exists a value 1500, which is exactly at the start of the range and the number itself (1600), hence the count 2. Similarly, 1250 has a count 2 because of the existence of another value 1250 and 2975 has a count of only 1 because the value prior to this is 2850 which is more than 100 points below it and doesn’t fall in the range created. The other counts and the values follow the same criterion.


2) ROW window Clauses:

Query:

SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM (sal) OVER (ORDER BY ename ROWS 2 PRECEDING) "Sliding Total"
FROM emp

Output:



Explanation:
This is a row type windowing clause and the important thing to note here is the clause ‘ROWS 2 PRECEDING’. What this implies here is that, the total which is calculated here is based on the previous two rows of the current columns.
For instance, take the first row, the ‘Sliding total’ is 1100, same as the value of ‘Sal’ and the second row value for ‘Sliding total’ stands as the sum of the first and the second row. This is because, the second row does not have two rows preceding it, hence the second value is taken from the immediate column. If we take the case of the third row, the ‘Sliding total’ value is the sum of the third row, first and the second row, that is (2850+1100+1600=5550) and lets take the fourth row, the value of ‘Sliding total’ here is the sum of the immediate two rows preceding it and that is (2450+2850+1600=6900) and so on.

Hence, this concludes a small introduction of Analytical Functions in SQL. Feel free to post any comments or queries on the above topic.


Basics in Bulk Binding

What is the need for Using Bulk Binding concepts in programs?

To answer this question, we must first understand how a data is fetched in a PL/SQL program. Every program which fetches data uses SQL at the back end, hence the need for a SQL engine to fetch data’s. Whenever a PL/SQL block calls a SQL statement, the control passes to SQL engine, this in-turn fetches the data from the database and transfers the data and the control back to PL/SQL engine for further processing.

If we code a program which uses a lot of data fetching and loops, then it causes a lot of Context Switching between PL/SQL and SQL engines, thereby decreasing the performance considerably. It is here where Bulk Binding comes in handy. When we use Bulk Binding concepts in our programs, it eliminates the multiple context switching between the two engines and considerably increases the performance of the program.

How Bulk Binding works?

As discussed earlier, Bulk Binding reduces the context switching between PL/SQL and SQL engines in a very simple manner. Here is how it achieves this:
Lets take a normal cursor which has a set of rows and we would like to manipulate the data’s fetched from this cursor in our program. This obviously needs a lot of context switching when we use a Cursor For Loop, but when we incorporate Bulk binding in the program, what it does is, it will fetch all the data’s from the database and store it in a temporary table in PL/SQL (The temporary table can be a VARRAY, PL/SQL TABLE, RECORD TYPE, %ROWTYPE, CURSOR TYPE). Once it is stored in these temporary tables, then we just need to use these tables to manipulate further in our programs as the data is now in PL/SQL engine. This eliminates a lot of context switching when a large data is concerned.

What are the types of Bulk Binding?

With SELECT or FETCH statements
BULK COLLECT INTO clause

In-Bind binding. (INSERT or UPDATE)
FORALL Clause

Out-Bind binding.
RETURNING clause

What are the attributes of Bulk Binding?

The main attribute of bulk Binding is SQL%BULK_ROWCOUNT(i)
This gives the number of rows affected when using a Bulk Binding concept.
The other attributes are associated with error handling. They are
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
SQL%BULK_EXCEPTIONS(i).ERROR_CODE

What are the typical usage of Bulk Binding?

There are two important places where we can use Bulk Binding and they are discussed as below:

In a SELECT or FETCH statement. Whenever we need to fetch data’s from the database we need to use the BULK COLLECT INTO clause, hence this comes in handy in the SELECT and the FETCH statements.

While manipulating, DELETING, UPDATING or INSERTING. Whenever there is a data manipulation in bulk amounts, we can use the FORALL option. It performs all the manipulation at one go.

What are the limitations in Bulk Binding?

Bulk binding also has its share of limitations and some of them are

a) FORALL clause must precede only a DML statement.
b) Cannot use the DBMS_OUTPUT statement after FORALL clause.

Is there a way to limit the data that is inserted using the Bulk Binding concept?

Yes, there is a way in which you can restrict the number of rows to be fetched in a BULK COLLECT. What you can do is, you can specify the LIMIT clause followed by any number. BULK COLLECT will fetch only those many records which satisfies the number following the LIMIT Clause.

Example: FETCH cursor_name BULK COLLECT INTO collections_name LIMIT x;

Here x stands for a numeric value restricting BULK COLLECT records.

What is the use of SAVE EXCPTIONS in Bulk Binding?

Whenever a PL/SQL program encounters the keyword SAVE EXCEPTIONS after a FORALL clause, it saves the exception in the SQL%BULK_EXCEPTIONS collection and does not interrupt the operation.
To explain in detail, lets say we are manipulating a thousand records, FORALL clause encounters an error in the 500th record, if SAVE EXCEPTIONS clause is not specified, then the operation gets interrupted causing a ROLLBACK of the entire operation and when SAVE EXCEPTIONS is specified then the exception which gets encountered midway through the operation is stored in the collection of errors called as BULK_EXCEPTION and the operation continues without interruption. Later we can view the exceptions caused in the operation by querying the error collection using SQL%BULK_EXCEPTIONS(i).ERROR_INDEX and SQL%BULK_EXCEPTIONS(i).ERROR_CODE.

What are the possible errors that may occur while using BULK BINDING?

The only error I can foresee getting with FORALL is this one:
ORA-22160: Element at index does not exist
And you will only get that if you are somehow binding to an index that does not exist.You may also get complaints if you use the index in an expression, which is not supported:
PLS-00430: FORALL iteration variable i is not allowed in this context

Now, lets see some of the sample programs using BULK BINDING concepts.

Sample BULK COLLECT program:

DECLARE
TYPE Cust_tab IS TABLE OF Customers_Active%ROWTYPE;
Custs Cust_tab;
BEGIN
SELECT Customer_Account_Id, Effective_Date, Expired_Date
BULK COLLECT INTO Custs

FROM Customers_Active
WHERE Effective_date BETWEEN
TO_DATE(’01-JAN-2004’ , ‘DD-MON-RRRR’) AND TRUNC(SYSDATE);
END;


Sample FORALL Program:

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10,30,70);
BEGIN
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal + 100 WHERE deptno = depts(i);
END;

So this should probably give you some insight on the basic concepts of BULK BINDING.
Feel free to post your comments and queries in the comments section.

Phases in Implementation

The picture below depicts the various phases in implementation of an Oracle Application Project.

Picture courtesy: UST Global

REF Cursors

What are Ref Cursors?

Ref cursor is a Data type. A variable created using this data type is usually called as a Cursor Variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.)


It is of two types:

Strong: With a Return Type
Weak: Without a Return Type


Advantages of Ref Cursors:

* Uses the same memory area for all the active sets created by different queries.
* Can be used to pass result sets between sub programs.
* Ability to change the query based on a certain criterion.


Difference between Static and Ref Cursors:

Static Cursors cannot be passed to sub programs whereas ref cursors can be passed between sub programs.
Static Cursors as the name suggests are Static and decided at the design time itself whereas Ref Cursors are changed during the execution time as per certain criterion.

Important Note: Ensure that any open cursor is closed before attempting to open the next cursor.

Examples of Ref Cursors:

A simple Ref Cursor:


declare type ref_cursor is REF CURSOR;
var_emp ref_cursor;
var emp.ename%type;
begin
open var_emp for select ename from emp;
loop
fetch var_emp into var;
exit when var_emp%notfound;
dbms_output.put_line(var);
end loop;
close var_emp;
end;

%ROWTYPE with Ref Cursor:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename ' - ' er.sal);
end loop;
close c_emp;
end;

RECORDS with Ref Cursor:


declare

type r_cursor is REF CURSOR;

c_emp r_cursor;

type rec_emp is record ( name varchar2(20), sal number(6) );

er rec_emp;

begin

open c_emp for select ename,sal from emp;

loop

fetch c_emp into er;

exit when c_emp%notfound;

dbms_output.put_line(er.name ' - ' er.sal);

end loop;

close c_emp;

end;

Multiple queries using Ref Cursor:

declare

type r_cursor is REF CURSOR;

c_emp r_cursor;

type rec_emp is record

( name varchar2(20),

sal number(6) );

er rec_emp;

begin

open c_emp for select ename,sal from emp where deptno = 10;

dbms_output.put_line('Department: 10');

dbms_output.put_line('--------------');

loop

fetch c_emp into er;

exit when c_emp%notfound;

dbms_output.put_line(er.name ' - ' er.sal);

end loop;

close c_emp;

open c_emp for select ename,sal from emp where deptno = 20;

dbms_output.put_line('Department: 20');

dbms_output.put_line('--------------');

loop

fetch c_emp into er;

exit when c_emp%notfound;

dbms_output.put_line(er.name ' - ' er.sal);

end loop;

close c_emp;

end;

Ref Cursor used as Parameters between Sub Programs:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record ( name varchar2(20),
sal number(6) );
procedure PrintEmployeeDetails(p_emp r_cursor) is
er rec_emp;
begin
loop
fetch p_emp into er;
exit when p_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails(c_emp);
close c_emp;
end loop;
end;

Thus, these are some of the uses of Ref Cursors. Feel free to post any comments on this article.

Source Courtesy: www.devshed.com