[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| pn_space_assign_emp_pkg.update_row
|
| ARGUMENTS : IN:
| p_as_of_date
| p_locn_type
| p_locn_code_from
| p_locn_code_to
| p_emp_cost_center
| OUT:
| Std. concurrent program out params (errbuf, retcode)
|
|
| MODIFICATION HISTORY
|
| 13-NOV-03 Vikas Mehta Created
| 26-OCT-05 Hareesha o ATG mandated changes for SQL literals using
| dbms_sql.
| 04-APR-06 Hareesha o Bug 5119241 Modified call to
| PN_SPACE_ASSIGN_EMP_PKG.Update_Row to pass
| new CC.
| 30-JUN-06 Hareesha o Bug #5262982 Select org_id too in the query
| along with all other columns.The new record
| created was not being populated with org_id.
| 10-AUG-07 Hareesha o Bug 6168505 , this package invalid at macerich's instance,
| because, asg_rec is referring to baseview rowtype,
| instead of _all rowtype.
+===========================================================================*/
PROCEDURE cc_sync_with_hr (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_as_of_date IN VARCHAR2,
p_locn_type IN pn_locations.location_type_lookup_code%TYPE,
p_locn_code_from IN pn_locations.location_code%TYPE,
p_locn_code_to IN pn_locations.location_code%TYPE,
p_emp_cost_center IN pn_space_assign_emp.cost_center_code%TYPE
) IS
asg_rec pn_space_assign_emp_ALL%ROWTYPE;
l_last_updated_by pn_space_assign_emp.last_updated_by%TYPE ;
l_last_update_login pn_space_assign_emp.last_update_login%TYPE ;
SELECT
FULL_NAME
FROM
PER_PEOPLE_F
WHERE person_id = p_employee_id
AND TRUNC(p_as_of_date) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND EMPLOYEE_NUMBER IS NOT NULL ;
l_last_updated_by := nvl(fnd_profile.VALUE ('USER_ID'), 0);
l_last_update_login := nvl(fnd_profile.value('LOGIN_ID'),0);
'SELECT
EMP_SPACE_ASSIGN_ID
,LOCATION_ID
,PERSON_ID
,PROJECT_ID
,TASK_ID
,EMP_ASSIGN_START_DATE
,EMP_ASSIGN_END_DATE
,COST_CENTER_CODE
,ALLOCATED_AREA_PCT
,ALLOCATED_AREA
,UTILIZED_AREA
,EMP_SPACE_COMMENTS
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORG_ID
FROM PN_SPACE_ASSIGN_EMP ';
l_where_clause := l_where_clause || ' AND location_id IN (SELECT location_id FROM pn_locations ' ;
dbms_sql.define_column (l_cursor, 13,asg_rec.LAST_UPDATE_DATE);
dbms_sql.define_column (l_cursor, 14,asg_rec.LAST_UPDATED_BY);
dbms_sql.define_column (l_cursor, 17,asg_rec.LAST_UPDATE_LOGIN);
dbms_sql.column_value (l_cursor, 13,asg_rec.LAST_UPDATE_DATE);
dbms_sql.column_value (l_cursor, 14,asg_rec.LAST_UPDATED_BY);
dbms_sql.column_value (l_cursor, 17,asg_rec.LAST_UPDATE_LOGIN);
l_log_context := ' inserting/updating data for assignment_id : ' || asg_rec.emp_space_assign_id ;
We do not need to insert new record but only need to update cost ceneter in this case. */
IF l_as_of_date = trunc(asg_rec.emp_assign_start_date) THEN
/* Call Update_Row in CORRECT mode */
PN_SPACE_ASSIGN_EMP_PKG.Update_Row(
asg_rec.emp_space_assign_id,
asg_rec.attribute1,
asg_rec.attribute2,
asg_rec.attribute3,
asg_rec.attribute4,
asg_rec.attribute5,
asg_rec.attribute6,
asg_rec.attribute7,
asg_rec.attribute8,
asg_rec.attribute9,
asg_rec.attribute10,
asg_rec.attribute11,
asg_rec.attribute12,
asg_rec.attribute13,
asg_rec.attribute14,
asg_rec.attribute15,
asg_rec.location_id,
asg_rec.person_id,
asg_rec.project_id,
asg_rec.task_id,
asg_rec.emp_assign_start_date,
asg_rec.emp_assign_end_date,
l_cost_center,
asg_rec.allocated_area_pct,
asg_rec.allocated_area,
asg_rec.utilized_area,
asg_rec.emp_space_comments,
asg_rec.attribute_category,
sysdate,
l_last_updated_by,
l_last_update_login,
'CORRECT',
l_out_date,
l_source_id /* Use process identifier for source column */
) ;
/* Need to update current record and insert new record
Call Update_Row in UPDATE mode */
PN_SPACE_ASSIGN_EMP_PKG.tlempinfo.source := l_source_id || '_' || asg_rec.emp_space_assign_id ;
PN_SPACE_ASSIGN_EMP_PKG.Update_Row(
asg_rec.emp_space_assign_id,
asg_rec.attribute1,
asg_rec.attribute2,
asg_rec.attribute3,
asg_rec.attribute4,
asg_rec.attribute5,
asg_rec.attribute6,
asg_rec.attribute7,
asg_rec.attribute8,
asg_rec.attribute9,
asg_rec.attribute10,
asg_rec.attribute11,
asg_rec.attribute12,
asg_rec.attribute13,
asg_rec.attribute14,
asg_rec.attribute15,
asg_rec.location_id,
asg_rec.person_id,
asg_rec.project_id,
asg_rec.task_id,
l_as_of_date, /* Use As of Date as start date */
asg_rec.emp_assign_end_date,
l_cost_center, /* Use HR cost center */
asg_rec.allocated_area_pct,
asg_rec.allocated_area,
asg_rec.utilized_area,
asg_rec.emp_space_comments,
asg_rec.attribute_category,
sysdate,
l_last_updated_by,
l_last_update_login,
'UPDATE',
l_out_date,
l_source_id /* Use process identifier for source column */
) ;
SELECT
A.DEFAULT_CODE_COMB_ID,
P.FULL_NAME
FROM
PER_PEOPLE_F P,
PER_ALL_ASSIGNMENTS_F A,
PER_PERIODS_OF_SERVICE B
WHERE
A.PERSON_ID = P.PERSON_ID
AND A.person_id = p_employee_id
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE = 'E'
AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID
AND TRUNC(p_as_of_date) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND TRUNC(p_as_of_date) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND (trunc(B.ACTUAL_TERMINATION_DATE)>= trunc(p_as_of_date) or B.ACTUAL_TERMINATION_DATE is null)
AND P.EMPLOYEE_NUMBER IS NOT NULL
AND A.DEFAULT_CODE_COMB_ID IS NOT NULL ;
' SELECT '|| l_column_name ||
' FROM gl_code_combinations
WHERE code_combination_id = :x_code_comb_id ';