[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_cost_distribution_row
( p_assignment_id IN NUMBER,
p_cost_keyflex_id IN NUMBER,
p_business_group_id IN NUMBER,
p_costing_level IN VARCHAR2,
p_index IN BINARY_INTEGER,
p_proportion IN NUMBER,
p_start_date IN DATE,
p_end_date IN DATE,
p_data_extract_id IN NUMBER,
p_cost_segments IN g_glcostmap_tbl_type,
p_chart_of_accounts_id IN NUMBER
);
SELECT USERENV('sessionid') into
lsession
FROM dual;
Select count(*) into l_cnt
from fnd_sessions
where session_id = lsession
and effective_date = p_date;
INSERT INTO fnd_sessions
(session_id,effective_date)
values (lsession,p_date);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
SELECT pp.position_id,
pp.position_definition_id,
pp.organization_id,
paf.person_id,
paf.primary_flag,
paf.assignment_status_type_id,
pp.name,
pp.business_group_id,
pp.date_effective,
pp.date_end,
pp.entry_grade_id,
pp.entry_grade_rule_id,
pp.entry_step_id,
pp.pay_basis_id,
pst.system_type_cd
FROM fnd_sessions fs,
hr_all_positions_f pp ,
per_shared_types pst ,
per_all_assignments_f paf ,
pay_all_payrolls_f ppay,
per_pay_bases ppb,
per_assignment_status_types past --bug 4020452
WHERE fs.session_id = userenv('sessionid')
AND fs.effective_date between pp.effective_start_date
and pp.effective_end_date
AND pp.business_group_id = p_business_group_id
AND pp.position_id > l_restart_position_id
AND ( (l_status_count > 0 and pst.business_group_id = p_business_group_id)
or
(l_status_count = 0 and pst.business_group_id is null) )
AND pp.availability_status_id = pst.shared_type_id
AND pst.system_type_cd in ('PROPOSED','ACTIVE', 'FROZEN')
AND fs.effective_date between paf.effective_start_date
and paf.effective_end_date
AND paf.position_id = pp.position_id
AND paf.business_group_id = p_business_group_id
AND paf.assignment_type = 'E'
/*Bug: 2109120 Start*/
-- AND paf.primary_flag = 'Y'
/*Bug: 2109120 End*/
AND fs.effective_date between ppay.effective_start_date
and ppay.effective_end_date
AND ppay.payroll_id = paf.payroll_id
AND ppay.gl_set_of_books_id = p_set_of_books_id
AND paf.pay_basis_id = ppb.pay_basis_id
/* bug 4020452 start */
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status <> 'TERM_ASSIGN'
/* bug 4020452 end */
/*
The following logic is used to restrict the positions for all the selected
organizations, if extract by org is enabled. Otherwise, we will ignore
the organizations avaiable in the business group
*/
AND ( p_extract_by_org = 'N'
OR
(p_extract_by_org = 'Y' and pp.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = 'Y' )
)
)
UNION ALL
SELECT pp.position_id,
pp.position_definition_id,
pp.organization_id,
0,
'Y',
to_number(NULL),
pp.name,
pp.business_group_id,
pp.date_effective,
pp.date_end,
pp.entry_grade_id,
pp.entry_grade_rule_id,
pp.entry_step_id,
pp.pay_basis_id,
pst.system_type_cd
FROM fnd_sessions fs,
hr_all_positions_f pp ,
per_shared_types pst
WHERE fs.session_id = userenv('sessionid')
AND fs.effective_date between pp.effective_start_date
and pp.effective_end_date
AND pp.business_group_id = p_business_group_id
AND pp.position_id > l_restart_position_id
AND pp.availability_status_id = pst.shared_type_id
AND ( (l_status_count > 0 and pst.business_group_id = p_business_group_id)
OR
(l_status_count = 0 and pst.business_group_id is null)
)
-- for bug 4533884 .removed frozen from the IN clause so that
-- vacant frozen positions will not be picked up
-- AND pst.system_type_cd in ('PROPOSED','ACTIVE','FROZEN')
AND pst.system_type_cd in ('PROPOSED','ACTIVE')
AND ( (NOT EXISTS
( SELECT 1
FROM per_all_assignments_f pafx,
-- bug 3777146 added the join with per_assignment_status_types
per_assignment_status_types past
WHERE fs.session_id = userenv('sessionid')
AND fs.effective_date between pafx.effective_start_date
and pafx.effective_end_date
AND pafx.assignment_status_type_id
= past.assignment_status_type_id
AND pafx.position_id = pp.position_id
-- Bug#3265678: This clause picks all occupied positions.
-- AND pafx.assignment_type <> 'A'
AND pafx.assignment_type = 'E'
AND past.per_system_status <> 'TERM_ASSIGN'
)
)
OR
( ( pp.position_type <> 'SINGLE')
AND
( nvl(pp.fte,1) >
( SELECT sum(nvl(value,1))
FROM per_assignment_budget_values_f pab,
per_all_assignments_f paf,
per_assignment_status_types past
-- bug 4020452 added the join with per_assignment_status_types
WHERE fs.session_id = userenv('sessionid')
AND fs.effective_date between paf.effective_start_date
and paf.effective_end_date
AND paf.assignment_status_type_id
= past.assignment_status_type_id
AND paf.position_id = pp.position_id
AND paf.assignment_type = 'E'
AND past.per_system_status <> 'TERM_ASSIGN' --bug 4020452
/* For Bug 2891574 start*/
--AND fs.effective_date between pab.effective_start_date
-- and pab.effective_end_date
AND pab.effective_start_date(+) <= fs.effective_date
AND pab.effective_end_date(+) >= fs.effective_date
/* For Bug 2891574 end*/
AND pab.assignment_id(+) = paf.assignment_id
AND pab.unit(+) = 'FTE'
)
)
)
)
/*
Logic to restrict the positions for all the selected organizations, if
extract by org is enabled. Otherwise, we will ignore organizations
avaiable in the business group
*/
AND ( p_extract_by_org = 'N'
OR
(p_extract_by_org = 'Y' and pp.organization_id in
( select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = 'Y'
)
)
)
ORDER BY 1,3,4 desc;
select position_structure
from per_business_groups
where business_group_id = p_business_group_id;
select application_column_name
from fnd_id_flex_segments_vl
where id_flex_code = 'POS'
and id_flex_num = l_pos_id_flex_num
and enabled_flag = 'Y'
order by segment_num;
Select count(*), parent_spine_id
from pay_rates
where business_group_id = p_business_group_id
-- Start bug no 3902996
and parent_spine_id = l_parent_spine_id
-- End bug no 3902996
and rate_type = 'SP'
group by parent_spine_id
having count(*) > 1;
SELECT effective_date
FROM FND_SESSIONS
WHERE session_id = USERENV('sessionid');
SELECT grade_spine_id
FROM per_spinal_point_steps
WHERE step_id = l_step_id;
SELECT effective_start_date,effective_end_date,
rate_id, grade_or_spinal_point_id, rate_type,
maximum,mid_value,minimum,sequence,value
FROM PAY_GRADE_RULES
WHERE business_group_id = p_business_group_id
AND grade_rule_id = l_grade_rule_id;
SELECT parent_spine_id
FROM PER_SPINAL_POINTS
WHERE spinal_point_id = l_grade_or_spinal_point_id
AND business_group_id = p_business_group_id;
SELECT pay_basis
FROM PER_PAY_BASES
WHERE pay_basis_id = l_pay_basis_id;
Select assignment_status_type_id
from per_assignment_status_types
where (business_group_id = p_business_group_id
or business_group_id is null)
and PER_SYSTEM_STATUS = 'TERM_ASSIGN'; */
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT count(*) into l_status_count
from per_shared_types
where business_group_id = p_business_group_id
and system_type_cd in ('PROPOSED','ACTIVE', 'FROZEN');
assign_stat_types.delete;
INSERT INTO PSB_POSITIONS_I
(
DATA_EXTRACT_ID ,
BUSINESS_GROUP_ID ,
HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
HR_POSITION_NAME ,
-- de by org
ORGANIZATION_ID ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
HR_POSITION_DEFINITION_ID,
SUMMARY_FLAG ,
ENABLED_FLAG ,
ID_FLEX_NUM ,
AVAILABILITY_STATUS ,
SALARY_TYPE ,
RATE_OR_PAYSCALE_ID ,
GRADE_ID ,
GRADE_STEP ,
SEQUENCE_NUMBER ,
VALUE ,
PAY_BASIS ,
SEGMENT1 ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
SEGMENT6 ,
SEGMENT7 ,
SEGMENT8 ,
SEGMENT9 ,
SEGMENT10 ,
SEGMENT11 ,
SEGMENT12 ,
SEGMENT13 ,
SEGMENT14 ,
SEGMENT15 ,
SEGMENT16 ,
SEGMENT17 ,
SEGMENT18 ,
SEGMENT19 ,
SEGMENT20 ,
SEGMENT21 ,
SEGMENT22 ,
SEGMENT23 ,
SEGMENT24 ,
SEGMENT25 ,
SEGMENT26 ,
SEGMENT27 ,
SEGMENT28 ,
SEGMENT29 ,
SEGMENT30 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE
)
VALUES
(
p_data_extract_id,
position_rec.business_group_id,
position_rec.position_id,
decode(position_rec.person_id,0,null,position_rec.person_id),
position_rec.name,
-- de by org
position_rec.organization_id,
position_rec.date_effective,
d_date_end,
position_rec.position_definition_id,
'Y',
'Y',
p_id_flex_num,
position_rec.system_type_cd,
l_salary_type,
l_rate_or_payscale_id,
position_rec.entry_grade_id,
l_grade_step,
l_sequence,
l_value,
l_pay_basis,
possegs(1),
possegs(2),
possegs(3),
possegs(4),
possegs(5),
possegs(6),
possegs(7),
possegs(8),
possegs(9),
possegs(10),
possegs(11),
possegs(12),
possegs(13),
possegs(14),
possegs(15),
possegs(16),
possegs(17),
possegs(18),
possegs(19),
possegs(20),
possegs(21),
possegs(22),
possegs(23),
possegs(24),
possegs(25),
possegs(26),
possegs(27),
possegs(28),
possegs(29),
possegs(30),
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date
);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Positions Interface',
p_restart_id => position_rec.position_id
);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Positions Interface',
p_restart_id => l_fin_position_id
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
SELECT
GRADE_RULE_ID ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
RATE_ID ,
GRADE_OR_SPINAL_POINT_ID,
RATE_TYPE,
MAXIMUM,
MID_VALUE,
MINIMUM,
SEQUENCE,
VALUE
FROM PAY_GRADE_RULES
WHERE BUSINESS_GROUP_ID = p_business_group_id
AND grade_rule_id > l_restart_grade_rule_id
ORDER BY grade_rule_id;
SELECT pgs.grade_id,pgs.grade_spine_id,psp.step_id,psp.sequence
FROM PER_GRADE_SPINES pgs,PER_SPINAL_POINT_STEPS psp
WHERE pgs.parent_spine_id = l_parent_spine_id
AND pgs.business_group_id = p_business_group_id
AND psp.business_group_id = p_business_group_id
AND pgs.grade_spine_id = psp.grade_spine_id
AND psp.spinal_point_id = l_grade_or_spinal_point_id;
SELECT parent_spine_id
FROM PER_SPINAL_POINTS
WHERE spinal_point_id = l_grade_or_spinal_point_id
AND business_group_id = p_business_group_id;
SELECT name
FROM PER_PARENT_SPINES
WHERE parent_spine_id = l_rate_or_payscale_id
AND business_group_id = p_business_group_id;
SELECT pay_basis, piv.element_type_id
FROM PAY_RATES pr, PER_PAY_BASES ppb, PAY_INPUT_VALUES piv
WHERE pr.parent_spine_id = l_rate_or_payscale_id
AND pr.rate_id = ppb.rate_id
AND pr.business_group_id = p_business_group_id
AND ppb.business_group_id = p_business_group_id
AND ppb.input_value_id = piv.input_value_id;
select grade_structure
from per_business_groups
where business_group_id = p_business_group_id; */
SELECT effective_date
FROM FND_SESSIONS
WHERE session_id = USERENV('sessionid');
SELECT name
FROM PAY_RATES
WHERE rate_id = l_rate_id;
SELECT name
FROM PER_GRADES
WHERE grade_id = l_grade_id;
SELECT ppb.pay_basis, piv.element_type_id
FROM PER_PAY_BASES ppb, PAY_INPUT_VALUES piv
WHERe ppb.rate_id = l_rate_id
AND ppb.business_group_id = p_business_group_id
AND ppb.input_value_id = piv.input_value_id;
Select count(*), parent_spine_id
from pay_rates
where business_group_id = p_business_group_id
-- Start bug no 3902996
and parent_spine_id = l_parent_spine_id
-- End bug no 3902996
and rate_type = 'SP'
group by parent_spine_id
having count(*) > 1;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_grade_stmt := 'SELECT pg.grade_id,pgv.concatenated_segments
FROM PER_GRADES pg,PER_GRADE_DEFINITIONS_KFV pgv
WHERE pg.grade_id = '||':ld_grade_id'||
' AND pg.business_group_id = '||p_business_group_id||
' AND pg.grade_definition_id = pgv.grade_definition_id'||
' AND pgv.id_flex_num = '||l_grade_id_flex_num;
INSERT INTO PSB_SALARY_I
( BUSINESS_GROUP_ID,
DATA_EXTRACT_ID,
SALARY_TYPE ,
RATE_OR_PAYSCALE_ID,
RATE_OR_PAYSCALE_NAME,
GRADE_ID ,
GRADE_NAME ,
GRADE_STEP ,
SEQUENCE_NUMBER ,
MINIMUM_VALUE ,
MAXIMUM_VALUE ,
MID_VALUE ,
ELEMENT_VALUE ,
ELEMENT_TYPE_ID ,
PAY_BASIS ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN,
CREATED_BY ,
CREATION_DATE )
VALUES
(
salary_rec.business_group_id,
p_data_extract_id,
l_salary_type,
l_rate_or_payscale_id,
l_rate_or_payscale_name,
Grade_rec.grade_id,
l_grade_name,
l_grade_step,
l_sequence,
l_minimum_value,
l_maximum_value,
l_mid_value,
l_element_value,
l_element_type_id,
l_pay_basis,
salary_rec.effective_start_date,
d_effective_end_date,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date
);
INSERT INTO PSB_SALARY_I
( BUSINESS_GROUP_ID,
DATA_EXTRACT_ID,
SALARY_TYPE ,
RATE_OR_PAYSCALE_ID,
RATE_OR_PAYSCALE_NAME,
GRADE_ID ,
GRADE_NAME ,
GRADE_STEP ,
SEQUENCE_NUMBER ,
MINIMUM_VALUE ,
MAXIMUM_VALUE ,
MID_VALUE ,
ELEMENT_VALUE ,
ELEMENT_TYPE_ID ,
PAY_BASIS ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN,
CREATED_BY ,
CREATION_DATE )
VALUES
(
p_business_group_id,
p_data_extract_id,
l_salary_type,
l_rate_or_payscale_id,
l_rate_or_payscale_name,
l_grade_id,
l_grade_name,
l_grade_step,
l_sequence,
l_minimum_value,
l_maximum_value,
l_mid_value,
l_element_value,
l_element_type_id,
l_pay_basis ,
salary_rec.effective_start_date,
d_effective_end_date,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date
);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Salary Interface',
p_restart_id => salary_rec.grade_rule_id
);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Salary Interface',
p_restart_id => l_fin_graderule_id
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
Select count(*), parent_spine_id
from pay_rates
where business_group_id = p_business_group_id
-- Start bug no 3902996
and parent_spine_id = l_parent_spine_id
-- End bug no 3902996
and rate_type = 'SP'
group by parent_spine_id
having count(*) > 1;
Select pp.business_group_id,
/* Start bug #4128475 */
--pp.name,
pp.hr_position_name,
/* End bug #4128475 */
pp.hr_position_id,
paf.assignment_id,
paf.primary_flag,
paf.assignment_status_type_id,
paf.person_id,
paf.organization_id,
paf.grade_id,
paf.job_id,
paf.payroll_id,
paf.people_group_id,
paf.normal_hours,
paf.frequency,
paf.set_of_books_id,
ppf.employee_number,
ppf.first_name,
ppf.full_name,
ppf.known_as,
ppf.last_name,
ppf.middle_names,
ppf.title,
/*For Bug No : 2594575 Start*/
--Stop extracting secured data of employee
--Removed the columns in psb_employees table
/*For Bug No : 2594575 End*/
ppf.effective_start_date,
ppb.pay_basis,
ppb.rate_basis,
ppb.rate_id
FROM fnd_sessions fs,
/* Start bug #4128475 */
-- psb_positions,
psb_positions_i pp,
/* End bug #4128475 */
per_all_assignments_f paf,
per_all_people_f ppf,
pay_all_payrolls_f ppay,
per_pay_bases ppb
WHERE fs.session_id = userenv('sessionid')
AND fs.effective_date between paf.effective_start_date and paf.effective_end_date
AND pp.data_extract_id = p_data_extract_id
AND pp.hr_position_id = paf.position_id
AND pp.hr_employee_id = paf.person_id
AND paf.assignment_id > l_restart_assignment_id
AND pp.business_group_id = p_business_group_id
and FS.EFFECTIve_date between ppf.effective_start_date
AND ppf.effective_end_date
AND paf.person_id = ppf.person_id
AND fs.effective_date between ppay.effective_start_date
AND ppay.effective_end_date
AND paf.payroll_id = ppay.payroll_id
AND ppay.gl_set_of_books_id = p_set_of_books_id
AND paf.pay_basis_id = ppb.pay_basis_id
AND paf.assignment_type = 'E'
/*For Bug No : 2109120 Start*/
--AND paf.primary_flag = 'Y'
/*For Bug No : 2109120 End*/
-- de by org
-- The following logic is used to restrict the positions for all the
-- selected organizations, if extract by org is enabled.
-- Otherwise, we will ignore the organizations available
-- in the business group.
AND (p_extract_by_org = 'N' OR
(p_extract_by_org = 'Y' and pp.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = 'Y')))
ORDER BY paf.assignment_id;
Select nvl(proposed_salary_n,0) proposed_salary,
change_date
from per_pay_proposals
where assignment_id = l_assignment_id
and change_date =
(select max(change_date) from
per_pay_proposals where
assignment_id = l_assignment_id
and approved = 'Y');
Select step_id
from Per_spinal_pt_placements_v
where assignment_id = l_assignment_id;
SELECT pss.grade_spine_id,pss.spinal_point_id,
pss.sequence
FROM per_spinal_pt_placements_v psp, per_spinal_point_steps pss
WHERE psp.step_id = pss.step_id
AND psp.assignment_id = l_assignment_id;
SELECT effective_date
FROM FND_SESSIONS
WHERE session_id = USERENV('sessionid');
SELECT pgs.grade_id,pgs.parent_spine_id,
pps.name
FROM per_grade_spines pgs, per_parent_spines pps
WHERE grade_spine_id = l_grade_spine_id
and pgs.parent_spine_id = pps.parent_spine_id;
SELECT rate_id
FROM PAY_GRADE_RULES
WHERE GRADE_OR_SPINAL_POINT_ID = l_grade_id
and rate_type = l_rate_type; */
SELECT rate_or_payscale_id
FROM PSB_SALARY_I
WHERE GRADE_ID = l_grade_id
and SALARY_TYPE = l_salary_type;
SELECT rate_or_payscale_id
FROM PSB_SALARY_I
WHERE GRADE_ID = l_grade_id
AND SALARY_TYPE = l_salary_type
AND grade_step = l_grade_step;
SELECT rate_or_payscale_id,element_value,
grade_step,sequence_number
FROM PSB_SALARY_I
WHERE RATE_OR_PAYSCALE_ID = l_rate_id
AND GRADE_ID = l_grade_id
AND SALARY_TYPE = l_salary_type
AND DATA_EXTRACT_ID = p_data_extract_id;
SELECT element_value
FROM PSB_SALARY_I
WHERE RATE_OR_PAYSCALE_ID = l_rate_or_payscale_id
AND GRADE_ID = l_grade_id
AND SALARY_TYPE = l_salary_type
AND GRADE_STEP = l_grade_step
AND SEQUENCE_NUMBER = l_sequence
AND DATA_EXTRACT_ID = p_data_extract_id;
Select assignment_id
from psb_employees_i
where hr_position_id = l_hr_position_id
and hr_employee_id = l_hr_employee_id
and data_extract_id = p_data_extract_id;
Select assignment_status_type_id
from per_assignment_status_types
where (business_group_id = p_business_group_id
or business_group_id is null)
and PER_SYSTEM_STATUS = 'TERM_ASSIGN'; */
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
assign_stat_types.delete;
delete psb_employees_i
where hr_position_id = l_hr_position_id
and hr_employee_id = l_hr_employee_id
and data_extract_id = p_data_extract_id;
INSERT INTO PSB_EMPLOYEES_I
(HR_EMPLOYEE_ID ,
HR_POSITION_ID ,
ASSIGNMENT_ID ,
GRADE_ID ,
GRADE_STEP ,
SEQUENCE_NUMBER ,
PAY_BASIS ,
RATE_ID ,
FIRST_NAME ,
FULL_NAME ,
KNOWN_AS ,
LAST_NAME ,
MIDDLE_NAMES ,
TITLE ,
BUSINESS_GROUP_ID ,
EFFECTIVE_START_DATE,
SET_OF_BOOKS_ID,
SALARY_TYPE ,
RATE_OR_PAYSCALE_ID,
ELEMENT_VALUE ,
PROPOSED_SALARY ,
CHANGE_DATE ,
EMPLOYEE_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
DATA_EXTRACT_ID )
VALUES
(
employee_rec.person_id,
employee_rec.hr_position_id,
employee_rec.assignment_id,
employee_rec.grade_id,
l_grade_step,
l_sequence,
employee_rec.pay_basis,
employee_rec.rate_id,
employee_rec.first_name,
employee_rec.full_name,
employee_rec.known_as,
employee_rec.last_name,
employee_rec.middle_names,
employee_rec.title,
employee_rec.business_group_id,
employee_rec.effective_start_date,
employee_rec.set_of_books_id,
l_salary_type,
l_rate_or_payscale_id,
l_element_value,
l_proposed_salary,
l_change_date,
employee_rec.employee_number,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date,
p_data_extract_id
);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Employees Interface',
p_restart_id => employee_rec.assignment_id
);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Employees Interface',
p_restart_id => l_assignment_id
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
for all the selected organizations, if extract by org is enabled. Otherwise, we will
ignore the organizations avaiable in the business group.
AND (p_extract_by_org = l_no
OR
(p_extract_by_org = l_yes and paf.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = l_yes)))
*/
Cursor C4 is
Select paf.assignment_id,
paf.position_id,
paf.person_id,
paf.business_group_id,
paf.payroll_id,
pcaf.proportion,
paf.organization_id,
paf.pay_basis_id,
pcaf.cost_allocation_keyflex_id,
pcaf.effective_start_date,
pcaf.effective_end_date
from fnd_sessions fs, per_all_assignments_f paf,
pay_cost_allocations_f pcaf,
pay_cost_allocation_keyflex pcak
where fs.session_id = userenv('sessionid')
and fs.effective_date between paf.effective_start_date and paf.effective_end_date
and paf.business_group_id = p_business_group_id
and paf.payroll_id > l_restart_payroll_id
and paf.assignment_id = pcaf.assignment_id
and pcaf.cost_allocation_keyflex_id
= pcak.cost_allocation_keyflex_id
AND (p_extract_by_org = l_no OR
(p_extract_by_org = l_yes and paf.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = l_yes)))
union all
Select paf.assignment_id,
paf.position_id,
paf.person_id,
paf.business_group_id,
paf.payroll_id,
to_number(null),
paf.organization_id,
paf.pay_basis_id,
to_number(null),
to_date(null),
to_date(null)
from fnd_sessions fs, per_all_assignments_f paf
where fs.session_id = userenv('sessionid')
and fs.effective_date between paf.effective_start_date and paf.effective_end_date
and paf.business_group_id = p_business_group_id
and paf.payroll_id > l_restart_payroll_id
and not exists (select 1
from pay_cost_allocations_f pcax
where pcax.assignment_id = paf.assignment_id
and fs.session_id = userenv('sessionid')
and fs.effective_date between pcax.effective_start_date and pcax.effective_end_date)
AND (p_extract_by_org = l_no OR
(p_extract_by_org = l_yes and paf.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = l_yes)))
order by 1,2;
Select assignment_id,
first_name,
last_name
from PSB_EMPLOYEES_I
where hr_position_id = le_position_id
and assignment_id = le_assignment_id
and data_extract_id = p_data_extract_id;
SELECT gl_account_segment,payroll_cost_segment
FROM pay_payroll_gl_flex_maps
WHERE payroll_id = l_payroll_id
AND gl_set_of_books_id = p_set_of_books_id
AND gl_account_segment in
( SELECT application_column_name
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = l_id_flex_code -- bug #4924031
AND application_id = l_application_id -- bug #4924031
AND enabled_flag = l_yes_flag -- bug #4924031
AND id_flex_num = l_chart_of_accounts_id )
ORDER BY gl_account_segment;
Select chart_of_accounts_id
from gl_sets_of_books
where set_of_books_id = p_set_of_books_id;
Select pay.cost_allocation_keyflex_id ,
pay.effective_start_date,
pay.effective_end_date
from pay_all_payrolls pay, pay_cost_allocation_keyflex pcak
where pay.payroll_id = le_payroll_id
and pay.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id;
Select element_type_id
from pay_input_values piv,
per_pay_bases ppb
where ppb.pay_basis_id = le_pay_basis_id
and piv.input_value_id = ppb.input_value_id;
Select pel.cost_allocation_keyflex_id ,
pel.effective_start_date,
pel.effective_end_date
from Pay_element_entries pee,Pay_element_links pel,
pay_cost_allocation_keyflex pcak, Pay_element_types pet
where pee.assignment_id = le_assignment_id
and pee.element_link_id = pel.element_link_id
and pel.element_type_id = pet.element_type_id
and pet.element_type_id = le_element_type_id
and pel.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id;
Select hru.cost_allocation_keyflex_id ,
hru.date_from,
hru.date_to
from hr_organization_units hru, pay_cost_allocation_keyflex pcak
where hru.organization_id = le_organization_id
and hru.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id;
SELECT count(*) seg_count
FROM fnd_id_flex_segments_vl
WHERE id_flex_code = l_id_flex_code -- bug #4924031
AND application_id = l_application_id -- bug #4924031
AND enabled_flag = l_yes_flag -- bug #4924031
AND id_flex_num = l_chart_of_accounts_id;
select 'exists'
from psb_ld_payroll_maps
where data_extract_id = p_data_extract_id;
Select nvl(effective_start_date,p_date) effective_start_date
from psb_ld_payroll_maps plp
where plp.payroll_id = le_payroll_id
and plp.data_extract_id = p_data_extract_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
/* Update Reentry */
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Costing Interface',
p_restart_id => prev_payroll_id
);
insert_cost_distribution_row(p_assignment_id => cost_rec.assignment_id,
p_cost_keyflex_id => cost_rec.cost_allocation_keyflex_id,
p_business_group_id => p_business_group_id,
p_costing_level => 'ASSIGNMENT',
p_index => l_index,
p_proportion => cost_rec.proportion,
p_start_date => cost_rec.effective_start_date,
p_end_date => cost_rec.effective_end_date,
p_data_extract_id => p_data_extract_id,
p_cost_segments => l_cost_segments,
p_chart_of_accounts_id => l_chart_of_accounts_id);
insert_cost_distribution_row(p_assignment_id => cost_rec.assignment_id,
p_cost_keyflex_id => C_payroll_rec.cost_allocation_keyflex_id,
p_business_group_id => p_business_group_id,
p_costing_level => 'PAYROLL',
p_index => l_index,
p_proportion => 0,
p_start_date => C_payroll_rec.effective_start_date,
p_end_date => C_payroll_rec.effective_end_date,
p_data_extract_id => p_data_extract_id,
p_cost_segments => l_cost_segments,
p_chart_of_accounts_id => l_chart_of_accounts_id);
insert_cost_distribution_row(p_assignment_id => cost_rec.assignment_id,
p_cost_keyflex_id => C_Element_rec.cost_allocation_keyflex_id,
p_business_group_id => p_business_group_id,
p_costing_level => 'ELEMENT LINK',
p_index => l_index,
p_proportion => 0,
p_start_date => C_Element_rec.effective_start_date,
p_end_date => C_Element_rec.effective_end_date,
p_data_extract_id => p_data_extract_id,
p_cost_segments => l_cost_segments,
p_chart_of_accounts_id => l_chart_of_accounts_id);
insert_cost_distribution_row(p_assignment_id => cost_rec.assignment_id,
p_cost_keyflex_id => C_org_rec.cost_allocation_keyflex_id,
p_business_group_id => p_business_group_id,
p_costing_level => 'ORGANIZATION',
p_index => l_index,
p_proportion => 0,
p_start_date => C_org_rec.date_from,
p_end_date => C_org_rec.date_to,
p_data_extract_id => p_data_extract_id,
p_cost_segments => l_cost_segments,
p_chart_of_accounts_id => l_chart_of_accounts_id);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Costing Interface',
p_restart_id => prev_payroll_id
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
Select element_type_id
from fnd_sessions fs, pay_input_values piv,
per_pay_bases ppb,
per_all_assignments_f paf
where fs.session_id = userenv('sessionid')
and fs.effective_date between paf.effective_start_date and paf.effective_end_date
and paf.assignment_id = p_assignment_id
and ppb.pay_basis_id = paf.pay_basis_id
and piv.input_value_id = ppb.input_value_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
Insert into psb_cost_distributions_i
(DATA_EXTRACT_ID ,
ASSIGNMENT_ID ,
BUSINESS_GROUP_ID ,
COSTING_LEVEL ,
PROPORTION ,
CHART_OF_ACCOUNTS_ID ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
SEGMENT1 ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
SEGMENT6 ,
SEGMENT7 ,
SEGMENT8 ,
SEGMENT9 ,
SEGMENT10 ,
SEGMENT11 ,
SEGMENT12 ,
SEGMENT13 ,
SEGMENT14 ,
SEGMENT15 ,
SEGMENT16 ,
SEGMENT17 ,
SEGMENT18 ,
SEGMENT19 ,
SEGMENT20 ,
SEGMENT21 ,
SEGMENT22 ,
SEGMENT23 ,
SEGMENT24 ,
SEGMENT25 ,
SEGMENT26 ,
SEGMENT27 ,
SEGMENT28 ,
SEGMENT29 ,
SEGMENT30 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
PROJECT_ID ,
TASK_ID ,
AWARD_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_ORGANIZATION_ID ,
DESCRIPTION )
values
(
p_data_extract_id,
p_assignment_id,
p_business_group_id,
'ASSIGNMENT',
--bug 3677529 added nvl function in the following line
NVL(PSP_LABOR_DIST.g_charging_instructions(i).percent,0),
p_chart_of_accounts_id,
PSP_LABOR_DIST.g_charging_instructions(i).effective_start_date,
PSP_LABOR_DIST.g_charging_instructions(i).effective_end_date,
l_segment1,
l_segment2,
l_segment3,
l_segment4,
l_segment5,
l_segment6,
l_segment7,
l_segment8,
l_segment9,
l_segment10,
l_segment11,
l_segment12,
l_segment13,
l_segment14,
l_segment15,
l_segment16,
l_segment17,
l_segment18,
l_segment19,
l_segment20,
l_segment21,
l_segment22,
l_segment23,
l_segment24,
l_segment25,
l_segment26,
l_segment27,
l_segment28,
l_segment29,
l_segment30,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date,
PSP_LABOR_DIST.g_charging_instructions(i).project_id,
PSP_LABOR_DIST.g_charging_instructions(i).task_id,
PSP_LABOR_DIST.g_charging_instructions(i).award_id,
PSP_LABOR_DIST.g_charging_instructions(i).expenditure_type,
PSP_LABOR_DIST.g_charging_instructions(i).expenditure_organization_id,
PSP_LABOR_DIST.g_charging_instructions(i).description
);
PROCEDURE insert_cost_distribution_row(
P_ASSIGNMENT_ID IN number,
P_COST_KEYFLEX_ID IN number,
P_BUSINESS_GROUP_ID in number,
P_COSTING_LEVEL in varchar2,
P_INDEX in binary_integer,
P_PROPORTION in number,
P_START_DATE in date,
P_END_DATE in date,
P_DATA_EXTRACT_ID in number,
P_COST_SEGMENTS in g_glcostmap_tbl_type,
P_CHART_OF_ACCOUNTS_ID in number) AS
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
Insert into psb_cost_distributions_i
(DATA_EXTRACT_ID ,
ASSIGNMENT_ID ,
BUSINESS_GROUP_ID ,
COSTING_LEVEL ,
PROPORTION ,
CHART_OF_ACCOUNTS_ID ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
COST_ALLOCATION_KEYFLEX_ID ,
SEGMENT1 ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
SEGMENT6 ,
SEGMENT7 ,
SEGMENT8 ,
SEGMENT9 ,
SEGMENT10 ,
SEGMENT11 ,
SEGMENT12 ,
SEGMENT13 ,
SEGMENT14 ,
SEGMENT15 ,
SEGMENT16 ,
SEGMENT17 ,
SEGMENT18 ,
SEGMENT19 ,
SEGMENT20 ,
SEGMENT21 ,
SEGMENT22 ,
SEGMENT23 ,
SEGMENT24 ,
SEGMENT25 ,
SEGMENT26 ,
SEGMENT27 ,
SEGMENT28 ,
SEGMENT29 ,
SEGMENT30 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE )
values
(
p_data_extract_id,
p_assignment_id,
p_business_group_id,
p_costing_level,
l_percent,
p_chart_of_accounts_id,
p_start_date,
p_end_date,
p_cost_keyflex_id,
l_segment1,
l_segment2,
l_segment3,
l_segment4,
l_segment5,
l_segment6,
l_segment7,
l_segment8,
l_segment9,
l_segment10,
l_segment11,
l_segment12,
l_segment13,
l_segment14,
l_segment15,
l_segment16,
l_segment17,
l_segment18,
l_segment19,
l_segment20,
l_segment21,
l_segment22,
l_segment23,
l_segment24,
l_segment25,
l_segment26,
l_segment27,
l_segment28,
l_segment29,
l_segment30,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date
);
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
SELECT attribute_id,business_group_id,
name,definition_type,
definition_structure,
definition_table,
definition_column,
data_type,
system_attribute_type,
attribute_type_id,
value_table_flag
FROM PSB_ATTRIBUTES_VL
WHERE business_group_id = p_business_group_id
AND attribute_id > l_restart_attribute_id
ORDER BY attribute_id;
Select job_structure
from per_business_groups
where business_group_id = p_business_group_id;
Select organization_id, name
from hr_organization_units
where business_group_id = p_business_group_id;
l_select_table varchar2(50);
l_select_column varchar2(50);
Select lookup_code,meaning,description
from fnd_common_lookups
where lookup_type = l_lookup_type;
Select name,select_column,
substr(select_table,1,instr(select_table,' ',1)) select_table
from psb_attribute_types
where attribute_type_id = l_attribute_type_id;
Select attribute_value_id
from psb_attribute_values
where attribute_id = lr_attribute_id
and attribute_value = lr_attribute_value
and data_extract_id = p_data_extract_id;
Select application_id,id_flex_code,
application_table_name,
set_defining_column_name
from fnd_id_flexs
where id_flex_name = l_definition_structure;
SELECT fstr.id_flex_num, fseg.application_column_name,
fseg.flex_value_set_id
FROM fnd_id_flex_structures_vl fstr,fnd_id_flex_segments_vl fseg
WHERE fstr.application_id = l_application_id
AND fstr.id_flex_code = l_id_flex_code
AND fstr.id_flex_structure_name = l_definition_table
AND fstr.id_flex_code = fseg.id_flex_code
AND fstr.id_flex_num = fseg.id_flex_num
AND fseg.segment_name = l_definition_column
AND fstr.application_id = fseg.application_id; -- bug #4924031;
Select application_id,application_table_name,
context_column_name
from fnd_descriptive_flexs_vl
where descriptive_flexfield_name = l_definition_structure;
Select fcon.descriptive_flex_context_code,
fcol.application_column_name,
fcol.flex_value_set_id
from fnd_descr_flex_contexts_vl fcon,fnd_descr_flex_column_usages fcol
where fcon.application_id = fcol.application_id
and fcon.descriptive_flexfield_name = l_definition_structure
and fcon.descriptive_flex_context_code = l_definition_table
and fcon.descriptive_flexfield_name = fcol.descriptive_flexfield_name
and fcon.descriptive_flex_context_code = fcol.descriptive_flex_context_code
and fcol.end_user_column_name = l_definition_column;
Select lookup_type
from per_common_lookup_types_v
where lookup_type_meaning = l_definition_table;
Select job_id, name
from per_jobs
where business_group_id = p_business_group_id;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Attribute Values Interface',
p_restart_id => prev_attribute_id
);
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
INSERT INTO PSB_ATTRIBUTE_VALUES_I
(
ATTRIBUTE_VALUE_ID ,
ATTRIBUTE_ID ,
ATTRIBUTE_VALUE ,
DESCRIPTION ,
DATA_EXTRACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE )
VALUES
(
l_attribute_value_id,
attribute_rec.attribute_id,
kvalue.value,
kvalue.meaning,
p_data_extract_id,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
l_debug_info := 'Inserting for Atrribute Id '
||attribute_rec.attribute_id
||', Atrribute Data Type '||l_data_type
||', Atrribute Value '||dvalue.value;
INSERT INTO PSB_ATTRIBUTE_VALUES_I
(
ATTRIBUTE_VALUE_ID ,
ATTRIBUTE_ID ,
ATTRIBUTE_VALUE ,
VALUE_ID ,
DESCRIPTION ,
DATA_EXTRACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE
)
VALUES
(
l_attribute_value_id,
attribute_rec.attribute_id,
-- Fix for bug #4075170 changed the date format to canonical.
-- But since DFF always stores date in canonical format, this conversion is not
-- necessary. So removed the conversion as part for Bug #4658351.
dvalue.value,
dvalue.id,
dvalue.meaning,
p_data_extract_id,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
l_debug_info := 'Inserting for Atrribute Id '
||attribute_rec.attribute_id
||', Atrribute Data Type '||l_data_type
||', Atrribute Value '||l_description;
INSERT INTO PSB_ATTRIBUTE_VALUES_I
(
ATTRIBUTE_VALUE_ID ,
ATTRIBUTE_ID ,
ATTRIBUTE_VALUE ,
DESCRIPTION ,
DATA_EXTRACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE
)
VALUES
(
l_attribute_value_id,
attribute_rec.attribute_id,
l_lookup_meaning,
-- Bug #4658351
-- Moved the date format conversion out of the insert statement.
l_description,
p_data_extract_id,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
lsql_stmt := 'Select distinct '||C_table_rec.select_column
||' From '||C_table_rec.select_table;
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
INSERT INTO PSB_ATTRIBUTE_VALUES_I
(
ATTRIBUTE_VALUE_ID ,
ATTRIBUTE_ID ,
ATTRIBUTE_VALUE ,
DATA_EXTRACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE
)
VALUES
(
l_attribute_value_id,
attribute_rec.attribute_id,
vdef_col,
p_data_extract_id,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
l_job_stmt := 'Select job_id, concatenated_segments '||
' from per_jobs pj, per_job_definitions_kfv pjv '||
'where pj.business_group_id = '||p_business_group_id||
' and pj.job_definition_id = pjv.job_definition_id and '||
'pjv.id_flex_num = '||l_job_id_flex_num;
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
INSERT INTO PSB_ATTRIBUTE_VALUES_I
(
ATTRIBUTE_VALUE_ID ,
ATTRIBUTE_ID ,
ATTRIBUTE_VALUE ,
VALUE_ID ,
DATA_EXTRACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE
)
VALUES
(
l_attribute_value_id,
attribute_rec.attribute_id,
lr_attribute_value,
lr_value_id,
p_data_extract_id,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
select psb_attribute_values_s.nextval into
l_attribute_value_id from dual;
INSERT INTO PSB_ATTRIBUTE_VALUES_I
(
ATTRIBUTE_VALUE_ID ,
ATTRIBUTE_ID ,
ATTRIBUTE_VALUE ,
VALUE_ID ,
DATA_EXTRACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE
)
VALUES
(
l_attribute_value_id,
attribute_rec.attribute_id,
lr_attribute_value,
lr_value_id,
p_data_extract_id,
l_last_update_date,
l_last_updated_by ,
l_last_update_login ,
l_created_by,
l_creation_date);
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Attribute Values Interface',
p_restart_id => l_fin_attribute_id
);
Select attribute_id,name,definition_type,definition_structure,
definition_table, definition_column,system_attribute_type,
attribute_type_id,data_type
from Psb_attributes_VL
where business_group_id = p_business_group_id
and attribute_id > l_restart_attribute_id;
Select b.person_id,b.assignment_id,a.position_id,
b.grade_id,a.job_id,a.organization_id,
nvl(a.fte,1) fte, a.earliest_hire_date,
--a.working_hours,a.frequency,
decode(b.frequency,'W',b.normal_hours,decode(a.frequency,'W',a.working_hours,null)) working_hours,
decode(b.frequency,'W','A','P') freq_flag,
a.position_type,
c.hr_position_name,
b.people_group_id ,
b.soft_coding_keyflex_id,
b.effective_start_date,
b.effective_end_date,
a.effective_start_date date_effective,
a.effective_end_date date_end
from fnd_sessions d, per_all_assignments_f b, hr_all_positions_f a , psb_positions_i c, psb_employees_i e
where d.session_id = userenv('sessionid')
and d.effective_date between a.effective_start_date and a.effective_end_date
and d.effective_date between b.effective_start_date and b.effective_end_date
and a.business_group_id = p_business_group_id
and a.position_id = c.hr_position_id
and c.data_extract_id = p_data_extract_id
and b.assignment_id = e.assignment_id
and e.data_extract_id = p_data_extract_id
and a.position_id = b.position_id
and c.hr_employee_id = b.person_id
and b.business_group_id = p_business_group_id
/*For Bug No : 2109120 Start*/
--and b.primary_flag(+) = 'Y'
/*For Bug No : 2109120 End*/
and b.assignment_type = 'E'
-- de by org
-- The following logic is used to restrict the positions for all the
-- selected organizations, if extract by org is enabled.
-- Otherwise, we will ignore the organizations available
-- in the business group.
and (p_extract_by_org = 'N' OR
(p_extract_by_org = 'Y' and a.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = 'Y')))
UNION ALL
Select to_number(NULL),to_number(NULL),a.position_id,
to_number(NULL),a.job_id,a.organization_id,
nvl(a.fte,1) fte, a.earliest_hire_date,
--a.working_hours,a.frequency,
decode(a.frequency,'W',a.working_hours,null) working_hours,
'P' freq_flag ,
a.position_type,
c.hr_position_name,
to_number(NULL) ,
to_number(NULL),
to_date(NULL),
to_date(NULL),
a.effective_start_date date_effective,
a.effective_end_date date_end
from fnd_sessions b , hr_all_positions_f a , psb_positions_i c
where b.session_id = userenv('sessionid')
and b.effective_date between a.effective_start_date and a.effective_end_date
and a.business_group_id = p_business_group_id
and a.position_id = c.hr_position_id
and c.data_extract_id = p_data_extract_id
and c.hr_employee_id is null
-- de by org
-- The following logic is used to restrict the positions for all the
-- selected organizations, if extract by org is enabled.
-- Otherwise, we will ignore the organizations available
-- in the business group
and (p_extract_by_org = 'N' OR
(p_extract_by_org = 'Y' and a.organization_id in
(select organization_id
from psb_data_extract_orgs
where data_extract_id = p_data_extract_id
and select_flag = 'Y')));
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
Select name, select_table,
substr(select_table,1,instr(select_table,' ',1)) select_tab,
select_column,select_key,
link_key,decode(link_type,'A','PER_ALL_ASSIGNMENTS','E',
'PER_ALL_PEOPLE','P', 'HR_ALL_POSITIONS','PER_ALL_ASSIGNMENTS') link_type,link_type l_alias2,
select_where
From Psb_attribute_types
Where attribute_type = d_attribute_type
and attribute_type_id = d_attribute_type_id;
Select application_id,id_flex_code,
application_table_name,
set_defining_column_name
from fnd_id_flexs
where id_flex_name = l_definition_structure;
SELECT fseg.application_column_name,
fstr.id_flex_num
FROM fnd_id_flex_structures_vl fstr,fnd_id_flex_segments_vl fseg
WHERE fstr.application_id = l_application_id
AND fstr.id_flex_code = l_id_flex_code
AND fstr.id_flex_structure_name = l_definition_table
AND fstr.id_flex_code = fseg.id_flex_code
AND fstr.id_flex_num = fseg.id_flex_num
AND fseg.segment_name = l_definition_column
AND fstr.application_id = fseg.application_id; -- bug #4924031;
Select application_id,application_table_name,
context_column_name
from fnd_descriptive_flexs_vl
where descriptive_flexfield_name = l_definition_structure;
Select fcol.application_column_name
from fnd_descr_flex_contexts_vl fcon,fnd_descr_flex_column_usages fcol
where fcon.application_id = fcol.application_id
and fcon.descriptive_flexfield_name = l_definition_structure
and fcon.descriptive_flex_context_code = l_definition_table
and fcon.descriptive_flexfield_name = fcol.descriptive_flexfield_name
and fcon.descriptive_flex_context_code = fcol.descriptive_flex_context_code
and fcol.end_user_column_name = l_definition_column;
Select lookup_type
from per_common_lookup_types_v
where lookup_type_meaning = l_definition_table;
Select job_structure
from per_business_groups
where business_group_id = p_business_group_id;
Select name
from hr_all_organization_units
where organization_id = l_organization_id;
Select value
from per_assignment_budget_values
where assignment_id = l_assignment_id
--changed the unit value from 'F' to 'FTE'
and unit = 'FTE';
Select first_name,last_name
from psb_employees_i
where hr_employee_id = l_person_id
and data_extract_id = p_data_extract_id;
Select start_date
from per_all_people
where person_id = l_person_id ;
Select name
from per_jobs
where job_id = l_job_id;
SELECT sum(nvl(value,1)) sum_fte
FROM fnd_sessions fs,
per_assignment_budget_values pab,
per_all_assignments_f paf,
per_assignment_status_types past
WHERE fs.session_id = userenv('sessionid')
AND fs.effective_date between paf.effective_start_date
and paf.effective_end_date
AND paf.position_id = l_pos_id
AND paf.assignment_type = 'E'
/* Bug 3796397 Start */
AND paf.assignment_status_type_id
= past.assignment_status_type_id
AND past.per_system_status <> 'TERM_ASSIGN'
/* Bug 3796397 End */
AND pab.assignment_id(+) = paf.assignment_id
AND pab.unit(+) = 'FTE';
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login :=FND_GLOBAL.LOGIN_ID;
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Position Assignments Interface',
p_restart_id => prev_attribute_id
);
l_sql_stmt := 'Select '||l_application_column_name||
' From Per_jobs,per_job_definitions '||
' Where per_jobs.job_id = '||':lc_job_id'||
' and per_jobs.job_definition_id = '||
' per_job_definitions.job_definition_id';
l_sql_stmt := 'Select '||l_application_column_name||
' From hr_positions,per_position_definitions '||
' Where hr_positions.position_id = '||':lc_position_id'||
' and hr_positions.position_definition_id = '||
' per_position_definitions.position_definition_id';
l_sql_stmt := 'Select '||l_application_column_name||
' From Per_grades,per_grade_definitions '||
' Where per_grades.grade_id = '||':lc_grade_id'||
' and per_grades.grade_definition_id = '||
' per_grade_definitions.grade_definition_id';
l_sql_stmt := 'Select '||l_application_column_name||
' From Pay_People_Groups'||
' Where pay_people_groups.people_group_id = '||
':lc_people_group_id';
l_sql_stmt := 'Select '||l_application_column_name||
' From pay_cost_allocations,pay_cost_allocation_keyflex '||
' Where pay_cost_allocations.assignment_id = '||
':lc_assignment_id'||
' and pay_cost_allocations.cost_allocation_keyflex_id = '||
' pay_cost_allocation_keyflex.cost_allocation_keyflex_id '||
' order by pay_cost_allocations.proportion';
l_sql_stmt := 'Select '||l_application_column_name||
' From hr_soft_coding_keyflex'||
' Where hr_soft_coding_keyflex.soft_coding_keyflex_id = '||
':lc_soft_coding_keyflex_id';
l_sql_stmt := 'Select '||l_application_column_name||
' From Per_Analysis_Criteria, Per_Person_Analyses'||
' Where Per_Person_Analyses.person_id = :lc_person_id '||
' And Per_person_analyses.id_flex_num = :lc_id_flex_num '||
' And Per_person_analyses.analysis_criteria_id = Per_analysis_criteria.analysis_criteria_id '||
' And Per_person_analyses.id_flex_num = Per_analysis_criteria.id_flex_num';
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
v_segment,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
Select ltrim(rtrim(substr(Attr_type_rec.select_table,
instr(Attr_type_rec.select_table,' ',1),
length(Attr_type_rec.select_table) - instr(Attr_type_rec.select_table,' ',1) + 1))) into l_alias1
from dual;
LTRIM(RTRIM(attr_type_rec.select_tab)) = 'PER_ALL_POSITIONS' AND
LTRIM(RTRIM(attr_type_rec.name)) = 'PER_POSITIONS' THEN
-- commented out l_alais1 as we are selecting from table HR_ALL_POSITIONS
d_sql_stmt := 'Select '||/*l_alias1*/Attr_type_rec.l_alias2||'.'
||l_application_column_name||
' From '||Attr_type_rec.select_tab||' '||
l_alias1||' , '||
Attr_type_rec.link_type||' '||Attr_type_rec.l_alias2||
' Where '||l_alias1||'.'||
Attr_type_rec.select_key||' = '||
Attr_type_rec.l_alias2||'.'||Attr_type_rec.link_key||
' and '||Attr_type_rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
d_sql_stmt := 'Select '||l_alias1||'.'
||l_application_column_name||
' From '||Attr_type_rec.select_tab||' '||
l_alias1||' , '||
Attr_type_rec.link_type||' '||Attr_type_rec.l_alias2||
' Where '||l_alias1||'.'||
Attr_type_rec.select_key||' = '||
Attr_type_rec.l_alias2||'.'||Attr_type_rec.link_key||
' and '||Attr_type_rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
if (Attr_type_rec.select_where is not null) then
d_sql_stmt := d_sql_stmt||' and '||Attr_type_rec.select_where;
l_debug_info := 'Inserting for Atrribute Name '
||Emp_attribute_rec.name
||', Atrribute Data Type '||l_data_type
||', Atrribute Value '||v_dsegment;
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
-- Fix for bug #4075170 changed the date format to canonical.
-- But since DFF always stores date in canonical format, this conversion is not
-- necessary. So removed the conversion as part for Bug #4658351.
v_dsegment,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
Select ltrim(rtrim(substr(Attr_type_rec.select_table,
instr(Attr_type_rec.select_table,' ',1),
length(Attr_type_rec.select_table) - instr(Attr_type_rec.select_table,' ',1) + 1))) into l_alias1
from dual;
q_sql_stmt := 'Select a.meaning '||
' From Fnd_Common_lookups a , '||
Attr_type_rec.select_tab||' '||l_alias1||' ,'||
Attr_type_rec.link_type||' '||Attr_type_rec.l_alias2||
' Where a.lookup_type = '||''''||
l_lookup_type||''''||
' and a.lookup_code = '||
l_alias1||'.'||Attr_type_rec.select_column||
' and '||l_alias1||'.'||Attr_type_rec.select_key||
' = '||Attr_type_rec.l_alias2||'.'||Attr_type_rec.link_key||
' and '||Attr_type_rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
if (Attr_type_rec.select_where is not null) then
q_sql_stmt := q_sql_stmt||' and '||Attr_type_rec.select_where;
l_debug_info := 'Inserting for Atrribute Id '
||Emp_attribute_rec.name
||', Atrribute Data Type '||l_data_type
||', Atrribute Value '||v_qsegment;
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
-- Bug #4658351
-- Moved the date format conversion out of the insert statement.
v_qsegment,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
Select ltrim(rtrim(substr(Attr_type_rec.select_table,
instr(Attr_type_rec.select_table,' ',1),
length(Attr_type_rec.select_table) - instr(Attr_type_rec.select_table,' ',1) + 1))) into l_alias1
from dual;
if (Attr_type_rec.select_table = Attr_type_rec.link_type) then
o_sql_stmt := 'Select '||
Attr_type_rec.select_column||
' From '||Attr_type_rec.select_tab||
' Where '||Attr_type_rec.select_tab||'.'||l_emp_col||
' = '||':v_emp_val';
o_sql_stmt := 'Select '||l_alias1||'.'||
Attr_type_rec.select_column||
' From '||Attr_type_rec.select_tab||' '||l_alias1||' , '||
Attr_type_rec.link_type||' '||Attr_type_rec.l_alias2||
' Where '||l_alias1||'.'||
Attr_type_rec.select_key||' = '||
Attr_type_rec.l_alias2||'.'||Attr_type_rec.link_key||
' and '||Attr_type_rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
if (Attr_type_rec.select_where is not null) then
o_sql_stmt := o_sql_stmt||' and '||Attr_type_rec.select_where;
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
v_osegment,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
l_job_stmt := 'Select concatenated_segments '||
'from per_jobs pj,per_job_definitions_kfv pjv '||
'where pj.job_id = '||':lc_job_id'||
' and pj.job_definition_id = pjv.job_definition_id '||
' and pjv.id_flex_num = '||l_id_flex_num;
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
l_job_name,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
l_organization_name,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
SELECT effective_start_date
FROM (
SELECT a.effective_start_date
FROM per_all_assignments_f a,
fnd_sessions b,
per_assignment_status_types c
WHERE a.position_id = Employee_rec.position_id
AND a.assignment_status_type_id = c.assignment_status_type_id
AND c.per_system_status = 'TERM_ASSIGN'
AND b.effective_date BETWEEN a.effective_start_date
AND to_date('31124712','DDMMYYYY')
AND b.session_id = userenv('sessionid')
ORDER BY a.effective_start_date DESC
)
WHERE ROWNUM <= 1
) LOOP
lv_effective_end_date := l_date_rec.effective_start_date;
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
lp_fte,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
FOR l_date_rec IN ( SELECT effective_start_date
FROM (
SELECT a.effective_start_date
FROM per_all_assignments_f a,
fnd_sessions b,
per_assignment_status_types c
WHERE a.position_id = Employee_rec.position_id
AND a.assignment_status_type_id = c.assignment_status_type_id
AND c.per_system_status = 'TERM_ASSIGN'
AND b.effective_date BETWEEN a.effective_start_date
AND to_date('31124712','DDMMYYYY')
AND b.session_id = userenv('sessionid')
ORDER BY a.effective_start_date DESC
)
WHERE ROWNUM <= 1
) LOOP
lv_effective_end_date := l_date_rec.effective_start_date;
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
lp_default_weekly_hours,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
INSERT INTO PSB_EMPLOYEE_ASSIGNMENTS_I
( HR_POSITION_ID ,
HR_EMPLOYEE_ID ,
DATA_EXTRACT_ID ,
ATTRIBUTE_NAME ,
ATTRIBUTE_VALUE ,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY,
CREATION_DATE
)
values
(Employee_rec.position_id,
Employee_rec.person_id,
p_data_extract_id,
Emp_attribute_rec.name,
lp_hiredate,
le_effective_start_date,
le_effective_end_date,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_created_by,
l_creation_date);
SELECT link_type
from PSB_ATTRIBUTES_VL a, PSB_ATTRIBUTE_TYPES B
WHERE a.business_group_id = p_business_group_id
AND a.system_attribute_type = 'FTE'
AND a.attribute_type_id = b.attribute_type_id
)
LOOP
l_link_type := l_fte_link_rec.link_type;
SELECT hr_position_id ,
MAX(attribute_value) total_fte ,
COUNT(hr_employee_id) total_employees
FROM psb_employee_assignments_i
WHERE data_extract_id = p_data_extract_id
AND attribute_value IS NOT NULL
AND hr_employee_id IS NOT NULL
AND attribute_name IN
(
SELECT name
FROM psb_attributes_VL
WHERE system_attribute_type = 'FTE'
AND business_group_id = p_business_group_id
)
GROUP BY hr_position_id
HAVING COUNT(hr_position_id) > 1
)
LOOP
-- Find the average FTE to be divided among the employees.
l_average_fte := ROUND (l_emp_assgn_rec.total_fte /
l_emp_assgn_rec.total_employees, 2 ) ;
( SELECT ROWID,
ROWNUM
FROM psb_employee_assignments_i
WHERE hr_position_id = l_emp_assgn_rec.hr_position_id
AND data_extract_id = p_data_extract_id
AND attribute_value IS NOT NULL
AND hr_employee_id IS NOT NULL
AND attribute_name IN
(
SELECT name
FROM psb_attributes
WHERE system_attribute_type = 'FTE'
AND business_group_id = p_business_group_id
)
)
LOOP
--
-- The allocate FTE must equal the total_fte. The following will ensure
-- that the last employee will get the remaining of the FTE.
--
IF l_emp_rec.rownum = l_emp_assgn_rec.total_employees THEN
l_fte := l_emp_assgn_rec.total_fte - l_allocated_fte ;
UPDATE psb_employee_assignments_i
SET attribute_value = l_fte
WHERE rowid = l_emp_rec.rowid ;
Update_Reentry
( p_api_version => 1.0 ,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_data_extract_id => p_data_extract_id,
p_extract_method => p_extract_method,
p_process => 'Position Assignments Interface',
p_restart_id => prev_attribute_id
);
Select nvl(sp1_status,'I'),
nvl(sp2_status,'I'),
nvl(sp3_status,'I'),
nvl(sp4_status,'I'),
nvl(sp5_status,'I'),
nvl(sp6_status,'I'),
nvl(sp7_status,'I'),
nvl(sp8_status,'I'),
nvl(sp9_status,'I'),
nvl(sp10_status,'I'),
nvl(sp11_status,'I'),
nvl(sp12_status,'I'),
nvl(sp13_status,'I'),
nvl(sp14_status,'I'),
nvl(sp15_status,'I'),
nvl(sp16_status,'I'),
nvl(sp17_status,'I'),
nvl(sp18_status,'I'),
nvl(sp19_status,'I'),
attribute1,
attribute2,
nvl(to_number(attribute3),0),
nvl(to_number(attribute11),0),
nvl(to_number(attribute12),0),
nvl(to_number(attribute13),0),
nvl(to_number(attribute14),0),
nvl(to_number(attribute15),0),
nvl(to_number(attribute16),0),
nvl(to_number(attribute17),0),
nvl(to_number(attribute18),0),
nvl(to_number(attribute19),0),
nvl(to_number(attribute20),0),
nvl(to_number(attribute21),0),
nvl(to_number(attribute22),0),
nvl(to_number(attribute23),0),
nvl(to_number(attribute24),0),
nvl(to_number(attribute25),0),
nvl(to_number(attribute26),0),
nvl(to_number(attribute27),0),
nvl(to_number(attribute28),0),
nvl(to_number(attribute29),0)
from psb_reentrant_process_status
where process_type = 'HR DATA EXTRACT'
and process_uid = p_data_extract_id;
Procedure Update_Reentry
( p_api_version IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_data_extract_id IN NUMBER,
p_extract_method IN VARCHAR2,
p_process IN VARCHAR2,
p_restart_id IN NUMBER
)
IS
Cursor C_Reenter_Upd is
Select rowid
from psb_reentrant_process_status
where process_type = 'HR DATA EXTRACT'
and process_uid = p_data_extract_id;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Reentry';
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
Insert Into Psb_Reentrant_Process_Status
(process_type,
process_uid,
attribute1,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29)
values
('HR DATA EXTRACT',
p_data_extract_id,
p_extract_method,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_attribute16,
l_attribute17,
l_attribute18,
l_attribute19,
l_attribute20,
l_attribute21,
l_attribute22,
l_attribute23,
l_attribute24,
l_attribute25,
l_attribute26,
l_attribute27,
l_attribute28,
l_attribute29);
Update Psb_Reentrant_Process_Status
set attribute11 = decode(p_process,'Positions Interface',l_attribute11,attribute11),
attribute12 = decode(p_process,'Salary Interface',l_attribute12,attribute12),
attribute13 = decode(p_process,'Employees Interface',l_attribute13,attribute13),
attribute14 = decode(p_process,'Costing Interface',l_attribute14,attribute14),
attribute15 = decode(p_process,'Attribute Values Interface',l_attribute15,attribute15),
attribute16 = decode(p_process,'Position Assignments Interface',l_attribute16,attribute16),
attribute17 = decode(p_process,'Data Extract Summary',l_attribute17,attribute17),
attribute18 = decode(p_process,'Validate Data Extract',l_attribute18,attribute18),
attribute19 = decode(p_process,'Copy Attributes',l_attribute19,attribute19),
attribute20 = decode(p_process,'Copy Elements',l_attribute20,attribute20),
attribute21 = decode(p_process,'Copy Position Sets',l_attribute21,attribute21),
attribute22 = decode(p_process,'Copy Default Rules',l_attribute22,attribute22),
attribute23 = decode(p_process,'PSB Positions',l_attribute23,attribute23),
attribute24 = decode(p_process,'PSB Elements',l_attribute24,attribute24),
attribute25 = decode(p_process,'PSB Employees',l_attribute25,attribute25),
attribute26 = decode(p_process,'PSB Costing',l_attribute26,attribute26),
attribute27 = decode(p_process,'PSB Attribute Values',l_attribute27,attribute27),
attribute28 = decode(p_process,'PSB Position Assignments',l_attribute28,attribute28),
attribute29 = decode(p_process,'PSB Apply Defaults',l_attribute29,attribute29)
where rowid = l_rowid;
END Update_Reentry;
Select nvl(sp1_status,'I'),
nvl(sp2_status,'I'),
nvl(sp3_status,'I'),
nvl(sp4_status,'I'),
nvl(sp5_status,'I'),
nvl(sp6_status,'I'),
nvl(sp7_status,'I'),
nvl(sp8_status,'I'),
nvl(sp9_status,'I'),
nvl(sp10_status,'I'),
nvl(sp11_status,'I'),
nvl(sp12_status,'I'),
nvl(sp13_status,'I'),
nvl(sp14_status,'I'),
nvl(sp15_status,'I'),
nvl(sp16_status,'I'),
nvl(sp17_status,'I'),
nvl(sp18_status,'I'),
nvl(sp19_status,'I'),
attribute1,
attribute2,
nvl(to_number(attribute3),0)
from psb_reentrant_process_status
where process_type = 'HR DATA EXTRACT'
and process_uid = p_data_extract_id;
l_last_update_date date;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
Insert Into Psb_Reentrant_Process_Status
(process_type,
process_uid,
sp1_status,
sp2_status,
sp3_status,
sp4_status,
sp5_status,
sp6_status,
sp7_status,
sp8_status,
sp9_status,
sp10_status,
sp11_status,
sp12_status,
sp13_status,
sp14_status,
sp15_status,
sp16_status,
sp17_status,
sp18_status,
sp19_status,
attribute1 ,
attribute2 ,
attribute3
)
values
('HR DATA EXTRACT',
p_data_extract_id,
l_sp1_status,
l_sp2_status,
l_sp3_status,
l_sp4_status,
l_sp5_status,
l_sp6_status,
l_sp7_status,
l_sp8_status,
l_sp9_status,
l_sp10_status,
l_sp11_status,
l_sp12_status,
l_sp13_status,
l_sp14_status,
l_sp15_status,
l_sp16_status,
l_sp17_status,
l_sp18_status,
l_sp19_status,
p_extract_method,
l_last_update_date,
to_char(l_refresh_num)
);
Update Psb_Reentrant_Process_Status
set sp1_status = decode(p_process,'Positions Interface','C',sp1_status),
sp2_status = decode(p_process,'Salary Interface','C',sp2_status),
sp3_status = decode(p_process,'Employees Interface','C',sp3_status),
sp4_status = decode(p_process,'Costing Interface','C',sp4_status),
sp5_status = decode(p_process,'Attribute Values Interface','C',sp5_status),
sp6_status = decode(p_process,'Position Assignments Interface','C',sp6_status),
sp7_status = decode(p_process,'Data Extract Summary','C',sp7_status),
sp8_status = decode(p_process,'Validate Data Extract','C',sp8_status),
sp9_status = decode(p_process,'Copy Elements','C',sp9_status), -- Fix for Bug #4726455.
sp10_status = decode(p_process,'Copy Elements','C',sp10_status),
sp11_status = decode(p_process,'Copy Position Sets','C',sp11_status),
sp12_status = decode(p_process,'Copy Default Rules','C',sp12_status),
sp13_status = decode(p_process,'PSB Positions','C',sp13_status),
sp14_status = decode(p_process,'PSB Elements','C',sp14_status),
sp15_status = decode(p_process,'PSB Employees','C',sp15_status),
sp16_status = decode(p_process,'PSB Costing','C',sp16_status),
sp17_status = decode(p_process,'PSB Attribute Values','C',sp17_status),
sp18_status = decode(p_process,'PSB Position Assignments','C',sp18_status),
sp19_status = decode(p_process,'PSB Apply Defaults','C',sp19_status),
attribute2 = l_last_update_date
where process_type = 'HR DATA EXTRACT'
and process_uid = p_data_extract_id;
Select name, select_table,attribute_type,
substr(select_table,1,instr(select_table,' ',1)) select_tab,
select_column,select_key,
link_key,decode(link_type,'A','PER_ALL_ASSIGNMENTS','E',
'PER_ALL_PEOPLE','P', 'HR_ALL_POSITIONS','PER_ALL_ASSIGNMENTS')
link_type,link_type l_alias2,
select_where
From Psb_attribute_types
where attribute_type_id = p_attribute_type_id;
Select application_id,application_table_name,
context_column_name
from fnd_descriptive_flexs_vl
where descriptive_flexfield_name = p_definition_structure;
Select fcol.application_column_name
from fnd_descr_flex_contexts_vl fcon,fnd_descr_flex_column_usages fcol
where fcon.application_id = fcol.application_id
and fcon.descriptive_flexfield_name = p_definition_structure
and fcon.descriptive_flex_context_code = p_definition_table
and fcon.descriptive_flexfield_name = fcol.descriptive_flexfield_name
and fcon.descriptive_flex_context_code = fcol.descriptive_flex_context_code
and fcol.end_user_column_name = p_definition_column;
Select ltrim(rtrim(substr(C_Attribute_Types_Rec.select_table,
instr(C_Attribute_Types_Rec.select_table,' ',1),
length(C_Attribute_Types_Rec.select_table)
- instr(C_Attribute_Types_Rec.select_table,' ',1) + 1)))
into l_alias1
from dual;
d_sql_stmt := 'Select '||l_alias1||'.' ||l_application_column_name||
' From '||C_Attribute_Types_Rec.select_tab||' '||
l_alias1||' , '||
C_Attribute_Types_Rec.link_type||' '||
C_Attribute_Types_Rec.l_alias2||
' Where '||l_alias1||'.'||
C_Attribute_Types_Rec.select_key||' = '||
C_Attribute_Types_Rec.l_alias2||'.'||
C_Attribute_Types_Rec.link_key||
' and '||C_Attribute_Types_Rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
if (C_Attribute_Types_Rec.select_where is not null) then
d_sql_stmt := d_sql_stmt||' and '||C_Attribute_Types_Rec.select_where;
if (C_Attribute_Types_Rec.select_table = C_Attribute_Types_Rec.link_type) then
o_sql_stmt := 'Select '||
C_Attribute_Types_Rec.select_column||
' From '||C_Attribute_Types_Rec.select_tab||
' Where '||C_Attribute_Types_Rec.select_tab||'.'||
l_emp_col|| ' = '||':v_emp_val';
o_sql_stmt := 'Select '||l_alias1||'.'||
C_Attribute_Types_Rec.select_column||
' From '||C_Attribute_Types_Rec.select_tab||' '||l_alias1||
' , '||
C_Attribute_Types_Rec.link_type||' '||
C_Attribute_Types_Rec.l_alias2||
' Where '||l_alias1||'.'||
C_Attribute_Types_Rec.select_key||' = '||
C_Attribute_Types_Rec.l_alias2||'.'||
C_Attribute_Types_Rec.link_key||
' and '||C_Attribute_Types_Rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
if (C_Attribute_Types_Rec.select_where is not null) then
o_sql_stmt := o_sql_stmt||' and '||C_Attribute_Types_Rec.select_where;
q_sql_stmt := 'Select a.meaning '||
' From Fnd_Common_lookups a , '||
C_Attribute_Types_Rec.select_tab||' '||l_alias1||' ,'||
C_Attribute_Types_Rec.link_type||' '||
C_Attribute_Types_Rec.l_alias2||
' Where a.lookup_type = '||''''||
l_lookup_type||''''||
' and a.lookup_code = '||
l_alias1||'.'||C_Attribute_Types_Rec.select_column||
' and '||l_alias1||'.'||C_Attribute_Types_Rec.select_key||
' = '||C_Attribute_Types_Rec.l_alias2||'.'||
C_Attribute_Types_Rec.link_key||
' and '||C_Attribute_Types_Rec.l_alias2||'.'||l_emp_col||
' = '||':v_emp_val';
if (C_Attribute_Types_Rec.select_where is not null) then
q_sql_stmt := q_sql_stmt||' and '||C_Attribute_Types_Rec.select_where;
Delete fnd_sessions
where session_id = (select USERENV('sessionid') from dual);
Select segment1, segment2, segment3,
segment4, segment5, segment6,
segment7, segment8, segment9,
segment10, segment11, segment12,
segment13, segment14, segment15,
segment16, segment17, segment18,
segment19, segment20, segment21,
segment22, segment23, segment24,
segment25, segment26, segment27,
segment28, segment29, segment30
from pay_cost_allocation_keyflex
where cost_allocation_keyflex_id = pcost_allocation_keyflex_id;