Home » Applications » Oracle Fusion Apps & E-Business Suite » Updating this composite View. Help Urgent
Updating this composite View. Help Urgent [message #231213] Mon, 16 April 2007 00:59 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Ours is a project of 11.0.3 to 11i upgrade. In 11.0.3 a table named PA_PROJECT_PLAYERS was used and the package in question has an update statement on this table.
But in 11i this is now a view based on 3 tables.
PA_PROJECT_PARTIES
PA_ROLE_CONTROLS
PA_PROJECT_ROLE_TYPES_B
The script for this view is

SELECT ppp.project_party_id, ppp.project_id, ppp.resource_source_id,
pprt.project_role_type, ppp.resource_id, ppp.resource_type_id,
ppp.start_date_active, ppp.grant_id, ppp.scheduled_flag,
ppp.last_update_date, ppp.last_updated_by, ppp.creation_date,
ppp.created_by, ppp.last_update_login, ppp.end_date_active,
ppp.record_version_number
FROM pa_project_parties ppp,
pa_role_controls prc,
pa_project_role_types_b pprt
WHERE ppp.object_type = 'PA_PROJECTS'
AND ppp.project_role_id = pprt.project_role_id
AND ppp.project_role_id = prc.project_role_id
AND prc.role_control_code = 'ALLOW_AS_PROJ_MEMBER'
AND ppp.resource_type_id = 101;

Now the update statement is as follows:

UPDATE PA_PROJECT_PLAYERS
SET END_DATE_ACTIVE = NULL
WHERE PROJECT_ID = l_project_id
AND END_DATE_ACTIVE = l_dummy_end_date;

Now how do i change this update statement to make it 11i compatible.
Re: Updating this composite View. Help Urgent [message #231226 is a reply to message #231213] Mon, 16 April 2007 01:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Depends on your reqs:

1. Simple solution
UPDATE pa_project_parties ppp
SET ppp.END_DATE_ACTIVE = NULL
WHERE ppp.PROJECT_ID = l_project_id AND
ppp.END_DATE_ACTIVE = l_dummy_end_date AND
ppp.object_type = 'PA_PROJECTS' AND
ppp.resource_type_id = 101;

2. More complicated one:
UPDATE pa_project_parties ppp
SET ppp.END_DATE_ACTIVE = NULL
WHERE ppp.PROJECT_ID = l_project_id AND
ppp.END_DATE_ACTIVE = l_dummy_end_date AND
ppp.object_type = 'PA_PROJECTS' AND
ppp.resource_type_id = 101 AND
EXISTS
(SELECT 1 FROM pa_role_controls prc
WHERE prc.role_control_code = 'ALLOW_AS_PROJ_MEMBER' AND
ppp.project_role_id = prc.project_role_id )

HTH.
Michael






icon7.gif  Re: Updating this composite View. Help Urgent [message #231653 is a reply to message #231226] Tue, 17 April 2007 22:32 Go to previous message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Thanks a lot Michael !!!
Previous Topic: System Administration(Menu & Function)
Next Topic: API LINK FROM CUSTOMER TO ORDER
Goto Forum:
  


Current Time: Fri Dec 09 23:03:56 CST 2016

Total time taken to generate the page: 0.10627 seconds