Wednesday, September 30, 2015

Oracle Apps R12: Check if the patch applied from sqlplus

Oracle Apps R12: Check if the patch applied from sqlplus

Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system:
SELECT   bug_number
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||3594604 || '%';

This table includes patches applied on the system:
SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||3594604 || '%'

Tuesday, September 29, 2015

Query to find Full Address of Legal Entity

SELECT (xlep.name || '(' || xlep.LEGAL_ENTITY_IDENTIFIER || ')'
           || CHR (10) ||hl.address_line_1
           || CASE WHEN hl.address_line_2 IS NOT NULL THEN CHR (10) || hl.address_line_2 END
           || CASE WHEN hl.address_line_3 IS NOT NULL THEN CHR (10) || hl.address_line_3 END
           || DECODE (
                 NVL (NVL (hl.town_or_city, hrlkp.meaning), hl.postal_code),
                 NULL,
                 NULL,
                 CHR (10)
              )
           || DECODE (hl.town_or_city, NULL, NULL, hl.town_or_city || ', ')
           || DECODE (hrlkp.meaning, NULL, NULL, hrlkp.meaning || ' ')
           || DECODE (hl.postal_code, NULL, NULL, hl.postal_code)
           || DECODE (ftt.territory_short_name, NULL, NULL, CHR (10))
           || ftt.territory_short_name
           || DECODE (hl.telephone_number_1, NULL, NULL, CHR (10))
           || DECODE (hl.telephone_number_1,
                      NULL, NULL,
                      'Tel: ' || hl.telephone_number_1)
           || DECODE (hl.telephone_number_2, NULL, NULL, CHR (10))
           || DECODE (hl.telephone_number_2,
                      NULL, NULL,
                      'Fax: ' || hl.telephone_number_2)
           || DECODE (hl.attribute1, NULL, NULL, CHR (10))
           || DECODE (hl.attribute1, NULL, NULL, 'Email: ' || hl.attribute1))
     FROM xle_entity_profiles xlep,
          xle_registrations xlr,
          hr_locations hl,
          fnd_territories_tl ftt,
          hr_lookups hrlkp
    WHERE 1 = 1
      AND xlep.legal_entity_id = xlr.source_id
      AND xlr.source_table = 'XLE_ENTITY_PROFILES'
      AND xlr.location_id = hl.location_id
      AND hl.country = ftt.territory_code
      AND hl.region_2 = hrlkp.lookup_code(+)
      AND hrlkp.lookup_type(+) = 'US_STATE'
     AND xlep.legal_entity_id = 29275--------Legal Entity Id

Friday, September 25, 2015

GENERAL NAMING CONVENTION IN OAF

GENERAL NAMING CONVENTION IN OAF



File Name length:-
File names are limited to 30.3 characters for OA Extension XML files (50.java for Java files).

Object Name length (regions, items, and so on):–

           For performance reasons, object names (internal ID’s) are limited to 30 characters.
           Common abbreviations are acceptable to keep names as short as possible.
          Acceptable abbreviations can be instantly understood by a third party consultant.
          Object names in pages must be unique in the entire page.
          Most names follow Java naming convention (mixed case).

Naming Standards for a Page:- The page name should convey the object it Presents.
            Page name ends with PG:- <object name>PG
            For ex:-    EmpSearchPG,  EmployeePG,  SupplierPG

Naming Standards for a Region:- The region name should convey the object it Presents.
            Region name ends with RN:- <object name>RN
            For ex:-   MainRN,  EmpSearchRN,PoHeaderRN

Naming Standards for an Object:-
                        EmpName,,,SearchEmpName,,ResultsEmpName

Naming Standards for an Entity Object:- The EO should be named for the objects stored in its 
                                                                     underlying entity.
            Entity Object name ends with EO:- <EntityName>EO
            For ex:-  EmployeeEO,    SupplierEO,   PurchaseOrderHeaderEO.

 Naming Standards for an Entity Association Object:-The AO name should convey the relationship between a parent and its child entities.

            Entity Association name ends with AO:-<parent>TO<child>AO
            For ex:- PoHeaderToLinesAO,   SupplierToSitesAO, DepartmentToEmployeeAO

Naming Standards for a View Object:-The VO name should convey the nature of  the query.
View Object name ends with VO:- <Descriptive Name>VO
For ex:-  EmployeeVO,  SupplierVO, PurchaseOrderVO.

Naming Standards for a View Link:- The VL name should convey the relationship
 between the master and detail VOs.
View Link name ends with VL:- <Master>To<Detail>VL

For ex:- EmployeeToDepartmenstVL,    PoHeaderToLinesVL

 Naming Standards for an Application Module:- The AM name should convey the purpose of the                                                                                     UI  module it services.

Application Module name ends with AM:-  <ModuleName>AM

For ex:-HelloWorldAM,   EmployeesAM,  SupplierAM.

OAF Structure of EO , VO and AM

The structure of OAF contains:-
·                     MODEL
·                     VIEW
·                     CONTROLLER

MODEL:-
The entire database related transactions in OAF pages will be taken care by the MODEL. Model is where the application implements business logic.

  The major components of Model are:-
  Application Module(AM).
  Entity Object(EO).
  View Object(VO).

Application Module:-

            Application module serves as a container for related BC4J objects.
            Application module provides transaction context.
            Application module establish database connection.

Entity Object:-

Entity object encapsulates business rules and logic.
Entity objects are mainly used by any program that inserts, updates or deletes data.
Entity object provides consistent data validation across all application.
Entity objects may be linked to other entity objects by entity association objects.

          Entity Object map to a database table or other data source.

Entity Objects can be created on database objects like Table, View, Synonym etc or other Entity Objects.


View Object:-

View object encapsulates a database query.
View object provide iteration over the query result set.
View object primarily based on Entity Objects (EO) or may be based on plain SQL or may be based on multiple Eos.
View object provides single point of contact for getting and setting Eos values.


 VIEW:-

View is nothing but the OAF page output.
Each UI (User Interface) widget corresponds to one or more java objects (Beans).
The java objects are used create HTML at run time.
Customers and third parties use Page Personalization to modify pages to fit business needs and user preferences.

CONTROLLER:-
            Controller controls the UI behavior.
            Controller classes define how your java beans behave.
            Controller classes subclass OAControllerImpl.
            OAPageBean is the main OA Framework page processing class.



There are methods in the Controller class to handle GET and POST requests.

  processRequest.
  processFormData.
  processFormRequest.



The OAPageBean calls the processRequest method when a browser issues a GET request.
The OAPageBean then calls processFormData for pages that insert or update data.
The OAPageBean calls the processFormRequest method when a browser issues a GET request.





More About Entity Objects

Entity Objects are mainly used for insert, update or delete the records from database.

Entity Object will take care of business logic and validation related to a table.

Entity Object Encapsulate the attribute level and entity level validation.

OA Framework supports both Java and PL/SQL entity objects

If we want to perform the DML operations on the standard (Seeded) table then we go for PL/SQL based Entity Object.

If we want to perform the DML operations on the custom table then we go for Java Based Entity Object

All the entity objects that you create are subclass of 

oracle.apps.fnd.framework.server.OAEntityImpl

class.

While Creating the Entity Object always generate accessors (setters/getters). 

EO should have following attributes(WHO COLUMNS):-   

  CreationDate
  CreatedBy
  LastUpdateDate
  LastUpdatedBy
  LastUpdateLogin

AAfter creating the entity object always attach that to VO


        

Entity Association:-

            Define a relationship between entity objects.

Facilitate access to data in related entity objects, May be based on database constraints, May be independent of database constraints

Consist of a source (master) and a destination (detail) entity.

There are two types of Entity Associations:-

  Reference Association.

  Composition Association.

Reference Association:-

Used for weak association between entities such as foreign keys for lookups.
Ex:- Requisition - Supplier association

Composition Association:-

Used for composite objects with strong "owning" relationship

Use if: child entity cannot exist without parent;
Child is deleted when parent is deleted

Example: RequisitionHeader – RequisitionLine association


Behavior of Composition Association:-

  When child is dirtied, parent is dirtied automatically

  When child is locked, parent is locked first automatically

  Validation order is child first, parent second.

  parent has final veto power on child modifications

  Insert/Update order is parent first, child second

  Delete order is child first, parent second




View Object (VO):-

View Object is mainly used for Manage collection of data and to display them in OAF page.

View Object represents a query result.

View Objects are used for joining, filtering, projecting and sorting your business data.

View Object can be created in three ways:-

  Generated SQL based on Eos.

  Expert Mode custom SQL with no underlying Eos.

  Expert Mode custom SQL manually mapped to Eos.

View Object can be created on the bases of Single Entity Object or more than one Entity Object.

View Object can also be created from SQL statements.

            A VO controls many view rows.

Each view row represents a row of the data that is queried from the database.

When you want aliases in the query, make sure that the attribute settings in the VO wizard include the alias names.

All the view objects that you create are subclass of 

oracle.apps.fnd.framework.server.OAViewObjectImpl class.


While creating always create View Row Java class (ViewRowImpl) and accessors (setters/getters).

Each VO should have:

  <YourName>VO.xml
  <YourName>VOImpl.java (optional)
  <YourName>ViewRowImpl.java (required)

After creating the view object always attach that to AM.




View Link:-

            A view link is a link between view objects.

You can create view links by providing the following:
  Source and destination views
  Source and destination attributes

Use a view link to create a master-detail relationship between view objects.

  Allows dynamic synchronization between parent and child VO.

  Child rowset is ‘refreshed’ when the current parent row changes.



    Application Module (AM):-

            Application module serves as a container for related BC4J objects.

AM defines the logical data model and business methods needed support an application task.

AM is the  interface between the Client transactions and Data Base transactions.

Types of AM:-

  Root AM (The AM attached to main region is called Root AM).

  Nested AM(The AM attached to child region is called Nested AM).

All the pages are attached to the AM.

The entire application module that you create is subclass of 

oracle.apps.fnd.framework.server.OAApplicationModuleImpl class.



Path classes for EO , VO, AM on server


          EO-related (business logic) .java and .xml files in

oracle.apps.<application shortname>.<module>.schema.server

Example: oracle.apps.xxx.po.schema.server.______________

           

               AM and VO (UI-related logic) .java and .xml files in

oracle.apps.<application shortname>.<module>.server

Example: oracle.apps.xxx.po.server.______________

               Poplist- and LOV-related VO and AM .java and .xmlfiles in

oracle.apps.<application shortname>.<module>.poplist.server
and

oracle.apps.<application shortname>.<module>.lov.server





Clear BNE Cache for WebADI Changes

It Sometime happens that WebAdi Changes doesn't reflect once migrated in controlled instances. Here are the quick steps(Generally perfor...