Wednesday, July 13, 2016

Implement External LOV in OA Framework

Implement External LOV in OA Framework
1. Create a New Workspace and Project
Right click Workspaces and click create new OAworkspace and name it as PRajkumarLovDemo. Automatically a new OA Project is also created. Name the project as LovDemo and package as prajkumar.oracle.apps.fnd.lovdemo

2. Create a New Application Module (AM)
Right Click on LovDemo > New > ADF Business Components > Application Module
Name -- LovAM
Package -- prajkumar.oracle.apps.fnd.lovdemo.server

3. Create a New View Object (VO)
Right click on LovDemo > New > ADF Business Components > View Object
Name -- LovVO
Package -- prajkumar.oracle.apps.fnd.lovdemo.server

Note - The VO is not based on any EO so click next and go to the query section and paste the query
SELECT          employee_number, full_name
FROM             per_all_people_f
WHERE           SYSDATE BETWEEN effective_start_date AND effective_end_date

4. Add View Object to Root UI Application Module

5. Create a New Page
Right click on LovDemo > New > Web Tier > OA Components > Page
Name -- LovPG
Package -- prajkumar.oracle.apps.fnd.lovdemo.webui

6. Select the LovPG and go to the strcuture pane where a default region has been created

7. Select region1 and set the following properties:  
ID -- PageLayoutRN
AM Definition -- prajkumar.oracle.apps.fnd.lovdemo.server.LovAM
Window Title -- List of values Demo Window
Title – List of values Demo

8. Right click PageLayoutRN and  click new Region
ID -- MainRN
Region Style – messageComponentLayout
Note - Style is given as messageComponentlayout because we are going to create only message components that is messageLovInput item in that region

9. Create a New Region
Right click on LovDemo > New > Web Tier > OA Components > Region
Name -- EmployeeLovRN
Package -- prajkumar.oracle.apps.fnd.lovdemo.webui
Region Style -- listOfValues
Scope -- Public

Note - The property Scope is the key property which makes the LOV region public and makes it usable in multiple pages

10. Select EmployeeLovRN. Right click on EmployeeLovRN in Structure pane and click table using wizard. In the wizard choose the prajkumar.oracle.apps.fnd.lovdemo.server.LovAM and select the LovVO1. Click Next
Region Id -- LovRN
Region style -- table

11. Shuttle the two attributes to the right side.That is EmployeeNumber and FullName

12. Click next, check the mapping and then finish

13. Select the field FullName and set the following properties:
Search Allowed -- True
Selective Search Criteria – True

Note - The first property lets users search on these values in the LOV, and the second property ensures that the users specify search criteria for at least one of these values to avoid a blind query

Attaching External LOV to Page:

14. Click on LovPG and right click the MainRN and click new messageLovInput
External Lov -- /prajkumar/oracle/apps/fnd/lovdemo/webui/EmployeeLovRN
Lov Region Item -- FullName
Return Item -- item1
Criteria Item -- item1
Prompt -- Employee Name

15. Congratulation you have successfully finished. Run Your page and Test Your Work 
 






Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...