[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT person_id,
name,
organization_id,
assignment_start_date,
assignment_end_date,
start_date,termination_date,
default_OU,
calendar_id,
p_type,
user_type,
res_exists,
per_start_date,
per_end_date,
per_emp_number,
per_email,
per_work_phone,
per_business_group_id,
per_first_name,
per_last_name,
per_middle_name,
job_name,
supervisor_id,
org_name,
resource_type,
job_id,
job_group_id,
location_id
FROM pa_r_project_resources_v
WHERE person_id = p_in_person_id
AND (p_type = p_person_type or p_person_type = 'ALL')
UNION
-- This select is to get each person_id from HR that is terminated
-- if the user wants to pull in those people.
SELECT person_id,
name,
organization_id,
assignment_start_date,
assignment_end_date,
start_date,termination_date,
default_OU,
calendar_id,
p_type,
user_type,
res_exists,
per_start_date,
per_end_date,
per_emp_number,
per_email,
per_work_phone,
per_business_group_id,
per_first_name,
per_last_name,
per_middle_name,
job_name,
supervisor_id,
org_name,
resource_type,
job_id,
job_group_id,
location_id
FROM pa_r_project_resources_term_v
WHERE p_pull_term_res = 'Y'
and person_id = p_in_person_id
and termination_date >= p_term_range_date
AND (p_type = p_person_type or p_person_type = 'ALL')
ORDER BY person_id, assignment_start_date;
SELECT person_id,
name,
organization_id,
assignment_start_date,
assignment_end_date,
start_date,termination_date,
default_OU,
calendar_id,
p_type,
user_type,
res_exists,
per_start_date,
per_end_date,
per_emp_number,
per_email,
per_work_phone,
per_business_group_id,
per_first_name,
per_last_name,
per_middle_name,
job_name,
supervisor_id,
org_name,
resource_type,
job_id,
job_group_id,
location_id
FROM pa_r_project_resources_v
WHERE per_emp_number >= nvl(l_from_emp_num, per_emp_number) and
per_emp_number <= nvl(l_to_emp_num, per_emp_number) and
organization_id = nvl(l_p_org_id, organization_id)
ORDER BY person_id, assignment_start_date;
SELECT person_id,
name,
organization_id,
assignment_start_date,
assignment_end_date,
start_date,
termination_date,
p_type,
user_type,
location_id,
per_start_date,
per_end_date,
per_emp_number,
per_email,
per_work_phone,
per_business_group_id,
per_first_name,
per_last_name,
per_middle_name,
job_name,
supervisor_id,
org_name,
resource_type,
job_id,
job_group_id
FROM pa_r_project_resources_ind_v
WHERE person_id = l_per_id
/* Start of Changes for Bug 6056112 */
UNION
SELECT person_id,
name,
organization_id,
assignment_start_date,
assignment_end_date,
start_date,
termination_date,
p_type,
user_type,
location_id,
per_start_date,
per_end_date,
per_emp_number,
per_email,
per_work_phone,
per_business_group_id,
per_first_name,
per_last_name,
per_middle_name,
job_name,
supervisor_id,
org_name,
resource_type,
job_id,
job_group_id
FROM pa_r_project_resources_ind_t_v
WHERE person_id = l_per_id
/* End of Changes for Bug 6056112 */
order by person_id, assignment_start_date;
select person_id
from per_people_f per,
per_person_types ptype
where per.person_id = p_person_id
and per.person_type_id = ptype.person_type_id
and (ptype.system_person_type = 'EMP'
OR ptype.system_person_type = 'EMP_APL');
select person_id
from per_all_people_f per /* per_people_f per Commented for bug 2983491 , Added per_all_people_f for it*/
where per.person_id = p_person_id
and (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y');
select asgn.assignment_id
from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
per_assignment_status_types status,
(select person_id, actual_termination_date from per_periods_of_service
union all
select person_id, actual_termination_date
from per_periods_of_placement) po
where asgn.person_id = p_person_id
and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
and asgn.person_id = po.person_id
and po.person_id = p_person_id
and asgn.assignment_status_type_id = status.assignment_status_type_id
and status.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
and asgn.assignment_type in ('E', 'C') --Added for 2911451
and rownum = 1;
select asgn.assignment_id
from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
(select person_id, actual_termination_date from per_periods_of_service
union all
select person_id, actual_termination_date
from per_periods_of_placement) po
where asgn.person_id = p_person_id
and asgn.primary_flag = 'Y'
and asgn.assignment_type in ('E', 'C') --Added for 2911451
and po.person_id = p_person_id
and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
and asgn.person_id = po.person_id
-- and pos.period_of_service_id = asgn.period_of_service_id -- FP M CWK
and rownum =1;
select asgn.job_id
from per_all_assignments_f asgn, /* from per_assignments_f per Commented for bug 2983491 , Added per_all_assignments_f for it*/
(select person_id, actual_termination_date from per_periods_of_service
union all
select person_id, actual_termination_date
from per_periods_of_placement) po
where asgn.person_id = p_person_id
and asgn.primary_flag = 'Y'
and asgn.assignment_type in ('E', 'C')
and po.person_id = p_person_id
and nvl(po.actual_termination_date, trunc(sysdate)) >= trunc(sysdate)
and asgn.person_id = po.person_id
-- and pos.period_of_service_id = asgn.period_of_service_id -- FP M CWK
and asgn.job_id is not null
and rownum = 1;
/*Procedure : CRM Insert
This procedure checks if the resource exists in CRM and inserts the resource into CRM by calling the CRM public API jtf_rs_resource.create_resource if it does not exist.
It also inserts the calendar for the resource in CRM. If the resource exists the procedure checks to see if the calendar in CRM has been end dated and if so inserts the new calendar.
The procedure also checks for internal resources having multiple assignments who might have a different calendar for each assignment. If calendar is not present in hr_organization_information the calendar_id is got from profile option.*/
PROCEDURE INSERT_INTO_CRM(
P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
P_PERSON_ID IN JTF_RS_RESOURCE_extns.SOURCE_id%TYPE,
P_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
P_START_DATE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
P_ASSIGNMENT_START_DATE IN DATE,
P_ASSIGNMENT_END_DATE IN DATE,
P_CALENDAR_ID IN NUMBER,
P_COUNT IN NUMBER,
X_CRM_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE , --File.Sql.39 bug 4440895
X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
P_START_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_start_date%TYPE,
P_END_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_end_date%TYPE,
P_SOURCE_NUMBER IN pa_r_project_resources_ind_v.per_emp_number%TYPE,
P_SOURCE_JOB_TITLE IN pa_r_project_resources_ind_v.job_name%TYPE,
P_SOURCE_EMAIL IN pa_r_project_resources_ind_v.per_email%TYPE,
P_SOURCE_PHONE IN pa_r_project_resources_ind_v.per_work_phone%TYPE,
P_SOURCE_ADDRESS1 IN HR_LOCATIONS.ADDRESS_LINE_1%TYPE,
P_SOURCE_ADDRESS2 IN HR_LOCATIONS.ADDRESS_LINE_2%TYPE,
P_SOURCE_ADDRESS3 IN HR_LOCATIONS.ADDRESS_LINE_3%TYPE,
P_SOURCE_CITY IN HR_LOCATIONS.TOWN_OR_CITY%TYPE,
P_SOURCE_POSTAL_CODE IN HR_LOCATIONS.POSTAL_CODE%TYPE,
P_SOURCE_COUNTRY IN HR_LOCATIONS.COUNTRY%TYPE,
P_SOURCE_MGR_ID IN pa_r_project_resources_ind_v.supervisor_id%TYPE,
P_SOURCE_MGR_NAME IN PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
P_SOURCE_BUSINESS_GRP_ID IN pa_r_project_resources_ind_v.per_business_group_id%TYPE,
P_SOURCE_BUSINESS_GRP_NAME IN pa_r_project_resources_ind_v.org_name%TYPE,
P_SOURCE_FIRST_NAME IN pa_r_project_resources_ind_v.per_first_name%TYPE,
P_SOURCE_LAST_NAME IN pa_r_project_resources_ind_v.per_last_name%TYPE,
P_SOURCE_MIDDLE_NAME IN pa_r_project_resources_ind_v.per_middle_name%TYPE)
IS
l_insert VARCHAR2(1) := 'N';
l_end_date_insert VARCHAR2(1) := 'N'; -- Bug 4668272
SELECT PER.PERSON_ID
FROM PER_ALL_PEOPLE_F PER, /*for bug 2983491 Replaced PER_PEOPLE_F PER , */
PER_ALL_ASSIGNMENTS_F ASGN
WHERE ASGN.PERSON_ID = PER.PERSON_ID
AND ASGN.PRIMARY_FLAG = 'Y'
AND ASGN.ASSIGNMENT_TYPE in ('E', 'C')
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASGN.EFFECTIVE_START_DATE AND ASGN.EFFECTIVE_END_DATE
AND (PER.EMPLOYEE_NUMBER IS NOT NULL OR PER.NPW_NUMBER IS NOT NULL)
AND PER.PERSON_ID = p_person_id;
select resource_id
from jtf_rs_resource_extns
where source_id = l_person_id
and category = l_category ;
SELECT 'X'
FROM jtf_cal_resource_assign
WHERE cal_resource_assign_id = l_cal_resource_assign_id;
SELECT rowid
FROM jtf_cal_resource_assign
WHERE cal_resource_assign_id = l_cal_resource_assign_id;
SELECT user_id
FROM fnd_user
WHERE employee_id = l_employee_id;
SELECT 'Y'
from jtf_cal_resource_assign
where resource_id = l_crm_resource_id
and resource_type_code = l_resource_type_code
and l_assignment_start_date between start_date_time and nvl(end_date_time, to_date('31/12/4712', 'DD/MM/YYYY'))
and primary_calendar_flag = 'Y';
SELECT 'Y'
from jtf_cal_resource_assign
where resource_id = l_crm_resource_id
and resource_type_code = l_resource_type_code
and l_assignment_end_date between start_date_time and nvl(end_date_time, to_date('31/12/4712', 'DD/MM/YYYY'))
and primary_calendar_flag = 'Y';
SELECT MIN(start_date_time)
from jtf_cal_resource_assign
where resource_id = l_crm_resource_id
and resource_type_code = l_resource_type_code
and primary_calendar_flag = 'Y'
and start_date_time between l_assignment_start_date and l_assignment_end_date;
SELECT MAX(end_date_time)
from jtf_cal_resource_assign
where resource_id = l_crm_resource_id
and resource_type_code = l_resource_type_code
and primary_calendar_flag = 'Y';
PA_DEBUG.set_err_stack('Insert_into_CRM');
pa_debug.g_err_stage := 'Log: Start of Insert_into_CRM procedure';
log_message('Inside insert_into_crm procedure');
SELECT 'Y'
INTO l_fde
FROM dual
WHERE EXISTS (
SELECT 'Y'
FROM per_all_people_f
WHERE person_id = l_person_id
AND (current_employee_flag = 'Y' OR
current_npw_flag = 'Y')
AND trunc(effective_start_date) > trunc(sysdate));
SELECT max(DATE_START)
INTO l_latest_start_date
FROM
(select person_id, date_start from per_periods_of_service
union all
select person_id, date_start from per_periods_of_placement) po
WHERE po.person_id = l_person_id
AND trunc(po.date_start) <= trunc(p_assignment_start_date); -- Added for bug 4465862;
SELECT start_date_active
INTO l_calander_start_date
FROM JTF_CALENDARS_B -- change to base table - bug 4350758
WHERE calendar_id = l_calendar_id;
log_message('Set insert = Y');
l_insert := 'Y';
log_message('Set insert = Y');
l_insert := 'Y';
l_end_date_insert := 'N';
l_end_date_insert := 'Y';
l_end_date_insert := 'Y';
l_end_date_insert := 'N';
IF (l_insert = 'Y') OR (l_end_date_insert = 'Y') THEN
log_message('Begin of insert into jtf_cal_resource_assign');
select jtf_cal_resource_assign_s.nextval
into l_cal_resource_assign_id
from dual;
pa_debug.g_err_stage := 'Log: Selected unique cal_resource_assign_id';
log_message('Selected unique cal_resource_assign_id');
pa_debug.g_err_stage := 'Log: User CRM API to Insert calendar into jtf_cal_resource_assign from '|| to_char(l_assignment_start_date,'DD-MON-RR')||' to '|| to_char(l_assignment_end_date,'DD-MON-RR');
log_message('Insert calendar into jtf_cal_resource_assign');
jtf_cal_resource_assign_pkg.insert_row(
X_ERROR => l_error,
X_ROWID => l_rowid,
X_CAL_RESOURCE_ASSIGN_ID => l_cal_resource_assign_id,
X_OBJECT_VERSION_NUMBER => 1,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_ATTRIBUTE_CATEGORY => null,
X_START_DATE_TIME => l_assignment_start_date,
X_END_DATE_TIME => l_assignment_end_date,
X_CALENDAR_ID => l_calendar_id,
X_RESOURCE_ID => l_crm_resource_id,
X_RESOURCE_TYPE_CODE => l_resource_type_code,
X_PRIMARY_CALENDAR_FLAG => 'Y',
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_CREATION_DATE => sysdate,
X_CREATED_BY => G_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => G_user_id,
X_LAST_UPDATE_LOGIN => G_login_id
);
log_message('Error in CRM Table Handler API for INSERT_ROW');
pa_debug.g_err_stage := 'Log: Error in CRM Table Handler API for INSERT_ROW';
pa_debug.g_err_stage := 'Log: After insert into jtf_cal_resource_assign';
log_message('End of insert into jtf_cal_resource_assign');
pa_debug.g_err_stage := 'Log: End of Insert_into_CRM procedure';
log_message('End of insert_into_crm procedure');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Insert_into_CRM'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END INSERT_INTO_CRM;
/*Procedure : PA Insert
This procedure inserts records into pa_resources and
pa_resource_txn_attributes table. This procedure calls the table
handler package PA_RESOURCE_PKG.INSERT_ROW1 and PA_RESOURCE_PKG.INSERT_ROW2 . */
PROCEDURE INSERT_INTO_PA(
P_RESOURCE_TYPE_ID IN PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE,
P_CRM_RESOURCE_ID IN PA_RESOURCES.JTF_RESOURCE_ID%TYPE,
X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
P_START_DATE IN PA_RESOURCES.START_DATE_ACTIVE%TYPE,
P_END_DATE IN PA_RESOURCES.END_DATE_ACTIVE%TYPE DEFAULT NULL,
P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE DEFAULT NULL,
P_NAME IN PA_RESOURCES.NAME%TYPE,
P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE DEFAULT NULL,
X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_resource_txn_attribute_id PA_RESOURCE_TXN_ATTRIBUTES.RESOURCE_TXN_ATTRIBUTE_ID%TYPE;
SELECT 'X'
FROM pa_resources
WHERE resource_id = l_resource_id;
SELECT 'X'
FROM pa_resource_txn_attributes
WHERE resource_txn_attribute_id = l_resource_txn_attribute_id;
SELECT 'Y'
FROM pa_resources
WHERE ROWID = l_rowid;
SELECT 'Y'
FROM pa_resource_txn_attributes
WHERE ROWID = l_rowid;
PA_DEBUG.set_err_stack('Insert_into_PA');
pa_debug.g_err_stage := 'Log: Start of Insert_into_PA procedure';
Select pa_resources_s.nextval
into x_resource_id
from dual;
select pa_resource_txn_attributes_s.nextval
into l_resource_txn_attribute_id
from dual;
pa_debug.g_err_stage := 'Log: Calling Insert_row1 to insert into PA_RESOURCES table';
log_message('Calling insert_row1 procedure to do the actual inserts into PA_RESOURCES table');
PA_RESOURCE_PKG.INSERT_ROW1 (
X_ROWID => l_rowid,
X_RESOURCE_ID => x_resource_id,
X_NAME => p_name,
X_RESOURCE_TYPE_ID => p_resource_type_id,
X_JTF_RESOURCE_ID => p_crm_resource_id,
X_START_DATE_ACTIVE => p_start_date,
X_END_DATE_ACTIVE => p_end_date,
X_UNIT_OF_MEASURE => l_unit_of_measure, -- added for bug 3921534
X_ROLLUP_QUANTITY_FLAG => l_rollup_quantity_flag, -- added for bug 3921534
X_TRACK_AS_LABOR_FLAG => l_track_as_labor_flag, -- added for bug 3921534
X_REQUEST_ID => G_request_id,
X_PROGRAM_ID => G_program_id,
X_PROGRAM_UPDATE_DATE => SYSDATE,
X_PROGRAM_APPLICATION_ID => G_application_id,
X_LAST_UPDATE_BY => G_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => G_user_id,
X_LAST_UPDATE_LOGIN => G_login_id,
X_RETURN_STATUS => x_return_status);
pa_debug.g_err_stage := 'Log: After Insert_row1 procedure';
log_message('After Insert Row 1 Procedure');
pa_debug.g_err_stage := 'Log: Calling Insert_row2 to insert into PA_RESOURCE_TXN_ATTRIBUTES table';
log_message('Call insert_row2 procedure to do the actual inserts into PA_RESOURCE_TXN_ATTRIBUTES table');
PA_RESOURCE_PKG.INSERT_ROW2 (
X_ROWID => l_rowid,
X_RESOURCE_TXN_ATTRIBUTE_ID => l_resource_txn_attribute_id,
X_RESOURCE_ID => x_resource_id,
X_PERSON_ID => p_person_id,
X_PARTY_ID => p_party_id,
X_RESOURCE_FORMAT_ID => 5,
X_REQUEST_ID => G_request_id,
X_PROGRAM_ID => G_program_id,
X_PROGRAM_UPDATE_DATE => SYSDATE,
X_PROGRAM_APPLICATION_ID => G_application_id,
X_LAST_UPDATE_BY => G_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => G_user_id,
X_LAST_UPDATE_LOGIN => G_login_id,
X_RETURN_STATUS => x_return_status ) ;
pa_debug.g_err_stage := 'Log: After Insert_row2 procedure';
log_message('End of insert_row2 procedure');
pa_debug.g_err_stage := 'Log: End of Insert_into_PA procedure';
log_message('End of insert_into_pa procedure');
FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_R_PROJECT_RESOURCES_PVT.Insert_into_PA'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
END INSERT_INTO_PA;
pa_hr_update_api.check_exp_OU(
p_org_id => p_default_ou
,x_return_status => l_valid
,x_error_message_code => l_code);
procedure to perform CRM checks, calls the Insert_into_Pa and
Insert_into_Orgs procedures to insert resource into PA. Depending on
the number of primary active assignments the Insert_into_Orgs
procedure is called that many times.
If the resource does exist in PA, the procedure checks to see if CRM
calendar has been changed and respectively process the CRM
details. */
PROCEDURE CREATE_INTERNAL_RESOURCE(
P_PERSON_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PERSON_ID%TYPE,
P_NAME IN PA_RESOURCES.NAME%TYPE,
P_ORGANIZATION_ID IN PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID%TYPE,
P_ASSIGNMENT_START_DATE IN DATE,
P_ASSIGNMENT_END_DATE IN DATE,
P_START_DATE IN DATE,
P_DEFAULT_OU IN NUMBER,
P_CALENDAR_ID IN NUMBER,
P_SYSTEM_TYPE IN PER_PERSON_TYPES.SYSTEM_PERSON_TYPE%TYPE,
P_USER_TYPE IN PER_PERSON_TYPES.USER_PERSON_TYPE%TYPE,
P_RES_EXISTS IN VARCHAR2,
P_COUNT IN NUMBER,
P_RESOURCE_TYPE IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
P_START_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_start_date%TYPE,
P_END_DATE_ACTIVE IN pa_r_project_resources_ind_v.per_end_date%TYPE,
P_SOURCE_NUMBER IN pa_r_project_resources_ind_v.per_emp_number%TYPE,
P_SOURCE_JOB_TITLE IN pa_r_project_resources_ind_v.job_name%TYPE,
P_SOURCE_EMAIL IN pa_r_project_resources_ind_v.per_email%TYPE,
P_SOURCE_PHONE IN pa_r_project_resources_ind_v.per_work_phone%TYPE,
P_SOURCE_ADDRESS1 IN HR_LOCATIONS.ADDRESS_LINE_1%TYPE,
P_SOURCE_ADDRESS2 IN HR_LOCATIONS.ADDRESS_LINE_2%TYPE,
P_SOURCE_ADDRESS3 IN HR_LOCATIONS.ADDRESS_LINE_3%TYPE,
P_SOURCE_CITY IN HR_LOCATIONS.TOWN_OR_CITY%TYPE,
P_SOURCE_POSTAL_CODE IN HR_LOCATIONS.POSTAL_CODE%TYPE,
P_SOURCE_COUNTRY IN HR_LOCATIONS.COUNTRY%TYPE,
P_SOURCE_MGR_ID IN pa_r_project_resources_ind_v.supervisor_id%TYPE,
P_SOURCE_MGR_NAME IN PER_ALL_PEOPLE_F.FULL_NAME%TYPE,
P_SOURCE_BUSINESS_GRP_ID IN pa_r_project_resources_ind_v.per_business_group_id%TYPE,
P_SOURCE_BUSINESS_GRP_NAME IN pa_r_project_resources_ind_v.org_name%TYPE,
P_SOURCE_FIRST_NAME IN pa_r_project_resources_ind_v.per_first_name%TYPE,
P_SOURCE_LAST_NAME IN pa_r_project_resources_ind_v.per_last_name%TYPE,
P_SOURCE_MIDDLE_NAME IN pa_r_project_resources_ind_v.per_middle_name%TYPE)
IS
l_resource_type_code PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE := 'EMPLOYEE';
SELECT jtf_resource_id
FROM pa_resources
WHERE resource_id = l_res_id;
pa_debug.g_err_stage := 'Log: Calling Insert_into_CRM procedure to check if resource exists in CRM';
log_message('Call insert_into_crm procedure to check if resource exists in CRM');
insert_into_crm(
P_CATEGORY => p_resource_type,
P_PERSON_ID => p_person_id,
P_NAME => p_name,
P_START_DATE => p_start_date,
P_ASSIGNMENT_START_DATE => p_assignment_start_date,
P_ASSIGNMENT_END_DATE => p_assignment_end_date,
P_CALENDAR_ID => l_cal_id,
P_COUNT => p_count,
X_CRM_RESOURCE_ID => x_crm_resource_id,
X_RETURN_STATUS => x_return_status,
P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
P_SOURCE_NUMBER => P_SOURCE_NUMBER,
P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
P_SOURCE_EMAIL => P_SOURCE_EMAIL,
P_SOURCE_PHONE => P_SOURCE_PHONE,
P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
P_SOURCE_CITY => P_SOURCE_CITY,
P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME);
pa_debug.g_err_stage := 'Log: After Insert_into_CRM procedure ';
log_message('Out of insert_into_crm procedure');
select resource_type_id into l_resource_type_id
from pa_resource_types
where resource_type_code = l_resource_type_code ;
pa_debug.g_err_stage := 'Log: Calling Insert_into_PA procedure';
log_message('Calling insert_into_pa');
INSERT_INTO_PA(
P_RESOURCE_TYPE_ID => l_resource_type_id,
P_CRM_RESOURCE_ID => x_crm_resource_id,
X_RESOURCE_ID => x_resource_id,
P_START_DATE => p_start_date,
P_PERSON_ID => p_person_id,
P_NAME => p_name,
X_RETURN_STATUS => x_return_status );
pa_debug.g_err_stage := 'Log: After Insert_into_PA procedure';
log_message('Out of insert_into_pa');
select resource_id into l_res_id
from pa_resource_txn_attributes
where person_id = p_person_id
and rownum = 1; -- added for bug 3086960.
pa_debug.g_err_stage := 'Log: Calling Insert_into_CRM procedure to check if resource exists in CRM';
log_message('Call insert_into_crm procedure to check if resource exists in CRM');
insert_into_crm(
P_CATEGORY => p_resource_type,
P_PERSON_ID => p_person_id,
P_NAME => p_name,
P_START_DATE => p_start_date,
P_ASSIGNMENT_START_DATE => p_assignment_start_date,
P_ASSIGNMENT_END_DATE => p_assignment_end_date,
P_CALENDAR_ID => l_cal_id,
P_COUNT => 1,
X_CRM_RESOURCE_ID => x_crm_resource_id,
X_RETURN_STATUS => x_return_status,
P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
P_SOURCE_NUMBER => P_SOURCE_NUMBER,
P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
P_SOURCE_EMAIL => P_SOURCE_EMAIL,
P_SOURCE_PHONE => P_SOURCE_PHONE,
P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
P_SOURCE_CITY => P_SOURCE_CITY,
P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME);
pa_debug.g_err_stage := 'Log: After Insert_into_CRM procedure';
log_message(' Out of insert_into_crm procedure');
select resource_id into l_res_id
from pa_resource_txn_attributes
where person_id = p_person_id and rownum=1;
SELECT 'X'
INTO l_exists
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM pa_resource_txn_attributes
WHERE resource_id = x_resource_id
AND person_id <> p_person_id);
update pa_resources
set jtf_resource_id = x_crm_resource_id,
request_id = G_request_id,
program_id = G_program_id,
program_update_date = sysdate,
program_application_id = G_application_id,
last_update_date = sysdate,
last_updated_by = G_user_id,
last_update_login = G_login_id
where resource_id = l_res_id;
pa_debug.g_err_stage := 'Log: Calling Insert_into_CRM procedure';
insert_into_crm(
P_CATEGORY => p_resource_type,
P_PERSON_ID => p_person_id,
P_NAME => p_name,
P_START_DATE => p_start_date,
P_ASSIGNMENT_START_DATE => p_assignment_start_date,
P_ASSIGNMENT_END_DATE => p_assignment_end_date,
P_CALENDAR_ID => l_cal_id,
P_COUNT => 1,
X_CRM_RESOURCE_ID => x_crm_resource_id,
X_RETURN_STATUS => x_return_status,
P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
P_SOURCE_NUMBER => P_SOURCE_NUMBER,
P_SOURCE_JOB_TITLE => P_SOURCE_JOB_TITLE,
P_SOURCE_EMAIL => P_SOURCE_EMAIL,
P_SOURCE_PHONE => P_SOURCE_PHONE,
P_SOURCE_ADDRESS1 => P_SOURCE_ADDRESS1,
P_SOURCE_ADDRESS2 => P_SOURCE_ADDRESS2,
P_SOURCE_ADDRESS3 => P_SOURCE_ADDRESS3,
P_SOURCE_CITY => P_SOURCE_CITY,
P_SOURCE_POSTAL_CODE => P_SOURCE_POSTAL_CODE,
P_SOURCE_COUNTRY => P_SOURCE_COUNTRY,
P_SOURCE_MGR_ID => P_SOURCE_MGR_ID,
P_SOURCE_MGR_NAME => P_SOURCE_MGR_NAME,
P_SOURCE_BUSINESS_GRP_ID => P_SOURCE_BUSINESS_GRP_ID,
P_SOURCE_BUSINESS_GRP_NAME => P_SOURCE_BUSINESS_GRP_NAME,
P_SOURCE_FIRST_NAME => P_SOURCE_FIRST_NAME,
P_SOURCE_LAST_NAME => P_SOURCE_LAST_NAME,
P_SOURCE_MIDDLE_NAME => P_SOURCE_MIDDLE_NAME);
pa_debug.g_err_stage := 'Log: After Insert_into_CRM procedure';
log_message(' Out of insert_into_crm procedure');
the resource exists in PA, if it does not then it calls the insert_into_pa
to insert the resource into pa_resources and pa_resource_txn_attributes
If the resource does exist in PA, the procedure just returns the resource_id
of the external resource */
PROCEDURE CREATE_EXTERNAL_RESOURCE(
P_PARTY_ID IN PA_RESOURCE_TXN_ATTRIBUTES.PARTY_ID%TYPE,
P_RESOURCE_TYPE IN PA_RESOURCE_TYPES.RESOURCE_TYPE_CODE%TYPE,
X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE, --File.Sql.39 bug 4440895
X_RETURN_STATUS OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
IS
l_resource_type_id PA_RESOURCE_TYPES.RESOURCE_TYPE_ID%TYPE;
select resource_type_id into l_resource_type_id
from pa_resource_types
where resource_type_code = p_resource_type;
select substrb(party_name,1,100), start_date, end_date
into l_name, l_start_date, l_end_date
from pa_party_resource_details_v
where party_id = l_party_id;
pa_debug.g_err_stage := 'Log: Before Insert_into_PA procedure for external people';
INSERT_INTO_PA(
P_RESOURCE_TYPE_ID => l_resource_type_id,
P_CRM_RESOURCE_ID => null,
X_RESOURCE_ID => x_resource_id,
P_START_DATE => l_start_date,
P_END_DATE => l_end_date,
P_PARTY_ID => l_party_id,
P_NAME => l_name,
X_RETURN_STATUS => x_return_status );
pa_debug.g_err_stage := 'Log: After Insert_into_PA procedure for external people';
log_message('** done with insert into PA for external people **');
select resource_id into l_res_id
from pa_resource_txn_attributes
where party_id = l_party_id;
P_SELECTION_OPTION IN VARCHAR2 DEFAULT NULL,
P_ORG_STR_VERSION_ID IN NUMBER DEFAULT NULL,
P_START_ORGANIZATION_ID IN NUMBER DEFAULT NULL,
-- End of parameters added for PJR Resource Pull Enhancements - Bug 5130414
X_RETURN_STATUS OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
X_RESOURCE_ID OUT NOCOPY PA_RESOURCES.RESOURCE_ID%TYPE) --File.Sql.39 bug 4440895
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
l_selection_option VARCHAR(20) := P_SELECTION_OPTION;
select resource_id
from jtf_rs_resource_extns
where source_id = l_person_id
and category = l_resource_type;
select decode(substr(USERENV('CLIENT_INFO'),1,1),
' ', NULL,
substr(USERENV('CLIENT_INFO'),1,10)) org from dual; */
select max(assignment_end_date)
from pa_r_project_resources_term_v res
where res.person_id = p_person_id; */
SELECT MAX (asgn_end_date) FROM
(select max(res.assignment_end_date) asgn_end_date
from pa_r_project_resources_v res
where res.person_id = p_person_id
and res.assignment_end_date IS NOT NULL
UNION
select max(res.assignment_end_date) ass_end_date
from pa_r_project_resources_term_v res
where res.person_id = p_person_id
and res.assignment_end_date IS NOT NULL ) ;
select max(assignment_end_date)
from pa_r_project_resources_ind_v res
, hr_organization_information org_info
, pa_all_organizations org
where res.person_id = l_person_id
and res.organization_id = org_info.organization_id
and org_info.org_information_context = 'Exp Organization Defaults'
and res.organization_id = org.organization_id
and org.pa_org_use_type = 'EXPENDITURES'
and org.inactive_date is null
and (org.organization_id,org.org_id) = (
select org1.organization_id, org1.org_id
from pa_all_organizations org1
where org1.pa_org_use_type = 'EXPENDITURES'
and org1.inactive_date is null
and org1.organization_id = org.organization_id
and rownum = 1 );
select 'Y'
from pa_resources_denorm
where person_id = l_person_id
and rownum=1;
SELECT prd.person_id,
prd.resource_effective_start_date,
prd.resource_effective_end_date
FROM pa_resources_denorm prd,
per_all_assignments_f paf,
per_assignment_status_types past
WHERE prd.person_id = paf.person_id
AND prd.person_id = p_person_id
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND paf.primary_flag = 'Y'
AND paf.assignment_type in ('E','C')
AND prd.resource_effective_start_date = paf.effective_start_date
AND past.per_system_status in ('SUSP_ASSIGN','SUSP_CWK_ASG')
ORDER BY prd.resource_effective_start_date ;
SELECT prd.person_id,
prd.resource_effective_start_date,
prd.resource_effective_end_date
FROM pa_resources_denorm prd
WHERE prd.resource_effective_end_date < sysdate
AND prd.resource_effective_end_date < l_prof_date
AND prd.resource_effective_end_date is not null
AND prd.person_id = p_person_id
ORDER BY prd.resource_effective_start_date ;
SELECT prd.person_id,
prd.assignment_start_date,
prd.assignment_end_date,
prd.organization_id
FROM pa_r_project_resources_term_v prd
WHERE per_end_date < sysdate
AND assignment_end_date < sysdate
AND per_end_date < l_prof_date
AND per_end_date is not null
AND organization_id = l_p_org_id
ORDER BY per_start_date ;
select resource_type_id into l_resource_type_id
from pa_resource_types
where resource_type_code = 'EMPLOYEE';
pa_debug.g_err_stage := 'Log: Before Insert_into_PA procedure for non-scheduled';
INSERT_INTO_PA(
P_RESOURCE_TYPE_ID => l_resource_type_id,
P_CRM_RESOURCE_ID => l_crm_resource_id,
X_RESOURCE_ID => x_resource_id,
P_START_DATE => l_start_date,
P_PERSON_ID => l_person_id,
P_NAME => l_name,
X_RETURN_STATUS => x_return_status );
pa_debug.g_err_stage := 'Log: After Insert_into_PA procedure for non-scheduled';
log_message('** insert into PA for non-scheduled member **');
select resource_id into l_res_id
from pa_resource_txn_attributes
where person_id = l_person_id
and rownum = 1; -- added for bug 3086960.
l_job_utilization := pa_hr_update_api.check_job_utilization
(p_job_id => eRec.job_id
,p_person_id => null
,p_date => null);
select 'Y'
into l_exists
from pa_resources_denorm
where person_id = l_person_id
and l_assignment_end_date BETWEEN resource_effective_start_date
AND resource_effective_end_date;
FND_MSG_PUB.delete_msg(p_msg_index =>FND_MSG_PUB.Count_Msg);
IF (l_selection_option <> 'EMP_RANGE' AND l_selection_option <> 'SINGLE_ORG') THEN -- bug 7482852
if l_term_range_date < add_months(trunc(sysdate), -12) THEN
-- hr_utility.trace('TERM_DATE_EXCEPTION ');
l_sel_clause := ' SELECT DISTINCT p.person_id ';
l_sel_clause := ' SELECT DISTINCT p.person_id ';
l_sel_clause := ' SELECT DISTINCT a.person_id ';
IF l_selection_option = 'EMP_RANGE' THEN
-- Employee range is specified
l_sel_clause := ' SELECT DISTINCT p.person_id ';
ELSIF l_selection_option = 'EMP_RANGE_ORG' THEN
-- Employee range and organization is specified
l_sel_clause := ' SELECT DISTINCT p.person_id ';
ELSIF l_selection_option = 'SINGLE_ORG' THEN
-- Organization is specified
l_sel_clause := ' SELECT DISTINCT a.person_id ';
ELSIF l_selection_option = 'START_ORG' THEN
-- Organization hierarchy and starting organization is specified
l_sel_clause := ' SELECT DISTINCT a.person_id ';
' SELECT hrorg.organization_id '||
' FROM hr_all_organization_units_tl hrorg, hr_organization_information orginfo '||
' WHERE hrorg.language = userenv(''LANG'') '||
' AND orginfo.organization_id = hrorg.organization_id '||
' AND orginfo.ORG_INFORMATION_CONTEXT = ''CLASS'' '||
' AND orginfo.ORG_INFORMATION1 = ''PA_EXPENDITURE_ORG'' '||
' AND orginfo.ORG_INFORMATION2 = ''Y'' '||
' AND hrorg.organization_id IN ( '||
' SELECT organization_id_child organization_id '||
' FROM PER_ORG_STRUCTURE_ELEMENTS '||
' WHERE org_structure_version_id = :org_str_version_id '||
' START WITH organization_id_parent = :start_org '||
' AND org_structure_version_id = :org_str_version_id '||
' CONNECT BY PRIOR organization_id_child = organization_id_parent '||
' AND org_structure_version_id = :org_str_version_id '||
' UNION ALL '||
' SELECT :start_org organization_id FROM dual) )'||
' AND a.assignment_type in (''E'',''C'') '|| /*Added condition for Bug 7012687 */
' AND a.job_id is not null'|| /*Added condition for Bug 7012687 */
' AND a.primary_flag = ''Y'' '; /*Added condition for Bug 7012687 */
ELSIF l_selection_option IS NULL THEN
-- Selection option not specified (Will never occur, but a check for backward compliance)
IF l_p_org_id IS NULL THEN
-- Employee range is specified
l_sel_clause := ' SELECT DISTINCT p.person_id ';
l_sel_clause := ' SELECT DISTINCT p.person_id ';
l_sel_clause := ' SELECT DISTINCT a.person_id ';
IF l_selection_option = 'EMP_RANGE' THEN
-- Employee range is specified
DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
ELSIF l_selection_option = 'EMP_RANGE_ORG' THEN
-- Employee range and organization is specified
DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
ELSIF l_selection_option = 'SINGLE_ORG' THEN
-- Organization is specified
DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_id',l_p_org_id);
ELSIF l_selection_option = 'START_ORG' THEN
-- Organization hierarchy and starting organization is specified
DBMS_SQL.BIND_VARIABLE(sql_cursor,':org_str_version_id',l_org_str_version_id);
ELSIF l_selection_option IS NULL THEN
-- Selection option not specified (Will never occur, but a check for backward compliance)
IF l_p_org_id IS NULL THEN
-- Employee range is specified
DBMS_SQL.BIND_VARIABLE(sql_cursor,':from_emp_num',l_from_emp_num);
log_message('Delete Terminate resource denorm data');
pa_debug.g_err_stage := 'Delete Terminate resource denorm data';
DELETE FROM PA_RESOURCES_DENORM
WHERE person_id = l_person_id
AND resource_effective_start_date > l_end_date;
delete from pa_resources_denorm
where person_id = l_person_id
and resource_effective_start_date > l_end_date;
DELETE FROM pa_resources_denorm
WHERE RESOURCE_EFFECTIVE_START_DATE = delrec2.RESOURCE_EFFECTIVE_START_DATE
AND PERSON_ID = delrec2.PERSON_ID;
DELETE FROM pa_resources_denorm
WHERE RESOURCE_EFFECTIVE_START_DATE = delrec.RESOURCE_EFFECTIVE_START_DATE
AND PERSON_ID = delrec.PERSON_ID; --bug#8840426
l_job_schedulable := PA_HR_UPDATE_API.check_job_schedulable
(p_job_id => eRec.job_id);
DELETE FROM pa_resources_denorm
WHERE person_id = delrec3.person_id
AND resource_effective_start_date = delrec3.assignment_start_date
AND resource_organization_id = l_p_org_id;
SELECT pa_workflow_itemkey_s.nextval
INTO itemkey
FROM dual;
l_process := 'PROCESS_CRM_UPDATE' ;
/* -- Insert to PA tables wf process information.
-- This is required for displaying notifications on PA pages.
BEGIN
PA_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'HR_CHANGE_MGMT'
,p_item_type => itemtype
,p_item_key => itemkey
,p_entity_key1 => to_char(p_person_id)
,p_entity_key2 => to_char(p_person_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
SELECT min(effective_start_date)
INTO l_current_asgn_start_date
FROM per_all_assignments_f
WHERE person_id = l_person_id
AND primary_flag = 'Y'
AND job_id IS NOT NULL
AND assignment_type in ('E', 'C');
SELECT TTL.USER_PERSON_TYPE
FROM PER_PERSON_TYPES_TL TTL
,PER_PERSON_TYPES TYP
,PER_PERSON_TYPE_USAGES_F PTU
WHERE TTL.LANGUAGE = USERENV('LANG')
AND TTL.PERSON_TYPE_ID = TYP.PERSON_TYPE_ID
AND TYP.SYSTEM_PERSON_TYPE IN ('EMP','EX_EMP','CWK','EX_CWK')
AND TYP.PERSON_TYPE_ID = PTU.PERSON_TYPE_ID
AND P_EFFECTIVE_DATE BETWEEN PTU.EFFECTIVE_START_DATE
AND PTU.EFFECTIVE_END_DATE
AND PTU.PERSON_ID = P_PERSON_ID
ORDER BY DECODE(TYP.SYSTEM_PERSON_TYPE
,'EMP' ,1
,'CWK' ,2
,'EX_EMP',3
,'EX_CWK',4
,5
);