Wednesday, February 15, 2012

Aging Reports in Oracle Apps Receivables - Quick Overview

Overview

Aging reports are used to review information about your open items so as to know how much funds have been held up and with whom. Aging reports display the customers who have outstanding balances and the amount each customer owes us which also helps to expedite the collection process.  Receivables aging reports Do Not include customers with a zero outstanding balance.
These reports can print both detail and summary information about your customer’s current and past due invoices, debit memos, and charge backs.  Receivables also gives the option to see credit memos, on–account credits, unidentified payments, and on–account and unapplied cash amounts.
As mentioned above, Oracle Receivables ages the transactions according to due date. The aging reports however select transactions according to gl date
Receivables aging reports includes all open items whose gl_date is before or the same as the “as of date” entered, and whose gl_date_closed is after the “as of date”. The default value for “as of date” is the current date.


 Categories of Aging Reports 

Receivables aging reports are categorized as follows:
  • Aging – By Account
  • Aging – By Amount
  • Aging – By Collector
  • Aging – By Salesperson 


 Report Parameters for Aging Reports 

Aging Bucket Name:  
Specify the bucket set from which the report information needs to be printed. The default bucket set is 'Standard'.
As of Date:
Specify the date as of which the transactions need to be aged. Receivables includes all open items whose GL date is before or the same as this date. The default is the current date.
Order By:
The option you want Receivables to use to sort your information. For example, you can sort by:
·        Customer Name (Aging - 4 and 7 Bucket reports)
·         Transaction Type (Aging - 4 and 7 Bucket reports)
·        Balance Due (only for 7 Buckets - By Amount report)
·        Salesperson (only for 7 Buckets - By Salesperson report)
Report Format:
The “Brief” format prints customer name and customer number with item information while the “Detailed” format prints address and contact address as well.
Report Summary:
The “Invoice Summary” option prints information on all customers' debit items.
The “Customer Summary” option prints customers' names with their total debit item balances.
Show on Account:
Specify whether to print credit items for your customers.
·        Do Not Show: Receivables does not display any of your identified or unidentified payments, or on-account credit memos.
·        Age: Receivables ages your credit items and includes the credit amounts in the appropriate aging bucket columns.
·        Summarize: Receivables displays the sum of your credit items in the Customer Credit Memos, Customer Payments, and the Customer Balance rows. This is the default option.
Show Receipts at Risk:
Receipts at Risk are receipts that have either not been cleared or factored receipts that have not been risk eliminated. Select one of the following values for your report:
·        Age:- Include receipts at risk in this report. Receivables displays the receipts at risk with other open receipts in the appropriate bucket and includes them when determining the customer's balance.
·        Summarize:- Receivables displays the sum of your receipts at risk in the Customer Credit Memos, Customer Payments, and the Customer Balance rows.
Do Not Show :- Receipts at risk will not be included in this report. This value is used as the default.


Wednesday, February 15, 2012 by Team search · 2

Friday, February 10, 2012

Performance tuning using BULK COLLECT with example


In this post, lets see the effect of BULK COLLECT over performance.Simple example are used.
The below code is written using with and with out Bulk feature to retrieve data from a table and populate the collection objects.

Without Bulk Collect

DECLARE
   TYPE namelist IS TABLE OF emp_info.ename%TYPE
      INDEX BY BINARY_INTEGER;
   TYPE sallist IS TABLE OF emp_info.sal%TYPE
      INDEX BY BINARY_INTEGER;
   CURSOR c1
   IS
      SELECT ename, sal
        FROM emp_info;

   names   namelist;
   sals    sallist;
   i       NUMBER   := 0;
BEGIN
   FOR rec_c1 IN c1
   LOOP
      names (i) := rec_c1.ename;
      sals (i) := rec_c1.sal;
      i := i + 1;
   END LOOP;
END;

The above is a very simple program that does not use bulk binds. There were
  146939 fetch calls against the cursor, requiring 146943 logical
reads and 1.57 CPU seconds. 

With Bulk Collect

DECLARE
   TYPE namelist IS TABLE OF emp.ename%TYPE;
   TYPE sallist IS TABLE OF emp.sal%TYPE;
   CURSOR c1
   IS
      SELECT ename, sal
        FROM emp_info;

   names   namelist;
   sals    sallist;
BEGIN
   OPEN c1;
   FETCH c1
   BULK COLLECT INTO names, sals;
END;

The above is a very simple program which uses bulk binds.
There were only 1 against the cursor instead of 146939, bringing logical reads down from 146943 to 475 and CPU time from 1.57 to 0.51 seconds.
This shows the effect of BULK BINDS on Performance Improvement.



Friday, February 10, 2012 by Team search · 0

How to feed Data into Oracle Apps Forms using Dataloader Utility

Friends, this post will quickly take a quick look on using the data loader software…

In General, there is no extensive setup required to run a load. The user must have an Oracle login and password and be logged in to the system. Lets walk through the steps necessary to create a simple load.

clip_image002

This is the DataLoad window. The spreadsheet section of the page is very similar to the spreadsheets in Microsoft Excel. Importing data from an Excel spreadsheet into the DataLoad spreadsheet is very easy.

First open the Excel file that should be copied.

clip_image004

For successful import, this file should have no titles or added fields in the spreadsheet. The spreadsheet above was created to import thirty discrete jobs into Oracle. It contains the job number, the type of job being created, the assembly name, the manufacturing class, quantity, job status, and the start date. It was ordered in this way based on the order in which this data is required when a job is created in Oracle.

clip_image006

The best way to determine exactly what is necessary for data input in Oracle is to open the window into which data will be entered and create a dummy input, recording every command used along the way. These will most likely include use of the TAB key and possibly the Enter key. Any commands that must be used should be recorded in an excel file. For a list of the available commands for use in DataLoad, a list can be found under the Edit menu, under “Commands” or by clicking the “Edit Commands” button in the toolbar.

clip_image008

For this example, the first line in the Book 3 Excel spreadsheet will be used, but will be re-formatted for use with DataLoad.

clip_image010

This first line is how the data were organized in the original file, while the second line has the same data, but it reformatted for DataLoad. The multiple “TAB” commands at the end of the second line will finish a record in Oracle and tab down into the second record for input of another discrete job. The inputs in the file mimic exactly what is necessary from a user when inputting a discrete job. After identifying how the file should be formatted, the rest of the original Excel spreadsheet should be reformatted as well.

clip_image012

A save command could be added to the end of the file, but I would suggest not using that command. If Oracle were to run slow during input, Dataload would continue trying to load information even if Oracle is not responding. This could cause data discrepancies if input errors occur. Visual review of new records in Oracle should be used before saving new inputs.

When the Excel spreadsheet has been formatted correctly, highlight the desired cells to be copied in the Excel spreadsheet. When importing into a DataLoad spreadsheet, DataLoad will automatically recognize the active Excel spreadsheet without being directed towards it.

clip_image014

Now go back into the DataLoad window. Click the “Import data from Excel” button in the toolbar to automatically copy all the cells selected in the excel spreadsheet. This button is on the far left in the picture below. Import can also be done by clicking “Excel Import” under the Tools menu.

clip_image016

The information from Excel is now in DataLoad and read to be copied into Oracle. First, be sure that the Oracle instance is open to the window in which the input will be completed, with the cursor in the field requiring the first input.

clip_image018

Back in DataLoad, there are two necessary fields to be filled before the load can begin, as well as one optional field. These are the Window, Command Group, and Description fields.

clip_image020

Under “Window”, DataLoad will automatically recognize every window open on the PC. Choose the Oracle instance running the input window. For the “Command Group”, choose 11i. This is an older version of the Oracle E-Business Suite that uses the same commands at R12. A description can be entered in the “Description” field, but is not necessary.

Before beginning the load, ensure that the data is correct. This load can be saved for use later. Also, ensure that the open instance of Oracle is still valid and doesn’t require re-login.

When the load is ready to begin, click on either the “Send data to form” button in the toolbar, or the “Send Data” option under the Tools option in the toolbar.

clip_image022

Data insertion will automatically begin. The user may be required to stand by and ensure that data is being entered correctly. When the load has completed, the user can look over the inserted data to ensure its accuracy. If there was an error during input, it may have occurred due to lag in Oracle during insertion. To change this, first close out of the Oracle form without saving the erroneous data and reopen the input form to be ready for the next try.

In DataLoad, the user can specify a delay time after data insertion to account for any lag in processing. To change the delay time, either click on the hourglass button in the toolbar or click the “Delays” option under Tools. This will open the DataLoad Delays window.

clip_image024 clip_image026

Upon changing the delay options, the load can be initiated once again. Continue to change this delay options as often as necessary to ensure an accurate load.

I hope it helped you understand the basics of Data Loader..

by Team search · 2

Friday, February 3, 2012

BPEL–Database Adapter Basics

 1. Overview

While developing a BPEL project we would be faced with so many situations here we need to interact with Database, use FTP, or to read or write a file to local system. For such sort of situations the JDeveloper as provided us with good number of Adapters, through these Adapters we can easily interact with external environment.

Almost all the adapters generate a XML schema once we create them. As XML is understood by all environments, interaction between different environments is simplified.

In JDeveloper we have some eleven adapters like Database Adapter, FTP Adapter, File Adapter, Oracle Application Adapter, JMS Adapter and many more.

This document deals with Database Adapter only.

What is Database Adapter?

Database Adapter is a tool provided by JDeveloper to interact with standard Database. This Adapter is having four different modes of interacting. Based on our requirement we can select a mode and use it to meet our requirement.

Types of Database Adapter

JDeveloper provides four types of operations that can be done using the database Adapter. Those are:

  • Call a Stored Procedure or Function.
  • Perform an Operation on table. (Which includes operations like insert, delete, select.)
  • Poll for New or Changed Records in table.
  • Execute Custom SQL

Below links will help you to understand the above mentioned four different operations of a Database Adapter….

1. How to create a BPEL Project  and a database adapter? 

http://www.shareoracleapps.com/2012/02/create-project-and-database-adapter-in.html

2. How to configure a Database Adapter to Call a Stored Procedure or Function?

http://www.shareoracleapps.com/2012/02/bpel-calling-stored-procedure-using.html

3. How to configure a Database Adapter to  poll for New or Changed records in table?

http://www.shareoracleapps.com/2012/02/bpel-creating-database-adapter-for.html

4. How to configure a Database Adapter to  Perform an Operation on table. (Which includes operations like insert, delete, select.)

http://www.shareoracleapps.com/2012/02/database-adapter-to-performs-simple.html

5. How to configure a Database Adapter to Execute Custom SQL

http://www.shareoracleapps.com/2012/02/database-adapter-to-handle-custom-sql.html

Friday, February 3, 2012 by Team search · 0

Database Adapter to handle a Custom SQL Statement

1. For executing a custom SQL Statement, select the fourth option in the below screen and click ‘Next’.

clip_image002

2. In next step you have to specify the custom SQL statement to be used, for this automatically a XML Schema Definition is generated. Click ‘Finish’ button, this completes the creation of database adapter.

clip_image004

3. Once you have created any Database Adapter and clicked ‘Finish’ button, we will get the below screen. It populates data for WSDL File location, Partner Link Type, and Partner Role.

clip_image006

I hope it helped you guys… Enjoy Sharing…

by Team search · 0

BPEL - Creating Database Adapter for Polling a Table

You might be having a requirement wherein we need to update a table or perform some action if ever there is some change in some specific table. For achieving these sort of requirements BPEL provides us with Database Adapter with a polling option.

1. Once you have dragged the database adapter in to the work area and giving the name of the adapter and connection name, you can find the below screen where you have to select ‘Poll for Changed records in Table’.

clip_image002

2. Once you click ‘Next’ you are asked to import the tables to be used. Once you have selected the tables to be imported, click OK.

clip_image004

3. In this step we have to specify the root table from the list of tables that we selected.

4. If ever you want to have some relations between the tables then you can create them in next step. Once you click on Create button then the following screen appears where you have to give the details of the relation you want to create like the parent table name, Child table name, and type of relation and so on. Once this is done press OK.

5. In the next step you can select the columns that you want to exclude in Queries. Click next.

6. Now in current step you have got a screen where in you have to select the operation to be done after table has been polled. The appropriate where condition is generated based on your selection in the following screen.

clip_image006

a. Delete the Row(s) that were Read: By using this, the rows are completely deleted. This scenario is most useful if ever the size of table is very large and you cannot afford to increase the number of rows in it. By doing this the efficiency of the table is increased. You can use this if ever the data being stored is not so important.

Once you have selected this option the next screen asks you the frequency of Polling, and other criteria. Now click ‘Next’.

According to the data given the final SQL statement is generated. You have an option to edit the SQL statement generated also.

b. Update a field in Table (Logical Delete): This is most widely used scenario. You can opt for this if ever you don’t want to check for changes in the same old rows but also cannot afford deleting these rows. Here we have a field in which we try to toggle the values from some constant to other constant. You will poll only on the rows which have some constant and once it is checked we will toggle that value.

c. Update a Sequencing Table: This is used to update a sequencing table based. Sequencing table feature can be used when there is a field whose value will keep on increasing E.g. Primary Key, creation date or last update date. New table will be created where last read value of the field is stored. Adapter checks for the changes based on the value in the custom table.

d. Updating different sequencing table in Another Database: This is used to update a different sequence table in another database. For this you need to give the database name required. This feature can be used when user doesn’t have access to create a new table in the host database.

e. Update Sequence File: This option works similar to above cases but uses a file on the server machine.

by Team search · 0

Database Adapter to performs a simple Operation on table–BPEL

In this post, let us see how to access a database to execute a simple query like insert, update, select, or delete; BPEL provides a direct method through which we can directly interact with database without any Queries. We just need to specify the table name and the operation to be performed.

Once you have selected the Database connection or created a new database connection the next step is selection of type in which we can interact with Database. In that list we have to select the second option of performing an operation on database.

In this type, we have multiple combinations of simple Operations from which we can check the required operations and use them directly without any Queries. The screen looks as given below.

clip_image002

1. Once we select the required operations and click next, we will have a screen asking us to import the tables from database which we want to access. You get a dialogue box asking for a filter for name based on which the tables would be displayed in the left panel. User has to select the required tables and click on arrow mark then the tables would be displayed on right side. Now once user clicks OK the tables in the right panel are imported.

clip_image004

2. If you selected multiple tables then JDeveloper asks for the master table in next step. Once you have selected the master table in next step you have to give the primary key on that master table.

3. If you want to have some relations between the tables then you can create them in this step. Once you click on Create button then the following screen appears where you have to give the details of the relation that you want to create like the parent table name, Child table name, and type of relation and so on. Once this is done press OK.

clip_image006

4. In next step, you can select the columns that you want to exclude in Queries. Once you click finish, the creation of Database adapter for a simple operation on table is completed.

 

by Team search · 0

How to create a Project and Database Adapter in BPEL

 

Let us first understand how to create a simple Project. Then we will see how to create an adapter.

Creating a Project

  1. Right click on the "Applications” and select “New Project” as shown in below Screen shot.

clip_image002

  1. In “New Project“ dialogue box select “BPEL Process Project” and click OK.

Note: We have three types of Projects, they are

a) Synchronous Process

b) Asynchronous Process

c) Empty Process

In our case we will be using empty Process.

  1. Give a name for your Project and select “Asynchronous BPEL Process” for Template as shown in the screen shot, and click Finish.

clip_image004

  1. Now on right side of JDeveloper we have a Component Palette. In this Palette select “Services”. By this a list of services are displayed from which select “Database Adapter” and drag it into the workspace as shown in the screen shot. Now Database Adapter Wizard starts.

Note: If ever it is not present then go to the “View” option in menu bar and select Component

Palette.

clip_image006

  1. Once you click on next button in the wizard, you are asked to give the name for the Database Adapter. In this case give “DatabaseAdapter” and click ‘Next’.
  1. In this step, you have to select the database that you want to interact. If ever you are interacting with a new database then click on “New” button by which Database Connection Wizard starts.

Creating a Database Connection

Database Adapter Wizard is a tool provided by the JDeveloper where in we can easily create a database adapter by providing minimum number of details like connection of database to be used, and table names.

For using this wizard you are required to create a database connection by providing the appropriate credentials. We have two methods of doing this. First one is creating the connection before hand by going into “Connections Navigator” and creating a new database connection by right clicking on Database option.

The second method is while creating a database adapter. This is explained below. You can follow the same steps while creating a connection using method one also.

    1. Once the Database connection wizard starts click on next button.
    2. In step 1, you have to give a name for the connection and “connection Type”. Mostly the connection type is defaulted to Oracle (JDBC).
    3. In step 2, give the user name and password through which you interact with the Database. “Role” field is optional so you may leave it. After you give these details press Next.

clip_image008

    1. Now, in step 3, we have to specify various details for the Database connection to establish as shown in the screen shot.

Note: The screen shot below shows the connection details for apps database.

image

    1. Then Test connection as shown in the wizard and once the Success message is displayed click “Finish”. This completes the creation of Database Connection.
    2. Once you have created the database connection, select its name from the drop down list. All other fields of the form are displayed automatically. Check if the JNDI name is populated.

by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.