Tuesday, July 24, 2012

Query Modes in OAF Query Region


Construction Modes:
There are three construction modes available. Here is a brief comparison of the three modes.

1] resultsBasedSearch:
  • OA Framework automatically renders both the Simple and Advanced search regions based on the designated queryable items in the associated table.
  • The search regions automatically include both a Go and a Clear button.
  • OA Framework automatically executes the underlying search when the user selects the Go button.

2] autoCustomizationCriteria:
  • OA Framework automatically renders both the Simple and Advanced search regions based on the corresponding Simple search and Advanced search regions that you define and specify as named children of the query region.
  •  The search regions automatically include a Go button. In addition, the Advanced search region includes a Clear button.
  • OA Framework automatically executes the underlying search when the user selects the Go button. However, developers must explicitly define mappings between items in the Search panel and items in the table region.

3] none
  • The Search regions are rendered based on the Simple Search and Advanced Search regions that you define and specify as named children of the query region.
  • You must implement your own Go button in this mode.
  • The underlying search must be executed by the developer.

Thursday, July 5, 2012

Formula column, Summary column and Place Holder column



In Oracle reports we use Formula column, Summary column and Place Holder column in different scenarios, this post is to avoid confusion and give clear picture as when and where we need to use these columns.


Formula column

We use formula column to calculate some information dynamically using information based on the columns of the data model or from the concurrent program parameters. It is basically used to apply some custom logic on input data and return some value.
Formula columns can be written using PL/SQL syntax and we can use pl/sql functions for computation on the data. Formula column will accept data of Character, Number, or Date type.


Inside or outside data group?
If we want to calculate a value for every row place the formula column in the group of the data model , so that it called every time for every record and if we want to derive a value at report level place the formula column outside to the data group.
Formula columns are generally preceded by CF_ to distinguish from other columns. Column names or parameters with a prefix ‘:’  in formula column are considers as input to a formula column.


Examples:
  1. Comparison of data in two different columns/variables and perform some action.
  2. Using some standard oracle pl/sql functions to find out some values like, finding out the master org using oracle standard function as shown below
    oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id())
  3. When you need to use any If-else block
  4. To execute some sql query to find out email-address of a party using party_id
Note: Formula column should return some or the other value.


Summary column

Summary columns are used for calculating summary information like sum, average etc,. on specific columns of a data group.  This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.
The datatype of a summary column depends on the data type of the source of the summary. If you change the data type of the source column, the datatype of the summary also changes.
In report layout summary columns should be used out of repeating frames. Summary columns will have only one parameter as input.
Below are the standard functions provided by Oracle  for a summary column


Function
Purpose
AverageCalculates the average of the column’s values within the reset group.
CountCounts the number of records within the reset group.
FirstPrints the column’s first value fetched for the reset group.
LastPrints the column’s last value fetched for the reset group.
MaximumCalculates the column’s highest value within the reset group.
MinimumCalculates the column’s lowest value within the reset group.
% of TotalCalculates the column’s percent of the total within the reset group.
Std. DeviationCalculates the column’s positive square root of the variance for the reset group.
SumCalculates the total of the column’s values within the reset group.
VarianceSums the squares of each column value’s distance from the mean value of the reset group and divides the total by the number of values minus 1.
All the above functions work only for number datatype column and return number datatype.



Place Holder column

Place holder column is an empty container at design time which is used to hold some value in run time, they are like local/global variables which can be used in the logic of a formula column or in report triggers. Value in the place holder column can be directly accessible in the report layout.


Inside or outside data group?
We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.

Page Numbering in XML Publisher/MS Word


Open the footer and press [Alt][F9] to display field codes. You should see this: If you dont get these fields, select header and press [Ctrl][F9]. Word displays empty braces {}. Enter field names PAGE or NUMPAGES inside the curly braces as shown below.

Page { PAGE } of { NUMPAGES }

Highlight { NUMPAGES} and press [Ctrl][F9] to create a new field that nests the Numpages field, like this:

Page { PAGE } of { { NUMPAGES} }

The insertion point will be where you need it, between the two opening braces, so just type =.
Click between the two closing braces and type -1. Here's what you should have:

Page { PAGE } of { = { NUMPAGES} -1 }

Highlight the contents of the footer and press [F9] to update the fields.
Press [Alt][F9] to switch to results mode.

FNDLOAD Scripts

Here are the FNDLOAD Scripts to Migrate 15 different AOL Entities

    Profile Options
    Forms
    Functions
    Menus
    Responsibilities
    Request Groups
    Request Sets
    Lookups
    Valuesets
    Descriptive Flexfields
    Key Flexfields
    Concurrent Programs
    Form Personalization
    Fnd Users
    Alerts

1.Profile Options:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
PROFILE PROFILE_NAME=”XXPRNAME” APPLICATION_SHORT_NAME=”PN”
Target:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=” XXPRNAME” APPLICATION_SHORT_NAME=”PN”

2.Forms:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME=”PN” FORM_NAME=”XXFRMNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt


3.Functions:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME=”PN” FUNCTION_NAME=”XXFUNNAME”
Target:
FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt


4.Menus:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt MENU MENU_NAME=”XXMNNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXMNNAME.ldt


5.Responsibilities:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME”
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME” APPLICATION_SHORT_NAME=”PN”


6.Request Groups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”PN”
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”PN”


7.Request Sets:
Source:
1. FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME=”XXRQSNAME”
2. FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME=”XXRQSNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt


8.Lookups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”PN” LOOKUP_TYPE=”XXLKPNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD aflvmlu.lct XXLKPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE=”XXLKPNAME” APPLICATION_SHORT_NAME=”PN”


9.Value sets:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD afffload.lct XXVALSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME” APPLICATION_SHORT_NAME=”PN”


10.Descriptive Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME=”PN”
DESCRIPTIVE_FLEXFIELD_NAME=”PN_LEASE_DETAILS” P_CONTEXT_CODE=”Global Data Elements”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXDFFNAME.ldt


11.Key Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’
APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXKFFNAME.ldt


12.Concurrent Programs:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM APPLICATION_SHORT_NAME=”PN” CONCURRENT_PROGRAM_NAME=”XXCPNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct XXCPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME=”XXCPNAME” APPLICATION_SHORT_NAME=”PN”


13.Form Personalization:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name=”XXFPNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt


14.FND Users:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XXUSERNAME.ldt FND_USER USER_NAME=’XXUSERNAME’
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./ XXUSERNAME.ldt


15.Alerts:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name=”XXALERTNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name=”XXALERTNAME”

Source: http://oracleappsdna.com/2011/08/fndload-scripts-to-migrate-aol-entities/
Related Posts Plugin for WordPress, Blogger...