[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Inserts and Updates. Added function
| Exists_Property_Id for validating Property_id.
| Added validations for Locations_Alias.
| 08-MAY-02 Kiran Hegde o Fixed Bug#2341761. Added column
| Standard_Type_Lookup_Code to PN_LOCATIONS_ITF.
| Modified inserts and updates appropriately.
| Changed the validations of Usable, Rentable,
| Assignable to match the validations in the form
| 14-MAY-02 Kiran Hegde o Added validations to Locations_Itf to behave
| similar to the forms for Assgn and Common Area
| 12-nov-02 Kiran Hegde o Removed all calls to enable_file_debug().
| 22-JAN-03 MRinal Misra o Put a check for rentable area must not be
| greater than gross area of parent and displayed
| msg. PN_GROSS_RENTABLE in log.
| 24-JAN-03 Kiran o Made several changes to Locations_Itf procedure
| 1. Added validations for Active Start Date and
| End date to behave like the Locations form.
| 2. Removed rollback from exception handling
| 3. Added validations for a valid combination of
| Id and Code for Locations.
| 4. Changed several messages
| 07-AUG-03 Kiran o Populated the Addresses %ROWTYPE variable
| before passing it to PNT_LOCATIONS_PKG.
| correct_update_row. Removd explicit update
| statements to update pn_addresses_all. Bug #
| 3079433. Changed 'IF' condition for calling
| validate_gross_area in 'COrrect' and 'Update'
| mode.
| 07-JAN-04 Daniel Thota o Added OUT param l_total_allocated_area_pct
| in call to pnp_util_func.get_allocated_area
| bug # 3354278
| 01-APR-05 piagrawa o Modified the signature to include org_id,
| Modified the select statements to retrieve values
| from _ALL tables and in INSERT_ROW call passed
| the value of p_org_id in place of
| fnd_profile.value('ORG_ID')
| 14-JUL-05 piagrawa o passed org id in call to Is_Id_Code_Valid
| 27-JUL-05 SatyaDeep o Added a check for office/section in the call to
| PNT_LOCATIONS_PKG.check_location_gaps for Bug#4503407
| 27-JUL-05 SatyaDeep o Added validation for NEW_ACTIVE_START_DATE
| and NEW_ACTIVE_END_DATE for Bug#4503407
| 23-NOV-05 Hareesha o Fetched org_id using cursor
| 01-DEC-05 Pikhar o Passed org_id in
| PNT_LOCATIONS_PKG.check_unique_building_alias
| 26-Nov-08 rthumma o Bug 6670882 : Modified to do a commit after the
| processing loop.
| 27-Nov-08 rthumma o Bug 6861678 : Selected the occupancy_status_code
| of the parent_location in cursor loccur to
| set the occupancy_status_code for the child.
+===========================================================================*/
PROCEDURE LOCATIONS_ITF (
p_batch_name VARCHAR2,
p_org_id NUMBER,
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
)
IS
l_succ NUMBER DEFAULT 0;
SELECT pli.*, pli.rowid,occupancy_status_code
FROM PN_LOCATIONS_ITF pli,
PN_LOCATIONS_ALL pnl
WHERE pli.parent_location_id = pnl.location_id(+)
AND pli.batch_name = p_batch_name
AND pli.transferred_to_pn IS NULL
ORDER BY pli.location_type_lookup_code, pli.active_start_date;
DELETE_RECORD EXCEPTION;
l_last_update_date DATE;
select min(active_start_date), nvl(max(active_end_date),PNT_LOCATIONS_PKG.G_END_OF_TIME)
into l_active_start_date, l_active_end_date
from pn_locations_all where location_id = loc.PARENT_LOCATION_ID
group by location_id;
put_log( 'Just before Insert');
PNT_LOCATIONS_PKG.INSERT_ROW (
x_rowid => l_rowid
,x_org_id => l_org_id
,x_LOCATION_ID => loc.location_id
,x_LAST_UPDATE_DATE => sysdate
,x_LAST_UPDATED_BY => fnd_global.user_id
,x_CREATION_DATE => sysdate
,x_CREATED_BY => fnd_global.user_id
,x_LAST_UPDATE_LOGIN => fnd_global.user_id
,x_LOCATION_PARK_ID => NULL
,x_LOCATION_TYPE_LOOKUP_CODE => loc.location_type_lookup_code
,x_SPACE_TYPE_LOOKUP_CODE => loc.space_type_lookup_code
,x_FUNCTION_TYPE_LOOKUP_CODE => loc.function_type_lookup_code
,x_STANDARD_TYPE_LOOKUP_CODE => loc.standard_type_lookup_code
,x_LOCATION_ALIAS => loc.location_alias
,x_LOCATION_CODE => loc.location_code
,x_BUILDING => loc.building
,x_LEASE_OR_OWNED => loc.lease_or_owned
,x_CLASS => loc.class
,x_STATUS_TYPE => loc.status_type
,x_FLOOR => loc.floor
,x_OFFICE => loc.office
,x_MAX_CAPACITY => loc.max_capacity
,x_OPTIMUM_CAPACITY => loc.optimum_capacity
,x_GROSS_AREA => loc.gross_area
,x_RENTABLE_AREA => loc.rentable_area
,x_USABLE_AREA => loc.usable_area
,x_ASSIGNABLE_AREA => loc.assignable_area
,x_COMMON_AREA => loc.common_area
,x_SUITE => loc.suite
,x_ALLOCATE_COST_CENTER_CODE => loc.allocate_cost_center_code
,x_UOM_CODE => loc.uom_code
,x_DESCRIPTION => NULL
,x_PARENT_LOCATION_ID => loc.parent_location_id
,x_INTERFACE_FLAG => NULL
,x_request_id => nvl(fnd_profile.value('CONC_REQUEST_ID'), 0)
,x_PROGRAM_ID => nvl(fnd_profile.value('CONC_PROGRAM_APPLICATION_ID'), 0)
,x_PROGRAM_APPLICATION_ID => nvl(fnd_profile.value('CONC_PROGRAM_ID'), 0)
,x_PROGRAM_UPDATE_DATE => SYSDATE
,x_STATUS => 'A'
,x_PROPERTY_ID => loc.property_id
,x_ATTRIBUTE_CATEGORY => loc.attribute_category
,x_ATTRIBUTE1 => loc.attribute1
,x_ATTRIBUTE2 => loc.attribute2
,x_ATTRIBUTE3 => loc.attribute3
,x_ATTRIBUTE4 => loc.attribute4
,x_ATTRIBUTE5 => loc.attribute5
,x_ATTRIBUTE6 => loc.attribute6
,x_ATTRIBUTE7 => loc.attribute7
,x_ATTRIBUTE8 => loc.attribute8
,x_ATTRIBUTE9 => loc.attribute9
,x_ATTRIBUTE10 => loc.attribute10
,x_ATTRIBUTE11 => loc.attribute11
,x_ATTRIBUTE12 => loc.attribute12
,x_ATTRIBUTE13 => loc.attribute13
,x_ATTRIBUTE14 => loc.attribute14
,x_ATTRIBUTE15 => loc.attribute15
,x_address_id => l_address_id
,x_address_line1 => loc.address_line1
,x_address_line2 => loc.address_line2
,x_address_line3 => loc.address_line3
,x_address_line4 => loc.address_line4
,x_county => loc.county
,x_city => loc.city
,x_state => loc.state
,x_province => loc.province
,x_zip_code => loc.zip_code
,x_country => loc.country
,x_territory_id => NULL
,x_addr_last_update_date => SYSDATE
,x_addr_last_updated_by => FND_GLOBAl.USER_ID
,x_addr_creation_date => SYSDATE
,x_addr_created_by => FND_GLOBAL.USER_ID
,x_addr_last_update_login => FND_GLOBAL.USER_ID
,x_addr_attribute_category => loc.addr_attribute_category
,x_addr_attribute1 => loc.addr_attribute1
,x_addr_attribute2 => loc.addr_attribute2
,x_addr_attribute3 => loc.addr_attribute3
,x_addr_attribute4 => loc.addr_attribute4
,x_addr_attribute5 => loc.addr_attribute5
,x_addr_attribute6 => loc.addr_attribute6
,x_addr_attribute7 => loc.addr_attribute7
,x_addr_attribute8 => loc.addr_attribute8
,x_addr_attribute9 => loc.addr_attribute9
,x_addr_attribute10 => loc.addr_attribute10
,x_addr_attribute11 => loc.addr_attribute11
,x_addr_attribute12 => loc.addr_attribute12
,x_addr_attribute13 => loc.addr_attribute13
,x_addr_attribute14 => loc.addr_attribute14
,x_addr_attribute15 => loc.addr_attribute15
,x_COMMON_AREA_FLAG => loc.common_area_flag
,x_ACTIVE_START_DATE => trunc(nvl(loc.active_start_date,PNT_LOCATIONS_PKG.G_START_OF_TIME))--Used trunc() :Bug 6009957
,x_ACTIVE_END_DATE => trunc(nvl(loc.active_end_date ,PNT_LOCATIONS_PKG.G_END_OF_TIME))--Used trunc() :Bug 6009957
,x_occupancy_status_code => l_occ_status /* Bug 6861678 */
,x_change_mode => nvl(loc.change_mode,'INSERT')
,x_return_status => l_returnstatus
,x_return_message => l_return_message
,x_bookable_flag => null
,x_source => loc.source);
put_log('Just after Insert');
select *
into v_loc_rec
from PN_LOCATIONS_ALL
where LOCATION_ID = loc.location_id
and location_code = loc.location_code
and active_Start_date = trunc(loc.active_start_date)
and active_End_date = trunc(nvl(loc.active_end_date, PNT_LOCATIONS_PKG.G_END_OF_TIME));
select *
into v_addr_rec
from pn_addresses_all
where address_id = v_loc_rec.address_id;
IF ( (nvl(loc.change_mode,'CORRECT') = 'UPDATE') and
loc.change_date is NULL ) THEN
fnd_message.set_name('PN', 'PN_CAFM_INVALID_CHANGE_DATE');
IF (nvl(loc.change_mode,'CORRECT') = 'UPDATE') AND
((loc.CHANGE_DATE < trunc(NVL(loc.new_active_start_date,
v_loc_rec.active_start_date))) OR
(loc.CHANGE_DATE > trunc(NVL(loc.new_active_end_date,
v_loc_rec.active_end_date)))) THEN
fnd_message.set_name('PN', 'PN_LOC_SPILT_DATE_MSG');
IF NVL(loc.change_mode, 'CORRECT') = 'UPDATE' THEN
l_active_start_date := trunc(loc.change_date);
l_pn_locations_rec.LAST_UPDATE_DATE := sysdate;
l_pn_locations_rec.LAST_UPDATE_LOGIN := nvl(fnd_profile.value('CONC_LOGIN_ID'), 0);
l_pn_locations_rec.LAST_UPDATED_BY := nvl(fnd_profile.value('CONC_USER_ID'), 0);
l_pn_locations_rec.PROGRAM_UPDATE_DATE := sysdate ;
l_pn_addresses_rec.LAST_UPDATE_DATE := sysdate;
l_pn_addresses_rec.LAST_UPDATED_BY := nvl(fnd_profile.value('USER_ID'), 0);
l_pn_addresses_rec.LAST_UPDATE_LOGIN := nvl(fnd_profile.value('CONC_LOGIN_ID'), 0);
put_log( 'U: Just before Correct/Update');
PNT_LOCATIONS_PKG.correct_update_row
( p_pn_locations_rec => l_pn_locations_rec,
p_pn_addresses_rec => l_pn_addresses_rec,
p_change_mode => nvl(loc.change_mode, 'CORRECT'),
p_as_of_date => loc.change_date,
p_active_start_date_old => trunc(loc.active_start_date),
p_active_end_date_old => trunc(v_loc_rec.active_end_date),
p_assgn_area_chgd_flag => l_asgn_area_chng_flag,
x_return_status => l_returnstatus,
x_return_message => l_return_message
);
put_log( 'U: Just after Correct/Update');
l_pn_locations_rec.LAST_UPDATE_DATE := sysdate;
l_pn_locations_rec.LAST_UPDATE_LOGIN := nvl( fnd_profile.value('CONC_LOGIN_ID'), 0);
l_pn_locations_rec.LAST_UPDATED_BY := nvl( fnd_profile.value('CONC_USER_ID'), 0);
l_pn_locations_rec.PROGRAM_UPDATE_DATE:= sysdate;
l_pn_addresses_rec.LAST_UPDATE_DATE := sysdate;
l_pn_addresses_rec.LAST_UPDATED_BY := nvl(fnd_profile.value('USER_ID'), 0);
l_pn_addresses_rec.LAST_UPDATE_LOGIN := nvl(fnd_profile.value('CONC_LOGIN_ID'), 0);
put_log( 'R: Just before Correct/Update');
PNT_LOCATIONS_PKG.correct_update_row
( p_pn_locations_rec => l_pn_locations_rec,
p_pn_addresses_rec => l_pn_addresses_rec,
p_change_mode => nvl(loc.change_mode, 'CORRECT'),
p_as_of_date => loc.change_date,
p_active_start_date_old => trunc(loc.active_start_date), --Used trunc() :Bug 6009957
p_active_end_date_old => trunc(v_loc_rec.active_end_date), --Used trunc() :Bug 6009957
p_assgn_area_chgd_flag => l_asgn_area_chng_flag,
x_return_status => l_returnstatus,
x_return_message => l_return_message
);
put_log( 'R: Just before Correct/Update');
put_log('R:Error in correctupdate_row ' || l_return_message);
put_log('Update ITF set transfer = Y');
UPDATE pn_locations_itf
SET transferred_to_pn = 'Y',
error_message = NULL
WHERE rowid = loc.rowid;
UPDATE pn_locations_itf
SET error_message = substr(l_error_message, 1, 240)
WHERE rowid = loc.rowid;
WHEN DELETE_RECORD THEN
NULL;
UPDATE pn_locations_itf
SET error_message = substr(l_error_message, 1, 240)
WHERE rowid = loc.rowid;
SELECT loc.LOCATION_CODE
FROM PN_LOCATIONS_ALL loc
WHERE loc.LOCATION_ID = p_Loc_Id;
SELECT loc.LOCATION_ID
FROM PN_LOCATIONS_ALL loc
WHERE loc.LOCATION_CODE = p_Loc_Code
AND org_id = p_org_id;
| 01-APR-05 piagrawa o Modified the select statements to retrieve values
| from _ALL tables
+===========================================================================*/
Function Get_Location_Type ( p_location_id Number ) Return VarChar2 is
l_lookup_type varchar2(30);
Select distinct location_type_lookup_code
into l_lookup_type
from PN_LOCATIONS_ALL
where location_id = p_location_id ;
| 01-APR-05 piagrawa o Modified the select statements to retrieve values
| from _ALL tables
+===========================================================================*/
Function Exists_Property_Id (
p_property_id NUMBER
)
Return Boolean IS
l_number Number;
Select 1
Into l_number
From Pn_Properties_all
Where Property_Id = p_property_id ;
| 20-Nov-03 Daniel o Modified SELECT to go after
| _ALL tables for PN_SPACE_ASSIGN_EMP
| Fix for bug # 3272712
| 26-MAY-04 abanerje o Added condition to check for p_assignable_area
| is -99. In this case error out indicating that
| the location is a common space.
| Bug #3598315
| 16-JUN-04 ftanudja o Created variable tlempinfo.
| o Removed all references to pn_space_assign_emp_pkg.
| tlempinfo.
| o Added validation for allocated_area for manual
| spc assignments.
| o Fixed id validation for entry type 'U','R'.
| o #3649373
| 01-APR-05 piagrawa o Modified the signature to include org_id,
| also in INSERT_ROW call passed
| the value of p_org_id in place of
| fnd_profile.value('ORG_ID')
| 19-JUL-05 sdm o Passed SOURCE from PN_EMP_ASSIGN_SPACE_ITF to
| Procedure PN_SPACE_ASSIGN_EMP.INSERT_ROW and
| Procedure PN_SPACE_ASSIGN_EMP.UPDATE_ROW
| 23-NOV-05 Hareesha o Modified get_profile_value to include org_id as
| parameter.
| 01-DEC-05 pikhar o passed org_id in pnp_util_func.get_cc_code and
| o pnp_util_func.valid_cost_center
| 31-Aug-06 Prabhakaro Bug #5449595 Validated project_id and task_id
| and if valid,Added project_id and task_id
| in insert_row and upate_row
| 11-OCT-06 acprakas o Bug#5587012. Passed location_id to procedure
| PNP_UTIL_FUNC.Validate_date_for_assignments instead
| of EMP_SPACE_ASSIGN_ID.
| 08-NOV-06 lbala o Bug#5636783.Added Commit prior to END LOOP and
| SAVEPOINT S2.Added code in exception block for WHEN
| OTHERS to update errored record count.
| 24-AUG-08 RKARTHA o Bug#6826510 - Round off the allocated_area_pct to
| two decimal places.
+===========================================================================*/
PROCEDURE space_allocations_itf (
p_batch_name VARCHAR2,
p_org_id NUMBER,
errbuf out NOCOPY VarChar2,
retcode out NOCOPY VarChar2
) IS
l_succ Number Default 0;
SELECT spc.*, spc.rowid
FROM pn_emp_space_assign_itf spc
WHERE spc.batch_name = p_batch_name
AND spc.transferred_to_pn is null;
SELECT project_id
FROM pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.org_id = l_org_id
AND NVL(proj.template_flag,'N') <>'Y';
SELECT task_id
FROM pa_tasks task
WHERE task.project_id = p_project_id
AND task.task_id = p_task_id;
put_log( 'Validating emp_space_assign_id and getting the old record for UPDATE/REPLACE' );
SELECT *
INTO tlempinfo
FROM pn_space_assign_emp_all
WHERE EMP_SPACE_ASSIGN_ID = space.EMP_SPACE_ASSIGN_ID;
PN_SPACE_ASSIGN_EMP_PKG.insert_row
(
X_ROWID => l_rowid_dummy,
X_org_id => l_org_id,
X_EMP_SPACE_ASSIGN_ID => space.EMP_SPACE_ASSIGN_ID,
X_ATTRIBUTE1 => space.ATTRIBUTE1,
X_ATTRIBUTE2 => space.ATTRIBUTE2,
X_ATTRIBUTE3 => space.ATTRIBUTE3,
X_ATTRIBUTE4 => space.ATTRIBUTE4,
X_ATTRIBUTE5 => space.ATTRIBUTE5,
X_ATTRIBUTE6 => space.ATTRIBUTE6,
X_ATTRIBUTE7 => space.ATTRIBUTE7,
X_ATTRIBUTE8 => space.ATTRIBUTE8,
X_ATTRIBUTE9 => space.ATTRIBUTE9,
X_ATTRIBUTE10 => space.ATTRIBUTE10,
X_ATTRIBUTE11 => space.ATTRIBUTE11,
X_ATTRIBUTE12 => space.ATTRIBUTE12,
X_ATTRIBUTE13 => space.ATTRIBUTE13,
X_ATTRIBUTE14 => space.ATTRIBUTE14,
X_ATTRIBUTE15 => space.ATTRIBUTE15,
X_LOCATION_ID => space.LOCATION_ID,
X_PERSON_ID => space.EMPLOYEE_ID,
X_PROJECT_ID => space.PROJECT_ID,
X_TASK_ID => space.TASK_ID,
X_EMP_ASSIGN_START_DATE => space.EMP_ASSIGN_START_DATE,
X_EMP_ASSIGN_END_DATE => space.EMP_ASSIGN_END_DATE,
X_COST_CENTER_CODE => space.COST_CENTER_CODE,
X_ALLOCATED_AREA_PCT => l_allocated_area_pct,
X_ALLOCATED_AREA => space.ALLOCATED_AREA,
X_UTILIZED_AREA => space.UTILIZED_AREA,
X_EMP_SPACE_COMMENTS => NULL,
X_ATTRIBUTE_CATEGORY => space.ATTRIBUTE_CATEGORY,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => NVL( fnd_profile.value('USER_ID'), 0),
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => NVL( fnd_profile.value('USER_ID'), 0),
X_LAST_UPDATE_LOGIN => NVL( fnd_profile.value('CONC_LOGIN_ID'), 0),
X_SOURCE => space.source
);
if ( NVL( space.change_mode, 'CORRECT' ) = 'UPDATE' ) then
if ( nvl(space.change_date, trunc(SYSDATE)) < tlempinfo.emp_assign_start_date OR
nvl(space.change_date, trunc(SYSDATE)) > NVL(tlempinfo.emp_assign_end_date,
to_date('12/31/4712','mm/dd/yyyy')) ) then
fnd_message.set_name('PN', 'PN_CAFM_SPACE_UNABLE_TO_UPD');
PN_SPACE_ASSIGN_EMP_PKG.update_row
(
X_EMP_SPACE_ASSIGN_ID => space.EMP_SPACE_ASSIGN_ID,
X_ATTRIBUTE1 => space.ATTRIBUTE1,
X_ATTRIBUTE2 => space.ATTRIBUTE2,
X_ATTRIBUTE3 => space.ATTRIBUTE3,
X_ATTRIBUTE4 => space.ATTRIBUTE4,
X_ATTRIBUTE5 => space.ATTRIBUTE5,
X_ATTRIBUTE6 => space.ATTRIBUTE6,
X_ATTRIBUTE7 => space.ATTRIBUTE7,
X_ATTRIBUTE8 => space.ATTRIBUTE8,
X_ATTRIBUTE9 => space.ATTRIBUTE9,
X_ATTRIBUTE10 => space.ATTRIBUTE10,
X_ATTRIBUTE11 => space.ATTRIBUTE11,
X_ATTRIBUTE12 => space.ATTRIBUTE12,
X_ATTRIBUTE13 => space.ATTRIBUTE13,
X_ATTRIBUTE14 => space.ATTRIBUTE14,
X_ATTRIBUTE15 => space.ATTRIBUTE15,
X_LOCATION_ID => space.LOCATION_ID,
X_PERSON_ID => space.EMPLOYEE_ID,
X_PROJECT_ID => space.PROJECT_ID,
X_TASK_ID => space.TASK_ID,
X_EMP_ASSIGN_START_DATE => space.EMP_ASSIGN_START_DATE,
X_EMP_ASSIGN_END_DATE => space.EMP_ASSIGN_END_DATE,
X_COST_CENTER_CODE => space.COST_CENTER_CODE,
X_ALLOCATED_AREA_PCT => l_allocated_area_pct,
X_ALLOCATED_AREA => space.ALLOCATED_AREA,
X_UTILIZED_AREA => space.UTILIZED_AREA,
X_EMP_SPACE_COMMENTS => NULL,
X_ATTRIBUTE_CATEGORY => space.ATTRIBUTE_CATEGORY,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => nvl(fnd_profile.value('USER_ID'), 0),
X_LAST_UPDATE_LOGIN => nvl(fnd_profile.value('CONC_LOGIN_ID'), 0),
X_UPDATE_CORRECT_OPTION => space.change_mode,
X_CHANGED_START_DATE => l_changed_start_date,
X_SOURCE => space.source
);
PN_SPACE_ASSIGN_EMP_PKG.update_row
(
X_EMP_SPACE_ASSIGN_ID => NVL(space.EMP_SPACE_ASSIGN_ID,tlempinfo.EMP_SPACE_ASSIGN_ID),
X_ATTRIBUTE1 => NVL(space.ATTRIBUTE1,tlempinfo.ATTRIBUTE1),
X_ATTRIBUTE2 => NVL(space.ATTRIBUTE2,tlempinfo.ATTRIBUTE2),
X_ATTRIBUTE3 => NVL(space.ATTRIBUTE3,tlempinfo.ATTRIBUTE3),
X_ATTRIBUTE4 => NVL(space.ATTRIBUTE4,tlempinfo.ATTRIBUTE4),
X_ATTRIBUTE5 => NVL(space.ATTRIBUTE5,tlempinfo.ATTRIBUTE5),
X_ATTRIBUTE6 => NVL(space.ATTRIBUTE6,tlempinfo.ATTRIBUTE6),
X_ATTRIBUTE7 => NVL(space.ATTRIBUTE7,tlempinfo.ATTRIBUTE7),
X_ATTRIBUTE8 => NVL(space.ATTRIBUTE8,tlempinfo.ATTRIBUTE8),
X_ATTRIBUTE9 => NVL(space.ATTRIBUTE9,tlempinfo.ATTRIBUTE9),
X_ATTRIBUTE10 => NVL(space.ATTRIBUTE10,tlempinfo.ATTRIBUTE10),
X_ATTRIBUTE11 => NVL(space.ATTRIBUTE11,tlempinfo.ATTRIBUTE11),
X_ATTRIBUTE12 => NVL(space.ATTRIBUTE12,tlempinfo.ATTRIBUTE12),
X_ATTRIBUTE13 => NVL(space.ATTRIBUTE13,tlempinfo.ATTRIBUTE13),
X_ATTRIBUTE14 => NVL(space.ATTRIBUTE14,tlempinfo.ATTRIBUTE14),
X_ATTRIBUTE15 => NVL(space.ATTRIBUTE15,tlempinfo.ATTRIBUTE15),
X_LOCATION_ID => NVL(space.LOCATION_ID,tlempinfo.LOCATION_ID),
X_PERSON_ID => NVL(space.EMPLOYEE_ID,tlempinfo.PERSON_ID),
X_PROJECT_ID => space.PROJECT_ID,
X_TASK_ID => space.TASK_ID,
X_EMP_ASSIGN_START_DATE => NVL(space.EMP_ASSIGN_START_DATE,tlempinfo.EMP_ASSIGN_START_DATE),
X_EMP_ASSIGN_END_DATE => NVL(space.EMP_ASSIGN_END_DATE,tlempinfo.EMP_ASSIGN_END_DATE),
X_COST_CENTER_CODE => NVL(space.COST_CENTER_CODE,tlempinfo.COST_CENTER_CODE),
X_ALLOCATED_AREA_PCT => NVL(l_allocated_area_pct,tlempinfo.ALLOCATED_AREA_PCT),
X_ALLOCATED_AREA => NVL(space.ALLOCATED_AREA,tlempinfo.ALLOCATED_AREA),
X_UTILIZED_AREA => NVL(space.UTILIZED_AREA,tlempinfo.UTILIZED_AREA),
X_EMP_SPACE_COMMENTS => NULL,
X_ATTRIBUTE_CATEGORY => NVL(space.ATTRIBUTE_CATEGORY,tlempinfo.ATTRIBUTE_CATEGORY),
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => nvl(fnd_profile.value('USER_ID'), 0),
X_LAST_UPDATE_LOGIN => nvl(fnd_profile.value('CONC_LOGIN_ID'), 0),
X_UPDATE_CORRECT_OPTION => space.change_mode,
X_CHANGED_START_DATE => l_changed_start_date,
X_SOURCE => NVL(space.source,tlempinfo.source)
);
update pn_emp_space_assign_itf
set transferred_to_pn = 'Y',
error_message = NULL
where ROWID = space.ROWID;
put_log('Updated transferred to pn flag in itf table');
update pn_emp_space_assign_itf
set error_message = substr(l_error_message, 1, 240)
where ROWID = space.ROWID;
update pn_emp_space_assign_itf
set error_message = substr(l_error_message, 1, 240)
where ROWID = space.ROWID;