Showing posts with label Value Set. Show all posts
Showing posts with label Value Set. Show all posts

Steps to create a dependent value set in Oracle Apps


Steps to create a dependent value set in 

Oracle Apps

In order to create a value set which is dependent on another valueset do the following steps:
1. Create a valueset which is the parent valueset.
2. Create a child valueset whose value is fetched after selecting a value of the parent valueset.

A complete example of the dependent valueset creation is done as follows:

1. Create a parent valueset.
Value Set Name: MJIL_ORGANIZATION_ID
Validation Type: Table
Maximum Size: 5


Edit Information:-
Table Application: Human Resources
Table Name: HR_ALL_ORGANIZATION_UNITS_TL
Table Columns Value: ORGANIZATION_ID
Type: Char
Size: 5
Table Columns Meaning: Name
Type: VarChar2
Size: 240




2. Create another valueset which is dependent on the previous valueset.
Value Set Name: MJIL_CUST_INVOICE_DEP_ORG_ID
Validation Type: Table
Maximum Size: 20





Edit Information:-
Table Application: Receivables
Table Name: RA_CUSTOMER_TRX_ALL
Table Columns Value: TRX_NUMBER
Type: Char
Size: 20

Where/Order By
WHERE INTERFACE_HEADER_ATTRIBUTE10=:$FLEX$.MJIL_ORGANIZATION_ID
ORDER BY TRX_NUMBER DESC



Value set basics in Oracle Apps

Question: What is value set?
Answer: It is a set of values

Question: Why do we need value sets?
Answer: You do not always want a user to enter junk free text into all the fields. Hence, Oracle Apps uses value set to validate that correct data is being entered in the fields in screen.

Question: Is value set attached to all the fields that require validations?
Answer : A big NO

Question: Then where lies the usage of value sets?
Answer: Broadly speaking, value sets are attached to segments in Flexfields. You can argue that value sets are also attached to parameters of concurrent program(but in reality oracle treats parameters as Descriptive Flexfields)

Question: This is insane, flexfields haven’t been covered in http://getappstraining.blogspot.com as yet?
Answer: Agreed, hence let’s restrict value set explanation to their usage in concurrent program parameters.

Question: Any examples?
Answer: For the namesake, lets add a Parameter to the concurrent program that we defined in “Concurrent Program Training Lesson link”. Lets add a parameter named “cost centre”, the values to this parameter must be restricted to one of the three values, i.e. HR, SEC, IT.

At the time of submission of the concurrent program the user should be able to pick a cost centre from a list. This is where value set gets used.

Lets now define a simple value set as described above.

Step 1. Go to Application Developer, and select menu /Validation/Set
Image


Step 2. Now define a value set of type Independent. We will cover the other most widely used Type “Table” latter.
Image


Step 3. Now, lets add three independent values to the value set for this Cost Centre list. Hence click on menu Values within Validation
Image

Step 4. Here we add the values for IT, HR, SEC to this independent value set.
Image

“CONTROL-S” to save the data

Step 5. Now let us go back to Concurrent Program that we created in earlier training lesson and Click on Parameters
Image


Step 6. Now lets create a parameter, and attach the value set that we created to this parameter.
Image


Step 7.
Now to test this, lets go to receivables manager and click on Requests.
Image
Click on Request,

Step 8.
Submit New Request, and then click on OK.
Image


Step 9
Now, we can see the values defined in the value set here.
Image

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...