Wednesday, March 23, 2016

Different Types of Tables in Oracle Apps,Changing existing report,

Different Types of Tables in Oracle Apps

1)      Interface Tables

2)      Base Tables

1)      Global Tables
2)      Org Specific Tables
3)      Language Specific Tables

Interface Tables :

These are intermediate tables between apps base tables and external tables. Oracle Program is used to transfer data from other tables to Apps Interface tables. Apps interface program is used to transfer data from Interface tables to Base tables.

Eg:
                  AP_INVOICE_HEADER_INTERFACE
                  AP_INVOICE_LINES_INTERFACE

Base Tables :

These are actual tables where the data will be stored. These tables will not be updated from the back end .
A from is dependent on one or more tables.

Eg:
                  AP_INVOICE_ALL
                   

            Based on storage of data, base tables are divided into following

1)      Global Tables
2)      Org Specific Tables
3)      Language Specific Tables

Global Tables :

            The data in this tables is not specific to any organization of a business. This data can be shared across all the organizations of business group.

            Eg:       Employs data, Suppliers data, Accounting data, Bank Transactions data

Org specific Tables :

            The data stored in this tables are specific to particular organization.
            Eg: Invoice Details
            Org Specific tables will have ALL as their suffix
            AP_INVOICE_ALL

Language Tables :

            Apps supports the multiple languages. These tables will maintain the language specific data.
            These table will have TL as it’s suffix




Different Types Columns in a table :

Eg :      MTL_SYSTEM_ITEMS_B

1) Data Columns :
These columns contains base transactional data, which is entered from the apps form.
            USER_NAME, DESCRIPTION, EMPLOYEE_ID, EMAIL_ADDRESS

2) Derived Columns :
     These are primary key columns. This data will be generated from oracle database sequence.
USER_ID

3) Who Columns :
          These are called as history columns. This data will be generated automatically by the oracle application form.




                        These columns are
1)      CREATED_BY     
2)      CREATION_DATE
3)      LAST_UPDATED_BY
4)      LAST_UPDATE_DATE
5)      LAST_UPDATE_LOGIN

Every apps table will have the above columns

4) Global Attribute Columns :
            These columns will maintain the functionality information, which is provided by the ORACLE Corp.
           
GLOBAL_ATTRIBUTE1
GLOBAL_ATTRIBUTE2, …. 10.

5) Additional Cols :
These columns are used to store the data, which is from additional fields added to the existing form during the customization of a form.
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3….. 15

6) Key Columns : These are called as segment columns. Used to maintain the key flex field data.
                        A field will have subfields
                        Eg:
                                    Account Num :
                                    COMPANY-COUNTRY-DEPARTMENT-ACCOUNT
                                    LG-IND-SALES-4523

            SEGMENT1, SEGMENT2, …. SEGMENT20.

           
Developing one more report and to application and execute

Eg:
Employees information report

            EmpId             EmpName                   Startdate

1)      develop the report
2)      transfer form client machine to server machine
3)      create executable
4)      create concurrent program and attach executable
5)      Create Request Group and attach  Concurrent Program
6)      create responsibility and attach  1)Request Group
                                                            2)Data    Group
                                                            3)Menu

7)      add the responsibility to user
8)      go to srs execute

Report with input Parameters

STEP 1: Develop the report with parameters

Requirement:

First Page :
                        Title from the user input
Second Page :

            UserId                         UserName                   CreationDate

Last Page :

                        Total Users :

Input parameters are

Enter From Id  :
Enter To Id :
Enter Title

1)      Create three parameter
P_From : number type (10)
P_To    : number type (10)
P_Title  : char (100)

2)      Data Model
Query :

SELECT USER_ID,
               USER_NAME,
               CREATION_DATE
FROM FND_USER
WHERE USER_ID BETWEEN :P_FROM AND :P_TO

                  Create Summary column for total count : USER_COUNT

3)      Layout model       

            Header Section : text filed à map with P_TITLE
            Main Section    : required layout and map the text fields with required fields
            Trailer Section  : Text Field à map with USER_COUNT
4) compile and test the report
5) save in the local machine : file name : user-param.rdf

STEP 2: transfer the file user-param.rdf   from local machine to the server

STEP 3 : LOGIN INTO APPLICATION

                        Create executable file

STEP 4:   Create concurrent program and attach the executable

                        Map the parameters
                        Click on parameters button ( at the bottom )

                        Seq                  : it is serial number ( unique value )
                        Parameter       :  Enter From Id  ( its prompt value )
                        Description     : as desired (optional)
                        Value Set         : data type ( 10/number pre-defined )
                        Token              : P_From ( it is a name of the input parameter in the report for mapping )
                        Required          : enable à mandatory field
                                                  Disable à optional filed
                        Range              : low  à for validation ( lower than other filed value )

STEP 5 : create request group and attach the concurrent program

STEP 6 : create responsibility and attach

                                    Request Group
                                    Data Group
                                    Menu

STEP 7 : add responsibility to user

STEP 8 : go to srs window

            Go to application home
            Select the any on the left side pane it shows current responsibility

            View à request
            Submit request


Change in the above report

            Lay out Model :

            Header Section :

                        Default Title : if user input is null it should be displayed

                        Select the label and place on the canvas ( user information report )

                        Write the trigger ( right click )


                        PL/SQL Editor :
                       
function B_5FormatTrigger return boolean is
begin
  if( :P_TITLE is null )then
  return (TRUE);
  else
            return
            false;
  end if;
end;

            Trailer Section :

                        Default label  : No data found ( it total no.of records are zero )
                       
                        Select the label and place on the canvas (No data found )

                        Write the trigger ( right click )


                        PL/SQL Editor :
                       
function B_6FormatTrigger return boolean is
begin
  if ( :USER_COUNT <=0 )THEN
           
  return (TRUE);
  else
            return false ;
  end if ;
 
end;

                        Text field it’s lable : disable if no records

                        PL/SQL Editor : ( for both same code )

if ( :USER_COUNT <=0 )THEN
           
  return false;
  else
            return true ;
  end if ;

Again Change in the above report

Using lexical parameters dynamically

          If input for both From Id and To Id is null where clause should be obtained dynamically

1)      Create lexical parameter : P_WHERE
2)            Query :

SELECT USER_ID,
               USER_NAME,
               CREATION_DATE
FROM FND_USER
&P_WHERE
Here lexical parameter is not reading form the user. It’s value populated dynamically. Hence it is not required to map  with  the application parameters

To populate dynamical write PL/CODE in the after parameter form trigger

function AfterPForm return boolean is
begin
           
            if ( :P_FROM is null and :P_TO is null ) then
                        :P_WHERE:='';
            else
                        :P_WHERE:='where USER_ID between :P_FROM and P_TO';
            end if ;
                       
  return (TRUE);
end;

CHECKING  THE REQUIRED OPTION : enable ( mandatory field )

Go to Create concurrent program à parameters à enable the fields

Working with date :

Requirement

Input :
            From Date :
            To Date :

            UserId                                     UserName                               CreationDate


            Total Users :

Step1: Develop the report
1)      Create Parameters
            P_FROM  :    Date type : date,  input mask : YYYY/MM/DD HH24:MI:SS
            P_TO        :    Date type : date,  input mask : YYYY/MM/DD HH24:MI:SS

2)      Data Model
            Query

            SELECT  USER_ID,
                USER_NAME,
            CREATION_DATE
            FROM FND_USER
            WHERE CREATION_DATE BETWEEN :P_FROM AND :P_TO

            create a summary column  : TOTAL_COUNT

3)      Layout  Model

            Header Section :
            Prepare the required title



            Main Section:

                        UserId                                      UserName                                CreationDate

                        Trailer Section :

                                    Total Users :

Repeat the other steps such registry and running as explained in the previous examples

Step 5 : while creation of concurrent program :
            Parameters :
            Pre defined Date value set : FND_STANDARD_DATE


In all the above example we have followed following steps
           
1)      Executable
2)      Concurrent Program
3)      Request Group
4)      Responsibility
5)      Users
6)      SRS

Here the question is why not use executable directly with users ?

What is the advantage in using Concurrent program ?
            Purpose :  single exe can be created with multiple Concurrent Programs
                                    with different input parameters
                                    with different output forms
                                    with different applications

different input parameters
eg:
            Client1 requires : only one parameter ( From Date)
           
            Client2 requires : two parameters ( From Date )
                                                              ( To Date )
different output forms:

eg:

            Client1 requires in PDF format
            Client2 requires in XML format
            Other client requires in excel sheet format


What is the advantage in using Request group ?

            To group the multiple programs

What is the advantage in using Responsibility  group ?
            Menu à we can add different menus with different Concurrent Program and Form can also be attached
            Request Group : Nothing
            Data Group  : Nothing

Default Types
1)      Constant
2)      SQL Statement
3)      Segment
4)      Current Date
5)      Current Time
6)      Profile

Constant
                        To pass the constant values such as numbers, chars, String , Date.
                        Enter From Id
                        Enter To Id
                        Enter Tile :
                        Eg:
                        Enter Tile :
                        Default Type : constant           Default Value : Users Information Report
SQL Statement
                        Enter From Id
                        Enter To Id
                        Enter Tile :

                        Enter From Id :
                        Default Type : sql statement   Default Value : select min(user_id) from fnd_user
The above select query returns user_id : -1, -ve value will not be considered for 10/number, we have to change data type as 15 char

                        Enter To Id :
                        Default Type : sql statement   Default Value : select max(user_id) from fnd_user

Segment :
                        This is to get previous parameter value to the next parameter
                        Enter From Id:
                        Enter To Id :
                        Default Type: segment             Default Value : Enter From Id

Value Sets :     System provides built-in ( predefined ) value sets
Value set is a list of values with validations which will be used to restrict the user without entering the invalid data in the Parameters
                        value sets are used in two scenarios.
                                    1)Concurrent Program parameters
                                    2)Flex fields


No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...