ORA-01791: not a SELECTed expression during valeset creation

I have come across this error while i was trying to use a select statement in the table column while creating the Table value set.I used the below sql statement

SELECT DISTINCT XIH.BATCH_NO FROM XXMCNG_IREQ_HEADERS XIH ORDER BY XIH.REQ_DATE;

Cause : There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

Action :
Remove the order by clause from the SQL statement.
(or)
create a view with the same query and the call the view in the Table column while creating the Table value set.

Note :
=====
Restrictions on the ORDER BY Clause.The following restrictions apply to the ORDER BY clause:

•If you have specified the DISTINCT operator in the statement,then this clause cannot refer to columns unless they appear in the select list.
•An order_by_clause can contain no more than 255 expressions.
•You cannot order by a LOB, LONG, or LONG RAW column, nested table, or varray.
•If you specify a group_by_clause in the same statement, then this order_by_clause is restricted to the following expressions:

◦Constants
◦Aggregate functions
◦Analytic functions
◦The functions USER, UID, and SYSDATE
◦Expressions identical to those in the group_by_clause
◦Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group.

 Also See : Creating Table Valuesets from backend in Oracle Applications.

Challa.

Amount in Words using XML/BI Publisher Tags

I once had a requirement to display the amount(number) to words(letters). And below is the approach i have followed.

We can convert the Amount into Words in two different ways:

1)Using the XML/BI Publisher Tag (Applicable only to R12).

2)Using the function IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TOTAL_AMOUNTS1)

1) Using XML/BI Publisher Tag:

a)  

--> CASE_INIT_CAP, CASE_UPPER, and CASE_LOWER are available to display the text  in Initial Caps,Upper Case,Lower Case respectively.

--> DECIMAL_STYLE_FRACTION1(default), DECIMAL_STYLE_FRACTION2, and DECIMAL_STYLE_WORDS are available.

--> USD in the above tag can be replaced by the curreny code required as per the requirement.

b)  

[ Sorry, there was a problem writing the tag in my browser,so i have taken a snapshot of it and pasted here. ]

2)  Using IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TOTAL_AMOUNTS1)

 Example :

declare
v1 VARCHAr2(2000);
begin
v1:=IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(p_amount =>101324546014);
dbms_output.put_line('amount: '||v1);

end; 

If you expect the Amount in Words to show as shown below

--> One Thousand Pounds and 14 Pence.

 --> One Hundred Euros and 1 Cent.
--> 1 Dollar and 99 Cents.

  

Refer the Oracle Document for setting the currency unit and sub_unit in the system. Click ME.

Challa. 

Display Message from FND_MESSAGES using Forms Personalization.

Responsibility: Application Developer
Navigation: Application > Messages



Create new message
Name: XX_TEST
Current Text Message: This is a test message for &USER_NAME



Save and close form.
Generate the message
Responsibility: Application Developer
Click on View > Requests in the menu to execute a concurrent program. Select Generate Messages program.





Click on Help > Diagnostics > Custom Code > Personalize
Create a new Personalization





Click on Actions

Sequence 10
Type: Builtin
Description: Retrieve the msg
Builtin Type: Execute a procedure
Argument: FND_MESSAGE.SET_NAME(‘XXCUST’, ‘XX_TEST’)




Sequence 11

Type: Builtin
Description: Set the USER token
Builtin Type: Execute a Procedure
Argument: fnd_message.set_token (‘USERNAME’, fnd_profile.value(‘USERNAME’))



Sequence 12

Type: Builtin
Description: Set the ORG token
Builtin Type: Execute a Procedure
Argument: fnd_message.set_token (‘ORG_ID’, fnd_profile.value(‘ORG_ID’))



Sequence 13
Type: Message
Description: Display the msg
Message Type: Show
Message Text: =FND_MESSAGE.GET





Challa.

To Get the XML file or Request Output file for XML Reports Concurrent Requests.

Get the XML File from a BI Publisher based concurrent request/report:

Get the XML File from a BI Publisher based concurrent request/report:

1.View->Requests
2.Find the request you're interested in
3.Click on Diagnostics button
4.Click the View XML

5.Save the file to your PC by doing File->Save As, *.xml

But where does the XML file get stored.?

Well, by default it is $APPLCSF/$APPLOUT/o{REQUEST_ID}.out.

But if that's the XML file, then where does the actual output file reside as the location of the XML file is where normal request output resides?
Get the Request Output File for a BI Publisher based concurrent request/report:

The output is $APPLCSF/$APPLOUT/{REPORTNAME}_{REQUEST_ID}_{COUNT}.PDF|RTF|EXCEL|HTML

Where

>  REPORTNAME is the concurrent program short name,
>  REQUEST_ID is the concurrent request ID
>  COUNT is a counter based on the number of times a request has been re-published.
>  One of PDF, RTF, EXCEL, HTML is the file extension/type dependent on the output formats chosen.

An example is: XXXX_FNDSCURS_2803880_1.EXCEL

Output file location courtesy of Tim's post here.

Cool, file locations identified..!!

Challa.

FNDLOAD

LDT is a file extension for a data file used with Oracle applications. LDT stands for Loader DaTa files. LDT files contain entity definitions, parent-child relationships, input parameters and data. LDT files to an LCT file which is used for the configuration. LDT files can be exported from the Oracle database using the FNDLOAD function.

LDT files can be opened and edited in a text editor, although doing so is not recommended.

Printer Styles : 
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct XX_FILE_NAME.ldt STYLE PRINTER_STYLE_NAME="printer style name"

Lookups: 
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_FILE_NAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name"

Descriptive Flexfield with all of specific Contexts:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FILE_NAME.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

Multiple Flexfields:

Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names

ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields
DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with ‘PER_’.

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FILE_NAME.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"

Key Flexfield Structures:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FILE_NAME.ldt KEY_FLEX P_LEVEL=:COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

Concurrent Programs: 
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_FILE_NAME.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"

Value Sets:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FILE_NAME.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

Value Sets with values:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FILE_NAME.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

Profile Options:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_FILE_NAME.ldt PROFILE PROXX_FILE_NAME="profile option"
APPLICATION_SHORT_NAME="prod"

Request Group:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_FILE_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"

Request Sets:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_FILE_NAME.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"

DOWNLOAD:
-- Request set Def --
FNDLOAD apps/$apps_pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct REQ_SET REQUEST_SET_NAME=

-- Request set link --
FNDLOAD apps/$apps_pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct REQ_SET_LINKS REQUEST_SET_NAME=

UPLOAD :
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct

FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct

Responsibilities:
FNDLOAD apps/pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FILE_NAME.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

Responsibilities with all Security Groups:
FNDLOAD apps/ 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct .ldt
FND_USER USER_NAME="" SECURITY_GROUP=% DATA_GROUP_NAME=%

Notes for using FNDLOAD against FND_USER :-

1. After uploading using FNDLOAD, user will be promoted to change their password again during their next sign-on attempt.

2. All the responsibilities will be extracted by FNDLOAD along with User Definition in FND_USER

3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users.

Menus:
FNDLOAD apps/ pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FILE_NAME.ldt MENU MENU_NAME="menu_name"

Forms/Functions/Personalizations:
FNDLOAD / 0 Y DOWNLOAD FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FILE_NAME.ldt FUNCTION FUNCTION_NAME=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_FILE_NAME.ldt FORM FORM_NAME=

OR

FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES function_name=

User/Responsibilities:
FNDLOAD apps/ pwd@database 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_FILE_NAME.ldt FND_USER

Alert:
FNDLOAD apps/pwd 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to download

Blob:
With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

Overwrite custom definitions:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/.lct $XX_TOP/import/.ldt CUSTOM_MODE=FORCE

Load an NLS Language:
FNDLOAD / 0 Y UPLOAD \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE

Migrate the role registration process from one instance to another:

a. Please navigate to the path: $FND_TOP /patch/115/import/US/umxrgsvc.ldt
b. The following command can be used to download:

FNDLOAD apps/@(instance name) O Y DOWNLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

c. The following command can be used to upload:

FNDLOAD apps/@(instance name) O Y UPLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

Transfer Custom Messages to another Instance:

a. Download the message from the source instance.

FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND MESSAGE_NAME=PASSWORD-INVALID-NO-SPEC-CHAR

b. Move the custom LDT file (password.ldt) over to the destination instance.

c. Upload the custom message to the destination instance.

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND CUSTOM_MODE=FORCE

Download UMX Roles and Role Assignment data from one instance and upload to another:

To download from one instance:

FNDLOAD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt WF_ROLE ORIG_SYSTEM=UMX%

To upload to another instance:
FNDLOAD 0 Y UPLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt

Notes:

1.Give special attention when downloading Menus or Responsibilities. In the case for several developers modifying Responsibilities and Menus, then be very careful. Not being careful will mean that untested Forms, Functions, and Menus will become available in the clients Production environment besides the tested Forms, Functions, and Menus.

2.Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes. By doing so, downloading and extracting all the test data in GL Codes that might not be applicable for production.

3.There are several variations possible for FNDLOAD. For example, restricting the download and upload to specific segments within Descriptive Flexfields.

4.FNDLOAD is very reliable and stable, if used properly.

5.Please test the FNDLOAD properly, so as to ensure that no unexpected data occurs.

6.As the name suggests, FNDLOAD is useful for FND related objects. However, in any implementation, its required to migrate the Setups in Financials and Oracle HRMS from one environment to another. Oracle iSetup can be used for this. Some of the things that can be migrated using Oracle iSetup are GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes & Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

XML DATA DEFINITION:

DOWNLOAD
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=DATA_SOURCE_CODE= TMPL_APP_SHORT_NAME=

UPLOAD
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct

The Above command downloads all the templates defined for this Particular Data Definition. For a particular template to be downloaded use

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=DATA_SOURCE_CODE= TMPL_APP_SHORT_NAME==

ALERTS:

DOWNLOAD 

$FND_TOP/bin/FNDLOAD apps/coco 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct ALR_ALERTS APPLICATION_SHORT_NAME=< application short name> ALERT_NAME=

UPLOAD
FNDLOAD apps/coco 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct

/*You can use some additional parameters such as*/

* ALR_DISTRIBUTION_LISTS APPLICATION_SHORT_NAME=AD where           APPLICATION_SHORT_NAME represents the Application Alert owner
* ALR_LOOKUPS
* ALR_MESSAGE_SYSTEMS
* ALR_ORACLE_MAIL_ACCOUNTS
* ALR_PROFILE_OPTIONS
* ALR_PERIODIC_SETS APPLICATION_SHORT_NAME=ALR

AME RULES:
CONDITIONS:
The script that downloads AME conditions allows you to download all conditions for a given transaction type or only those associated with a particular attribute or group of attributes.

DOWNLOAD
FND_TOP apps/ 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesconk.lct AME_CONDITIONS CONDITION_KEY= TRANSACTION_TYPE_ID=
APPLICATION_SHORT_NAME=

UPLOAD
FNDLOAD apps/@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesconk.lct

Dynamic Approval group /Approver Groups:

An approver group can either be an ordered set of one or more approvers (persons and/or user accounts) or it can be a list, which is dynamically generated at rule evaluation time.

DOWNLOAD
FND_TOP apps/ 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesappg.lct AME_APPROVAL_GROUPS APPROVAL_GROUP_NAME= TRANSACTION_TYPE_ID=
APPLICATION_SHORT_NAME=

UPLOAD
FNDLOAD apps/@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesappg.lct

Dynamic Approval group config:

DOWNLOAD
FNDLOAD apps/@sourcedb 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesaagc.lct ameapprovalgroupusage.ldt AME_APPROVAL_GROUP_CONFIG APPROVAL_GROUP_NAME='Dyn. Post HROPs Approval Group' TRANSACTION_TYPE_ID='HRSSA' APPLICATION_SHORT_NAME='PER'

UPLOAD
FNDLOAD apps/@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesaagc.lct ameapprovalgroupusage.ldt

AME Rule: 

An approval rule is a business rule that helps determine a transactional approval process. Rules are constructed from conditions and actions.
The AME rules can be downloaded for information about the rule (e.g. name, description, etc) along with associated conditions and rule type.

DOWNLOAD
$FND_TOP/bin/FNDLOAD apps/ 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesrulk.lct AME_RULES RULE_KEY= TRANSACTION_TYPE_ID=
APPLICATION_SHORT_NAME=

# You can find Rule Key in AME_RULES table

UPLOAD
FNDLOAD apps/@destinationdb 0 Y UPLOAD $PER_TOP/patch/115/import/amesrulk.lct XX_FILE_NAME.ldt

AME Rule Action Type Usage:

DOWNLOAD
FNDLOAD apps/ 0 Y DOWNLOAD amesactu.lct .ldt AME_ACTION_USAGES APPLICATION_SHORT_NAME= TRANSACTION_TYPE_ID= [RULE_KEY=]

# Rule Key is found in AME_RULES table

UPLOAD
FNDLOAD apps/apps 0 Y UPLOAD amesactu.lct .ldt

Transaction Types:

An application that uses AME to govern its transactions approval processes is termed an integrating application. An integrating application may divide its transactions into several categories where each category requires a distinct set of approval rules. Each set of rules is called a transaction type. Different transaction types can use the same attribute name to represent values that are calculated in different ways or fetched from different places.

DOWNLOAD
FNDLOAD apps/ 0 Y DOWNLOAD amescvar.lct .ldt AME_CALLING_APPS APPLICATION_SHORT_NAME= TRANSACTION_TYPE_ID=

UPLOAD
FNDLOAD apps/ 0 Y UPLOAD amescvar.lct .ldt

Attribute:

DOWNLOAD
FNDLOAD apps/ 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesmatt.lct AME_ATTRIBUTES ATTRIBUTE_NAME= TRANSACTION_TYPE_ID=
APPLICATION_SHORT_NAME=

UPLOAD

FNDLOAD apps/ 0 Y UPLOAD amesmatt.lct .ldt

Attribute Usage:

DOWNLOAD
FNDLOAD apps/ 0 Y DOWNLOAD $PER_TOP/patch/115/import/amesmatr.lct .ldt AME_ATTRIBUTE_USAGES ATTRIBUTE_NAME= TRANSACTION_TYPE_ID=
APPLICATION_SHORT_NAME=

UPLOAD
FNDLOAD apps/ 0 Y UPLOAD amesmatr.lct .ldt

WEB ADI integrator:
FNDLOAD apps/ @ 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneint.lct BNE_INTEGRATORS INTEGRATOR_ASN= "PER" INTEGRATOR_CODE=<"Integrator code">

Web ADI contents:
FNDLOAD apps/ @0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct BNE_CONTENTS CONTENT_ASN="PER" CONTENT_CODE=<"Content code">

Web ADI mappings:
FNDLOAD apps/ @0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct BNE_MAPPINGS MAPPING_ASN="PER" MAPPING_CODE=<"mapping code">

Web ADI layout:
FNDLOAD apps/ @0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct BNE_LAYOUTS LAYOUT_ASN="PER" LAYOUT_CODE =

Web ADI parameter list:
FNDLOAD apps/ @0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneparamlist.lct< file name> BNE_PARAM_LISTS PARAM_LIST_ASN="PER" PARAM_LIST_CODE=<"parameter list code">

WEB ADI component:
FNDLOAD apps/ @0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecomp.lct BNE_COMPONENTS COMPONENT_ASN="PER" COMPONENT_CODE=<"Component code">

Post-Accounting Programs SLA:
Downloading Post-Accounting Programs using the following syntax:

FNDLOAD /[@connect]0 Y DOWNLOAD @XLA:patch/115/import/xlapgseed.lct XLA_POST_ACCT_PROGS APPLICATION_ID=

Uploading Post-Accounting Programs using the following syntax:

FNDLOAD /[@connect]0 Y UPLOAD @XLA:patch/115/import/xlapgseed.lct

Work Flows:

Download
WFLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD

Upload
WFLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD



Challa.

Creating Table valuesets from backend in oracle applications

1) Creation of value set through API :

BEGIN
/*You need to initialize the session mode before the call*/ 
FND_FLEX_VAL_API.SET_SESSION_MODE('customer_data');/*Mandatory*/ 
FND_FLEX_VAL_API.CREATE_VALUESET_TABLE
(
 VALUE_SET_NAME =>'PO_TEST_VALUE_SET',
 DESCRIPTION =>'createdfrombackend',
 SECURITY_AVAILABLE =>'N',
 ENABLE_LONGLIST =>'N',
 FORMAT_TYPE   =>'Char',
 MAXIMUM_SIZE =>20,
 precision => NULL,
 numbers_only =>'N',
 uppercase_only  =>'N',
 right_justify_zero_fill =>'N',
 min_value  => NULL,
 MAX_VALUE   => NULL,
 TABLE_APPLICATION => 'Purchasing',
 table_appl_short_name =>'PO' ,
 TABLE_NAME =>'PO_REQUISITION_HEADERS PRH',
 ALLOW_PARENT_VALUES =>'N',
 VALUE_COLUMN_NAME =>'PRH.SEGMENT1',
 VALUE_COLUMN_TYPE  =>'Char',
 value_column_size  =>20,
 meaning_column_name  => NULL,
 meaning_column_type  => NULL,
 MEANING_COLUMN_SIZE  => NULL,
 ID_COLUMN_NAME    =>NULL,--'PRH.SEGMENT1',
 ID_COLUMN_TYPE   =>NULL,--'Char',
 ID_COLUMN_SIZE   =>null,--u20,
 WHERE_ORDER_BY    =>'where rownum<=100',
 ADDITIONAL_COLUMNS => NULL
  );
commit;
  Exception
  WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);
  end;

After executing the above code it will generate a message saying Anonymus Block created. To verify that if it is created. Please check the table Fnd_Flex_Value_Sets

select * 
from fnd_flex_value_sets 
where flex_value_set_name = 'PO_VALUE_SET';


2) Deletion of Valueset can be done by executing the below code :

BEGIN
FND_FLEX_VAL_API.DELETE_VALUESET(VALUE_SET => 'PO_VALUE_SET');
END;

This will delete the Valueset.

3) Query to extract all the valusets :

/*Query to extract all the valuesets*/
SELECT FFVS.FLEX_VALUE_SET_ID ,
FFVS.FLEX_VALUE_SET_NAME ,
FFVS.DESCRIPTION SET_DESCRIPTION ,
FFVS.VALIDATION_TYPE,
FFV.FLEX_VALUE,
FFVT.DESCRIPTION VALUE_DESCRIPTION,
FFV.ENABLED_FLAG,
FFV.LAST_UPDATE_DATE,
FFV.LAST_UPDATED_BY,
FFV.ATTRIBUTE1,
FFV.ATTRIBUTE2,
FFV.ATTRIBUTE3 --INCLUDE ATTRIBUTE VALUES BASED ON DFF SEGMENTS
FROM FND_FLEX_VALUE_SETS FFVS ,
            FND_FLEX_VALUES FFV ,
            FND_FLEX_VALUES_TL FFVT
WHERE
FFVS.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_ID = FFVT.FLEX_VALUE_ID
AND FFVT.LANGUAGE = USERENV('LANG')
AND FLEX_VALUE_SET_NAME LIKE 'VALUE_SET_NAME'
ORDER BY FLEX_VALUE ASC;

Challa.

OVER and PARTITION BY - Analytic Function

I have come across a situation to sum-up a particular column of a table without using the group by clause. And below is the way i tackled it using the Analytic Function(OVER and PARTITION BY).

OVER :

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, the below query:

SELECT SUM(Cost) OVER () AS Cost, OrderNum
FROM Orders;

Will return something like this:

Cost        OrderNum
10.00            345
10.00            346
10.00            347
10.00            348

Quick translation :

SUM(cost) –  get me the sum of the COST column

OVER –   for the set of rows

() –   that encompasses the entire result set.

OVER(PARTITION BY) :

OVER, as used in our previous example, exposes the entire result-set to the aggregation…”Cost” was the sum of all [Cost]  in the result-set.  We can break up that result-set into partitions with the use of PARTITION BY:

SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo
FROM Orders;

My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:

Cost  OrderNum   CustomerNo
 8.00      345                1
 8.00      346                1
 8.00      347                1
 2.00      348                2

Quick translation :

SUM(cost) – get me the sum of the COST column

OVER – for the set of rows….

(PARTITION BY CustomerNo) – …that have the same CustomerNo.

For more Analytic Functions Click Me.

Challa.

BI Publisher Template Viewer

BI Publisher Template Viewer

In the times of BIP 11g, online layout editing and frolicking free data modeling, a wee little tool is almost forgotten: The BI Publisher Template Viewer. Never heard of it? Well, read on then...

The template viewer is a handy little tool installed along the BI Publisher Desktop (aka Word Add-In or Template Builder). It's main purpose is to assist in debugging non-RTF templates such as eText or Excel templates but it can be used for any other template type (excluding the new .xpt templates built using the 11g online editor).

If you launch it from the respective Start menu item on your Windows workstation, it presents itself as a simple Java application.



The usage is plain simple, for example:

1. To debug a template you use the Browse... button to specify a working directory. The directory must contain at least one sample XML file and at least one template file. You could give it a first try with the sample folders delivered with the BI Publisher Desktop.

2. In the Data window, select the sample XML file to use.

3. In the Template window, select the template to use.

4. Specify an output format and locale and click the Start Processing button.

Depending on the log level (see below), verbose output is generated and finally the document viewer (such as Adobe Acrobat Reader) is launched to display the generated output document. Of course, errors are displayed in the output window if the template is buggy.

The debug level and report parameters can be set in the Setting (Default) tab:


In the above example, the log level is set to STATEMENT (Maximum Log).




Challa.