Oracle EBS & Fusion Cloud Blog: VO Extension Steps in OAF ? with example

VO Extension Steps :

Identify the VO we need to extend.
Get the VO Object from Java Top which we want to extend and keep it inside Jdeveloper My Projects folder of your Jdev Setup.
Then Create a new VO and select the extend option as the parent VO which is going to extended.This will be done in Jdeveloper Wizard. Now the Parent VO SQL will be shown in Jdeveloper Wizard (Sql Editor) and as per our requirement we can modify or can add New Attributes.
Once the VO Creation is complete we have to right click on project and select the substitution option. their we have to choose old VO and New VO. once it is done the substitution is complete.
We need to compile the project.
After compilation ,a XML entry will be created in the JPX file. the JPX file generally is the name of the project. so now we need to import the JPX to MDS and have to move the java and VO XML to Java Top.
After Import/Register , we add the new filed in the OAF page through personalization and then give reference in this field for a New Customer VO Object.

Example For VO Extension: (How to add New colum in VO and Page):

Step 1:- First Go to your OAF page in the application , in which you will do View Object Extension.

This is the Page in which we will do View Extension. Here I want to display(After Report Status column) the DFF attribute5 in this Track Submitted Expense Reports Status Window.

Step 2:- To see the Properties of this Page and to know which View Object is working behind this Page. for this we need to go to 'About this Page' in the Bottom of the Page.

Step 3:- You can see like below and Click on the Expand All option and in the Controller Section you can see the Controller Name

Check your fields and you can find your VO there

Below is the VO for your fields.

this Highlighted VO details you can find in the BC4J Components.

Step 4:-Click '+' icon for Business Components References Details.

Step 5:- This is the Location of the View Object under Java_Top under which you will do Extension.

Step 6:- Now you get to know that where is your Standard View Object is Placed in the Oracle application Under Java_Top.

Then you Need to go to Server Under Java_Top then Under Java_Top follow the same path 'oracle/apps/ap/oie/server/' and copy the 'TrackExpesneReportsVO' from Server to your Local Desktop.

One Important Thing.

You need to create the same path in your JDev folder under' 'jdevhome'

Under JdevHome there is MyProjects' folder under this folder you have to Create same folder structure like oracle/apps/ap/oie/server/' and then this copied file 'TrackExpesneReportsVO' in this Location.

The rule is you have to create same folder structure like the Folder Structure created for The Standard Controller in the Application server Java_Top.

Step 7:-This is the steps you have to implement before doing Start Extension in the JDeveloper.

Step 8:- Now open the JDeveloper

Step 9:- Now we will create OA Workspace and Project to do VO Extensions.

Step 10:- Give the Name of OA Workspace. Click Ok.

Step 11:- Now Give the Name of the Project same like OA workspace.

Package Path:- Choose the same like Standard View Object but choose the application Top start with xx like this this change 'xxap'

But here i am using standard application top , it is practice instance i dont have that to Use.

Step 12:- Then Finish this Project.

Step 13:- Now you can see your OAF Project has been completed. Now we will do Controller Extension under this Project.

Step 14:- Download your VO oracle.apps.ap.oie.server.TrackExpenseReportsVO

Go to Jdeveloper select Project and Refresh you can see your VO like Below.

Step 15:- Creation VO Extension

Now right Click on the Project as below and click New.

Step 16:- Now Create View Object as below :-

Click on OK

Click on Next

Step 17:- Create Extended VO Name and select the Package.

Now select the Standard VO which you want to Extend Click on the Browse Button under Extends as below and Select the Standard View Object 'TrackExpesneReportsVO' which we want to Extend in this VO Extension.

Step 18:- Select the 'TrackExpesneReportsVO' Standard Page Controller.

Click on Ok.

Step 19:- This is the Extended view Object Final Screen Click Next.

Step 20:- This is The Query working for the Standard View Object. Copy the Query of the View Object in the Toad.

Step 21:- Now Add the Custom column 'Attribute5' which you want to display in the OAF Page. Always add the New Column in the Last.

after adding the column attribute5 query looks below

SELECT

AI.DESCRIPTION PURPOSE,

AI.INVOICE_CURRENCY_CODE CURRENCY_CODE,

AI.INVOICE_DATE REPORT_DATE,

AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,

AI.INVOICE_NUM REPORT_NUMBER,

TO_CHAR(decode(nvl(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),0),

0, decode(AI.CANCELLED_DATE,

null, APS.GROSS_AMOUNT,

AERH.TOTAL),

nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,

FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AI.INVOICE_CURRENCY_CODE, 30)) ||' '|| AI.INVOICE_CURRENCY_CODE REPORT_TOTAL_CURRENCY,

TO_CHAR(decode(nvl(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),0),

0, decode(AI.CANCELLED_DATE,

null, APS.GROSS_AMOUNT,

AERH.TOTAL),

nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,

FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AI.INVOICE_CURRENCY_CODE, 30))

REPORT_TOTAL,

P.PERSON_ID EMPLOYEE_ID,

AERH.REPORT_HEADER_ID REPORT_HEADER_ID,

P.FULL_NAME FULL_NAME ,

DECODE(AI.CANCELLED_DATE,null,

nvl(aerh.expense_status_code, DECODE(APS.GROSS_AMOUNT ,0,'PAID',

decode(AI.Payment_status_flag,'Y','PAID',

'N','INVOICED',

'P','PARPAID',NULL))),

'CANCELLED') STATUS_CODE,

AERH.source SOURCE,

NULL CURRENT_APPROVER,

ROUND(sysdate - AI.LAST_UPDATE_DATE) DAYS_SINCE_ACTIVITY,

AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,

AERH.HOLDING_REPORT_HEADER_ID,

AI.VENDOR_ID VENDOR_ID,

AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,

AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,

'CurrentApproverName' CURRENT_APPROVER_SWITCHER,

to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),

AI.INVOICE_ID INVOICE_ID,

AERH.IMAGE_RECEIPTS_STATUS IMAGE_RECEIPTS_STATUS_CODE,

AERH.ATTRIBUTE5 TRACK_ATTRIBUTE5

FROM

AK_WEB_USER_SEC_ATTR_VALUES A,

PO_VENDORS PV,

AP_INVOICES AI,

AP_EXPENSE_REPORT_HEADERS AERH,

PER_PEOPLE_X P,

AP_PAYMENT_SCHEDULES APS

WHERE AI.INVOICE_ID= APS.INVOICE_ID

AND AI.INVOICE_ID = AERH.VOUCHNO

AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'

AND AI.SOURCE in ('XpenseXpress','SelfService', 'CREDIT CARD', 'Both Pay')

AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'

AND PV.EMPLOYEE_ID = A.NUMBER_VALUE

AND A.WEB_USER_ID = :1

AND P.PERSON_ID = PV.EMPLOYEE_ID

AND PV.VENDOR_ID = AI.VENDOR_ID

AND DECODE (AI.PAYMENT_STATUS_FLAG,

'Y', sysdate - AI.LAST_UPDATE_DATE,

decode(APS.GROSS_AMOUNT , 0 ,sysdate - AI.LAST_UPDATE_DATE,0)

) <= 30

AND (AERH.SOURCE <> 'Both Pay' OR AERH.REPORT_HEADER_ID IS NULL)

UNION

SELECT

AI.DESCRIPTION PURPOSE,

AI.INVOICE_CURRENCY_CODE CURRENCY_CODE,

AI.INVOICE_DATE REPORT_DATE,

AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,

AI.INVOICE_NUM REPORT_NUMBER,

TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),

0, decode(AI.CANCELLED_DATE,

null, APS.GROSS_AMOUNT,

AERH.TOTAL),

nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,

FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AI.INVOICE_CURRENCY_CODE, 30)) ||' '|| AI.INVOICE_CURRENCY_CODE REPORT_TOTAL_CURRENCY,

TO_CHAR(decode(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),

0, decode(AI.CANCELLED_DATE,

null, APS.GROSS_AMOUNT,

AERH.TOTAL),

nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)),

FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AI.INVOICE_CURRENCY_CODE, 30))

REPORT_TOTAL,

P.PERSON_ID EMPLOYEE_ID,

AERH.REPORT_HEADER_ID REPORT_HEADER_ID,

P.FULL_NAME FULL_NAME ,

DECODE(AI.CANCELLED_DATE,null,

nvl(aerh.expense_status_code, DECODE(APS.GROSS_AMOUNT ,0,'PAID',

decode(AI.Payment_status_flag,'Y','PAID',

'N','INVOICED',

'P','PARPAID',NULL))),

'CANCELLED') STATUS_CODE,

AERH.source SOURCE,

NULL CURRENT_APPROVER,

ROUND(sysdate - AI.LAST_UPDATE_DATE) DAYS_SINCE_ACTIVITY,

AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,

AERH.HOLDING_REPORT_HEADER_ID,

AI.VENDOR_ID VENDOR_ID,

AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,

AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,

'CurrentApproverName' CURRENT_APPROVER_SWITCHER,

to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),

AI.INVOICE_ID INVOICE_ID,

AERH.IMAGE_RECEIPTS_STATUS IMAGE_RECEIPTS_STATUS_CODE,

AERH.ATTRIBUTE5 TRACK_ATTRIBUTE5

FROM

AK_WEB_USER_SEC_ATTR_VALUES A,

PO_VENDORS PV,

AP_INVOICES AI,

AP_EXPENSE_REPORT_HEADERS AERH,

PER_PEOPLE_X P,

AP_PAYMENT_SCHEDULES APS

WHERE AI.INVOICE_ID= APS.INVOICE_ID

AND AI.INVOICE_ID = AERH.VOUCHNO

AND AI.INVOICE_TYPE_LOOKUP_CODE||'' in ('STANDARD','MIXED','PAYMENT REQUEST')

AND AI.SOURCE IN ('SelfService','Both Pay', 'CREDIT CARD')

AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'

AND AI.PAID_ON_BEHALF_EMPLOYEE_ID = A.NUMBER_VALUE

AND A.WEB_USER_ID = :2

AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID

AND PV.VENDOR_ID = AI.VENDOR_ID

AND DECODE (AI.PAYMENT_STATUS_FLAG,

'Y', sysdate - AI.LAST_UPDATE_DATE,

decode(APS.GROSS_AMOUNT , 0 ,sysdate - AI.LAST_UPDATE_DATE,0)

) <= 30

AND (AERH.SOURCE <> 'Both Pay' OR AERH.REPORT_HEADER_ID IS NULL)

UNION ALL

SELECT

AERH.DESCRIPTION PURPOSE,

AERH.DEFAULT_CURRENCY_CODE CURRENCY_CODE,

AERH.WEEK_END_DATE REPORT_DATE,

AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,

AERH.INVOICE_NUM REPORT_NUMBER,

TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AERH.DEFAULT_CURRENCY_CODE,30)) ||' '|| AERH.DEFAULT_CURRENCY_CODE REPORT_TOTAL_CURRENCY,

TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AERH.DEFAULT_CURRENCY_CODE,30)) REPORT_TOTAL,

PER_EMPLOYEE.PERSON_ID EMPLOYEE_ID,

AERH.REPORT_HEADER_ID REPORT_HEADER_ID,

PER_EMPLOYEE.FULL_NAME FULL_NAME,

NVL(AERH.expense_status_code,

AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(AERH.Source, AERH.Workflow_approved_flag,

AERH.report_header_id, 'Y', 'N')) STATUS_CODE,

AERH.source SOURCE,

/*NVL (PER_APPROVER.full_name, AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,

AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)) CURRENT_APPROVER, */

DECODE (NVL (AERH.approval_type, 'PER'), 'PER', NVL (PER_APPROVER.full_name, AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,

AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)), AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,

AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)) CURRENT_APPROVER,

ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,

sysdate - AERH.LAST_UPDATE_DATE)) DAYS_SINCE_ACTIVITY,

AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,

AERH.HOLDING_REPORT_HEADER_ID,

0 VENDOR_ID,

AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,

AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,

DECODE(AERH.expense_current_approver_id,

-99999, 'AMEMultipleApprovers',

decode(PER_APPROVER.full_name,

null,'CurrentApproverName','AMESingleApprover')) CURRENT_APPROVER_SWITCHER,

to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),

-1 INVOICE_ID,

AERH.IMAGE_RECEIPTS_STATUS IMAGE_RECEIPTS_STATUS_CODE,

AERH.ATTRIBUTE5 TRACK_ATTRIBUTE5

FROM

AK_WEB_USER_SEC_ATTR_VALUES A,

AP_EXPENSE_REPORT_HEADERS AERH,

PER_PEOPLE_X PER_EMPLOYEE,

PER_PEOPLE_X PER_APPROVER

WHERE AERH.VOUCHNO +0 =0

AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'

AND AERH.EMPLOYEE_ID = A.NUMBER_VALUE

AND A.WEB_USER_ID = :3

AND PER_EMPLOYEE.PERSON_ID = AERH.EMPLOYEE_ID

AND (AERH.Source <> 'NonValidatedWebExpense'

OR AERH.Workflow_approved_flag IS NULL)

AND AERH.expense_current_approver_id = PER_APPROVER.person_id (+)

AND DECODE(AERH.expense_status_code,'PAID',ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,sysdate - AERH.LAST_UPDATE_DATE)),30) <= 30

AND AERH.SOURCE <> 'Both Pay'

UNION

SELECT /*+ leading(a aerh) push_pred(per_employee) push_pred(per_approver) */

AERH.DESCRIPTION PURPOSE,

AERH.DEFAULT_CURRENCY_CODE CURRENCY_CODE,

AERH.WEEK_END_DATE REPORT_DATE,

AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,

AERH.INVOICE_NUM REPORT_NUMBER,

TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AERH.DEFAULT_CURRENCY_CODE,30)) ||' '|| AERH.DEFAULT_CURRENCY_CODE REPORT_TOTAL_CURRENCY,

TO_CHAR(nvl(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE+nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),AERH.TOTAL),FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AERH.DEFAULT_CURRENCY_CODE,30)) REPORT_TOTAL,

PER_EMPLOYEE.PERSON_ID EMPLOYEE_ID,

AERH.REPORT_HEADER_ID REPORT_HEADER_ID,

PER_EMPLOYEE.FULL_NAME FULL_NAME,

NVL(AERH.expense_status_code,

AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(AERH.Source, AERH.Workflow_approved_flag,

AERH.report_header_id,'Y','N')) STATUS_CODE,

AERH.source SOURCE,

/*NVL (PER_APPROVER.full_name, AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,

AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)) CURRENT_APPROVER, */

DECODE (NVL (AERH.approval_type, 'PER'), 'PER', NVL (PER_APPROVER.full_name, AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,

AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)), AP_WEB_OA_ACTIVE_PKG.GetCurrentApprover(AERH.Source,

AERH.Workflow_approved_flag, AERH.report_header_id, AERH.expense_status_code)) CURRENT_APPROVER,

ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,

sysdate - AERH.LAST_UPDATE_DATE)) DAYS_SINCE_ACTIVITY,

AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,

AERH.HOLDING_REPORT_HEADER_ID,

0 VENDOR_ID,

AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,

AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,

DECODE(AERH.expense_current_approver_id,

-99999, 'AMEMultipleApprovers',

decode(PER_APPROVER.full_name,

null,'CurrentApproverName','AMESingleApprover')) CURRENT_APPROVER_SWITCHER,

to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),

-1 INVOICE_ID,

AERH.IMAGE_RECEIPTS_STATUS IMAGE_RECEIPTS_STATUS_CODE,

AERH.ATTRIBUTE5 TRACK_ATTRIBUTE5

FROM

AK_WEB_USER_SEC_ATTR_VALUES A,

AP_EXPENSE_REPORT_HEADERS AERH,

PER_PEOPLE_X PER_EMPLOYEE,

PER_PEOPLE_X PER_APPROVER

WHERE AERH.VOUCHNO +0=0

AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'

AND AERH.PAID_ON_BEHALF_EMPLOYEE_ID = A.NUMBER_VALUE

AND A.WEB_USER_ID = :4

AND PER_EMPLOYEE.PERSON_ID = PAID_ON_BEHALF_EMPLOYEE_ID

AND AERH.EMPLOYEE_ID IS NULL

AND (AERH.Source <> 'NonValidatedWebExpense'

OR AERH.Workflow_approved_flag IS NULL)

AND AERH.expense_current_approver_id = PER_APPROVER.person_id (+)

AND DECODE(AERH.expense_status_code,'PAID',ROUND(NVL(sysdate - AERH.EXPENSE_LAST_STATUS_DATE,sysdate - AERH.LAST_UPDATE_DATE)),30) <= 30

AND AERH.SOURCE <> 'Both Pay'

UNION ALL

/* This select is for invoice imported reports by contingent workers */

SELECT

AI.DESCRIPTION PURPOSE,

AI.INVOICE_CURRENCY_CODE CURRENCY_CODE,

AI.INVOICE_DATE REPORT_DATE,

AERH.REPORT_SUBMITTED_DATE REPORT_SUBMITTED_DATE,

AI.INVOICE_NUM REPORT_NUMBER,

TO_CHAR(decode(nvl(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),0),

0, decode(AI.CANCELLED_DATE,

null, APS.GROSS_AMOUNT,

AERH.TOTAL),

nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,

FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AI.INVOICE_CURRENCY_CODE, 30)) ||' '|| AI.INVOICE_CURRENCY_CODE REPORT_TOTAL_CURRENCY,

TO_CHAR(decode(nvl(nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0),0),

0, decode(AI.CANCELLED_DATE,

null, APS.GROSS_AMOUNT,

AERH.TOTAL),

nvl(AI.AMT_DUE_CCARD_COMPANY, AERH.AMT_DUE_CCARD_COMPANY) + nvl(AI.AMT_DUE_EMPLOYEE, AERH.AMT_DUE_EMPLOYEE) + nvl(AERH.MAXIMUM_AMOUNT_TO_APPLY,0)) ,

FND_CURRENCY_CACHE.GET_FORMAT_MASK

(AI.INVOICE_CURRENCY_CODE, 30))

REPORT_TOTAL,

P.PERSON_ID EMPLOYEE_ID,

AERH.REPORT_HEADER_ID REPORT_HEADER_ID,

P.FULL_NAME FULL_NAME ,

DECODE(AI.CANCELLED_DATE,null,

nvl(aerh.expense_status_code, DECODE(APS.GROSS_AMOUNT ,0,'PAID',

decode(AI.Payment_status_flag,'Y','PAID',

'N','INVOICED',

'P','PARPAID',NULL))),

'CANCELLED') STATUS_CODE,

AERH.source SOURCE,

NULL CURRENT_APPROVER,

ROUND(sysdate - AI.LAST_UPDATE_DATE) DAYS_SINCE_ACTIVITY,

AERH.RECEIPTS_STATUS RECEIPTS_STATUS_CODE,

AERH.HOLDING_REPORT_HEADER_ID,

AI.VENDOR_ID VENDOR_ID,

AERH.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY,

AERH.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE,

'CurrentApproverName' CURRENT_APPROVER_SWITCHER,

to_char(AERH.LAST_UPDATE_DATE, 'DD-MON-RRRR HH:MI:SS'),

AI.INVOICE_ID INVOICE_ID,

AERH.IMAGE_RECEIPTS_STATUS IMAGE_RECEIPTS_STATUS_CODE,

AERH.ATTRIBUTE5 TRACK_ATTRIBUTE5

FROM

AK_WEB_USER_SEC_ATTR_VALUES A,

AP_INVOICES AI,

AP_EXPENSE_REPORT_HEADERS AERH,

PER_PEOPLE_X P,

AP_PAYMENT_SCHEDULES APS

WHERE AI.INVOICE_ID= APS.INVOICE_ID

AND AI.INVOICE_ID = AERH.VOUCHNO

AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'

AND AI.SOURCE in ('XpenseXpress','SelfService', 'CREDIT CARD', 'Both Pay')

AND A.ATTRIBUTE_CODE = 'ICX_HR_PERSON_ID'

AND AI.PAID_ON_BEHALF_EMPLOYEE_ID = A.NUMBER_VALUE

AND A.WEB_USER_ID = :5

AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID

AND AP_WEB_DB_HR_INT_PKG.IsPersonCwk(AI.PAID_ON_BEHALF_EMPLOYEE_ID)='Y'

AND DECODE (AI.PAYMENT_STATUS_FLAG,

'Y', sysdate - AI.LAST_UPDATE_DATE,

decode(APS.GROSS_AMOUNT , 0 ,sysdate - AI.LAST_UPDATE_DATE,0)

) <= 30

AND (AERH.SOURCE <> 'Both Pay' OR AERH.REPORT_HEADER_ID IS NULL)

Step 22:-Paste the Changed Sql query in the VO Object and Click Next.

Step 23:-Select the required Check Box and click Finish.

Step 24:- This is the Extended VO created As Below.

Step 25:- Now We will do Substitution of this Custom view Object.

Right click on the Project and Click Project Properties.

Go to the Substitutions:-

select standard VO and extended VO and Click on Add Button

Click on Ok Button.

Step 26:- Move the VO file in Java_Top and then Import this View Object with the Help of this Script.

java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/oracle/apps/ap/oie/server/ TrackExpenseReportsVOEx.xml -rootdir $JAVA_TOP -username apps -password apps -dbconnection ""(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebs.apps.com)(PORT=1521))(CONNECT_DATA=(SID=vis)))"

Step 27:- To check the extension has done or not go to the sql developer execute the

following command

begin

jdr_utils.listCustomizations('/oracle/apps/ap/oie/server/TrackExpenseReportsVOEx'); end;

It will give the path.

Step 28:- Bounce the Apache Web Server.

Step 29:- Go to the front end go to the page which u want to extend.

Click on about this page. Expand all in business components check whether the extended VO is reflecting or not.

oracle.apps.ap.oie.server.TrackExpenseReportsVOEx

Step 29:- Now again go Back to the Web page in application where we want to do this View Object Extension. Now Click on the 'Personalize Link' Highlighted below.