Oracle Application Framework (OAF) View Object Extension

January 4, 2015 · by altunkan · in Oracle Application Framework (OAF)

Hello,

During extension developments for OAF pages, sometimes we need extra fields which are not available in standard view object. In this kind of situations, we are creating a new view object and make it extend standard view object. Please follow carefully all the steps in this tutorial in order to avoid JDeveloper bug messages.

First of all, change the following profile values to “Yes”.

Personalize Self-Service Defn (FND_CUSTOM_OA_DEFINTION)
FND: Diagnostics (FND_DIAGNOSTICS)

Open the page which you will extend view object. For this tutorial, I used a view object which is placed in Vendor Summary Query page in Account Payables. I am going to add ADDRESS_LINE1 column which is actually a site information and do not included in standard view object. Since a supplier may have more than one address, I am going to use rownum=1 in where condition.

Page MDS Path: /oracle/apps/pos/supplier/webui/SuppSummPG

Click to “About This Page” link and open page XML hierarchy and expand pageLayout region.

Scan the page from up to down and find the SuppSummVO view object that attached to the supplier table in the page. If view object name is not clickable, go down to the page and open expand “Business Component References Details” link. Click to the view object link. You will be forward to the page where sql query and correspending java attributes reside. Check that ADDRESS_LINE1 column is not in the query and attribute list.

The standard view object is placed in POS module. During extension development, in order to handle dependencies and compilation errors in JDeveloper, download pos folder from $JAVA_TOP/oracle/apps in EBS application server to <JDEV_USER_HOME>/myprojects/oracle/apps via FTP.


[appldemo@demo ~]$ cd $JAVA_TOP/oracle/apps
[appldemo@demo apps]$ pwd
/u01/data_appl/apps/apps_st/comn/java/classes/oracle/apps
[appldemo@demo apps]$

[appldemo@demo ~]$ cd $JAVA_TOP/oracle/apps

[appldemo@demo apps]$ pwd

/u01/data_appl/apps/apps_st/comn/java/classes/oracle/apps

[appldemo@demo apps]$

At this point, JDeveloper should display the downloaded pos module with inside packages in project navigator. In order to create custom view object, right click to the project and select “New”. Select Business Tier -> ADF Business Components -> View Object and click OK button.

Create view object wizard should be opended. Click to Next button in initial step.

At first step we need to define a proper name and package for new view object.

Package	Name
xxntc.oracle.apps.pos.supplier.server	xxSuppSummVO

Click to the Browse button and find the SuppSummVO view object and select it. With this action, custom view object can extend standard view object query and attributes.

When Browse button is clicked and if view object list is empty then close the wizard. Expand the packages in project and open Create View Object wizard again.

Without changing anything, go up to the step 7.

If you do not aim to change getter and setter or change sql query where clause in runtime, uncheck Generate Java File ticks.

Click to the Next and click to Finish.

Double click to the custom view object and open the wizard.

Before moving to the next part, we need to write a simple PL/SQl function which will return address_line1 for us. I prefer writing pl/sql function instead of directly adding to the sql query because it will be much easier to modify in future. In addition, It makes a certain difference with standard columns and custom columns in sql query.


create or replace function apps.xx_vo_extension (p_vendor_id in number) return varchar2 is
    l_sonuc             varchar2(200);
begin

    select address_line1
      into l_sonuc 
      from ap_supplier_sites_all 
     where vendor_id = p_vendor_id
       and rownum = 1;
       
    return l_sonuc;
    
exception when others then           
    return null;
end;

create or replace function apps.xx_vo_extension (p_vendor_id in number) return varchar2 is

l_sonuc varchar2(200);

begin

select address_line1

into l_sonuc

from ap_supplier_sites_all

where vendor_id = p_vendor_id

and rownum = 1;

return l_sonuc;

exception when others then

return null;

end;

Go to the “Attributes” section in view object wizard and click to “New” button.

Add an attribute with following information:

Name	Type	Updatable	Mapped to Column or SQL	Alias	Type	Expression
XxAddressLine1	String	Never	Checked	xx_address_line1	VARCHAR2(240)	apps.xx_vo_extension(pv.vendor_id)

Later, We will use “Name” attribute in personalization. “Alias” attribute is also very important when modifying standard sql query.

Modify the sql query. Add column with same alias. Pay attention to line 32 and 87.


SELECT hp.party_name,
       hp.party_id AS party_id,
       hp.duns_number_c AS duns,
       pv.vendor_id,
       pv.vendor_name,
       pv.individual_1099 AS taxpayer_id,
       pv.vat_registration_num AS tax_reg_num,
       pv.segment1,
       pv.VENDOR_NAME_ALT AS alternate_name,
       pv.end_date_active AS end_date_active,
       pv.start_date_active AS start_date_active,
       pv.ONE_TIME_FLAG,
       pv.VENDOR_TYPE_LOOKUP_CODE,
       pv.PARENT_VENDOR_ID,
       parent.vendor_name AS parent_vendor_name,
       pv.PAYMENT_PRIORITY,
       parent.segment1 AS parent_Segment1,
       pv.TAX_REPORTING_NAME,
       pv.terms_id,
       terms.name AS terms_desc,
       pv.FEDERAL_REPORTABLE_FLAG,
       pv.STATE_REPORTABLE_FLAG,
       pv.PAY_GROUP_LOOKUP_CODE,
       pay_group.description AS pay_group_desc,
       aptt.description AS income_tax_type,
       hp.organization_name_phonetic,
       plc.DISPLAYED_FIELD AS VENDOR_TYPE_DISPLAY,
       pv.type_1099 AS income_tax_type_code,
       pv.employee_id AS employee_id,
       pecx.employee_num AS employee_number,
       apps.xx_vo_extension (pv.vendor_id) xx_address_line1
  FROM hz_parties hp,
       ap_suppliers pv,
       ap_suppliers parent,
       ap_terms_tl terms,
       fnd_lookup_values pay_group,
       AP_INCOME_TAX_TYPES aptt,
       po_lookup_codes plc,
       per_employees_current_x pecx
 WHERE     pv.party_id = hp.party_id
       AND pv.party_id = pecx.party_id(+)
       AND parent.vendor_id(+) = pv.parent_vendor_id
       AND pv.terms_id = terms.term_id(+)
       AND terms.language(+) = USERENV ('LANG')
       AND terms.enabled_flag(+) = 'Y'
       AND pv.pay_group_lookup_code = pay_group.lookup_code(+)
       AND pay_group.lookup_type(+) = 'PAY GROUP'
       AND pay_group.view_application_id(+) = 200
       AND pay_group.language(+) = USERENV ('lang')
       AND pv.type_1099 = aptt.income_tax_type(+)
       AND pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE(+)
       AND plc.lookup_type(+) = 'VENDOR TYPE'
       AND pv.organization_type_lookup_code IN
              ('INDIVIDUAL', 'FOREIGN INDIVIDUAL')
UNION ALL
SELECT hp.party_name,
       hp.party_id AS party_id,
       hp.duns_number_c AS duns,
       pv.vendor_id,
       pv.vendor_name,
       pv.num_1099 AS taxpayer_id,
       pv.vat_registration_num AS tax_reg_num,
       pv.segment1,
       pv.VENDOR_NAME_ALT AS alternate_name,
       pv.end_date_active AS end_date_active,
       pv.start_date_active AS start_date_active,
       pv.ONE_TIME_FLAG,
       pv.VENDOR_TYPE_LOOKUP_CODE,
       pv.PARENT_VENDOR_ID,
       parent.vendor_name AS parent_vendor_name,
       pv.PAYMENT_PRIORITY,
       parent.segment1 AS parent_Segment1,
       pv.TAX_REPORTING_NAME,
       pv.terms_id,
       terms.name AS terms_desc,
       pv.FEDERAL_REPORTABLE_FLAG,
       pv.STATE_REPORTABLE_FLAG,
       pv.PAY_GROUP_LOOKUP_CODE,
       pay_group.description AS pay_group_desc,
       aptt.description AS income_tax_type,
       hp.organization_name_phonetic,
       plc.DISPLAYED_FIELD AS VENDOR_TYPE_DISPLAY,
       pv.type_1099 AS income_tax_type_code,
       pv.employee_id AS employee_id,
       pecx.employee_num AS employee_number,
       apps.xx_vo_extension (pv.vendor_id) xx_address_line1
  FROM hz_parties hp,
       ap_suppliers pv,
       ap_suppliers parent,
       ap_terms_tl terms,
       fnd_lookup_values pay_group,
       AP_INCOME_TAX_TYPES aptt,
       po_lookup_codes plc,
       per_employees_current_x pecx
 WHERE     pv.party_id = hp.party_id
       AND pv.party_id = pecx.party_id(+)
       AND parent.vendor_id(+) = pv.parent_vendor_id
       AND pv.terms_id = terms.term_id(+)
       AND terms.language(+) = USERENV ('LANG')
       AND terms.enabled_flag(+) = 'Y'
       AND pv.pay_group_lookup_code = pay_group.lookup_code(+)
       AND pay_group.lookup_type(+) = 'PAY GROUP'
       AND pay_group.view_application_id(+) = 200
       AND pay_group.language(+) = USERENV ('lang')
       AND pv.type_1099 = aptt.income_tax_type(+)
       AND pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE(+)
       AND plc.lookup_type(+) = 'VENDOR TYPE'
       AND (pv.organization_type_lookup_code NOT IN
               ('INDIVIDUAL', 'FOREIGN INDIVIDUAL')
            OR pv.organization_type_lookup_code IS NULL)

100

101

102

103

104

105

106

107

108

109

110

111

112

SELECT hp.party_name,

hp.party_id AS party_id,

hp.duns_number_c AS duns,

pv.vendor_id,

pv.vendor_name,

pv.individual_1099 AS taxpayer_id,

pv.vat_registration_num AS tax_reg_num,

pv.segment1,

pv.VENDOR_NAME_ALT AS alternate_name,

pv.end_date_active AS end_date_active,

pv.start_date_active AS start_date_active,

pv.ONE_TIME_FLAG,

pv.VENDOR_TYPE_LOOKUP_CODE,

pv.PARENT_VENDOR_ID,

parent.vendor_name AS parent_vendor_name,

pv.PAYMENT_PRIORITY,

parent.segment1 AS parent_Segment1,

pv.TAX_REPORTING_NAME,

pv.terms_id,

terms.name AS terms_desc,

pv.FEDERAL_REPORTABLE_FLAG,

pv.STATE_REPORTABLE_FLAG,

pv.PAY_GROUP_LOOKUP_CODE,

pay_group.description AS pay_group_desc,

aptt.description AS income_tax_type,

hp.organization_name_phonetic,

plc.DISPLAYED_FIELD AS VENDOR_TYPE_DISPLAY,

pv.type_1099 AS income_tax_type_code,

pv.employee_id AS employee_id,

pecx.employee_num AS employee_number,

apps.xx_vo_extension (pv.vendor_id) xx_address_line1

FROM hz_parties hp,

ap_suppliers pv,

ap_suppliers parent,

ap_terms_tl terms,

fnd_lookup_values pay_group,

AP_INCOME_TAX_TYPES aptt,

po_lookup_codes plc,

per_employees_current_x pecx

WHERE pv.party_id = hp.party_id

AND pv.party_id = pecx.party_id(+)

AND parent.vendor_id(+) = pv.parent_vendor_id

AND pv.terms_id = terms.term_id(+)

AND terms.language(+) = USERENV ('LANG')

AND terms.enabled_flag(+) = 'Y'

AND pv.pay_group_lookup_code = pay_group.lookup_code(+)

AND pay_group.lookup_type(+) = 'PAY GROUP'

AND pay_group.view_application_id(+) = 200

AND pay_group.language(+) = USERENV ('lang')

AND pv.type_1099 = aptt.income_tax_type(+)

AND pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE(+)

AND plc.lookup_type(+) = 'VENDOR TYPE'

AND pv.organization_type_lookup_code IN

('INDIVIDUAL', 'FOREIGN INDIVIDUAL')

UNION ALL

SELECT hp.party_name,

hp.party_id AS party_id,

hp.duns_number_c AS duns,

pv.vendor_id,

pv.vendor_name,

pv.num_1099 AS taxpayer_id,

pv.vat_registration_num AS tax_reg_num,

pv.segment1,

pv.VENDOR_NAME_ALT AS alternate_name,

pv.end_date_active AS end_date_active,

pv.start_date_active AS start_date_active,

pv.ONE_TIME_FLAG,

pv.VENDOR_TYPE_LOOKUP_CODE,

pv.PARENT_VENDOR_ID,

parent.vendor_name AS parent_vendor_name,

pv.PAYMENT_PRIORITY,

parent.segment1 AS parent_Segment1,

pv.TAX_REPORTING_NAME,

pv.terms_id,

terms.name AS terms_desc,

pv.FEDERAL_REPORTABLE_FLAG,

pv.STATE_REPORTABLE_FLAG,

pv.PAY_GROUP_LOOKUP_CODE,

pay_group.description AS pay_group_desc,

aptt.description AS income_tax_type,

hp.organization_name_phonetic,

plc.DISPLAYED_FIELD AS VENDOR_TYPE_DISPLAY,

pv.type_1099 AS income_tax_type_code,

pv.employee_id AS employee_id,

pecx.employee_num AS employee_number,

apps.xx_vo_extension (pv.vendor_id) xx_address_line1

FROM hz_parties hp,

ap_suppliers pv,

ap_suppliers parent,

ap_terms_tl terms,

fnd_lookup_values pay_group,

AP_INCOME_TAX_TYPES aptt,

po_lookup_codes plc,

per_employees_current_x pecx

WHERE pv.party_id = hp.party_id

AND pv.party_id = pecx.party_id(+)

AND parent.vendor_id(+) = pv.parent_vendor_id

AND pv.terms_id = terms.term_id(+)

AND terms.language(+) = USERENV ('LANG')

AND terms.enabled_flag(+) = 'Y'

AND pv.pay_group_lookup_code = pay_group.lookup_code(+)

AND pay_group.lookup_type(+) = 'PAY GROUP'

AND pay_group.view_application_id(+) = 200

AND pay_group.language(+) = USERENV ('lang')

AND pv.type_1099 = aptt.income_tax_type(+)

AND pv.VENDOR_TYPE_LOOKUP_CODE = plc.LOOKUP_CODE(+)

AND plc.lookup_type(+) = 'VENDOR TYPE'

AND (pv.organization_type_lookup_code NOT IN

('INDIVIDUAL', 'FOREIGN INDIVIDUAL')

OR pv.organization_type_lookup_code IS NULL)

Click to Apply and OK buttons and close wizard and save JDeveloper. Copy POS folder from myprojects to myclasses in operating system. Right click to the project and click “Make”. You may get some warnings, just ignore them. The important thing is compiling without any error.

If project may be compiled without any problem, right click to the project and open properties. From left menu expand “Business Components” and select “Substitutions”. In the right side of wizard, there must be two window: Available and Subsitute. Select “SuppSummVO” from Available window and select “xxSuppSummVO” from Subsitute window. Click to the Add buton. Click to OK button and close the wizard. One again make and compile the project.

If no problem occurs until this point, you will be able to see <Subsitutes> tag in <YourProject>.jpx file.


<Substitutes>
     <Substitute OldName ="oracle.apps.pos.supplier.server.SuppSummVO" NewName ="xxntc.oracle.apps.pos.supplier.server.xxSuppSummVO" />
</Substitutes>

</Substitutes>

We need to import jpx file into EBS metadata services and upload custom view object to the JAVA_TOP/xx/.. path in application server.

In order to import substitutions (jpx file) into database, use the following script. Change it according to your computer and database instance.


C:\\DemoOAF\jdevbin\oaext\bin\jpximport C:\\DemoOAF\jdevhome\jdev\myclasses\Demo.jpx -username apps -password <apps_password> -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SID=<sid>)))"

C:\\DemoOAF\jdevbin\oaext\bin\jpximport C:\\DemoOAF\jdevhome\jdev\myclasses\Demo.jpx -username apps -password <apps_password> -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SID=<sid>)))"

After uploading vo files is done, bounce the oc4j and apache servers. Login to EBS and open the same page. Click to the “About This Page” link. Expand “Business Component References Details” and you must see your custom view object in the list.

Click to your custom view object and you must see xx_address_line1 in both sql query and attributes section. This means that you can use this attribute during personalization.

You can check your subsitution with following pl/sql script:


begin
jdr_utils.listCustomizations('/oracle/apps/pos/supplier/server/SuppSummVO');
end;

begin

jdr_utils.listCustomizations('/oracle/apps/pos/supplier/server/SuppSummVO');

end;

Result: /oracle/apps/pos/supplier/server/customizations/site/0/SuppSummVO

To Print Document:


begin
jdr_utils.printDocument('/oracle/apps/pos/supplier/server/customizations/site/0/SuppSummVO');
end;

begin

jdr_utils.printDocument('/oracle/apps/pos/supplier/server/customizations/site/0/SuppSummVO');

end;

Result:


<?xml version='1.0' encoding='UTF-8'?>
<customization xmlns="http://xmlns.oracle.com/jrad" xmlns:ui="http://xmlns.oracle.com/uix/ui" xmlns:oa="http://xmlns.oracle.com/oa" xmlns:user="http://xmlns.oracle.com/user" version="10.1.3_1312" xml:lang="en-US"
               customizes="/oracle/apps/pos/supplier/server/SuppSummVO">
   <replace with="/xxntc/oracle/apps/pos/supplier/server/xxSuppSummVO"/>
</customization>

<?xml version='1.0' encoding='UTF-8'?>

<customization xmlns="http://xmlns.oracle.com/jrad" xmlns:ui="http://xmlns.oracle.com/uix/ui" xmlns:oa="http://xmlns.oracle.com/oa" xmlns:user="http://xmlns.oracle.com/user" version="10.1.3_1312" xml:lang="en-US"

customizes="/oracle/apps/pos/supplier/server/SuppSummVO">

</customization>

Open the page and click to “Personalize Suppliers Table”.

Click to “Create Item” in order to create a new item in the table.
Choose level as “Site” and Item Style “Message Style Text”.

Do the following changes:

ID	Prompt	View Attribute	View Instance
xx_address_line1	xx_address_line1	XxAddressLine1	xxSuppSummVO

Result:

Tags: apps, oaf, oaf vo extension, oracle, oracle apps, oracle ebs, view object extension, vo extension

3 Responses

Syed · November 16, 2015 at 06:34:16 · →

Very nice….. VO Extension explained in detail.
Specto Training · March 2, 2016 at 06:31:10 · →

Vey very nice blog!!
Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best

for all your blogging efforts.

MS Dynamics Training
Arif · April 25, 2017 at 23:24:26 · →

Good post keep up more..

Oracle Application Framework (OAF) View Object Extension

3 Responses

Leave a Reply Click here to cancel reply.

Visitors

Subscribe to Blog via Email