| Updating this composite View. Help Urgent [message #231213] |
Mon, 16 April 2007 00:59  |
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  |
michael_bialik
Messages: 621
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
