Saturday Aug 18, 2012

Compare Technical Terms in Oracle Apps with Real Time Example

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 Puneet 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.

 

Puneet 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.


Puneet 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.

 

Puneet 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. Puneet 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.

 

Puneet’s customer, Fruit Mart pays him in installments (partial payments). So Puneet has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Puneet receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Puneet 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.

 

Puneet’s fruit business has begun to improve and has attracted more and more customers. As a result, Puneet 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, Puneet 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, Puneet pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

 

At the end of every month, Puneet 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, Oracle has 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 its 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, and Fixed Assets etc.

Wednesday Aug 15, 2012

Oracle AP Invoice APIs

These APIs are handful when you want to do Insert, Update or Delete programmatically for some business requirements (rare cases!)

 

API Name

Procedure Name

Description

AP_AI_TABLE_HANDLER_PKG

insert_row

Inserts a row in AP_INVOICES_ALL

update_row

Updates a row in AP_INVOICES_ALL

delete_row

Deletes a row in AP_INVOICES_ALL. Also subsequently delete rows in the related tables like AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL, AP_PAYMENT_SCHEDULES_ALL, AP_HOLDS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL

 

 

API Name

Procedure Name

Description

AP_AIL_TABLE_HANDLER_PKG

check_unique

Check the Uniqueness of a Row

insert_row

Inserts a row in AP_INVOICE_LINES_ALL

update_row

Updates a row in AP_INVOICE_LINES_ALL

delete_row

Deletes a row in AP_INVOICE_LINES_ALL. Also subsequently delete rows in the related tables like AP_INVOICE_DISTRIBUTIONS_ALL

 

 

API Name

Procedure Name

Description

AP_AID_TABLE_HANDLER_PKG

check_unique

Check the Uniqueness of a Row

insert_row

Inserts a row in AP_INVOICE_DISTRIBUTIONS

update_row

Updates a row in AP_INVOICE_DISTRIBUTIONS

delete_row

Deletes a row in AP_INVOICE_DISTRIBUTIONS

 

 

API Name

Procedure Name

Description

AP_AIP_TABLE_HANDLER_PKG

insert_row

Inserts a row in AP_INVOICE_PAYMENTS

update_amounts

Update amounts in AP_INVOICE_PAYMENTS

 

 

API Name

Procedure Name

Description

AP_AC_TABLE_HANDLER_PKG

insert_row

Inserts a row in AP_CHECKS_ALL

update_row

Updates a row in AP_CHECKS

delete_row

Deletes a row in AP_CHECKS

update_amounts

Update amounts in AP_CHECKS

 

Trading Community Architecture (TCA)

Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. 

 

 

Monday Aug 06, 2012

Entity Object Extension in OAF

In the exercise we have taken “Create Data Entry OAF Page“can be found @

https://blogs.oracle.com/prajkumar/entry/insert_data_oaf_page

 

We are going to extend InsertEO. Our business requirement is to add Validation on Column1. The length of entered data in Column1 should be more than 5 Characters.

 

 

1. FTP this project from application server and open in JDeveloper

 

2. Create a New Entity Object (EO)

Right click on InsertDemo > New > ADF Business Components > Entity Object

 

Name – ExtInsertEO

Package -- prajkumar.oracle.apps.fnd.insertdemo.schema.server

Extends -- prajkumar.oracle.apps.fnd.insertdemo.schema.server.InsertEO

 

 

 

Click on Next - > New from Table

 

 

Include all the attributes of parent EO

 

 

Next -> Next

 

Check Validation Method check box and Finish

 

 

3. Add following code in ExtInsertEOImpl.java

 

import oracle.apps.fnd.framework.OAException;
 
protected void validateEntity()
{
 super.validateEntity();

 String column1Value = getColumn1();

 if (column1Value.length() < 5)
 {
  String message = "Length of String" + " " + column1Value + "is less than 5 characters";
  throw new OAException(message, OAException.INFORMATION);
 }
}

 

4. Substitute your New EO with parent EO

Right click on InsertDemo > Project Properties > Business Components > Substitutions

In Available list select InsertEO and in Substitute list select New EO ExtInsertEO and click on Add and then Ok

 

 

5. After substitution import *.jpx

In our case it will modify InsertDemo.jpx at project location

i.e. -- D:\xxxx\jdevhome\jdev\myclasses

 

Open Command Prompt and go to following location of your project

D:\xxxx\jdevbin\oaext\bin

Use this Import Command to import jpx

 

 

6. Bounce the server

 

7. Verify the substitution has applied properly

Run InsertPG page and click on About this Page link

 

 

 

Expand Business Component References Details

Under that section click on InsertVO which is EO based [it should be ExtInsertEO based]

 

 

 

 

8. Congratulation you have successfully finished. Run Your InsertPG page and Test Your Work

 

 

 

Saturday Aug 04, 2012

End Date Responsibility for Oracle FND User

API - fnd_user_pkg.delresp

Example --

Consider FND_USER = ‘PRAJKUMAR’ having responsibility ‘Application Developer’

 

 

Let us try to END_DATE ‘Application Developer’ Responsibility with fnd_user_pkg.delresp API

-- -----------------------------------------------------------------
-- End Date Responsibility from Oracle FND User
-- -----------------------------------------------------------------
DECLARE
   v_user_name                   VARCHAR2 (100) := 'PRAJKUMAR';
   v_responsibility_name   VARCHAR2 (100) := 'Application Developer';
   v_application_name        VARCHAR2 (100) := NULL;
   v_responsibility_key        VARCHAR2 (100) := NULL;
   v_security_group              VARCHAR2 (100) := NULL;


BEGIN
   SELECT  fa.application_short_name,
                     fr.responsibility_key,
                     frg.security_group_key
    INTO        v_application_name,
                      v_responsibility_key,
                      v_security_group
    FROM      fnd_responsibility       fr,
                     fnd_application            fa,
                     fnd_security_groups  frg,
                     fnd_responsibility_tl   frt 
    WHERE  fr.application_id               = fa.application_id
     AND        fr.data_group_id             = frg.security_group_id
     AND        fr.responsibility_id          = frt.responsibility_id
     AND        frt.LANGUAGE                 = USERENV ('LANG')
     AND        frt.responsibility_name  = v_responsibility_name;

      fnd_user_pkg.delresp
      (    username           => v_user_name,
           resp_app            => v_application_name,
           resp_key             => v_responsibility_key, 
           security_group   => v_security_group 
      );

     COMMIT;

     DBMS_OUTPUT.put_line (   'Responsiblity '
        || v_responsibility_name
        || ' is removed from the user '
        || v_user_name
        || ' Successfully'
        );


EXCEPTION
     WHEN OTHERS THEN
              DBMS_OUTPUT.put_line
               (   'Error encountered while deleting responsibilty from the user and the error is '
                    || SQLERRM
                );
END;
/

After API Run --

 

EO based VO Extension in OAF

In the exercise we have taken “OAF Search Page“can be found @

https://blogs.oracle.com/prajkumar/entry/create_oaf_search_page

 

We are going to extend SearchVO of SearchPG page. Our business requirement is to add Creation Date attribute in the results table region

 

 

1. Analyze the Page

Click on “About this Page” link to check that which ViewObject is associated with the table region [ResultsRN]

 

 

 

 

Here we can see that results table has SearchVO attached to it. Now click on Business Component Reference details and check the path of SearchVO

 

 

Now click on SearchVO which is an EO based [SearchEO] to check whether it has Creation Date attribute or not which we want to add

 

 

Here we can see that our VO does not have CreationDate attribute.

So for adding this attribute we need to perform VO Extension.

FTP this project from application server and open in JDeveloper

 

2. Create a New View Object (VO)

Right click on SearchDemo > New > ADF Business Components > View Object

Package -- prajkumar.oracle.apps.fnd.searchdemo.server

Name – ExtSearchVO

Extends -- prajkumar.oracle.apps.fnd.searchdemo.server.SearchVO

 

 

The next screen allows the addition or deletion of Entity Objects. No change is required here for this extension so simply select Next

 

 

The next pane allows additional attributes to be added or attributes to be removed. In this case add CreationDate attributes to the selected list

 

Note -- Please note the new attribute you would be adding will be coming as Transient

 

Click on Next -> Next

 

 

Modify above SQL statement with below SQL to add new attribute CREATION_DATE

 

SELECT  SearchEO.COLUMN1,
                SearchEO.COLUMN2,
                SearchEO.ROWID,
                SearchEO.CREATION_DATE
FROM     XX_SEARCH_DEMO     SearchEO

 

Click on Next -> Next

Generate ExtSearchVORowImpl 

 

 

Click Finish

 

 

Note -- Now as our attribute created as Transient hence to fix this issue we have two options:

1. We need to correct our XML file generated as it is a known error with Jdeveloper

2. Double click on that attribute i.e CreationDate and in the View Object Attribute window, enter attribute name in 'Expression' column i.e. CreationDate

 

Lets try first method --

Open your ExtSearchVO.xml file in some notepad editor.

Scroll to bottom of that file

Update

 

with

 

 

3. Substitute your New VO with parent VO

Right click on SearchDemo > Project Properties > Business Components > Substitutions

In Available list select SearchVO and in Substitute list select New VO ExtSearchVO and click on Add and then Ok

 

 

4. After substitution import *.jpx

In our case it will modify SearchDemo.jpx at project location i.e. -- D:\xxxx\jdevhome\jdev\myclasses

Open Command Prompt and go to following location of your project

D:\xxxx\jdevbin\oaext\bin

 

Use this Import Command to import jpx

 

5. Personalize the page to create new item

On SearchPG click on Personalize Page link

Select Complete View Radio Button

 

 

Click on Create Item on Table: (ResultTable)

 

 

Set item style as messageStyledText

Set Following Properties

Id – CreationDateId

Data Type -- Date

Prompt – Creation Date

View Instance – ExtSearchVO1

View Attribute – CreationDate

 

 

Click Apply > Return to Application

 

6. Congratulation you have successfully finished. Run Your SearchPG page and Test Your Work

Note -- You can notice page has new Column with name Creation Date

 

 

 

Wednesday Aug 01, 2012

Set Default Value for messageTextInput in OAF

1. Create a New OA Workspace and Empty OA Project

File > New > General > Workspace Configured for Oracle Applications

File Name – PrajkumarSetDefaultValDemo

 

Automatically a new OA Project will also be created

 

Project Name -- SetDefaultProj

Default Package -- prajkumar.oracle.apps.fnd.setdefaultproj

 

2. Create Application Module AM

Right Click on SetDefaultProj > New > ADF Business Components > Application Module

Name -- SetDefaultAM

Package -- prajkumar.oracle.apps.fnd.setdefaultproj.server

Check Application Module Class: SetDefaultAMImpl Generate JavaFile(s)

 

3. Create a OA components Page

Right click on SetDefaultProj > New > Web Tier > OA Components > Page

Name -- SetDefaultPG

Package -- prajkumar.oracle.apps.fnd.setdefaultproj.webui

 

4. Select SetDefaultPG and go to the strcuture pane where a default region has been created

 

5. Select region1 and set the following properties:

 

Attribute

Property

ID

PageLayoutRN

Region Style

pageLayout

Form Property

True

Auto Footer

True

Window Title

Set Default Value Window Title

Title

Set Default Value Header

AM Definition

prajkumar.oracle.apps.fnd.setdefaultproj.SetDefaultAM

 

6. Create the Second Region

Right click on PageLayoutRN > New > Region

 

Attribute

Property

ID

MainRN

Region Style

messageComponentLayout


7. Create messageTextInput

MainRN > New > messageTextInput

 

Attribute

Property

ID

MyTextitemId

Style Property

messageTextInput

Length

20

Maximum Length

50

 

8. Save Your Work

 

9. Add a Controller

MainRN > Set New Controller

 

Package Name -- prajkumar.oracle.apps.fnd.setdefaultvaldemo.webui

Class Name -- SetDefaultValCO

 

10. Edit Your Controller

Add Following Code in processRequest

 

import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextInputBean;

public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
 super.processRequest(pageContext, webBean);

 String myValue = "My Default Value";
 OAMessageTextInputBean textBean = (OAMessageTextInputBean)webBean.findChildRecursive("MyTextitemId");

 textBean.setValue(pageContext, myValue);
}

 

11. Test Your Work

 

About

Welcome to My Oracle World Puneet Rajkumar

Search

Categories
Archives
« August 2012 »
SunMonTueWedThuFriSat
   
2
3
5
7
8
9
10
11
12
13
14
16
17
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today