Gopal's Oracle ERP School

OA Framework Tutorial 01

2011-09-03T00:16:00.001-07:00

After having received some requests from Oracle EBS newbies, I have decided to do some video tutorials to explain how screens can be built in OA Framework. For this purpose, I will build a simple case study[Person Maintenance Screen].
To begin with, I will create a simple page in OA Framework, and in following sessions, I will add additional Functionality[creation/changes] to the same page.

Welcome to the first Audio-Video tutorial in this series.
Objective :- To create a working page in OA Framework, without writing a single line of code in java.

Step 1 Create a table and insert sample data[3 records] for this demo.

The above Step 1 will do the below:-

1. Creates a table named xx_person_details to capture Person Details

2. Create a API to help insert records in this table

3. Inserts three records into table xx_person_details

4. In jDeveloper, create an OA Workspace and default Project for this OA Framework tutorial

create table xx_person_details
(
PERSON_ID        NUMBER NOT NULL
,FIRST_NAME       VARCHAR2(50)
,LAST_NAME        VARCHAR2(50)
,EMAIL            VARCHAR2(100)
,student_flag     VARCHAR2(1)
,name_of_university VARCHAR2(100)
,last_update_date DATE
,last_update_login INTEGER
,last_updated_by INTEGER
,creation_date DATE
,created_by INTEGER
) ;

CREATE OR REPLACE PROCEDURE xx_insert_person_prc(p_person_rec IN OUT xx_person_details%ROWTYPE) IS
BEGIN
SELECT per_people_s.NEXTVAL
INTO p_person_rec.person_id
FROM dual;
p_person_rec.last_update_date := SYSDATE;
p_person_rec.creation_date     := SYSDATE;
p_person_rec.last_update_login := -1;
p_person_rec.last_updated_by   := 71690;
p_person_rec.created_by        := 71690;
INSERT INTO xx_person_details
VALUES p_person_rec;
END xx_insert_person_prc;
/

DECLARE
l_person_rec xx_person_details%ROWTYPE;
BEGIN
l_person_rec.first_name   := 'Anil';
l_person_rec.last_name    := 'Passi';
l_person_rec.email        := 'passi.anil@gmail.com';
l_person_rec.student_flag := 'N';
xx_insert_person_prc(l_person_rec);
l_person_rec.first_name := 'Tom';
l_person_rec.last_name := 'Kumar';
l_person_rec.email      := 'tom.kumar@gmail.com';
xx_insert_person_prc(l_person_rec);
l_person_rec.first_name := 'Mark';
l_person_rec.last_name := 'Kapoor';
l_person_rec.email      := 'tom.kumar@gmail.com';
xx_insert_person_prc(l_person_rec);
commit;
END;
/

SELECT count(*)
FROM xx_person_details ;

commit;

New Project

Create OA Workspace..

File Name : OAFsem13.jws ( Workspace name )

File Name : OAFsem13.jpr ( Project name )

Default Package : tutorial13.oracle.apps.ak.xxperson13

-Next

DBC File Name : test40.dbc

User Name : ax07926

Password : erptest

Application Short Name : XXRM

Responsibility Key : XXRM_R1_YEDEK_PARCA

-OK

Step 2 Create the required BC4J and java packages for this OA Framework Screen

This step will create

xxPersonDetailsEO                  [ Entity Object ]
xxPersonDetailsVO                  [ View Object ]
xxPersonAM                            [ Application Module ]

Relation between view Object and AM, in effect creating an instance of the View Object.
Name of this view Object instance will be xxPersonDetailsVO1

Click here for Audio Video Link of Step 2

Add Business Components

OAFsem13.jpr -> Right-> New Business Components Package

-Next

-Bitir

-Tamam

Tutorial13.oracle.apps.ak.xxperson13.schema.server

Business component created

Create New Business Component

OAFsem13.jpr -> Right ->New Business Components Package

-İleri

-Bitir

Tutorial13.oracle.apps.ak.xxperson13.server

Business component created..

Create New Business Component

OAFsem13.jpr -> New Business Components Package

-Next

-Bitir

Tutorial13.oracle.apps.ak.xxperson13.webui

Business component created..

Create Entity Object

Tutorial13.oracle.apps.ak.xxperson13.schema.server -> Right -> New Entity Object...

-İleri

Name : XXPersonDetailsEO

Package : tutorial13.oracle.apps.ak.xxperson13.schema.server

Schema Object : XX_PERSON_DETAILS_S

-ileri

-İleri

-Yes

Validation Method

Create Method

-İleri

-Bitir

Entity Object Created..

Create View Object..

XXPersonDetailsEO -> Right -> New Default View Object

View Object Created

XXPersonDetailsEOView -> Right -> Move package

Tutorial13.oracle.apps.ak.xxperson13.server

XXPersonDetailsEOView moved to tutorial13.oracle.apps.ak.xxperson13.server

XXPersonDetailsEOView -> Right -> Rename XXPersonDetailsEOView

View Object name changed..

Create Application Module

Tutorial13.oracle.apps.ak.xxperson13.server -> Right -> New Application Module

-Next

Name : XXPersonAM

-Next

-Bitir

Application Module Created

OAFsem13.jpr -> Right -> New

OA Components -> Page

Tamam

Name : XXPersonMainPG

Package : tutorial13.oracle.apps.ak.xxperson13.webui

Tamam

XXPersonMainPG created

region1 -> Properties

ID : MainRN

AM definition :tutorial13.oracle.apps.ak.xxperson13.server.XXPersonAM

Window Title : Main Person Page for Search

Title : Main Person Page for Search

-Save

MainRN -> New -> Region

region1 created..

region1 properties

ID : QueryRN

Region Style : query

Construction Mode : resultBasedSearch

Include Simple Panel : True

Include Views Panel : True

Include Advanced : True

QueryRN -> Right -> New -> Region Using Wizard

-İleri

Region Style : table

PersonId – FirstName – LastName – Email selected...

-İleri

Style : messageStyled

-İleri

-Bitir

QueryRN table created..

Personld Properties

Search Allowed : True

Sort Allowed : ascending

FirstName Properties

Search Allowed : True

LastName Properties

Search Allowed : True

-Save

-Rebuild the Project

-Run the Page

Deployment of Page in APPS - OAF

2011-08-30T04:34:00.001-07:00

The steps in brief are :

· Development of the TestPagePG.xml page in local machine
· FTP the related source code/files to the Oracle APPS environment.
· Importing the page to the Database through Import Command.
· Registration of the page in the Oracle Apps environment.

On project compilation the class files along with xml files are generated in Myclasses.

The Folder Structure for xml pages and respective Controllers are as below
C:\Jdevhome\Jdev\myclasses\xxx\oracle\apps\fnd\framework\webui\*
C:\Jdevhome\Jdev\myclasses\xxx\oracle\apps\fnd\framework\server\*

The First step will be to move the files into the JAVA_TOP with the help of any FTP Tool.

Just drag and dropped the xxx folder from local m/c to Apps Java top path.

Importing the XML files:

Run the import scripts for the Page and Regions.

The import command is

import C:\Jjdevhome\jdev\myprojects\xx\oracle\apps\fnd\framework\webui\TestPagePG.xml -rootdir C:\jdevhome\jdev\myprojects -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.255.35.20)(PORT=1525))(CONNECT_DATA=(SID=DEV)))"

The command is to be run from Jdeveloper/Jdevbin/Jdev/Bin from command prompt.

Registering the Main Page in APPS.

Login through System Administrator Resp.

Select Application -- Function.

1. Enter the Function Name, User Function Name and Description

2. Enter Properties (Tab) Type as SSWA jsp function

3. In Web HTML Tab enter the page path
OA.jsp?page=/xxx/oracle/apps/fnd/framework/webui/TestPG

Attach the function with a desired menu and then run from the respective responsibility.

Last and the important step :-- DONOT forget to bounce the Appache server.

Important Profile Options in OAF

2011-08-30T04:28:00.001-07:00

FND_Diagnostics

Setting the FND : Diagnostics (FND_DIAGNOSTICS) profile option to "Yes" will enable the diagnostics global button to be rendered on the screen. Pressing this button brings the user to an interface where the user can choose what type of logged messages to display.

Personalization Levels

Personalizations can be enabled at the function, site, operating unit or responsibility level. Personalizations at lower levels override personalizations at higher levels. Values inherit the definition from the level immediately above unless changed.

FND: Personalization Region Link Enabled :
Valid values: Yes - renders the "Personalize Region" links above each region in a page. Each link takes you first to the Choose Personalization Context page, then to the Page Hierarchy Personalization page with focus on the region node from which you selected the "Personalize Region" link.

Personalize Self-Service Defn – Set this profile to Yes to allow personalizations.

Disable Self-Service Personalization - Yes will disable all personalizations at any level.

FND: Personalization Document Root Path (new in 11.5.10) - Set this profile option to a tmp directory with open (777) permissions for migrating personalizations between instances.

How to See Log on Page

Enable profile Option FND_Diagnostics to "Yes" at User OR Site Level.

In Controller write this code:-
pageContext.writeDiagnostics(this, "Checking profile options", 1);

In Application Module write this code
getOADBTransaction().writeDiagnostics(this, "Checking Profile Option", 1);

Now to see log on screen Click on “Diagnostics” on Page [Top right on page]

Then Choose Show Log on Screen from the picklist and choose log level as Statement Level and Click on Go

Learn OAF

2011-08-30T04:26:00.001-07:00

Learn OAF

Getting started with OA Framework

Oracle Applications Framework (OA Framework) is the Oracle Applications development and deployment platform for HTML-based business Applications.

OA Framework is based on the industry-standard J2EE MVC design pattern.

The MVC architecture is a component-based design pattern with clean interfaces between the Model, View, and Controller.

♣ The Model is where the application implements its business logic. All the BC4J components in OAF comes under Model like AM (Application Module), VO (View Object), EO (Entity Object), VL (View Link) & AO (Association Object).

♣ The View is where the application implements its user interface. View means the UI (User Interface) that is visible to the Users.

♣ The Controller is where the application handles user interaction and directs business flow. Controller is a simple javaclass file that contains methods for initial page request and post back request.

For more details please refer

Things good to know

Overview of the OA-Framework Components

The next step is to determine the correct JDev patch for your development system. We can find the correct version of Jdeveloper with two ways:

1) You can check the framework version in your instance by using http://host:port/OA_HTML/OAInfo.jsp,

2) Click “About this page” link on the bottom left of any OA Page and click on Technology Components.

If you are not able to see it please refer below link

Important Profile Options in OAF

After knowing the patch number you can download it from https://support.oracle.com

Note ID: ID 787209.1 - How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.

♣ Once downloaded, follow the installation steps set out in the OAEXT_README.txt document , located in the root directory of your patch file.

♣ The JDeveloper patch includes a very comprehensive documentation library, after installation you can locate the documentation index here: DEV_INSTALL_DIR/jdevdoc/index.htm

♣ Follow the “You are Customer, Consultant or Support Representative” in Chapter 1: “Setting Up your Development Environment” of the OA Framework

I suggest you study Chapters 1, 2 and 3 of the OA Framework developers guide; this will present you with a detailed introduction to OA Framework development concepts. Chapter 8 of the developers guide covers OA Framework standards and guidelines.

The Oracle Applications Framework ToolBox Tutorial is a comprehensive step by step guide to OAF development, extension and personalization. The tutorial will guide you from creating your first “Hello World” program right through to multi-step update pages, partial page rendering techniques and advanced charts and graphs.

Where do I get help?

♣ Oracle Applications Framework Developer's Guide (Included in JDev Installation)

The Developer's Guide fully documents the capabilities of the Framework including instructions, examples and essential standards for implementing business-tier objects, UI components and server-side features.

♣ Oracle Applications Framework ToolBox Tutorial Application (Included in JDev Installation)

The ToolBox Tutorial application is a sample application accompanied by extensive examples with step-by-step instructions that demonstrate the usage of business objects and UI components to build OA Framework based application pages, against a simple Purchase Order type application schema, installed on your 11i instance.

♣ OA Framework Discussion Forum on the Oracle Technology Network

Discussion forum for OA Framework Extensions and the OA Extension to Oracle9i JDeveloper, OA Framework Forum (http://forums.oracle.com/forums/forum.jspa?forumID=210). You can use the forum to post questions and exchange information with other users working with OA Framework technology.

♣ Oracle Applications Product Documentation

Some products may provide additional information on extending application specific business objects and functionality. Consult Oracle Metalink (http://metalink.oracle.com) under the respective product for more information.

About OAF – Downloading and Configuring JDeveloper

2011-08-30T04:16:00.000-07:00

This article will describe the process for downloading & installing the correct version of JDeveloper for development on your instance of E-Business Suite. It will discuss how to identify the correct version of JDeveloper to use and how to configure your OAF environment on your local machine.
Oracle JDeveloper (JDev) is the development tool we use to develop Oracle Applications Framework components, due to variations in the framework different versions of Oracle Applications require development to be performed on different versions of JDeveloper or at least the OAF flavour of JDeveloper. JDeveloper can be downloaded from metalink however prior to visiting meta link you will need to establish which version and patch set you are running as regards Oracle Apps.
To identify the correct version of Oracle Applications Framework we need to activate diagnostics. You can do this by ensuring the profile option “FND: Diagnostics” is set to “Yes”. Close your browser session and restart Oracle Applications, Log in and click the “About this Page” Link which can be found in the page footer.

Fig 1

When the “About Page” loads click the “Technology Components” Tab. You will be able to identify the version of Oracle Applications from the OA Framework version.

Fig 2

Now that we have identified the correct version of OAF we can visit metalink https://metalink.oracle.com note 416708.1 and download the corresponding version of JDeveloper. Download the file to a convenient directory on your machine, in this example we would download patch 6908968.

Fig 3

JDeveloper comes as a zip file and does not require any installation as such. We do however need to follow a few setup instructions to get up and running particularly if you are a developer that works on various versions of E-Business suite and thus will be using different versions of JDeveloper.
First unzip the JDeveloper zip file to a convenient directory. I recommend that you create a separate directory such as C:\JDeveloper\JDEV_R12_RUP4 . This will allow you to create further directories in the event that you download more versions and they will not conflict with each other. Once unzipped you will have three directories:
JDEVBIN: This is the JDeveloper software directory and contains the executables for the IDE.
JDEVDOC: This contains all help documentation including the OAF Developer Guide which will become invaluable to you as an OAF developer
JDEVHOME: This is where by default all of your project files and code will be created, sort of a my documents folder for JDeveloper.
Once installed we will need to create some shortcut links for your desktop so that you can launch JDeveloper and access the Developer Guide. Locate and create shortcuts for the following files for your desktop:
C:\yourInstallDirectory\jdevbin\jdev\bin\jdevW.exe for windows and jdev.exe for any other operating system.
C:\yourInstallDirectory\devdoc\index.htm
The jdevW.exe/jdev.exe links will launch JDeveloper and the index.htm will open the help documentation which contains a link to the OAF Dev Guide.
Before we launch JDeveloper we will complete one more configuration task and re-point the JDEVHOME directory to a central location i.e. My Documents or another partition where you keep all your files. This process is very helpful when it comes to running multiple versions of JDeveloper. Right Click on the “My Computer” icon and select the “Properties” option. Click the “Advanced” tab and then “Environment Variables”. Create a new user variable by clicking on the “New” button and enter JDEV_USER_HOME as the environment variable name and the path to your central directory as the value i.e. d:\my_files\jdev_home.

Fig 4

Click OK and close the “My Computer” windows.
When JDeveloper is launched it will now automatically save your files and settings to the central user home rather than the default home in the installation directory. Always ensure that you back up your central user home to prevent the loss of critical project files.
Now we have our JDeveloper environment ready we need to get some environment specific setup completed so that you can interact with your E-Business Suite instance. In order to enable us access to apps we will need to obtain the DBC file and copy it to our local machine. The best way of doing this is to log into the application server using a tool like putty and obtain the file from $FND_SECURE by ftp using a tool like winscp, failing this you could obtain it from your DBA. Once you have your DBC file create a folder in your JDEVHOME directory called dbc_files\secure\ (Remember the JDEVHOME is the location that we assigned to the JDEV_USER_HOME environment variable in the previous steps) and copy the DBC file to it.

Fig 5

Now that we have our DBC file we can open JDeveloper and configure the database connection. Launch your JDeveloper download by clicking the jdevW.exe link that we created earlier.

If you are using JDeveloper 10g i.e. for an Oracle Applications Version of R12 then you will be prompted to see if you want to migrate from another version, select “No” for this example. You will also see a tip of the day, click the “Close” button.

Fig 6

Click on the Connections tab and right click the “Database” folder and select “New Database Connection”.

Fig 7

If you are using JDeveloper 9i then the Database folder can be found in the “System – Navigator” pane under the connections node, you will not have the Connections Tab.

Fig 8

You will be presented with the Database Connection Wizard. Click “Next”

When prompted enter a name to represent your connection, this can be anything you like however I have just used the database SID for simplicity

Fig 9

Click “Next” and enter the database username and password, this will need to be the apps account and password, leave the role field blank.

Fig 10

Click “Next” and enter the database hostname, port and SID

Fig 11

Click “Next” and click “Test Connection”, you should receive a successful connection message.

Fig 12

OK, now that we have the database connection configured we need to make sure that we can connect to our E-Business suite instance from within JDeveloper. We can use the toolbox tutorial files to do this which get shipped with the JDeveloper build.
Click File > Open and navigate to JDEV_USER_HOME/myprojects and select the file toolbox.jws If you are running JDeveloper 10g you will automatically be in your JDev User Home with 9i you may have to navigate there manually due to a bug in the IDE.
If you are running JDeveloper 9i select the “Show Categories” icon, this will help us identify the test jsp page to run later.

Fig 13

If you are running JDeveloper 10g select the “Applications Navigator” tab to see the opened workspace.

Now that we have the toolbox workspace open we need to modify the project settings in Tutorial.jpr, navigate to the project node under the toolbox node and right click on the “Tutorial” node and select “Project Properties” in 10g and “Project Settings” in 9i.

You will be presented with a properties screen with several options in the left hand pane, expand the “Oracle Applications” node and select the “Run Time” sub menu item. Your screen will look similar to this.

Fig 14

Configure the credentials as above. Select your DBC file that we downloaded earlier, enter YOUR username and password for Oracle Applications and ensure that you have the responsibility “Framework Toolbox Tutorial” assigned to that logon. To check that your logon is configured correctly run the SQL:

select fnd_web_sec.validate_login('','') from dual;

This should return ‘Y’ as the result indicating that your account is setup correctly.

Repeat the above test for the Guest account specified in your DBC file (Usually GUEST/ORACLE).

Enter ICX as the application short name and FWK_TBX_TUTORIAL as the responsibility, these parameters define which responsibility we will be running the project under.

Select OK when you have finished.

We are now ready to run the test project:

In JDeveloper 9i navigate to the file test_fwktutorial under Workspaces > toolbox.jws > Tutorial.jpr > HTML Sources > test_fwktutorial.jsp

In JDeveloper 10g navigate to the file test_fwktutorial under Applications > toolbox > tutorial > Web Content > test_fwktutorial.jsp

Right click the test_fwktutorial.jsp node and select “Run” in 10g and “Run test_fwktutorial.jsp” in 9i

Fig 15

If you are running on a remotely hosted environment prepare for this process to be slow (anywhere from 2 – 10 minutes dependant on your network) you are now effectively running an embedded server from your local machine and a lot of information will be pinging to and from your machine and the application server on the environment, normally these two components would be hosted on the same server and thus the performance will be seriously degraded.

Once complete you should see the below screen.

Fig 16

Congratulations! You have successfully configured JDeveloper.

If you experience problems configuring your JDeveloper version then refer to meta link note 330236.1 for guidelines on raising a service request.

How to Install JDeveloper and Test it? � Oracle Apps Framework

2011-08-26T05:45:00.000-07:00

How to Install JDeveloper and Test it? � Oracle Apps Framework

2011-08-24T03:14:00.000-07:00

PO Position Hierarchy in R12

Buyer Setup

2011-08-23T05:27:00.001-07:00

1) In HRMS ‘People > Enter and maintain’,Create New Employee whose Last name must be same as User name Which we are logged in. Go to Assignment, Enter Org,Position and Job.Save the record.
2) In Sysadmin ‘Security > User > Define’,Query for the user & enter ‘person’ field with employee name created in HRMS.Save the record.
3) In PO ’ Setup >Personal > Buyers ‘, Create new buyer for our user. Now we can create a PO
4) In PO ’ Setup >Approvals > Approval groups ’, Create an approval group.
5) Go to ‘Setup >Approvals > Approval Assignments’ , Select the position ( as given in HRMS) and assign approval group for different document types. Now we can approve Documents

How To Use Oracle E-Business Suite Learn By Example Story

2011-08-19T02:53:00.001-07:00

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers.

The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications. Harry
keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.

Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. In Oracle Apps, bags and kgs are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Harry maintains a book called
ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments. Harry’s customer, Fruit Mart pays him in installments (partial payments). So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Harry receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Harry to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

Harry’s fruit business has begun to improve and has attracted more and more customers. As a result, Harry decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY. Due to increase in customers, Harry needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Harry pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.

Oracle Accounts Payables handles all the payments to the vendors.

Oracle Inventory
deals with the items you maintain in stock, warehouse etc.

Order Management helps you collect all the information that your customers order.

Oracle Receivables help you collect the money for the orders that are delivered to the customers.

Oracle Human Resources helps maintain the Employee information, helps run paychecks etc.

Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.

All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

There is a lot more in Oracle applications. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.

Terminology often used in Oracle Applications:

Invoice
Receipt
Customer
Vendor
Buyer
Supplier
Purchase Order
Requisition
ACH: Account Clearance House
Sales Order
Pack Slip
Pick Slip
Drop Ship
Back Order
ASN: Advance Shipping Notice
ASBN: Advance Shipping Billing Notice
ATP: Available to Promise
Lot/Serial Number
DFF: Descriptive Flex Fields
KFF: Key Flex Fields
Value Sets
Organization
Business Unit
Multi Org
Folders
WHO Columns
Oracle Reports
Oracle Form
Workflow Builder
Toad
SQL Developer
SQL Navigator
Discoverer Reports
XML/BI Publisher
ADI: Application Desktop Integrator
Winscp
Putty

Payment batches in Payables

2011-08-18T03:51:00.001-07:00

Introduction:

We will see how we can use Payments Batches to create payments for multiple invoices that meet the selection criteria you specify.

Process:

The below diagram illustrates the sequence of steps to be followed to complete the payment batch. Each of the steps is initiated from the Payment Batch Actions window.

1. Initiate the payment batch by entering criteria for invoices you want to pay. Payables selects invoices and builds the payments; it determines which invoices will be paid on each payment document and lists this information for you on the Preliminary Payment Register.

At any time before formatting, you can optionally modify the payment batch. You can prevent payment to a supplier, prevent payment of a particular invoice, change the invoice amount, or add an invoice that Payables did not originally select.

Note: System will default current date as a Pay Through Date. This date will identify the invoice due date that system will use to pick up all due invoices within the timeframe. Pay Through Date could be before or after the current date.

Click on Actions and Check Select Invoices and Build Payments as shown in the below screenshot.

2. Format payments to have Payables produce an output file.

Click on Actions and Check Format Payments as shown in the below screenshot.

3. Print checks from the output file, create electronic payments, or deliver the output file to your bank to disburse electronic payments.

4. Confirm the payment batch by recording the document numbers associated with each payment. During this step Payables updates the invoice status to paid and associates a payment number with the invoice and invoice payment.

Click on Actions and Confirm Payment Batch as shown in the below screenshot

Give in the Status and Document Number and Click Confirm.

The To field should have the value derived using the below formula:

To = From + Overall Payment Count – 1 (Example: 3000578 = 3000571 + 8 – 1)

Notes:

You can check the status of a payment batch at any time during the payment batch process by checking the Status field in the Payment Batches window. You can also check the status of payment processes in the Requests window.

You can cancel the Payment batch at any time before you confirm by selecting Cancel CheckBox in Actions Window.

You can restart a payment batch if a concurrent process fails. You can also restart check printing if the printer malfunctions during check printing.

Procure To Pay Cycle

2011-08-18T03:49:00.001-07:00

In this article, we will see the steps involved in Procure to Pay Cycle. Here is the diagrammatic representation:

1) Create Requisition:

Requisition is nothing but a formal request to buy something (like Inventory material, office supplies etc) needed for the enterprise. Only an employee can create one. There are two types of requisitions:

Internal Requisition: Internal requisitions provide the mechanism for requesting and transferring material from one inventory to other inventory.

Purchase requisition: Unlike Internal requisitions, Purchase requisitions are used for requesting material from suppliers.

Navigation:

Purchasing Vision Operations (USA) > Requisitions > Requisitions

Choose the requisition type and enter the Item, quantity, Price details in the Lines tab.

In Source Details tab, specify the Buyer name.

Click the Distributions button. Enter the Charge Account.

Save the work. The status of the requisition will now be “Incomplete”. And now the Approve button is highlighted. The requisition needs to be approved first before proceeding further by the concerned authority. Submit this requisition for Approval by clicking on the Approve button. The status will now be updated to “In Process” .The workflow then will send an Approval notification to the concerned person (derived based on hierarchy used—Position or Supervisor hierarchy) using which he can Approve or Reject the requisition.

At any time the status of requisition can be checked using the Requisition summary window.

Navigation:

Requisitions > Requisition Summary

Enter requisition number and click on the find button.

We can also check the Action History of requisition (it will show details about who has submitted, approved and cancelled the requisitions)

as below:

Navigation:

Tools menu > Action History.

Underlying Tables:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL

2) Create Purchase Order:

There are 4 types of Purchase Orders:

1. Standard PO: A Standard PO is created for one–time purchase of various items

2. Planned PO: A Planned PO is a long–term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

3. Blanket agreement: A Blanket PO is created when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not know the detail of your delivery schedules.

4. Contract agreement: Contract purchase agreements are created with your suppliers to agree

on specific terms and conditions without indicating the goods and services that you will be purchasing

Navigation for creating a standard PO:

Purchase Orders > Purchase Orders

Choose type as Standard Purchase Order. Enter the Supplier, Buyer. In the Lines tab, specify the line number, line type, Item, quantity, price etc.

Click Terms to enter terms, conditions, and control information for purchase orders.

Click Currency button to enter and change currency information for purchase orders, RFQs, and quotations.

Click Shipments button to enter multiple shipments for standard and planned purchase order lines Purchase order shipment specifies the quantity, ship–to organization and location, date you want your supplier to deliver the items on a purchase order line, and country of origin for the items. When you save, Purchasing creates distributions depending on the default information available.

To enter more shipment information, select the More tab.

Enter the Receipt Close Tolerance percent, Invoice Close Tolerance percent to set the receiving and invoice close point.
Select one of the following options for Match Approval Level:

Two–Way: Purchase order and invoice quantities must match within tolerance before the

corresponding invoice can be paid.

Three–Way: Purchase order, receipt, and invoice quantities must match within tolerance

before the corresponding invoice can be paid.

Four–Way: Purchase order, receipt, accepted, and invoice quantities must match within

tolerance before the corresponding invoice can be paid.

Select an Invoice Match Option:

Purchase Order: Payables must match the invoice to the purchase order.

Receipt: Payables must match the invoice to the receipt.

Save the work.

Click the Receiving Controls button to enter receiving control information for purchase orders.

- Enter the maximum acceptable number of Days Early and Days Late for receipts.

- Enter the Action for receipt date control.

- Enter the maximum acceptable over–receipt Tolerance percent (receipts that exceed the

quantity received tolerance).

- Enter the Action for Overreceipt Quantity.

- Select Allow Substitute Receipts to indicate that receivers can receive substitute items in place

of ordered items.

- Enter the default Receipt Routing that you assign goods: Direct Delivery, Inspection Required,

or Standard Receipt.

- Enter the Enforce Ship To location option to determine whether the receiving location must be

the same as the ship–to location.

Save the work.

Click Distributions button to enter distributions for the shipments.

Select more tab to enter more details and the requisition number (optional).

Save the work.

Click on the Approve button to initiate the Approval process.

Underlying Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL (REQ_HEADER_REFERENCE_NUM in Distributions table is the Requisition number for this PO)

3)Create Receipt:

Create a receipt to receive the items in the Purchase Order.

Navigation:

ReceivingàReceipts

Enter the PO Number and select find button.

Go to Lines, check the lines you want to receive in the PO.

Click on Header button and Save which creates the receipt.

Receipt Tables are:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

4)Create Invoice in Payables:

Once the goods are received, it’s time to pay the vendor for the goods purchased and hence the invoices are created.

Navigation:

Payables, Vision Operations (USA) > InvoicesàEntryàInvoices

Enter type — Standard, supplier information and amount.

Click the Match button to match to either Purchase Order or Receipt (depending on the Invoice Match option specified on the PO) and avoid manually entering the invoice.

Enter the PO Number you want match to and click Find.

Select the lines required and click on Match button.

Click on Distribute button
to navigate to the Match to Purchase Order Distributions window.

This creates the invoice and you can see the status of the invoice as “Never Validated”. it has to be Validated and Accounted before you can pay it.

Validating the Invoice:

Click on Actions Button and Select “Validate”. Click on OK button.

Now you can see the status of the invoice as “Validated”, if there are no issues during validation.

Create Accounting Entries:

Click on Actions Button and Select “Create Accouting”. Click on OK button.

Now we can see the Accounted status as “Yes”.

You can see the Accounting Entries here:

Tools àView Accounting

Invoice Tables:

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Accounting Entries Tables:

AP_ACCOUNTING_EVENTS_ALL

AP_AE_HEADERS_ALL

AP_AE_LINES_ALL

5)Making a Payment:

Go to the Invoice window and query the invoice you want to pay. You would see Amount paid as 0.00 before you make a payment.

Click Actions button. Select “Pay in full” and click “OK”.

Select the Bank Account and Document. Save the Work.

Now that the payment is made, when you query for the invoice in Invoice window, you will the Amount Paid as $4,000.00.

Create Accounting entries for payment.

Click Actions and select “Create Accounting”

Select the void checkbox to cancel the payment.

View Accounting Entries:

In the Payments window, query for the payment.

Tools menuàView Accounting

Payment Tables:

AP_INVOICE_PAYMENTS_ALL

AP_PAYMENT_SCHEDULES_ALL

AP_CHECKS_ALL

AP_CHECK_FORMATS

AP_BANK_ACCOUNTS_ALL

AP_BANK_BRANCHES

AP_TERMS

You can also pay the invoices using Payment Batch screen. Refer to the article “Make AP Payments through Payment Batches”

6)Transfer to General Ledger:

Navigation:

Payables Responsibility > View Requests

Run the concurrent program “Payables Transfer to General Ledger” with the required parameters.

Journal Import:

Refer to the Article “Order to Cash Cycle”.

Posting:

Refer to the Article “Order to Cash Cycle”.

Order To Cash Cycle

2011-08-18T03:46:00.005-07:00

Order ToIn this article, we will go through the Order to Cash cycle. The below are the steps in short:

Enter the Sales Order
Book the Sales Order
Launch Pick Release
Ship Confirm
Create Invoice
Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
Transfer to General Ledger
Journal Import
Posting

Let’s get into the details of each step mentioned above.

Enter the Sales Order:

Navigation:

Order Management Super User Operations (USA)>Orders Returns >Sales Orders

Enter the Customer details (Ship to and Bill to address), Order type.

Click on Lines Tab. Enter the Item to be ordered and the quantity required.

Line is scheduled automatically when the Line Item is saved.

Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.

You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.

Save the work.

Underlying Tables affected:

In Oracle, Order information is maintained at the header and line level.

The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.

At this stage, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL is ‘Entered’

Book the Sales Order:

Book the Order by clicking on the Book Order button.

Now that the Order is BOOKED, the status on the header is change accordingly.

Underlying tables affected:

At this stage:

The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be

‘BOOKED’

The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be

‘AWAITING_SHIPPING’.

Record(s) will be created in the table WSH_DELIVERY_DETAILS with

RELEASED_STATUS=’R’ (Ready to Release)

Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.

At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts

into MTL_DEMAND

Launch Pick Release:

Navigation:

Shipping > Release Sales Order > Release Sales Orders.

Key in Based on Rule and Order Number

In the Shipping Tab key in the below:

Auto Create Delivery: Yes

Auto Pick Confirm: Yes

Auto Pack Delivery: Yes

In the Inventory Tab:

Auto Allocate: Yes

Enter the Warehouse

Click on Execute Now Button.

On successful completion, the below message would pop up as shown below.

Pick Release process in turn will kick off several other requests like Pick Slip Report,

Shipping Exception Report and Auto Pack Report

Underlying Tables affected:

If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES.

DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.

The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).

Pick Confirm the Order:

IF Auto Pick Confirm in the above step is set to NO, then the following should be done.

Navigation:

Inventory Super User > Move Order> Transact Move Order

In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.

Ship Confirm the Order:

Navigation:

Order Management Super User>Shipping >Transactions.

Query with the Order Number.

Click On Delivery Tab

Click on Ship Confirm.

The Status in Shipping Transaction screen will now be closed.

This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading

Underlying tables affected:

RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)

FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“

FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“

Create Invoice:

Run workflow background Process.

Navigation:

Order Management >view >Requests

Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with

INTERFACE_LINE_CONTEXT = ’ORDER ENTRY’

INTERFACE_LINE_ATTRIBUTE1= Order_number

INTERFACE_LINE_ATTRIBUTE3= Delivery_id

and spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.

The Invoice created can be seen using the Receivables responsibility

Navigation:

Receivables Super User> Transactions> Transactions

Query with the Order Number as Reference.

Underlying tables:

RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.

RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

Create receipt:

Navigation:

Receivables> Receipts> Receipts

Enter the information.

Click on Apply Button to apply it to the Invoice.

Underlying tables:

AR_CASH_RECEIPTS_ALL

Transfer to General Ledger:

To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.

Navigation:

Receivables> View Requests

Parameters:

Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
Specify the GL Posted Date, defaults to SYSDATE.
Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.

.

Underlying tables:

This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.

Journal Import:

To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.

Navigation:

General Ledger > Journal> Import> Run

Parameters:

Select the appropriate Source.
Enter one of the following Selection Criteria:

No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.

All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.

Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.

If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
Define the Journal Import Run Options (optional)

Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.

Choose Create Summary Journals to have journal import create the following:

• one journal line for all transactions that share the same account, period, and currency and that has a debit balance

• one journal line for all transactions that share the same account, period, and currency and that has a credit balance.
Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
Choose whether to Import Descriptive Flexfields, and whether to import them with validation.

Click on Import button.

Underlying tables:

GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES

Posting:

We have to Post journal batches that we have imported previously to update the account balances in General Ledger.

Navigation:

General Ledger> Journals > Enter

Query for the unposted journals for a specific period as shown below.

From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.

If you know the batch name to be posted you can directly post using the Post window

Navigation:

General Ledger> Journals> Post

Underlying tables:

GL_BALANCES.

Oracle API’s to Create User,Reset Password and Add Responsibility

2011-08-18T02:42:00.000-07:00

I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.

Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
begin
  fnd_user_pkg.createuser (
  x_user_name => v_user_name,
  x_owner => null,
  x_unencrypted_password => v_password,
  x_session_number => v_session_id,
  x_start_date => sysdate,
  x_end_date => null,
  x_email_address => v_email
  );
  commit;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  ROLLBACK;
end;

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
 v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
  );
  if v_status =true then
  dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
  commit;
  else
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
  END if;
end;
Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.


declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
begin
  select
    r.responsibility_key ,
    a.application_short_name
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
  where
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  );
  commit;
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
end;

Oracle Api: Messaging

2011-08-18T02:34:00.001-07:00

In Oracle application messaging is typically handled in different way. If you are working in Oracle application you may notice different types of alert window you will get depending upon the condition. In oracle application this would be typical handled by whole set of bundle API , which is called as FND_MessageAPI.
Then the very next question is what is FND_MESSAGE
In simple word , Messaging functionality controlled by FND_MESSAGE package and in the FNDSQF library.
What this API's do
-Manipulate messages which normally defined in the Message Dictionary under application developer responsibility.
-Client and Server-side procedures place messages on the message stack or in the global area.
-Client-side procedures retrieve messages from the stack and display them from the currently running form
Depending upon the development need , lets understand the various function in three different way.

Server side
client Side

Here are the list of Procedure and Functions which can be used for retrieving and Buffer Messages at Client Side.

Set_Name :Retrieves message from Message Dictionary and sets it on the stack
Set_String rocedure, used to place the passed string on the message stack.
Set_Token :Substitutes token with specified value
Retrieve :Retrieves a message from the server-side buffer, translates and substitutes tokens, and sets message on the stack
Get (function) :Retrieves a message from the stack. Returns the retrieved message
Clear : Clears the message stack
Get_Encoded :This is a function, returns an encoded message from the message stack.
Parse_encoded :This is a procedure used to parse the Application Short Name and the message Name out of an encoded message

For the retrieving and Buffer Messages at Server Side here are some standard procedure:

Set_Name Sets a message name in the global area without retrieving it from the Message Dictionary
Set_Token Adds a token/value pair to the global area without doing the substitution
Clear Clears the message stack
Get_Number :This is function, returns number of the specified message. Function returns 0 if the message has no number, or the message number is zero. If the message can not be found, function returns NULL
Raise_error :This is Procedure that can raises an application error, returning the message information contained in the package variables

In fact for displaying purpose these are the standard procedures and Functions we can use from API, these are mostly used for displaying message at Client Side.

Error isplays an error message in a forms modal window.
Show isplays an informational message in a forms modal window.:
Warn (function) isplays a warning message in a forms modal window. Allows user to accept or cancel the operation. Returns TRUE if user accepts.:
Question :Displays a message and up to three buttons in a forms modal window. Similar to a Forms Alert.
Hint isplays a message in the forms status line.
Erase :Clears the forms status line.
Debug :Used for debugging. Display the specified string.
Example: FND_Message.Debug('here is errorâ€¦');

Apart from this you may also used few other like:

WORKING :This is used to display a message in a dialog box without any buttons. The dialog box must be closed explicitly with WORKING_CLOSE.
WORKING_CLOSE This can used to close the dialog box invoked by the WORKING procedure.
HISTORY :used to display a message in a dialog box, with the History button. If the user presses this button they see all the
messages on the stack

Is there any way to displaying Messages from Concurrent Programs
Yes, there are two procedure which can be used :

Show isplays top message on the stack in the out file. Does not print message number
Error isplays top message on the stack in the log file. Prints the message number if it is not zero

Hope this would be great help in understanding and utilizing the functionality. Next will discuss how to implement these.

OAF – Modifying SQL IN Framework Pages

2011-08-18T02:32:00.001-07:00

OAF – Modifying SQL Behind Framework Pages

Posted on December 9, 2009. Filed under: Oracle Applications Framework | Tags: E-Business Suite, JPXImporter, MDS, OAF, Oracle, Oracle Applications Framework, R11, R12, SQL, Substitution, VO, VO Substitution |

This article will discuss the process of modifying the seeded SQL that exists behind an Oracle Applications Framework Page, by a Framework page we mean any browser based EBS interface screen including selfservice pages. The article will discuss the analysis process involved in making such a change, the implementation of the extension using JDeveloper 10g and the subsequent uploading and “Substitution” of the new code within the MDS (Meta Data System) Repository.

All OAF Pages use a number of ”View Objects” to execute SQL queries against the E-Business Suite Database. These View Objects are actually XML files on the application server that contain an SQL query, this XML file is used in conjunction with a Java Utility called the JPX Importer to load a pointer to the content of the XML file into a number of Database tables that EBS uses to identify which SQL queries it should be executing when a page loads. These tables are collectively known as the MDS (Meta Data System) Repository. The MDS holds a lot more than just SQL query definitions but for the scope of this article we need not cover the other content.
For this article we are going to use the Oracle Projects Search screen as our OAF web page on an R12 instance. We are going to modify the SQL that is used to query back a list of projects so that the SQL only returns projects that are within their current start date and end date as opposed to returning all projects.

Analysis

First of all we need to identify which “View Object” we will be working with, to do this we will need to activate OAF diagnostics. To do so locate the profile option “FND: Diagnostics” and set it to “Yes”. Now take a look at the page we are going to be extending…

Fig 1

By turning on the “FND: Diagnostics” profile option you will now be able to see two new links on the page. The “Diagnostics” link which will appear in the header and the footer of the page and the “About This Page” link which will appear in the footer only. In order to identify the VO that is being used by the page we will first examine the “About This Page Link”.
When you click on the “About This Page” link you will see an array of tabs all of which do various tasks which we can use as OAF developers however for the purposes of this article we will be looking at the default “Page” tab which you see when the page opens.
All OAF pages are made up of a number of BC4J (Business Components for Java) and Web elements known as the MDS content or “Web Beans”. The BC4J content exists as compiled java code and XML under JAVA_TOP on the application server and the web content exists as meta data in the MDS Repository. The web content is organised into a tree structure i.e. you have a page which contains a table which contains fields etc… When the page loads this “Bean Hierarchy” is rendered according to its structure and this structure is what we can see in the “Page Definition” section of the Page tab. Click the “Expand All” Link.
If you are familiar with OAF development this screen will be very apparent to you as representing the page structure layout that you are used to seeing in JDeveloper when building a page and navigating it will be simple. If you are not used to OAF page development and you are simply trying to identify the SQL behind this page then examine the View Object Column until you see populated content, you should then be able to determine that the “Name” column holds content such as “messageStyledText: Project Manager” and that this is describing the “Project Manager” column that we see on the OAF page. We can also see that there is a collection of columns indented below the “Bean” “table: My Projects Result Container” which refers to the result table that we see on the page.

Fig 2

We can see in Fig 2 that the “My Projects Result Container” is driven by the “ProjectListVO” View Object. In order to examine the SQL within the VO and to identify more information from the VO Click on the “ProjectListVO” link.

From the “About View Objects” screen we can see a large SQL statement, The “Entity Objects” associated with the VO, and a list of the VO’s available attributes. Any of these attributes could be used as additional content on the “Project Search” page simply by personalising the page however we will not be covering that process in this article.

At the top of the page we can also see the name of the VO and a file path in the form: oracle.apps.pa.project.server.ProjectListGenVO this gives us the location of the VO on JAVA_TOP. Having identified the VO and its location on JAVA_TOP we can close the “About This Page” diagnostic screen and return to the Projects Search Screen.

Implementation

Now that we have identified the VO we need to extend, oracle.apps.pa.project.server.ProjectListGenVO, we need to setup our JDeveloper environment so that we can create a new View Object that contains our extended code. If you have not already got your JDeveloper environment setup then do so now, you can read how to at the Apps Tech Blog post “OAF – Downloading and Configuring JDeveloper”.

Start JDeveloper and follow the instructions below. Please note that this article only covers the procedure for JDeveloper 10g. The process although similar is slightly different in 9i.

In the Applications Navigator right click the Applications Node and select “New OA Workspace”

In the file name box enter a suitable file name, if you are often working with different clients then it may be an idea to call this XXCLIENT.jws, if you are working for your own company you may want to call it XXCUST.jws, the workspace name is however completely personal preference and up to you as a developer how you organise your workspaces and projects within JDeveloper. Leave the directory path as your default myprojects folder in your JDev Home, ensure that the “Add a New OA Project” check box is selected.

Fig 3

Select “OK”.

The new project wizard will now be launched, select “Next”.

In the project name enter a meaningfull name, I recommend that the project name be pertinent to the piece of work you are doing i.e. XXCUST_projects_list, leave the default directory as your myprojects folder in the JDev Home. For a VO substitution the default package name will need to represent the VO file path on JAVA_TOP with the exception that it must be prefixed with your custom application shortname, the prefix can actually be anything you like however it is recommended that the custom application shortname is used, in this example we would specify the default package as xxcust.oracle.apps.pa.project.server

Fig 4

Click “Next”

Ensure that the “Use repository for design time” box is not selected and click “Next”.

Select your DBC file, enter your applications username and password, enter ICX as the responsibility application shortname and responsibility key. For more information regarding this setup step including obtaining your DBC file and setting up your user account please refer to the Apps Tech Blog post “OAF – Downloading and Configuring JDeveloper”.

Fig 5

Click “Next”

Click “Finish”

Next we need to copy the existing VO component from the apps server and import it into our local file system. In order to do this we must setup a file structure on our local machine in our project folder so we can copy the VO into it. Log onto the applications server and navigate to $JAVA_TOP, change into the oracle/apps/pa/ directory (Or which ever product top you are working with)

Fig 6

Zip the project application directory and save it to a directory that you can ftp from. i.e. zip -r $HOME/pa.zip pa

FTP the pa.zip file in binary mode back to your local machine and extract it to your user JDev Home under myprojects/oracle/apps/ (You will have to create the directory structure manually if it does not exist), you should also extract the contents to your myclasses folder in the same way i.e. myclasses/oracle/apps/

You should now have the following file structure in your local file system under the oracle.apps directory in myprojects and myclasses

Fig 7

Click the “Refresh” button on the Applications Navigator tab of JDeveloper, you should notice that a new business components package under oracle.apps.pa has now appeared.

Fig 8

Now that we have the existing projects business components in our environment we need to create our new VO object that will be used in place of the existing one. Remember we do not customise existing components in OA Framework we extend the existing ones which means creating a new object that extends the original and we notify the framework that we want to use the new object rather than the seeded one.

We now need to test that the VO we want to substitute does not contain any java errors following the download. Navigate to the VO in the applications navigator window i.e. Application Sources > oracle.apps.pa > project > server > ProjectListGenVO, right click the VO and click edit (If you have not already done so you will now need to configure your database connection, for more details on how to do this see the apps tech blog article “OAF – Downloading and Configuring JDeveloper”). If the VO opens without error then we are ready to move onto the next step. If you get an error message saying that java errors exist in either the voNameImpl.java file or the voNameRowImpl.java file then you will need to obtain the original source files for the files listed as being in error and add them to your myprojects folder in the relevant directory and recompile the project, unfortunately Oracle do not allow you to obtain the source files and you may need to employee the services of an Oracle consultant who would have access to the files.

Before closing the base VO window take a look at the “Java” settings and make a note of the boxes that are checked/unchecked, we will need this for later.

If you havn’t already done so Close the Edit VO window.

Right click on the project node and select “New”. Select “View Object” under Business Tier > ADF Business Components and click “OK”

The View Object Wizard has been launched, click “Next”.

Specify the package as xxcust.oracle.apps.pa.project.server (This component package will hold our extended object so we need the custom application prefix). Specify the VO name as the name of the custom application concatenated with the orginal VO name i.e. XxcustProjectListGenVO. Select the original VO in the “extends” box using the browse button and select the original VO i.e. oracle.apps.pa.project.ProjectListGenVO

Fig 9

Click “Next”

On step 2 of the VO wizard you can see the SQL statement that we first saw when we were analysing the page. For this example we are simply going to wrap the SQL as follows and add an additional where clause statement:

SELECT * FROM (ORIGINAL_QUERY) WHERE trunc(sysdate) between project_start_date and nvl(project_end_date,sysdate)

Click the “Test” button to validate that your modified SQL is valid.

Click “Next” until you reach the end of the wizard, If you encounter the following error at step 4 “Each row in the query results column msut be mapped to a unique query attribute in the mapped entity columns” then their is something wrong with the base VO, in this example I did experience this error and found that the original VO SQL had 3 missing aliases that the view object attributes were referencing, In order to correct the error you must make the change to the base VO by editing the SQL, I successfully added the missing aliases and then re-ran the create new VO instructions above and it worked correctly.

Once we have clicked through to the end of the wizard the final step is to create the relevant java files. When a VO is implemented we always generate a voNameImpl.java file but we dont always have a voNameRowImpl.java file, the best way to tell which files to implement is to look at the java screen for the seeded VO and ensure that our new VO has the same settings.

In this case we need both so our final wizard screen would look like this

Fig 10

Now click “Finish” and the new business components package will be created containing our new extended VO.

Fig 11

Right click on the custom business components package and select “Make”, this will compile the Impl and RowImpl java files.

Now that we have our newly extended VO we need to create a substitution file. This substitution file will be an xml based file that will contain a mapping from the old VO to the new VO, we will use this file to tell Oracle Application to use our newly extended VO rather than the old one by uploading it to the MDS repository using the JPX Import tool.

Right click on your project node (XXCUST_projects_list) and select “Project Properties”, click “Substitutions” under the “Business Components” menu. In the “Available” pane select the original VO i.e. oracle.apps.pa.project.server.ProjectListGenVO, and in the right hand “Substitute” pane select the new VO i.e. xxcust.oracle.apps.pa.project.server.XxcustProjectListGenVO. Once you have done this click the “Add” button and select “OK”.

Fig 12

In your JDev home in the myprojects folder you will see a file called projectName.jpx in my case it would be called XXCUST_projects_list.jpx, this is your substitution file and we will use this later when we deploy the substitution.

Deployment

In order to deploy our solution to the actual ebusiness suite instance we need to copy the relevant java and xml files to java top and also upload our substituion file to the MDS repository. You can do this just by simply transfering the files using an FTP tool however I find it is best to create an archive file that will retain the folder hierarchy and allow you to issue a single transfer and unpack command. For a large OAF project we may have files that have to be transfered to different locations and because of this I tend to create 3 types of jar files called BC4J, MDS and SRC. These three files will contain the BC4J files like class files and VO’s, the MDS files such as pages and regions and the java source files.

Following the transfer we can issue a java call from the command line on the apps server to perform the MDS upload.

For this example a VO substitution only involves BC4J objects and java source files, the solution does not require that we package up our source code so for the sake of simplicity I will just create one archive file called BC4J.jar

Right click on the project node and select “New”, select “Jar File” under the General > Deployment Profiles menu. Name the profile as something meaningful, in this example I will stick to convention and call it ”XXCUST_PROJECTS_LIST_BC4J” . Leave the directory set as the default myprojects folder and click “OK”.

In the deployment profile properties deselect the “Include Manifest File” option. On the filters menu deselect the root folder and navigate to the xxcust.oracle.apps.pa.project.server directory, select all the files in this directory and click “OK”

Fig 13

You will see in JDeveloper that you now have a deployment profile listed under you application sources node, right click the .deploy file and select “Deploy to JAR file”, You will see a deploy tab appear next to the compile log and this will confirm that the deployment was successfull. If you experience issues with compilation i.e. there are issues with some of the files in your project that are not connected to your custom files then simply remove them from the project by selecting the top level component package i.e. oracle.app.pa and click the “Exclude Project Content” button (Little file symbol with a red cross on it) and re-try the deployment.

Fig 14

Inspect your myprojects folder and you will see you now have a “Deploy” directory, in this directory will be your jar file ready for deployment to the apps server.

Now that we have both our BC4J objects for java_top and our substituion file for the MDS we need to transfer them both to the apps server, FTP both the files (remember to send the jar file in binary format and the jpx file in ascii) to a convienient directory on the apps server.

Now extract the jar file to java_top i.e.

cp home/kturley/*.jar $JAVA_TOP

jar -xvf /$JAVA_TOP/XXCUST_PROJECTS_LIST_BC4J.jar

Now upload the substitution file to the MDS using the following command:

java oracle.jrad.tools.xml.importer.JPXImporter $HOME/kturley/XXCUST_projects_list.jpx -username apps -password xxxx -dbconnection “(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hostname.oracle.co.uk)(PORT=1551))(CONNECT_DATA=(SID=XXXXX)))”

Make sure you specify the correct values for

1. Path of you jpx file

2. apps password

3. host name

4. port

5. database SID

Finally restart the webserver so that you can see your changes in the application

For R11:
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop
$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start
For R12:
$INST_TOP/admin/scripts/adoacorectl.sh stop
$INST_TOP/admin/scripts/adoacorectl.sh start

Value Set In Oracle Apps For Validation

2011-08-18T02:01:00.000-07:00

Value Sets

Value Set is a collection of values. It validates the values entered by the user for a flex-field, report parameters or a concurrent.

The navigation path: Application :- Validation :- Sets

Value Set is associated with the following validations which holds the key to the value sets.

Types of Validations:

None :- Validation is minimal.

Independent :- The data input is based on previously defined list of values.

Dependent :- The data input is checked against a defined set of values.

Table :- The data input is checked against values in a table.

Special :- The data input is checked against a flex-field.

Pair :- The data input is checked against two flex-field specified range of values.

Translatable independent :- The data input is checked against previously defined list of values.

Translatable dependent :- The data input is checked against defined set of values.

Significance of $FLEX$

$FLEX$: enables to match the prior segment with either value set name or segment name.

Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter then

In the value set definition of v2 = value $FLEX$.v1

Note:

Changes You Should Never Make

You should never make these types of changes (old value set to new value set) because you will corrupt your existing key flex-field combinations data:

. Independent to Dependent

. Dependent to Independent

. None to Dependent

. Dependent to Table

. Table to Dependent

. Translatable Independent to Translatable Dependent

. Translatable Dependent to Translatable Independent

. None to Translatable Dependent

. Translatable Dependent to Table

. Table to Translatable Dependent

Oracle Apps Dyanamic Messages For Developer

2011-08-18T01:57:00.000-07:00

This is one of several “Oracle Applications Basics” articles that are aimed at oracle applications program developers and cover a number of commonly used development elements when interacting with the E-Business Suite.

This article covers the use of application messages and focuses on the use of the pl/sql package FND_MESSAGE as well as the use of the generic loader to download and upload messages between your environments.
During the development of custom programs it is common to use customised messages in our code to implement error messages, confirmation messages, log entries etc…
These messages will be seen and interpreted by your end users and it is important to ensure that these messages can easily be changed in the event that your customer wishes to do so. The reason for changing them maybe simple such as a spelling mistake or more complex like assigning a generic error code to all error messages used by the system to aid in support activities.
The Oracle E-Business suite provides a message library that allows us to implement our messages and at the same time provides the facility to easily change these messages via the E-Business suite standard user interface.
This article will cover the creation of messages via the E-Business suite user interface and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message
To create a message in the E-Business suite message library you will need the “Application Developer” responsibility. Once logged in navigate to Application Developer > Application > Messages. This will launch a form and it will look like figure 1:

: Figure 1

Enter a unique name for your message, for custom messages this is often in the form XX_NN_MM where
XX is the custom schema prefix you are using (Usually just XX)
NN is the module code or short name for the extension that the message belongs
MM the message description
I would advise that you always use a unique NN portion to the message name to make it easy for you to identify your messages i.e. you can query back all messages for XX_MYMOD%, this will help you later when you are compiling your messages for your installation scripts.
Select the language that your message is written in and the application that the message belongs, this will usually be the custom application setup by your development team lead. If you don’t yet have a custom application and you are developing custom application modules then you will need one.
Enter the message text in the “Current Message Text” box.
Click the save icon.
For message naming conventions you should always consult your build standards documentation for the customer you are implementing for to ensure you have observed any standard development approach that may have been implemented at your site.
Example:

: Figure 2

Retrieving a message using PL/SQL
Once we have saved the message in E-Business suite via the standard message form we need to interact with the message library via our custom code to enable us to implement the message in the way it was intended to be used.
In order to retrieve the message from the database we need to use a few different standard foundation API’s in the FND_MESSAGE package. An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Not required but good practice)
This process will look like this in your PL/SQL
For more details regarding fnd_global.apps_initialise see my other blog entry “Oracle Apps Basics – Session Context”

set serveroutput on

DECLARE

 my_message VARCHAR2(100);

BEGIN

 --Initialise Apps Session

 fnd_global.apps_initialize( user_id      => 1290

                          ,  resp_id      => 50257

                          ,  resp_appl_id => 10003

);

 --Clear the existing session

 FND_MESSAGE.CLEAR;

 --Tell ebusiness suite which message you want (custom application short name/message name)

 FND_MESSAGE.SET_NAME('XX','XX_MYMOD_MESSAGE1');

 --Retrieve the message

 my_message := FND_MESSAGE.GET;

 --Output the message

 DBMS_OUTPUT.PUT_LINE(my_message);

END;

anonymous block completed

Test Message 1

Using Tokens
The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time. This is useful for adding things like timestamps and user names to the existing message.
We will expand the example used in the previous section to demonstrate the use of tokens in our messages.
Open the E-Business Suite message creation form and query back your previous message.
In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. modify your message text from “Test Message 1″ to “Test Message Retrieved at &TIMESTAMP by user &USERNAME”
Tokens can be called what ever you want but they must be prefixed with an ampersand and they must be in uppercase.
To demonstrate the use of our new tokens we will add an additional 2 API calls to our previous code which will set the tokens to the desired values and then resolve the message using the substituted values, our code will now look like this:


   
set serveroutput on
 
DECLARE
 my_message VARCHAR2(100);
BEGIN
 --Initialise Apps Session
 fnd_global.apps_initialize( user_id      => 1290
                          ,  resp_id      => 50257
                          ,  resp_appl_id => 10003
                           );
 
 --Clear the existing session
 FND_MESSAGE.CLEAR;
 
 --Tell ebusiness suite which message you want (custom application short name/message name)
 FND_MESSAGE.SET_NAME('XXC','XX_MYMOD_MESSAGE1');
 
 --Set the timestamp message token with the current database timestamp 
 FND_MESSAGE.SET_TOKEN('TIMESTAMP',to_char(SYSDATE,'DD-MM-YYYY HH:MI:SS'));
 
 --Set the username message token with the current applications user
 FND_MESSAGE.SET_TOKEN('USERNAME',FND_GLOBAL.USER_NAME);
 
 --Retrieve the message
 my_message := FND_MESSAGE.GET;
 
 --Output the message
 DBMS_OUTPUT.PUT_LINE(my_message);
END;
 
anonymous block completed
Test Message Retrieved at 02-04-2009 03:16:59 by user TURLEYK

Downloading and Uploading Messages using the Generic Loader
The generic loader can be used to download/upload existing E-Business Suite messages.
To download our example message we would use the following command at the Unix prompt on the mid-tier:


   FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct MSG_XX_MYMOD_MESSAGE1.ldt 
FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XX' MESSAGE_NAME="XX_MYMOD_MESSAGE1"

To Upload our example message we would use the following command at the Unix prompt on the mid-tier:

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct MSG_XX_MYMOD_MESSAGE1.ldt

How to use Oracle Message

2011-08-18T01:54:00.001-07:00

This document discuss about message dictionary in oracle applications. It talks about the standards to be followed while defining the messages in oracle applications. It also discusses the usage of messages in the forms and concurrent programs

Message Dictionary lets you catalog messages for display from your application without hard coding them into your forms and programs.

Using Message Dictionary, you can:

Define standard messages you can use in all your applications
Provide a consistent look and feel for messages within and across all your applications
Define flexible messages that can include context–sensitive variable text
Change or translate the text of your messages without regenerating or recompiling your application code.

Define Messages

Following things should be complete before defining the messages.

1. Register your application. Standard applications come registered with oracle applications and hence you can skip this step if you are defining messages for any standard application.

2. Create a mesg directory under your application’s base directory (e.g $AP_TOP if application is oracle payables) where oracle application object library can store your message files.

Navigate to below mentioned path to define your messages.

Navigation – Application Developer->Application->Messages

Figure 1. Define Messages Navigation

Click on messages and following screen will appear.

Following fields are required while defining messages.

Name – Any meaningful name made of letters, numbers, hypens and underscores upto 30 characters can be used. This is used while invoking message dictionary through forms or concurrent programs.

Language-Language code for the language in which you want to get your message displayed. The common value is US.

Application – Name of the application for which you want to define the message.

Current Message Text – The text of the message which you to be displayed when your message is used in forms or concurrent programs.

Following fields are optional while defining messages.

Number - Enter any appropriate number for your message. If you define a non-zero message number for your message, Message Dictionary automatically prepends your message with the prefix APP:- (or its translated equivalent). Message Dictionary treats 0 and null as the same (and does not display the APP:- or the message number).

Figure 2.1 Define Messages

Type- Use the message type to classify your messages. The message type does not affect how your messages can be called in your code.

In Oracle Applications, the message type is used to help translators translate messages within length expansion constraints that vary by message type. For certain message types, this form enforces message byte length limits smaller than 1800. The message length limits (in bytes) are 60 for Menu Entry, 250 for Hint, and 80 for Title (note that these limits are for translated messages, so messages in English should be 30% shorter if they will be translated).

Maximum Length - In Oracle Applications, the maximum length (in bytes) is used to help translators translate messages within length expansion constraints imposed by the form or program that uses the message. Specifying a maximum length is particularly important where the expansion constraint is tighter than would otherwise be expected for a particular type of message. If your message is in English, the maximum length you specify should be at least 30% longer than your actual English message to allow for expansion during translation.

Figure 2.2 Define Messages

Description - You should enter information in this field that would help explain the context of this message to translators. This field is required if the message is of type Token or Other.

After defining your message you need to run “Generate Messages” program to build your message file. Your new messages take effect as soon as your concurrent request finishes successfully and you have placed the new file in the appropriate directories.

Navigation – Application Developer->Other->Requests->Run

Figure 3 Running Generate messages Navigation

Figure 4 Running Generate messages parameters

Required Parameters to Generate Messages Program

Language – Enter the language in which you want the messages to be generated. The normal value used is US.

Application – Enter the application name for which you have defined the message.

Mode – Following four values are possible for this parameter

a. DB_TO_RUNTIME – This will create a msg file under the application top directory.
b. DB_TO_SCRIPT – This will create a script(.msg file) that can be used to load messages to other database. This would be normally used in case of upgradation only.
c. SCRIPT_TO_DB This will take a script(.msg file) and load the messages in it to the database. This would be normally used in case of upgradation only.
d. SCRIPT_TO_RUNTIME – This will take script(.msg file) and load the messages in to the database and also create the msg file under application top.
Optional Parameters of Generate Messages Program
File Application Shortname (or Local) – Here we should enter the application short name of the application for which we want to generate the msg file. Generally this field is left blank and system automatically generates the file name.
Filename – Here we should enter the name of msg file to be generated. Generally this field is left blank and system automatically generates the file name.

Application Message Standards

Oracle Applications use messages to communicate with users. Typical messages include warnings and error messages, brief instructions, and informative messages that advise your user about the progress of concurrent requests, work done, and anything else of interest or helpful to users.

Forms–based applications display messages on the user’s screen; applications also print messages to output and log files. Messages are part of the product, and should be treated with the same amount of care and attention to the user interface as a form.

These message standards help you write messages that are brief, clear, and informative. When you follow these standards and use Message Dictionary, your application provides messages that are easy to understand, document, edit, and translate into other languages, resulting in an application that is easy to support and enhance.

Message Naming Standards

The following suggested standards provide a consistent naming convention for message names.

Message names are hard coded into forms and programs and should never change.

Use descriptive words and make your message names meaningful to other developers. Your name should clearly summarize the content or purpose of the message. • Message names can contain no more than 30 characters. • Make message names all uppercase. Although message names are case insensitive, this provides consistency so that messages can be searched for easily.

Do not use spaces in message names; use underscore characters instead to separate words.

Message names should not contain message numbers (or prefixes), because message numbers may change over time.

Include your two–to–three–character application short name or abbreviation as the first word in each message name (optional).

Message Numbering Standards

The following suggested standards provide a consistent convention for message numbers

Use a number for all errors and warnings, but not hints, notes or questions.
Do not put the message prefix or a message number in your message text.
All errors and warnings now have message numbers to help developers trace a message if the problem comes from an installation running in a language other than English.
Oracle recommends that you number messages using 400000 (400,000) and up (the largest possible message number is about 2 billion).

Message Description Standards

The following suggested standards provide a consistent convention for message descriptions.

Description should include where and how the message is used (button label, form message, boilerplate, etc.). If it is a text fragment used to build another message (as a token value), include the text defined for the target message and the target message name so the translator can see the expected context of the fragment.
Include what text is expected to replace the token.If your message contains a nonobvious variable token name, use your notes field to tell translators what your token represents.

Message Content Standards

Messages should never exceed about 1260 characters in English. This allows space for the messages to be translated to ”longer” languages such as German.

Message Token Standards

Always make tokens in your message uppercase so they are easy to identify in a messages manual or in a message file being translated.
Avoid using tokens to substitute for words or phrases in a sentence.
These are nearly impossible to translate, because different token values
may require different translations for the surrounding text.
Avoid hard coding text into your code
Use descriptive variable token name

Calling messages

Following standard procedure can be called to show predefined oracle applications message through forms or concurrent programs.

FND_MESSAGE.CLEAR – Clears the message stack of all the messages.
FND_MESSAGE.DEBUG – Immediately displays the string passed to it as input parameter.
FND_MESSAGE.ERASE – Clears the oracle Forms status line.
FND_MESSAGE.ERROR - Displays an error message in an Oracle Forms modal window or a concurrent program log file. (Example: “Invalid value entered.”).
It takes its message from the stack, displays the message, and then clears all the messages from the message stack.
FND_MESSAGE.GET - Retrieves a translated and token–substituted message from the message stack and then clears that message from the message stack. This could be used for getting a translated message for a forms built–in or other function. Assumes you have already called FND_MESSAGE.SET_NAME and, if necessary, FND_MESSAGE.SET_TOKEN. It returns up to 2000 bytes of message.
FND_MESSAGE.HINT - Displays a message in the Oracle Forms status line. It takes its message from the stack, displays the message, and then clears that message from the message stack. The user may still need to acknowledge the message if another message immediately comes onto the message line.
FND_MESSAGE.QUESTION - Displays a message and up to three buttons in an Oracle Forms modal window. (Example: “Please choose one of the following actions: ”) It takes its message from the stack, and clears that message. After the user selects a button, It returns the number of the button selected.
For each button, you must define or use an existing message in Message Dictionary (under the Oracle Application Object Library application) that contains the text of the button. This routine looks for your button name message in the Oracle Application Object Library messages, so when you define your message, you must associate it with Oracle Application Object Library (the “FND” application) instead of your application.
FND_MESSAGE.RETRIEVE - Retrieves a message from the database server, translates and substitutes tokens, and sets the message on the message stack.
FND_MESSAGE.SET_NAME - Retrieves your message from Message Dictionary and sets it on the message stack. You call it once for each message you use in your client–side PL/SQL procedure. You must call this procedure before you call FND_MESSAGE.SET_TOKEN.
FND_MESSAGE.SET_STRING - Takes an input string and sets it directly on the message stack. The string does not need to be defined in the Messages window. These strings may be hard coded into the form and are not translated like messages defined in Message Dictionary.
FND_MESSAGE.SET_TOKEN - Substitutes a message token with a value you specify. You call FND_MESSAGE.SET_TOKEN once for each token/value pair in a message. The optional translate parameter can be set to TRUE to indicate that the value should be translated before substitution. (The value should be translated if it is, itself, a Message Dictionary message name.)
FND_MESSAGE.SHOW - Displays an informational message in an Oracle Forms modal window or a concurrent program log file. (Example: “To complete this function, please enter the following... ”). It takes its message from the stack, displays the message, and then clears only that message from the message stack.
FND_MESSAGE.WARN - Displays a warning message in an Oracle Forms modal window and allows the user to either accept or cancel the current operation. (Example: “Do you wish to proceed with the current operation?”) FND_MESSAGE.WARN returns TRUE if the user accepts the message (that is, clicks OK), or FALSE if the user cancels. It takes its message from the stack, displays the message, and clears that message from the message stack.

Most frequently used procedures are FND_MESSAGE.SET_NAME and FND_MESSAGE.SET_TOKEN

Example

/*Here FND_MESSAGE.SET_NAME is used to retrieve the message and put it on the message stack. Message name is ‘WANT TO CONTINUE’.*/

FND_MESSAGE.SET_NAME (’FND’, ‘WANT TO CONTINUE);

/*Next FND_MESSAGE.SET_TOKEN is used to replace the token ‘PROCEDURE’ with text ‘Compiling this flexfield’.

FND_MESSAGE.SET_TOKEN (’PROCEDURE’, ’Compiling this

flexfield’);

IF FND_MESSAGE.WARN THEN
/* User want to continue */

ELSE
/* User want to cancel*/

END;

Frequently asked Questions

1. Which program is required to be executed after defining messages in the oracle applications?
2. What is token in the message?
3. Which procedure should we use to display an information message through a form?
4. Which procedure can be used to display the button along with messages?
5. What are the two prerequisites before defining messages for any application?
6. Why do we have oracle applications message standards?
7. What is the significance of DB_TO_RUNTIME value of the mode parameter while running “Generate Messages” program?
8. Why do we use FND_MESSAGE.SET_NAME procedure?
9. Which application should be used while defining message for Buttons?
10. Can we use messages in the report triggers?

Comparison between XML Publisher and Oracle Reports

2011-08-17T07:14:00.001-07:00

In Oracle Reports, a single executable RDF file contains the data source, layout, business logic, and the language-specific prompts. However, BI Publisher separates these components into different layers. Following are some of the key limitations of Oracle Reports that are overcome by BI Publisher:

· Multiple layouts If you wish to publish the data from a report in more than one format—for example, in both an Excel and HTML layout—then multiple RDF files have to be prepared. If any bug fixes are required, fixes will have to be applied to both RDF files.

· Multiple language Global implementations for a large organization usually require the same report to be developed in multiple languages. In case of Oracle Reports, developers will have to create different copies of the same RDF files and place them in the /reports/ directory. In case of any bug fixes to data or any changes to layout, all the copies of reports have to be amended.

· Trivial tasks Even trivial tasks like changing the prompt or minor changes to the layout require a developer in Oracle Reports. Using BI Publisher, these tasks can be implemented by trained support staff or business analysts.

· Distribution The distribution capabilities in the Oracle Reports tool within E-Business Suite are very restrictive. To distribute the output of Oracle Reports, many companies implementing E-Business Suite have to purchase third-party products or even build their own in-house reports distribution framework. With BI Publisher, a mechanism named bursting can automate distribution of the reports using industry standard delivery protocols.

· Securing the output When distributing the output of Oracle Reports in E-Business Suite, it is not possible to secure the output using a password. BI Publisher facilitates password protection of the documents.

Due to the previously listed limitations, the cost of customization and maintenance is very high for Oracle Reports in Multilanguage implementations. In the case of BI Publisher, the data layer, presentation layer, and the translation layer are separate. Additionally, BI Publisher is based on open standards and thus overcomes many of the limitations of Oracle Reports. In addressing some of the limitations of Oracle Reports and adopting open standards, Oracle has allowed implementations to potentially reduce the overall cost of build, customization, and ongoing maintenance of reports.

2011-08-17T04:21:00.001-07:00

1. What are the various types of reports ?
* Tabular, Master Detail, Form, Form Letter, Mailing Labels, Matrix

2. What is the difference between Master - Detail Report and report created by breaks ?
* Master/detail data models are very similar to break report data models. However, a master/detail data model is created using two queries, each of which owns at least one group, and a data link. A break report data model is created using one query and at least two groups. While reports based on a single query are usually more efficient than reports based on multiple queries, sometimes the structure of your data tables may require you to link multiple tables.

3. What are Anchors ?
* An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

4. What are the various types of anchors in Reports ?
A There are two types of anchors in Oracle Reports:
* implicit (anchors that Oracle Reports creates when a report is run)
* explicit (anchors you create)

Implicit Anchors : At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.

Explicit Anchors : Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog

9. What are the various report triggers ? What is their order of firing ?
A There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order :
* Before Parameter Form
* After Parameter Form
* Before Report
* Between Pages
* After Report

Apart from the above Five Report Triggers, there are three other types of triggers :
* Validation Triggers
* Format Triggers
* Action Triggers

Before Form : Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).

After Form : Fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).

Before Report : Fires before the report is executed but after queries are parsed and data is fetched.

Between Pages : Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)

After Report : Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.

Validation Triggers : Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.

Format Triggers : Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

Action Triggers : Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

19. What are Placeholder Columns ?
A A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).

20. What are the various Module Types in Reports ?
A You can build three types of modules with Oracle Reports:
* external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules
* external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules
* reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules

22. What are Physical and Logical pages in Reports ?
A A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.

Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.

Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.

23. What are the various page layout sections in Oracle Reports ?
A A report has three sections : the report header pages, report body/margin pages, and report trailer pages.

27. What are various types of parameters ?
A There are two types of parameters:
* default (called system parameters)
* user-created (called bind and lexical parameters)

30. How do you reference parameters and columns in reports ?
A In two ways :
* As bind references
* As lexical references

31. What are Bind Referencing and Lexical Referencing ?
* Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.

Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

33. Can you create a group without any break columns ?
* No.

35. Types of Matrix report ?
* 1. Single query 2. Multi query 3. Nested Query 4. Matrix Break

37. In Reports, how can you print one record per page in the output ?
* Set the Maximum records per page property of the Repeating frame to 1.

38. How do you print a Report 2.5 report in character mode ?
* Set MODE = 'Character' in the Parameter form

39. What are widow lines ?
* Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.

40. What are widow records ?
* Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.

41. What is 'page protect' property for objects ?
* Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.

42. What is the 'Print Condition Type' property ?
* 'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.

44. What are the various values of the 'Print Condition Type' property in Reports ?
* The various values are :
All : All means the object and all of its contents will be printed on all logical pages of the Print Condition Object. The object will be repeated on any overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.

All but First : All but First means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the first logical page. The object will be formatted only on overflow pages of the Print Condition Object and will be truncated at the logical page boundary, if necessary.

All but Last : All but Last means the object and all of its contents will be printed on all logical pages of the Print Condition Object except the last logical page. The object will be repeated on any overflow pages of the Print Condition Object except the last one and will be truncated at the logical page boundary, if necessary.

*Default : Default means that Oracle Reports will use object positioning to set the Print Condition Type to either *First or *Last for you. (The asterisk indicates that Oracle Reports specified the setting for you.)

First : First means that the object and all of its contents will only be printed on the first logical page of the Print Condition Object. The object will be formatted and will overflow to subsequent pages, if necessary.

Last : Last means that the object and all of its contents will only be printed on the last logical page of the Print Condition Object. The object will be formatted after the Print Condition Object and will overflow to subsequent pages, if necessary.

* For repeating frames, the print condition type property refers to every logical page of every instance (record) of the repeating frame. If you specify a Print Condition Type setting of All and a Print Condition Object setting of Enclosing Object for a field F_1, it prints in every instance of repeating frame R_1 on every logical page. If you specify a Print Condition Type setting of All but First in the above case , then the field will be printed only in those cases where a single instance (row or record) of the repeating frame spans across more than one page (which is normally not the case since many records are there in a single logical page and not one record in many pages). In such a case the field will be printed on all subsequent pages of this instance except the first page.

43. What is the 'Print Condition Object' property ?
* 'Print Condition Object' property specifies the object on which to base the Print Condition Type of the current object. For example, if you specify a Print Condition Type of All and a Print Condition Object of Anchoring Object, the current object will be triggered to print on every logical page on which its anchoring object (parent object) appears.

45. What are the various values of the 'Print Condition Object' property in Reports ?
* The various values are :
Anchoring Object : Anchoring Object is the parent object to which the current object is implicitly or explicitly anchored.
Enclosing Object : Enclosing Object is the object that encloses the current object.

46. What is the horizontal of vertical sizing property of objects ?
* Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.

47. What are the various values of the horizontal of vertical sizing property ?
* The various values are :
Contract : Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")

Expand : Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")

Fixed : Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.

Variable : Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.

51. How do you display a message in reports ?
* SRW.Message

54. What are the various values of 'Print Panel Order' property of report ?
* The various values are :
Across/Down : Across/Down means the physical pages of the report body will print left-to-right then top-to-bottom.

Down/Across : Down/Across means the physical pages of the report body will print top-to-bottom and then left-to-right.

55. What is the 'Print Direction' Property of Repeating frames ?
* 'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.

56. What are the various values of the 'Print Direction' Property of Repeating frames ?
* The various values are :
Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.

Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.

Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.

Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.

59. What is the 'Keep with Anchoring Object' object property ?
* 'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.

60. What is 'Page Break Before' object property ?
* 'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.

61. What is 'Page Break After' object property ?
* 'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.

63. What is the 'Break Order' property of columns ?
* The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.

64. What are the various types of links ?
* The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).

65. Name some of the procedures in the SRW package ?
* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report

66. What are the various report layout regions ?
* There are three report regions in the Layout editor :
* header
* body/margin
* trailer

Header : The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.

Body/Margin : The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations.
A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.

Trailer : The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.

70. What is more efficient : Maximum rows or Group Filter ?
* Maximum Rows in the Query property sheet restricts the number of records fetched by the query. A group filter determines which records to include and which records to exclude. Since Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you are using a Filter of Last or Conditional, Oracle Reports must retrieve all of the records in the group before applying the filter criteria. As a result, Maximum Rows or a Filter of First is faster.

Oracle Apps AR Overview

2011-08-17T02:21:00.000-07:00

Enter Invoices

Use this form to enter invoices and debit memos one at a time, or as a group called a batch. Oracle Receivables lets you use batch control totals to ensure that you enter invoices accurately. When
you enter an invoice, Oracle Receivables uses your AutoAccounting rules to determine your default general ledger accounts. Oracle Receivables now lets you assign invoicing and accounting rules to invoices you enter through this form. Use Revenue Recognition Program to control when your accounting distributions are created.You can also use this form to review and update information for your invoice. After your invoice has posted to your general ledger you can still update most information. Oracle Receivables maintains a complete audit trail of all the posted changes you make to your accounting entries. Oracle Receivables does not maintain an audit trail when you change an invoice that has not been posted.

Batch (RA_BATCHES)
- Source, Name, Batch Date, GL Date, Period,
- Status (New,Open,Closed, Out of Balance), Currency, Comments
Invoice (RA_CUSTOMER_TRX)
- Number, Invoice Date, Complete,
- Ship To Customer - Name, Number, Address, Contact,
- Bill To Customer - Name, Number, Address, Contact,
- Commitment (Gaurantee,Deposit), Agreement, Invoice Source,
- Invoice Type, Document No, Transaction Cross Reference To,
- Payment Terms, Due Date, GL Date, Salesperson, Remit To, Currency
- Payment Method (Check,Wire,Direct Deposit),
- Customer Bank - Name, Branch, Account
- Invoice - Default Tax (Standard,Exempt,Require), Print Option, Status,
PO Number, PO Revision, PO Date, Invoicing Rule (Advance,Arrears),
- Frieght - Amount, Carrier, Ship Date, Waybill No, FOB Point, Account,
- Sales - Sold To, Sold To No, Territory,
- Receivable Account, Special Instructions, Comments

Default Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Rep Name, Number, Revenue %, Other %

Invoice Lines (RA_CUSTOMER_TRX_LINES)
- Line Number, Item Code, Description, Line Trans Flexfield (AutoInv)
- Quantity, UOM, Tax(Std,Exempt,Req), Unit Price, Extended Price
- Addl Line Info - Order Number, Date, Revision, Line, Sales Order Source,
Invoicing Rule, Accounting Rule
- Tax - Line Number, Tax Code, Tax Rate, Tax Amount,

Revenue Account Assignment (Lines) (RA_CUST_TRX_LINE_GL_DIST)
- Percent, Amount, Account Code, GL Date
- Account Assignment (Tax) - Percent, Amount, Account Code, GL Date

Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Rep Name, Number, Revenue %, Revenue Amount

Invoice Installments (AR_PAYMENT_SCHEDULES)
- Line, Due Days, Due Date, Original Amount , Balance Amount, Status

Maintain Invoices

Use this form to update your invoices, debit memos, and chargebacks. You can change the due date, PO number, salesperson, and remit-to address of a debit item. You can also place a debit item in dispute by specifying a dispute amount, exclude a debit item from finance charges, or update the bill-to information. Oracle Receivables also lets you enter or update the exchange rate of your debit item as long as your debit item does not have any activity against it.You can also use this form to record additional information in the form of notes about your debit items. The profile option for this form that controls whether you can change invoice due dates is Invoice Due Date. If your system administrator sets this option to No, you cannot update an invoice due date in the Installment zone.

Enter Recurring Invoices

Use this form to create automatically recurring invoices for your customers. For example, you need to bill your customers for services or products provided regularly, but you don't want to create a new invoice every time. Oracle Receivables provides the recurring invoices with the same characteristics as the original invoice, except that all dates such as the invoice date, GL date,and due dates are determined using the recurring rule you specify. You can schedule recurring invoices as often as you like and create recurring invoices from closed invoices.When you create recurring invoices Oracle Receivables does not derive the exchange rates and tax rates from the recurred invoice date. Instead, it copies the exchange rate and tax rate from your model invoice. Consequently, if you are creating recurring invoices in a foreign currency, or have tax rates which change over time, you may need to update the exchange and tax rate manually.You can use the Invoice Entry form to update the rates to the
correct rates for your recurred invoice date.
Model
- Source Batch, Source Invoice Number, Customer, GL Date, Invoice Type,
- Invoice Date, Terms, Due Date
Recurring Schedule
- Rule (Monthly,Daily,Yearly ...), First Invoice Date, First GL Date,
- Number of Days, Number of Times
Recurring Invoices
- Invoice Number, Invoice Date, GL Date, Due Date, Amount

Enter Commitments

Use this form to enter or change your customer commitments. You can define commitments for any customer, to record deposits or guarantees of future purchases. Oracle Receivables also lets you
update certain information depending on the commitment status.
Commitment (RA_CUSTOMER_TRX)
- Batch Source, Commitment Number, Date, Bill To, Address, Contact,
- Remit To, Commitment Type(Deposit, Guarantee), Document No, GL Date,
- Agreement, Cross Reference To Transaction, Terms, Primary Salesperson,
- Print, Start Date, End Date, Invoice Text, Currency, Amount, Item,
- Debit Account Code, Credit Account Code
- Commitment Addl Info - Payment Method, Customer Bank Name,
Customer Bank Branch, Customer Bank Account, Special Instructions,
Purchase Order, Purchase Order Revision, Purchase Order Date,
Sales Territory, Line Transaction Flexfield (Unique Line # for Auto Invoice)

Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Person, Number, Revenue Credit %, Revenue Credit Amount

Quick Transaction Entry

Use this form to enter simple one-line invoices, debit memos and on-account credits. If you want to review, update, or delete invoices and debit memos use the Enter Invoices form. If you want to review, update, or delete on-account credits use the Enter Credit memo form.
Batch (RA_BATCHES)
- Source, Name, Batch Date, GL Date, Period,
- Status (New,Open,Closed, Out of Balance), Currency, Comments
Invoices (RA_CUSTOMER_TRX)
- Invoice Number, Invoice Date, Customer Number, Source Batch, Invoice Type,
- Currency, Item Code, Description, Quantity, UOM, Unit Price, Extended Amount,
- Ship To Location, Bill To Location, Terms, Freight Amount, Tax Code, Tax Rate,
- Tax Amount, Sales Person, GL Date

Enter Credit Memos

Use this form to enter, update, and review on-account credits or credit memos against specific invoices, debit memos, or commitments. Oracle Receivables creates the appropriate accounting entries and reverses any sales credits you assigned to your salespeople. Oracle Receivables allows you to apply on-account credits to your customers open debit items in the Enter Receipts and Reapply Credits forms. To speed data entry, you can define credit memo lines in the Define Memo Lines form. These credit memo lines display as QuickPick choices in this form. You can also enter credit memo lines directly in the Credit Memo Lines zone of this form. You print your credit memos in the Print Invoices form.
You can specify the debit item to credit in this form, or create an on-account credit by not specifying one. On-account credits are credits that you assign to your customer's account that are not related to a specific invoice. You can assign on-account credits to debit items in the Reapply Credit form or when you enter receipts.
Batch (RA_BATCHES)
- Source, Name, Batch Date, GL Date, Period,
- Status (New,Open,Closed, Out of Balance), Currency, Comments

Invoices (Query) (RA_CUSTOMER_TRX)
- Invoice Number, Invoice Date, Customer Number, Customer Name,
- Source Batch, Commitment Number, Currency

Credit Memo (RA_CUSTOMER_TRX)
- Source, Number, Complete, Credit Memo Date, Credit Memo Type,
- Transaction, Reason, Document No, GL Date, Currency,
- Line/Tax: Amount, Percent, Freight,
- Credit Method For: Invoices With Rules, Split Term Invoices,
- Salesperson, Customer Reference, Print Option, Print Date, Comments,
- Ship To Customer, Number, Address, Contact,
- Bill To Customer, Number, Address, Contact,
- Payment Method, Customer Bank Name, Branch, Account,
- Receivable Account Code, Freight Account Code

Credit Memo Lines (RA_CUSTOMER_TRX_LINES)
- Line Number, Item Code, Description, Amount, Quantity, Unit Price,
- Reference, Credit Reason, Line Transaction Flexfield

Revenue Account Assignment (Lines) (RA_CUST_TRX_LINE_GL_DIST)
- Percent, Amount, Account Code, GL Date

Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Sales Rep Name, Number, Revenue %, Revenue Amount

Reapply Credits

Use this form to apply, reapply, or unapply on-account credits.Oracle Receivables lets you create on-account credits in the Enter Credit Memos and Quick Transactions forms and then apply them here.
Correcting your mistake is easy since you can reapply or unapply applications before or after posting them to your general ledger. Oracle Receivables gives you the information you need to update any unresolved on-account credits you may have.You can only apply items and receipts to commitments or transactions against a commitment which bring its balance closer to zero.

Enter Invoice Adjustments

Use this form to make either positive or negative adjustments to your invoices, debit memos, chargebacks, on-account credits, deposits, and guarantees. Oracle Receivables currently does not permit adjustments to credit memos. Creating adjustments in Oracle Receivables is easy since you can use predefined adjustment activities. Oracle Receivables automatically provides the accounting information for you. Define your adjustment activities in the Define Receivables Activity form.Oracle Receivables automatically verifies that your adjustment is within your adjustment approval limits. Define adjustment approval limits for your users in the Define Adjustment Limits form. If the amount of the adjustment that you are entering is within your assigned adjustment approval limits, then Oracle Receivables automatically creates the adjustment. If the amount of the adjustment that you are entering is outside your adjustment approval limit range, then Oracle Receivables creates a pending adjustment with a status of Waiting Approval in the Approve Adjustments form. Pending adjustments require approval from a user with the appropriate approval limit before they affect the remaining balance of the transaction. If you need to gather more information about your item, you can place your adjustment on hold by assigning it a status of More Research.

Invoice (Query Invoice To Be Adjusted)
- Invoice Number, Customer Number, Customer Name

Adjustments (AR_ADJUSTMENTS)
- Activity Name (OverCharge, WriteOff),
- Adjustment Type (Charge,Invoice,Line,Freight,Tax), Amount,
- GL Date, Adjustment Date, Accounting Code, Adj Reason,
- Comment, Status (Approved,Pending Approval, Research Required)

Sales Credit Adjustment

With this form you can change revenue and non-revenue sales credits for your invoices, credit memos, and debit memos. If AutoAccounting is based on salesperson and you have not made changes to the revenue accounts in the Enter Invoices form, Oracle Receivables automatically updates your revenue accounts when you change sales credit information in this form. When you change the sales credits for a line, Oracle Receivables creates a record that reverses the original accounting when you save. For example, you have an invoice for which Salesperson A is assigned to 100% of the sales revenue. Now you decide to give Salesperson B 25% of the sales revenue. You must first change the revenue percent of Salesperson A to 75% and then enter a new line for Salesperson B. Oracle Receivables creates a record for Salesperson A that reverses the sales credit for 25%.
Invoice (Query Invoice To Be Adjusted)
- Invoice Number, Source Batch, Customer Name, Currency, Date,
- GL Date, Status
Line Sales Credits (RA_CUST_TRX_LINE_SALESREPS)
- Line Number, Sales Rep Name, Revenue %, Revenue Amount

Enter Exchange Rate Adjustments

Use this form to adjust the rate type, rate date, and exchange rate
for a foreign currency receipt.

Approve Adjustments

Use this form to approve, hold, reject or request more information about pending adjustments. Oracle Receivables automatically enforces the adjustment limits that you define in the Define Adjustment Limits form as you enter your adjustments. You can approve adjustments that are within your individual approval limits. You can create all other actions even if an adjustment is outside your user approval limits. You can also review adjustment histories and record your comments. Adjustment records that require approval do not affect the balance or status of the transaction you are adjusting. When you approve adjustments in this form, Oracle Receivables automatically updates the balance of the transaction you are adjusting. You can only approve a transaction that has been selected and approved for automatic receipt generation, if the user profile option AR: Invoices with Unconfirmed Receipts is set to Adjustment or Adjustment and Credit.
Review
- Creator, Last Reviewer, Order By (Status,Amount)
Adjustments (AR_ADJUSTMENTS)
- Action (Approved, Pending Approval, Rejected, Research Required),
- Adjustment Number, Invoice Number, Type, Amount, Status, Activity Name,
- Customer, GL Date

Submit AutoAdjustment

Use this form to automatically adjust the remaining balances of all open invoices, debit memos, credit memos, and chargebacks that meet specific criteria you define. You can select debit items by remaining amount, remaining percent, due date, transaction type, customer name, or customer number. Additionally, you can specify your adjustment type, activity, GL date, and reason for each AutoAdjustment submission. If the adjustment amount is within your adjustment approval limits, Oracle Receivables automatically approves your adjustment. If the adjustment amount is outside your adjustment approval limits, Oracle Receivables creates pending adjustments with a status of Waiting Approval.

Enter Receipts

Use the Enter Receipts form to enter manual receipts and apply them to transactions in open accounting periods. You can also create chargebacks or adjustments against these transactions in this form.The Enter Receipts form also lets you apply on-account credits to your receipts. If you need to create a new on-account credit while in Enter Receipts, you can use the Zoom feature to go to the Enter Credit Memos form, create your on-account credit, and then return to the Enter Receipts form. You can apply on-account credits to specific debit items in the Reapply Credits form.Your system administrator sets profile options that affect this form. If the Allow Actions profile option is set to No, then you cannot navigate to the Actions zone to create adjustments or chargebacks. The AR: Cash - Default Amount Applied profile option governs the default application amount when you apply your receipts in the receipt Applications zone. Valid choices for this profile option are Remaining Amount of the Invoice and Unapplied Amount of the Receipt. You can override this profile option at the user level in the Update Personal Profile Options form.
Receipt Batch (AR_BATCHES)
- Source, Name, Deposite Date, Currency, Batch Date, GL Date,
- Receipt Class (Manual Receipt, Wire), Payment Method,
- Remittance Bank Account, Status, Comment, Control Count/Amounts
Receipt (AR_CASH_RECEIPTS)
- Receipt Number (Check #,Credit Card #), Receipt Date, GL Date, Currency,
- Amount, Method, Bank Account, Deposit Date, Apply Date, Invoice Number

Mass Receipt Application
Specific Receipt Applications (AR_RECEIVABLE_APPLICATIONS)
- Customer Number, Invoice Number, Amount, Balance Due
Action
- Adjustments and Chargebacks

QuickCash

Use the QuickCash form to quickly enter receipts into Oracle Receivables. You can apply your receipts to one or many transactions, place receipts on-account, and enter receipts as either Unidentified or Unapplied. Oracle Receivables lets you process all your invoice-related receipts with this form. You can simply enter the information you have now, and reconcile any unapplied and unidentified amounts later using either the Enter Receipts form or the Enter Invoice Adjustments form. Since allQuickCash receipts and applications are stored in a temporary area, you can use the QuickCash form as an additional level of security until you submit them through Post Batch. For example, if you hire temporary help during peak periods, have them perform their work in this form. You can review their work prior to updating your customers' balances. Once you process your QuickCash receipt batches, use the Post QuickCash form to update your customer balances.
Receipt Batch (AR_BATCHES)
- Source, Name, Deposite Date, Currency, Batch Date, GL Date,
- Receipt Class (Manual Receipt, Wire), Payment Method,
- Remittance Bank Account, Status, Comment, Control Count/Amounts
Receipts (AR_INTERIM_CASH_RECEIPTS)
- Receipt Number (Check #,Credit Card #), Receipt Date, Currency, Invoice Number,
- Customer Name, Number, Billing Location, Payment Method, Remittance Bank

Enter Miscellaneous Transactions

Use this form to enter your non-invoice related transactions such as investment income, interest income, and refunds into Oracle Receivables. Oracle Receivables simplifies your data entry in thisform by utilizing the distribution sets and the activities that you defined during your set up process for miscellaneous transactions. Use the Miscellaneous Transactions report to review all of these transactions.
Receipt Batch (AR_BATCHES)
- Source, Name, Deposite Date, Currency, Batch Date, GL Date,
- Receipt Class (Manual Receipt, Wire), Payment Method,
- Remittance Bank Account, Status, Comment, Control Count/Amounts
Receipts (AR_CASH_RECEIPTS)
- Activity (Interest Income, Invest Income, Misc Cash, Misc Refund, Stock Option),
- Distribution Set, Receipt Number, Receipt Date, GL Date, Currency, Amount,
- Tax Code, Payment Method, Remittance Bank, Deposit Date,
- Reference Type (Receipt,Payment,Remittance), Reference Number
Accounting Distribution (AR_MISC_CASH_DISTRIBUTIONS)
- Percent, Amount, Accounting Code, Comments, Posted

Reverse Receipts

Use this form to reverse your receipts. You can use this form to reverse both standard invoice-related receipts and non-invoice related receipts entered in the Enter Miscellaneous Transactions form.
To reverse a receipt you can either create a standard reversal or debit memo reversal. When you choose to create a standard reversal, Oracle Receivables automatically creates reversal journal entries for your general ledger, and reopens all of the debit and credit items that you closed with the original receipt. When you choose to create a debit memo reversal for your receipt, Oracle Receivables does not update any of the receipt activity associated with this original receipt. Your new debit memo reversal is actually a new receivable. If you choose to create a standard reversal for a receipt you applied in the Enter Receipts form, Oracle Receivables automatically reverses any adjustments or chargebacks that you created in the Enter Receipts form as long as you have not posted.
Receipt Reversal (Query) (AR_CASH_RECEIPTS)
- Receipt- Receipt Number, Customer, Batch, Bank, Payment Method
- Reversal - Reversal Date, GL Date, Debit Memo,
Category (Unsufficient Funds,Reverse Payment,Stop Payment), Reason

Reapply Receipts

Use this form to apply unidentified, unapplied, or on-account receipts to invoices, debit memos, chargebacks, or on-account credits. You can reapply receipts to both automatic and manually entered receipts. You can also reapply receipts that you previously applied in error, before or after posting these items to your general ledger. You can use the Zoom feature to navigate to the View Transaction History form and review additional details about your receipt, debit item, or credit item.
Receipt (Query) (AR_CASH_RECEIPTS)
- Receipt Number, Method, Date, Amount, Customer, Batch, Category
Receipt Applications (AR_RECEIVABLE_APPLICATIONS)
- Action (Delete, Reapply), Invoice Number, Type, Inv Amount, Balance Due
Reapply Applications
- Customer Number, Invoice Number, Amount, GL Date, Apply Date

Maintain Lockbox Transmission Data

Use this form to review and edit your lockbox transmission information that you have imported into Oracle Receivables from your bank files. Oracle Receivables lets you review, delete, edit,and maintain your lockbox transmission data here before you proceed to the lockbox validation step. Oracle Receivables displays the lockbox and batch records first, followed by the receipts and overflow records for each transmission. Oracle Receivables also lets you edit your lockbox data here when it fails the validation step. Once the lockbox program has successfully validated your transmission data, you can review and edit it in the QuickCash form. If you are updating data, be sure to update only those fields that have data which corresponds to the transmission format that you used when you submitted the import process. You can use AutoCash Rules with Lockbox to automatically apply receipts to open debit items if you do not specify debit item information in your lockbox transmission format.
Transmission Records (AR_PAYMENTS_INTERFACE)
- Name, Creation Date, Lock Box Number, Batch Name, Item Number, Receipt Number,
- Overflow Sequence, Record Identifier, Status, Comments
- Control Info - Bank Origination Number, Destination Account, Deposit Date, GL Date,
Deposit Time, Currency, Transmission Record Count, Transmission Amount,
Lockbox Batch Count, Lockbox Amount, Lockbox Receipt Count,
Batch Receipt Count, Batch Amount,
- Receipt Info - Overflow Indicator, Receipt Date, Receipt Number, Payment Method,
Remittance Amount, Customer Number, Billing Location, Transmit Routing,Account,
- Receipt Applications - Applied Amount, Invoice Number, Status,
- Receipt Attributes - Attribute (1 - 15)

Reconcile Receipts

Use this form to reconcile your bank account and create new batches of cleared receipts for receipts with a receipt class which requires clearance. You perform this step to mark receipts as cleared. When you clear a receipts Oracle Receivables credits your cash account and debits your remittance or factoring account.Oracle Receivables allows you to query up existing batches of cleared receipts, create new batches of cleared receipts, and add cleared receipts to existing batches, in this form. Oracle Receivables only allows you to query up receipts for reconciliation which you have remitted to the bank accounts that you specify in this form, if these receipts have a receipt class which requires remittance.

Batch (AR_BATCHES)
- Batch Name, Batch Date, Bank Name, Branch, Account, Currency, GL Date,
- Receipt Selection Criteria - Remittance Dates Range, Receipt Number Range
Clear Receipts (AR_CASH_RECEIPT_HISTORY)
- Action (Clear, Reverse), Receipt Number, Receipt Amount, Date Cleared,
- Amount Cleared, GL Date, Status, Remit Date, Maturity Date

Create Automatic Remittances

Use this form to mark receipts that you want to include in a remittance batch. You can create unapproved, approved, or approved and formatted remittance batches. If the AR: Automatic Remittance Creation profile is set to Format, then you can create, approve and format the remittance batch in one step. If this profile option is set to Approve, then Oracle Receivables lets you create and approveyour remittance batches in this form. If it is set to Create, then you can only create remittance batches here, and need a user for whom the option is set to Format to approve and format them in the Approve Automatic Remittances form. When you save your work, Oracle Receivables initiates a single concurrent process which produces a report giving you details of the remittance batches which have been created. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information. Oracle Receivables will only include receipts with Payment Methods for which the receipt class requires remittance in the remittance batches that it creates.
Creation Criteria (AR_BATCHES)
- Batch Name, Batch Date, Remittance Method, Payment Method, GL Date, Currency,
- Remittance Bank, Branch, Account, Action (Create,Approve, Format)
- Receipt Selection Criteria - Receipt Dates Range, Receipt Number Range,
Customer Number Range, Maturity Date Range, Document Number Range

Approve Automatic Remittances

Use this form to review your remittance batches for approval and formatting.The actions that you can take depend upon the value that your system administrator has set for the profile option AR: Automatic Remittance Creation. Oracle Receivables will only create remittance batches from receipts with a payment method whose receipt class requires remittance. After you have submitted a remittance batch for approval or formatting you can still review it in this form, but you cannot view receipt level information. If you update a receipt's remittance bank account, Oracle Receivables excludes the receipt from this batch unless you are remitting to the clearing institution for this new remittance bank.When you save your remittance action, Oracle Receivables displays the concurrent request id of the concurrent process you have submitted to approve the batch of remittances that you specify. This concurrent process produces a report giving you details of the batches which have been approved. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information.

Format Automatic Remittances

Create Automatic Receipts
Use this form to create unapproved, approved, or approved and formatted automatic receipts. Whether you can create approved or formatted automatic receipts depends on the value that your system
administrator has set for the AR: Automatic Receipt Creation Authority profile option. When you save your record, Oracle Receivables initiates a single concurrent process which produces a report giving you details of the automatic receipt batches which have been created. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information. If confirmation is not required for the receipt class that you assigned to the payment method you entered for the creation of this automatic receipt batch, then Oracle Receivables automatically confirms all of the receipts within this batch when you approve the batch. If your receipt class requires confirmation then Oracle Receivables requires that you confirm the automatic receipts in this batch in the Confirm Automatic Receipts form, once they are approved. If you have defined customer relationships and you want to create automatic receipts for the parent customer, then you must assign this parent customer's bank accounts to the invoices paid with the automatic receipt.
Creation Criteria (AR_BATCHES)
- Batch Name, Batch Date, Payment Method, GL Date, Currency,
- Action (Create,Approve, Format)
- Invoice Selection Criteria - Invoice Dates Range, Invoice Number Range,
Customer Number Range, Due Date Range, Document Number Range

Approve Automatic Receipts

Use the Approve Automatic Receipts form to approve,delete and format your automatic receipts. The actions that you can take in this form depend upon the value that your system administrator hasset for the AR: Automatic Receipt Creation Authority profile option.Query up the automatic receipts that you want to approve, delete or format, and then select the action you want to take. Oracle Receivables allows you to update the rate date, type and rate of your foreign currency automatic receipt batches if the status of the batch is Completed Creation.When you save your record, Oracle Receivables initiates a single concurrent process which produces a report giving you details of the automatic receipt batches which have been approved. If you choose to format your batch then Oracle Receivables initiates an additional process which creates the formatted batch information. If confirmation is not required for the receipt class that you assigned to the payment method you entered for the creation of thisautomatic receipt batch, then Oracle Receivables automatically confirms all of the receipts within this batch when you approve the batch. If your receipt class requires confirmation then Oracle Receivables requires that you confirm the automatic receipts in this batch in the Confirm Automatic Receipts form, once they are
approved.

Format Automatic Receipts

Confirm Automatic Receipts

Use this form to enter customer confirmations of approved automatic receipts and their applications. Oracle Receivables updates your customer balances when you confirm a receipt. After you query up the automatic receipt you want to confirm, you can review and update the invoices you have selected to apply to the receipt as well as remittance bank and customer bank information. Oracle Receivables allows you to update the invoice application amounts and bank information.Oracle Receivables only requires that you use this form to confirm receipts with payment methods that belong to receipt classes which require confirmation. You can query receipts for receipt classes which do not require confirmation, if they are not yet included in a remittance batch, but you can only update their remittance and factoring information. Oracle Receivables does not allow you to delete a batch of automatic receipts that has a status of Completed Approval if you have confirmed any of the receipts within this batch.
Receipt (Query) (AR_CASH_RECEIPTS)
- Receipt Number, Document Number, Receipt Class, Payment Method,
- Receipt Date, Receipt Amount, Currency, Customer Name, Customer Number,
- Location
- Confirmation Info - Confirm, GL Date, Confirmation Date, Factor,
Confirmed Amount,Customer Reference
- Remittance Detail (Old/New) - Maturity Date, Remittance Bank Name,
Remittance Bank Branch, Remittance Bank Account, Customer Bank Name,
Customer Bank Branch, Customer Bank Account
Invoice Details
- Balance Due, Approved Amount, Invoice Number, Invoice Type, Invoice Date

Post QuickCash

Use this form to recognize and update your customer's accounts with receipts entered in the QuickCash forms or through the Process Lockbox program. Oracle Receivables displays only those batches which have not been posted to update customer accounts. For batches entered through Lockbox, you can skip this step by choosing to post the batch to customer accounts in the Run LockBox Interface
form.
Available Batches (AR_BATCHES)
- Options (Select), Source, Name, Batch Date, Status, Request

Record A Call

Use this form to record information about your customer calls.Oracle Receivables provides collectors with essential call details and makes it easy to record call information and review previous calls. Use this form to log call topics, outcomes, and actions. You can place amounts in dispute in the Call Actions zone. Oracle Receivables also lets you view a summary of your customer calls in the View Call History form. Use \ Other Zoom to quickly review other information in the View Invoice Image, View Transaction History, and View Call History forms while you talk to your customer. You can also review additional customer payment or open invoice information in the View Receipts by Customer and View Customer Account Detail forms. If you enter this form through the Choose Next Screen option from another form, Oracle Receivables defaults information into the Customer, Contact, and Call Topic zones.
Customer
- Name, Address, Contact Who Called
Call Topic
- Call Taken By, Invoice Details
Call Out Come
- Customer Response, Promise, Follow Up, Call Out Come, Memo Pad
Call Action
- Action, Amount, Memo Pad, Notify

View Transaction History

Use this form to review the history of a transaction. The complete history includes receipts, credit memos, on-account credits, adjustments, and debit items. With Choose Next Screen, you can move easily between inquiry screens without using the main menu. Oracle Receivables completes your new form with information about the transaction or receipt you are reviewing when you use Choose Next Screen to review an open debit item, on-account credit, credit memo information, an image of your debit item, on-account credit,or credit memo, and customer calls.

View Invoice Image

Use this form to review detailed information about a transaction. With Choose Next Screen, you can move easily between inquiry screens without using the main menu. When you choose next screen, Oracle Receivables fills in your new form with information regarding the transaction you are reviewing. You can use this to navigate to forms where you can review application and adjustment activity, call history, or quickly find other transactions for the customer.

View Batch Control Amounts

Use this form to quickly determine the status of your receipt batches. Oracle Receivables displays receipt batch information so you can see where the control counts and amounts are not equal to your actual counts and amounts. Use the View Batch Detail and the View Batch Unapplied Amounts forms to further review your receipt batches and the receipts they contain.

View Batch Unapplied Amounts

Use this form to review unidentified and unapplied receipt counts and amounts by receipt batch. You can review receipt batches based on the search criteria you specify. Oracle Receivables lets you quickly access complete information and determine where you need to continue your work. You create receipt batches when you use the QuickCash, Enter Receipts, and Enter Miscellaneous Transactions forms. Use the Review Receipts by Batch form to access more detailed information about any of your receipt batches.

View Batch Detail

Use this form to review all payments within a receipt batch. You query the receipt batch you want to review in the Batch zone, thenyou can use additional criteria to query receipts within that batch.

View Receipts by Customer

Use this form to review your customers' receipts. You can review receipts for a particular customer or all receipts based on the search criteria you enter in the Receipts zone. No matter how you select receipts to review, Oracle Receivables always displays customer information as you navigate to each receipt. Use the ViewCustomer Account Detail form to review the status of a particular customer's account.

View Transmission History

Use this form to review information about lockbox transmissions you submit through the Submit Lockbox Processing form. You can review information about the transmission record set in the form: use the Maintain Lockbox Transmission Data form to review individual records within the transmission.

View Customer Account Detail

Use this form to review the status of a customer's account. Oracle Receivables displays details of all receipts and transactions for your customer. Oracle Receivables also lets you see the current customer account balances in your functional currency using the most recent exchange rate. Oracle Receivables displays different information depending on the option you select in the customer zone.

View Customer Account Summary

Use this form to review your customer accounts. Oracle Receivables lets you choose your customers for review by using a wide range of search criteria. Oracle Receivables displays all customers and customer account balance information that match your search criteria. Use Choose Next Screen to review additional details for each customer account including account summary information by accounting period, credit statistics, aging information, credit profile, and account details. Oracle Receivables displays all amounts in your functional currency. Use \ Other Zoom to navigate directly to the Customer Account Detail form where you can view thedetails of all items for each customer.

View Call History

Use this form to review previous customer call information. Oracle Receivables gives you detailed call history information to make collections more efficient.You can see which customers are not
meeting their commitments on outstanding receivables, and then decide what actions you need to take. Oracle Receivables lets you review the calls here that you entered in the Record A Call form.

Customer Inquiry

Use this form when you need to identify a customer and you only have a minimal amount of information. Oracle Receivables can identify customers from a partial customer name, a payment number, a contact name, an address, a city, a state, a postal code, a transaction number, or a telephone number.

Run Concurrent Programs

- Run Auto Invoice
- Run Customer Interface
- Run Tax Rate Interface
- Run Revenue Recognition

Submit Lockbox Processing

Use this form to submit your lockbox transmission processes and transfer payment information from your bank files into Oracle Receivables. Oracle Receivables lets you submit each step of the lockbox transmission process as a separate request or submit all of the transmission processes as one request, depending upon your specific business needs. For example, you may choose to import data into Oracle Receivables and examine it before validating it within Oracle Receivables. Upon examination and approval, you can submit the validation step and Oracle Receivables automatically validates your data and creates QuickCash payment batches. Oracle Receivables lets you view your payment batches in the QuickCash form before updating your customers' accounts using Post QuickCash. Oracle Receivables gives you the flexibility to either examine and control each step, or submit all of the steps at once. The 'Process Lockboxes' program transfers payments received by your bank to Oracle Receivables. Since this program is entering Payments into Oracle Receivables, the program must be smart enough to enter the primary billing location as well. If your bank is incapable of transmitting the billing location of your payments, then the program should choose the primary billing location of the identified customer. Oracle Receivables will give you the option to determine the fate of your payments when there is no billing location available. This option will be enabled in the 'Define Lockboxes' form. Your options will be to reject the payments when the billing location does not exist or to ignore the missing billing location. If you desire the first option, the Oracle Receivables will issue an error message indicating that the billing location was not available. The 'Process Lockboxes' program must evaluate the status of the 'Bill-To Site'parameter in the 'QuickCash' form to determine if the 'Billing Location' is required.

Open/Close Accounting Periods

Use this form to open and close accounting periods in your receivables calendar. You define your receivables calendar in the Define Calendar form. Oracle Receivables references the statuses of these accounting periods to control transaction entry and journal entry creation to your general ledger. You may not enter an activity in a closed accounting period.

Open Next Accounting Period
- Latest Open Period, Open Next Period
Accounting Periods
- Period Status (Open,Close,Future), Period Number, Fiscal Year,
- Period Name,Begin Date, End Date

Run GL Interface

Use this form to create journal entries to post in detail or summary. Oracle Receivables first transfers transaction data into the GL_INTERFACE table and generates the Posting Execution Report. Use the Posting Execution Report to see a summary of transactions that are imported into the GL_INTERFACE table. Transactions that failed validation are printed in the Unposted Items Report produced from this run. From here, if you have Oracle General Ledger installed you can use Journal Import to create journal entries to post to your general ledger. If you do not have Oracle General Ledger installed you can use your feeder system to import data from the GL_INTERFACE table.
Transfer Crireria
- Summary or Detail, GL Posted Date, GL Start Date, GL End Date,
- Run Journal Import

12 Steps for Enabling Flexfields in Oracle Report

2011-08-17T00:43:00.001-07:00

If any one want to use Flexfield in report, there is minimum steps you have to follow to make things Functional. These are the standard steps as per AOL guide.
Step 1 Define your Before Report Trigger
Here you suppose to call FND SRWINIT from the Before Report Trigger like :

SRW.USER_EXIT('FND SRWINIT');

This user exit sets up information for use by flexfields, user profiles, the concurrent manager, and other Oracle Applications features as discussed in one of previous post.
Step 2 Define your After Report Trigger
Next, is you need to call FND SRWEXIT from the After Report Trigger like :

SRW.USER_EXIT('FND SRWEXIT');

Step 3 Define your required parameters

In this step you need to define the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.
Lexical Parameters

P_CONC_REQUEST_ID
P_FLEXDATA

You must always create the P_CONC_REQUEST_ID lexical parameter. "FND SRWINIT" uses this parameter to retrieve information about the concurrent request that started this report.
The P_FLEXDATA parameter holds the SELECT fragment of the SQL query. The initial value is used to check the validity of a query containing this parameter and to determine the width of the column as specified by the column alias. Its initial value is some string that contains columns with a cumulative width more than the expected width required to hold the data. Make sure the width of this column is sufficient. If there are total 30 segments in the table then the safest initial value will be:
(SEGMENT1||'\n'||SEGMENT2||'\n'||SEGMENT3 ... SEGMENT30)
You determine the width by determining the length of that string. That length is roughly the number of characters in the table alias plus the length of the column name, times the number of segments your code combinations table contains, times the number of structures you expect, plus more for delimiter characters as shown in the string above.

Step 4 Define your rest other parameters

You define the rest of the parameters your report needs by using the Data Model Painter. You use these parameters in the user exit calls and SQL statements.

Step 5 Call FND FLEXSQL from your Before Report Trigger to populate P_FLEXDATA

Next, given that you want to display flexfield information like concatenated values and descriptions, and arrange them in order, you make one call to FND FLEXSQL from the Before Report Trigger specifying the lexical parameters. This call changes the value of the lexical parameter P_FLEXDATA at runtime to the SQL fragment that selects all flexfields value data. For example, the parameter changes to (SEGMENT1||'\n'||SEGMENT2||'\n'||SEGMENT3||'\n'||SEGMENT4).
When you incorporate this lexical parameter into the SELECT clause of a query, it enables the query to return the concatenated segment values that are needed as input to other AOL user exits. These exits then retrieve the actual flexfield information for display purposes.
Here is an example FND FLEXSQL call. Notice that the arguments are very similar to other flexfield routine calls; CODE= and NUM= designate the key flexfield and its structure, respectively. For a report on a different key flexfield (such as the System Items flexfield), you would use a different CODE and NUM.

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT('FND FLEXSQL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
OUTPUT=":P_FLEXDATA"
MODE="SELECT"
DISPLAY="ALL"');

You should always reference any source column/parameter that is used as a source for data retrieval in the user exit. This guarantees that this column/parameter will contain the latest value and is achieved by "SRW.REFERENCE" call as shown above.
Step 6 Call FND FLEXSQL from your Before Report Trigger to populate other parametersWhat you suppose ti do here is just need to call FND FLEXSQL once for every lexical parameter.
Step 7 Define your report query or queries
Define your report query Q_1:

SELECT &P_FLEXDATA C_FLEXDATA
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN
= &P_STRUCT_NUM

The query fetches the data required to be used as input for the FLEXIDVAL user exit later.
When the report runs, the call to FND FLEXSQL fills in the lexical parameters. As a result the second query would look something like:

SELECT (SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||
SEGMENT4) C_FLEXDATA
FROM CODE_COMBINATIONS_TABLE
WHERE CODE_COMBINATIONS_TABLE.STRUCTURE_DEFINING_COLUMN = 101< /FONT >

Step 8 Create formula columns
Now create columns C_FLEXFIELD and C_DESC_ALL (and any others your report uses) corresponding to the values and descriptions displayed in the report. They all are in group G_1.
Step 9 Populate segment values formula column
Now you need to retrieve the concatenated flexfield segment values and description which you incorporate the flexfields user exits in these columns. In the column definition of C_FLEXFIELD, you incorporate the FND FLEXIDVAL user exit call in the formula field. You pass the concatenated segments along with other information to the user exit, and the user exit populates the concatenated values in this column as specified by the VALUE token. A typical call to populate segment values in this column looks as follows:

SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
DATA=":C_FLEXDATA"
VALUE=":C_FLEXFIELD"
DISPLAY="ALL"');
RETURN(:C_FLEXFIELD);

Refer this how to enable user exits in reports.
Step 10 Populate segment descriptions< /STRONG>

To populate the segment description use DESCRIPTION="C_DESC_ALL" instead of VALUE="C_FLEXFIELD" as in the previous call. The user exit call becomes:
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL
CODE="GL#"
NUM=":P_STRUCT_NUM"
APPL_SHORT_NAME="SQLGL"
DATA=":C_FLEXDATA"
DESCRIPTION=":C_DESC_ALL"
DISPLAY="ALL"');
RETURN(:C_DESC_ALL);
You have created parameters and columns that are containers of all the values to be displayed. Now, in the following steps, you create the layout to display these values on the report.

Step 11 Create your default report layout

First choose Default Layout to generate the default layout. Deselect C_FLEXDATA. Specify a "Label" and a reasonable "Width" for the columns you want to display.

Step 12 Finish your report By adjusting report layout.

Interview Questions For Oracle Apps

2011-08-17T00:27:00.003-07:00

Qns: How will you developed reports from scratch to Registrations.

Ans:

STEP1: Write Query according to Specs.

STEP2: Add User Parameter in Query.

STEP3: Design Data Model.

STEP4: Design Layout according to specs.

STEP5: Defined Mandatory Parameter P_CONC_REQUEST_ID in report.

STEP6: Use User exit in reports.

STEP7: FTP the report the appropriate top in reports/US folders.

STEP8: registered reports in Oracle Apps (Note Explain all steps of apps registrations)

Qns: What is Difference between D2k and APPS Reports?

Ans:

D2K	Apps Reports
No User Exit	User Exit
P_Conc_Request_id not Required	P_CONC_REQUEST_ID Mandatory Parameter

Qns: What is Format Trigger?

Ans: Format trigger is used to print conditionally printing of Layout. Format trigger return Boolean values.

Qns: What do you mean by Anchoring in Reports?

Ans : With the help of Anchoring Relative position of two objects will be fixed.

Qns: What are Reports Trigger and Their Firing Sequence?

Ans:

BEFORE PARAMETER FORM
AFTER PARAMETER FORM
BEFORE REPORT
BETWEEN PAGES
AFTER REPORT

Qns: What do you mean by Confine Mode in Reports?

Ans : Confine Mode Means Locking of Frames. If Confine Mode is on then you can not move the Object outside the Frame. If it is off you can move the objects.

Qns: Whats is Difference between Formula Column, Summary and Placeholder Column.

Ans:

Formula Column: Formula Column is used to compute Values. Formula Column always returns the Values.
Summary Column: Summary Column is used for Calculating summary function like Sum, Max. Min, Count.
Placeholder Columns: Placeholder Column is used to place the Variables.

Qns: Whats is Difference between and Lexical and Bind Parameter.

Ans:

Bind Parameter	Lexical Parameter
With the help of Bind Parameter You Can Pass Values.	With the help of Lexical Parameter You can pass string. You can pass lexical parameter in Select, From , Where Clause
Denoted by Colon	Denoted by Ampersand(&) sign

Qns: How will you Print Conditionally Layout in reports.

Ans: With the help of Format trigger.

For example code like:

IF :P_TYPE

Qns: How will you call Concurrent Program through Reports.

Qns: Whats are User Exit in Reports?

Qns: In Which Folder You will put the Report on the Server?

Qns : How will you Register the Reports?

Qns : What do you Mean by Token in Reports.

Qns : What are Various Default Type in Concurrent Programs?

Qns : What do you incompatibility in Concurrent Programs?

Qns: How will you Capture ORG_ID Dynamically in Concurrent Programs.

Qns : Suppose I have two Parameter Start Date and End Date and I want My Start Date should be lower then End Date. How will you Validate.

Qns : What are Various Execution Method in Concurrent Programs in executables.

Qns : What is Difference between Application Name Defined in Concurrent Program Executables and Concurrent Programs.

Qns : Whats is Mandatory Parameter to Register PL/SQL Procedure in Apps?

Qns: How will you Call Reports with in Reports?

Qns: What do you Mean by Incompatibility in Reports?

Qns : What is significance of Mandatory Parameter in P_CONC_REQUEST_ID?

Qns: How will you put Message in Log File?

Qns : How will you put Message of Reports in LOG Files.

SRW Package in Oracle Report

2011-08-16T06:07:00.001-07:00

SRW Package in Oracle Report

20 May

SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.

The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.

SRW.MESSAGE:

It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.

SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);

Example:

function foo return boolean is

begin

if :sal < 0 then

SRW.MESSAGE(100, 'Found a negative salary. Check the EMP table.');

raise SRW.PROGRAM_ABORT;

else

:bonus := :sal * .01;

end if;

return(true);

end;

SRW.PROGRAM_ABORT:

This exception stops the report execution and raises the following error message: REP-1419: PL/SQL program aborted. SRW.PROGRAM_ABORT stops report execution when you raise it.

SRW.DO_SQL:

This procedure executes the specified SQL statement from within Reports Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.

Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Reports Builder.

Example:

FUNCTION CREATETABLE RETURN BOOLEAN IS

BEGIN

SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL

PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');

RETURN (TRUE);

EXCEPTION

WHEN SRW.DO_SQL_FAILURE THEN

SRW.MESSAGE(100, 'ERROR WHILE CREATING TEST_EMP TABLE.');

RAISE

SRW.PROGRAM_ABORT;

END;

SRW.DO_SQL_FAILURE:

Reports Builder raises this exception when the SRW.DO_SQL packaged procedure fails. This exception stops the report execution and raises the following error message:

REP-1425: Error running DO_SQL package – REP-msg ORA-msg.

SRW.GET_REPORT_NAME:

This function returns the file name of the report being executed.

SRW.GET_REPORT_NAME (report_name);

Example:

function AfterPForm return boolean is

my_var varchar2(80);

BEGIN

SRW.GET_REPORT_NAME (my_var);

SRW.MESSAGE(0,'Report Filename = '||my_var);

RETURN (TRUE);

END;

SRW.RUN_REPORT:

This procedure synchronously executes the specified report within the context of the currently running report.

SRW.RUN_REPORT (“report=test.rdf … “)

SRW.SET_FIELD:

This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally change a field’s value.

SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);

Example:

Suppose you want to conditionally change the number of a field, based on each employee’s salary. In the format trigger for the field, you could type the following:

FUNCTION CHGFIELD RETURN BOOLEAN IS

TMP NUMBER;

BEGIN

if :sal >= 5000 then

tmp := :sal * 1.10;

srw.set_field (0, tmp);

else

srw.set_field (0, 4000);

end if;

RETURN (TRUE);

END;

SRW.SET_FIELD should be used only to change the contents of a field’s datatype, not change the field to a different datatype.

Others in Brief:

SRW.SET_FONT_FACE: This procedure specifies font face for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_FACE(‘arial’);
SRW.SET_FONT_SIZE: This procedure specifies font size for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_SIZE(10);
SRW.SET_FONT_STYLE: This procedure specifies font style for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);
SRW.SET_FORMAT_MASK: This procedure specifies the format mask for the DATE or NUMBER field. SRW.SET_FORMAT_MASK(‘mask’);
SRW.SET_TEXT_COLOR: This procedure specifies the global text color of the CHAR, DATE, or NUMBER field. SRW.SET_TEXT_COLOR(‘color’);

Use of SRW Package in Report

SRW Package:

The report execution can be controlled

Display messages at run time

Layout field can be initialized

Formatting can be done

Make the user to exit report

DDL statements can be performed

Displaying messages:

The SRW.MESSAGE procedure displays the message. It also displays the code and text that the user has specified.

The message text is displayed in a small dialog box in the following syntax.

Msg-code: Text

Code: digits from 0 – 10

Text: Max 190 characters

Warning or Error Messages:

SRW.MESSAGE will not terminate the user from continuing. So this can be used for displaying warning messages.

SRW.PROGRAM_ABORT will terminate the program after displaying the message

RAISE SRW.PROGRAM_ABORT;

Running nested reports:

For running one report from another report the user has to use SRW.RUN_REPORT

Go to layout editor, create a button and label it as ‘Click this button’

Open the property palette of the button and set the Button Behaviour: Type=pl/sql: Pl/sql Trigger= provide the code

procedure U_ButtonButtonAction is

begin

srw.run_report('module=C:\Documents and Settings\Administrator\Desktop\POXDETIT.rdf destype=Screen');

end;

Restricting data:

the srw.set_maxrow procedure sets the max number of records that can be retrieved for a specific query.

In the BEFORE REPORT trigger, you could use the srw.set_maxrow procedure to ensure that only the required num of records are fetched.

begin

if :p_dname = 'RESEARCH' then

srw.set_maxrow ('Q_2', 2);

end if;

end;

Using DDLs in Reports:

If the user has to execute any SQL Statement from the report builder then the srw.do_sql procedure is to be used. With the help of SRW.DO_SQL the user can add any DDL or DML operation to the report.

DML are faster in pl/sql, instead in DO_SQL. Since DDLs can not be performed in pl/sql we make use DO_SQL in report builder.

Srw.do_sql (sqlstatement char);

Setting Format Attributes:

Use the SRW.SET_ procedure to set the format attributes. Mainly used to make some change in the appearance of the layout objects.

Format attributes	Value
Text Color	Red
Font Face	Impact
Font Weight	Bold
Font Style	Italic
Font Pattern	Solid diamond

Function age_mask return Boolean is

Begin

If :age < 25 then

Srw.set_text_color(‘Red’);

Srw.set_font_face(‘Impact’) ;

Srw.set_font_weight(‘’srw.bold_weight) ;

Srw.set_font_style(‘srw.italic_style’);

Srw.set_fill_pattern(‘solid diamond’);

End if;

End;

STEPS TO REGISTER XML REPORT IN APPS

2011-08-16T06:02:00.001-07:00

STEPS TO REGISTER XML REPORT IN APPS

1)Develop the .rdf file and .rtf file

2)Register both .rdf and .rtf file

.rdf File registration :

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

1)Move the .rdf file into CUS_TOP\11.5.0\Reports\US

2)Create Execuiteable

Concurrent Program Output = XML

Request Group

Responsibility

User

SRS

3)Copy the Concurrent Program Short name

.rtf File Regitration:

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

1)Goto the XML Publisher Administrator Responsibility

2)Select Data Definations form

Enter Data Defination Name = Enter Any Name

Code = Concurrent Program Short Name

Application = Any application

startdate = Current Date

3)Copy the DataDefination name we have to add this to the Templete

4)Open the Templete Form

Enter Templete Name : Any Name

DataDefination : enter data defination whatever we have created

Code : Concurrent Program Short Name

Type : rtf

application : any Application

File : Upload the .rtf file

Laguage : English

Territory : United States

5)Select Apply button

After register the .rtf file submit concurrent Program from SRS window system will

automatically pick the .rtf file and generates the Output.

If we want to customze the .rtf file we have to download by using templete , Customize then

upload the .rtf templete