APP-PAY-6956 Element entry overlaps with another entry of the same type

This script will identify a date data problem with element entries:

SELECT   '*' e, pee.assignment_id, pee.effective_start_date,

         pee.effective_end_date, pee.element_entry_id

    FROM pay_element_entries_f pee

   WHERE pee.creator_type = 'SP'

     AND EXISTS (

            SELECT pee2.effective_start_date

              FROM pay_element_entries_f pee2

             WHERE pee2.assignment_id = pee.assignment_id

               AND pee2.creator_type = 'SP'

               AND pee2.element_entry_id != pee.element_entry_id

               AND pee2.element_link_id = pee.element_link_id)

     AND NOT EXISTS (

            SELECT ppp.change_date

              FROM per_pay_proposals ppp

             WHERE ppp.change_date = pee.effective_start_date

               AND pee.assignment_id = ppp.assignment_id)

UNION

SELECT   ' ' e, pee.assignment_id, pee.effective_start_date,

         pee.effective_end_date, pee.element_entry_id

    FROM pay_element_entries_f pee

   WHERE pee.creator_type = 'SP'

     AND EXISTS (

            SELECT pee2.effective_start_date

              FROM pay_element_entries_f pee2

             WHERE pee2.assignment_id = pee.assignment_id

               AND pee2.creator_type = 'SP'

               AND pee2.element_entry_id != pee.element_entry_id

               AND pee2.element_link_id = pee.element_link_id)

     AND EXISTS (

            SELECT ppp.change_date

              FROM per_pay_proposals ppp

             WHERE ppp.change_date = pee.effective_start_date

               AND pee.assignment_id = ppp.assignment_id)

ORDER BY assignment_id, effective_start_date

Read more

APP-07599 Supervisor is not valid for the duration of the assignment

Assigning a newly hired supervisor to an existing employee; system only accepts "Update" and changes the assignment date. 

The assignment date is not applicable to the supervisor but to the position/job being held by the employee.

The problem is the supervisor's start date is later than the employee's start date.

In pure business terms, the notion of recording a supervisor for a given date as one who hasn't been hired yet doesn't make sense.

Read more

ORA-06502: PL/SQL: numeric or value error

Cause:

Error 1: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL

Error 2:  if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action:


Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.




Read more

ORA-01400: cannot insert NULL into (string)

Cause:  An attempt was made to insert a NULL into the column "USER"."TABLE"."COLUMN".

For Example,

I created a table as below

CREATE TABLE DEPT

(

DEPT_NO      NUMBER NOT NULL,

DEPT_NAME    VARCHAR2(240) NOT NULL);

When i tried to execute this statement, i got the following error as shown.

INSERT INTO DEPT

(DEPT_NO)

VALUES

(

101

)

Action:

Retry the operation with a value other than NULL.

INSERT INTO DEPT

(DEPT_NO)

VALUES

(

101,

'IT'

)

Read more

How to integrate a report (RDF) with Oracle Apps

Once you have developed the report in Oracle Report builder, you need to integrate it with Oracle Applications, so that users could run it as a concurrent program request.

I am assuming that you have created the report (RDF), so lets start integrating with Oracle.


1) Navigation: System Administrator --> Concurrent --> Program --> Executable

Executable: Name of the executable file name of your choice. It is not referenced anywhere in the system.
Short Name: This name is referenced when we define the report and its parameters. This name is also referenced when we register XML report with Oracle. See below.
Application: Name of the application, preferably you should create a custom schema.
Execution Method: Oracle Reports
Executable File Name: The file name which is placed at server.


2) Navigation: System Administrator --> Concurrent --> Program --> Define

Program: Name of the concurrent program which will be shown to user, when one tries to run it.
Short Name: This name is not used anywhere for reference. so your choice. Usually we give it same as Executable short name.
Application: Name of Application.
Executable-> Name: Short Name of the Report Executable we defined in Step 1.
Output-> Format: XML if you want report in XML Format, else select any of your requirement.
Style: Portrait, Landscape, whichever is best suited to your report dimension.

Preferably we go with default values. If your requirement is different, select your relevant option. Save the form and press Parameters button.

Here is some detail before you define parameters.

Sequence

Choose the sequence numbers that specify the order in which your program receives parameter values from the concurrent manager.

Enabled

Disabled parameters do not display at request submission time and are not passed to your execution file.

Argument Detail

You specify information about your parameter almost exactly as you define a flexfield segment.

Value Set

Enter the name of the value set you want your parameter to use for validation. You can only select from independent, table, and non-validated value sets.

The maximum size of your value set is 240 characters.

Attention:  If you are using a value set of dates, this value set should have a format type of either Standard Date or Standard DateTime if you are using the Multilingual Request feature.

Default Type

If you want to set a default value for this parameter, identify the type of value you need.

• This default value for your parameter automatically appears when you enter your parameter window. 
• You determine whether the default value is a constant or a context-dependent value by choosing the default type.
• Your default value should be a valid value for your value set. Otherwise you see an error message when you enter your parameter window on the Run Request window and your default value does not appear.

Valid values for each default type include: 

Constant:    The default value can be any literal value.

Profile:        

• The default value is the current value in the user profile option defined in the Default Value field. 
• Use the profile option name, not the end-user name. You do not need to include $PROFILE$.

SQL Statement: 

        The default value is determined by the SQL statement you defined in the Default Value field.

Segment:  The default value is the value entered in a prior segment of the same parameter window.

Enable Security

If the value set for this parameter does not allow security rules, then this field is display only. Otherwise you can elect to apply any security rules defined for this value set to affect your parameter list.

Range

Choose either Low or High if you want to validate your parameter value against the value of another parameter in this structure.

Display

Indicate whether to display this parameter in the Parameters window when a user submits a request to run the program from the Submit Requests window.

Display Size

Enter the field length in characters for this parameter.

Description Size

Enter the display length in characters for the parameter value description.

Prompt

A user sees the prompt instead of the parameter name in the Parameters window of the Submit Requests window.

Concatenated Description Size

Enter the display length in characters for the parameter value description. 

The user sees the parameter value in the Parameter Description field of the Submit Requests and View Requests forms. 

The Parameter Description field concatenates all the parameter values for the concurrent program. 

Token

For a parameter in an Oracle Reports program, the keyword or parameter appears here. The value is case insensitive. For other types of programs, you can skip this field. 

At this point, no user will be able to execute this concurrent program request.

So what we need to do so is, to attach this report to a request group, which is specified on the responsibility. Eventually, Users to which responsibility is attached, will be able to run the report.

So navigate to

Navigation: System Administrator --> Security --> Responsibility --> Request

After doing this step, relevant users will be able to run the concurrent program request.

How to integrate XML Report with Oracle

Read more

Using $PROFILES$ in Value sets in Oracle

When we open a form, some values are pre-loaded in some dynamic variables like Person_id , organization_id, user_id etc.

We can use these values to limit the results.

Let me show you an example...its an easy one.

I will show employee date of birth in a form filed based on its Person id loaded in $PROFILES$.Value

It is same like creating a Table Validated value with a little change in WHERE CLAUSE

See: How to create Table Validated Value set


Lets create a value set.

Press "Edit Information"

WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND PERSON_ID = :$PROFILES$.PER_PERSON_ID ( Automatically passed)

After i attached the value set to a field, Here is the result.

Value Sets in Oracle Apps

Leave a comment for any query.

Read more

Using $FLEX$ in Value sets in Oracle

$FLEX$ is used in value sets to get the selected value of some other value set on a field on the form.

So you could compare that value with a column in second value set and get the filtered result.

Let me show you through an example if you could not understand :)

Lets create two independent value sets.

Value set 1

Enter values 

Value set 2

Enter values

Scenario: I attached value set1 to form field 1 and created a Table validated value set and fetched the values from value set 2 on the basis of value selected in value set 1

How to create Table Validated Value set

Here are creation details of Table validated value set

Press Edit Information

TABLE NAME: fnd_flex_values_vl ffvv , FND_FLEX_VALUE_SETS ffvs

WHERE CLAUSE: 

where ffvv.flex_value_set_id = ffvs.flex_value_set_id

and ffvs.flex_value_set_name = 'XX_FLEX_2_VALUE_SET'

and ffvv.description = :$FLEX$.XX_FLEX_1_VALUE_SET

Value selected in field 1 is matched to description in XX_FLEX_2_VALUE_SET.

I attached value set 1 to form filed 1 and XX_FLEX_USAGE to form filed 2.

Here is the result.

Bingo...We achieved the result...

Value Sets in Oracle Apps

Kindly provide your feedback.

Leave a comment for any query.

Read more

Validation type 'Translatable Dependent' in Value sets in Oracle

Definition:- A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the flexfield structure. However, a translated value can be used.

Flexfield Value Security cannot be used with Translatable Independent or Translatable Dependent value sets.

Note: The Accounting Flexfield does not support Translatable Independent and Translatable Dependent value sets.

Lets create a Translatable dependent value set based on parent value set we created in an earlier post.

Translatable Independent Value set


Lets create a Translatable dependent value set.

Select the parent value set. i.e. XX_INDEPENDENT_VALUE_SET

Enter values against each parent value of XX_INDEPENDENT_VALUE_SET which we selected as parent for XX_DEPENDENT_VALUE_SET.

After attachment of value set to a form field, result was as below.

Please note that values saved for these value sets will be PAK for Pakistan and LHR for Lahore.

Value Sets in Oracle Apps

I hope you enjoyed the post.

Kindly provide your feedback.

Read more

Validation type 'Translatable Independent' in Value sets in Oracle

Definition:- A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used.

Lets take a scenario...Consider we want to provide list of values of countries and but want to return their shot code like PAK, USA etc. So we need to create a Translatable Independent to achieve this task.

I created a value set show below.

Now lets enter its values from Flexfield values form:-

After i attached the value set to a form field, result was shown as below. Short code like PAK, BAN will be saved in the database. 

Another example can be, if you want to show data in one format and value be returned in different format. 

Display                     Return value

Dammam- Lahore       DMM-LHR

Colombo-Jeddah         JDH-CLM

Leave a comment for any query.

Read more

Validation type 'Special' in Value sets in Oracle

Oracle Special Value Set

Special value set configuration and uses.

I have observed that many a times we need to restrict users to a limited, conditional value entries either in DFF or when submitting concurrent request. 

Normally we can use dependent value set, but when dynamic or some specific check is required, then special value set is better and only choice. 

Below I have described all the steps for configuring value set. Assign this value set to DFF or concurrent req. parameter as required.

1. Go to Application Developer --> Application --> Validation --> Set.
2. Create a new value set - Enter value set name, description, List type = List of values.
3. Select Validation Type = Special from left bottom of the screen.
4. Click on Edit information.
5. Select Event = Validate.
6. Now in function, you can write pl/sql code or call any function from database.
7. The logic of validation will be as per requirement.
8. The code syntax will be FND PLSQL " entire function "
9. To read the value which user has entered in DFF or as conc. request paremeter, use lc_in := :!value;
10. To raise error if the enter value is not correct, use fnd_message.raise_error;
11. Use Application message. Display appropiate message.
12. Raising error makes sure that user cannot continue with the invalid value and he will be forced to corret entry.
13. The character size is limited, but by calling database function, one can put complex validation.

-- Below is the code to validate that user can select a date which is 3 months before sysdate. This is a parementer in report in which user should be allowed to see data for 3 months or before, but he cannot see recent quater data.

FND PLSQL "declare

l_value varchar2( 20 ) := :!value ;
l_valid NUMBER := 0 ;
BEGIN 
SELECT (sysdate - to_date(l_value,'dd-mon-yyyy')) 
INTO       l_valid
FROM    dual;
IF (l_valid <= 91) THEN 
fnd_message.set_name( 'FND', 'FND_GENERIC_MESSAGE' ) ;
fnd_message.set_token( 'MESSAGE', 'Date must be atleast 3 calendar months prior to current 
ate' );
fnd_message.raise_error ;
END IF ;
END;
"

This content was taken from http://viralji.blogspot.com/2010/10/oracle-special-value-set.html

Value Sets in Oracle Apps

For complete information See : Oracle Docs for Special Value sets

Read more