[Home] [Help]
4508: a.segment21, a.segment22, a.segment23, a.segment24, a.segment25,
4509: a.segment26, a.segment27, a.segment28, a.segment29, a.segment30,
4510: a.effective_start_date
4511: FROM psb_cost_distributions_i a,
4512: psb_employees_i b,
4513: psb_employees be,
4514: psb_positions c,
4515: psb_position_assignments d
4516: WHERE a.assignment_id = b.assignment_id
4509: a.segment26, a.segment27, a.segment28, a.segment29, a.segment30,
4510: a.effective_start_date
4511: FROM psb_cost_distributions_i a,
4512: psb_employees_i b,
4513: psb_employees be,
4514: psb_positions c,
4515: psb_position_assignments d
4516: WHERE a.assignment_id = b.assignment_id
4517: AND a.data_extract_id = p_data_extract_id
6936: -- because psb_positions IS populated with proper hr_employee_id prior to
6937: -- running thIS process.
6938: /*For Bug No : 2594575 Start*/
6939: --Stop extracting secured data of employee
6940: --Removed the columns in psb_employees table
6941: /*For Bug No : 2594575 End*/
6942:
6943: Cursor C_Employees IS
6944: Select pe.hr_employee_id,
6956: salary_type,rate_or_payscale_id,
6957: element_value,proposed_salary,
6958: employee_number,
6959: pp.set_of_books_id
6960: FROM psb_employees_i pe,
6961: psb_positions pp
6962: WHERE pe.data_extract_id = p_data_extract_id
6963: AND pe.data_extract_id = pp.data_extract_id
6964: AND pe.hr_position_id = pp.hr_position_id
6970: /*Cursor Added for Bug#4135280 */
6971: /*Modified the cursor for Bug#4166493, to take care of date tracked HR data*/
6972: CURSOR C_Terminated_Employees IS
6973: SELECT distinct position_assignment_id,pp.position_id,pe.hr_employee_id
6974: FROM psb_position_assignments ppa,psb_positions pp,psb_employees pe,
6975: per_all_assignments_f paf
6976: WHERE ppa.position_id=pp.position_id
6977: AND ppa.data_extract_id=p_data_extract_id
6978: AND pp.hr_position_id > l_restart_hr_position_id
6989: AND pp.hr_employee_id IS NULL
6990: AND ppa.assignment_type='EMPLOYEE';
6991:
6992: /* SELECT distinct position_assignment_id,pp.position_id,pe.hr_employee_id
6993: FROM psb_position_assignments ppa,psb_positions pp,psb_employees pe,
6994: per_all_assignments_f paf
6995: WHERE ppa.position_id=pp.position_id
6996: AND ppa.data_extract_id=p_data_extract_id
6997: AND pp.hr_position_id > l_restart_hr_position_id
7180: p_return_status => l_return_status,
7181: p_msg_count => l_msg_count,
7182: p_msg_data => l_msg_data,
7183: p_data_extract_id => p_data_extract_id,
7184: p_process => 'PSB Employees',
7185: p_status => l_status,
7186: p_restart_id => l_restart_hr_position_id
7187: );
7188:
7256: p_msg_count => l_msg_count,
7257: p_msg_data => l_msg_data,
7258: p_data_extract_id => p_data_extract_id,
7259: p_extract_method => p_extract_method,
7260: p_process => 'PSB Employees',
7261: p_restart_id => prev_hr_position_id
7262: );
7263: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7264: raise FND_API.G_EXC_ERROR;
7431:
7432:
7433: --
7434: -- UpDATE 'EMPLOYEE' type assignments in 'REFRESH' mode..
7435: -- UpDATE psb_employees table as well.
7436: --
7437: -- de by org
7438: --
7439: -- l_extract_method contains the organization's status. Hence using it
7442: IF (l_extract_method = 'REFRESH') THEN
7443:
7444: /*For Bug No : 2594575 Start*/
7445: --Stop extracting secured data of employee
7446: --Removed the columns in psb_employees table
7447: /*For Bug No : 2594575 End*/
7448:
7449: -- UpDATE psb_employees table.
7450: UpDATE Psb_Employees
7445: --Stop extracting secured data of employee
7446: --Removed the columns in psb_employees table
7447: /*For Bug No : 2594575 End*/
7448:
7449: -- UpDATE psb_employees table.
7450: UpDATE Psb_Employees
7451: set first_name = employee_rec.first_name,
7452: full_name = employee_rec.full_name,
7453: known_as = employee_rec.known_as,
7446: --Removed the columns in psb_employees table
7447: /*For Bug No : 2594575 End*/
7448:
7449: -- UpDATE psb_employees table.
7450: UpDATE Psb_Employees
7451: set first_name = employee_rec.first_name,
7452: full_name = employee_rec.full_name,
7453: known_as = employee_rec.known_as,
7454: last_name = employee_rec.last_name,
7589: END IF; -- Check (p_extract_method = 'REFRESH') to refresh element_value.
7590:
7591: --
7592: -- Create 'EMPLOYEE' assignments in 'CREATE' mode.
7593: -- Populate employees in psb_employees table as well.
7594: --
7595: IF (l_extract_method = 'CREATE' or l_employee_dummy = 0) THEN
7596:
7597: Select psb_employees_s.nextval INTO l_employee_id
7593: -- Populate employees in psb_employees table as well.
7594: --
7595: IF (l_extract_method = 'CREATE' or l_employee_dummy = 0) THEN
7596:
7597: Select psb_employees_s.nextval INTO l_employee_id
7598: FROM dual;
7599:
7600: /*For Bug No : 2594575 Start*/
7601: --Stop extracting secured data of employee
7598: FROM dual;
7599:
7600: /*For Bug No : 2594575 Start*/
7601: --Stop extracting secured data of employee
7602: --Removed the columns in psb_employees table
7603: /*For Bug No : 2594575 End*/
7604:
7605: -- Populate PSB_EMPLOOYEES table as it IS 'CREATE' mode.
7606: INSERT INTO PSB_EMPLOYEES
7602: --Removed the columns in psb_employees table
7603: /*For Bug No : 2594575 End*/
7604:
7605: -- Populate PSB_EMPLOOYEES table as it IS 'CREATE' mode.
7606: INSERT INTO PSB_EMPLOYEES
7607: ( EMPLOYEE_ID ,
7608: HR_EMPLOYEE_ID ,
7609: EMPLOYEE_NUMBER ,
7610: FIRST_NAME ,
7752: p_msg_count => l_msg_count,
7753: p_msg_data => l_msg_data,
7754: p_position_assignment_id =>terminated_rec.position_assignment_id);
7755:
7756: --Delete the terminated and to be orphanned record in psb_employees
7757: --Note that we are deleting for the DE and hence all the
7758: -- worksheets for this DE.
7759:
7760: delete from psb_employees
7756: --Delete the terminated and to be orphanned record in psb_employees
7757: --Note that we are deleting for the DE and hence all the
7758: -- worksheets for this DE.
7759:
7760: delete from psb_employees
7761: where hr_employee_id=terminated_rec.hr_employee_id
7762: and data_extract_id=p_data_extract_id;
7763:
7764: END LOOP; -- FOR terminated_rec
7770: p_msg_count => l_msg_count,
7771: p_msg_data => l_msg_data,
7772: p_data_extract_id => p_data_extract_id,
7773: p_extract_method => p_extract_method,
7774: p_process => 'PSB Employees',
7775: p_restart_id => prev_hr_position_id
7776: );
7777: if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7778: raise FND_API.G_EXC_ERROR;
7784: p_msg_count => l_msg_count,
7785: p_msg_data => l_msg_data,
7786: p_data_extract_id => p_data_extract_id,
7787: p_extract_method => p_extract_method,
7788: p_process => 'PSB Employees'
7789: );
7790:
7791: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7792: RAISE FND_API.G_EXC_ERROR;
8441: vacancy_flag varchar2(1) := 'N';
8442:
8443: BEGIN
8444: Select count(*) INTO lcount
8445: FROM psb_employees_i
8446: WHERE hr_position_id = p_position_id
8447: AND data_extract_id = p_data_extract_id;
8448:
8449: IF (lcount >= 1) THEN
9137: LOOP
9138: l_currency_code := C_currency_rec.currency_code;
9139: END LOOP;
9140: --
9141: -- Process all the employees FROM psb_employees along with their ATTRIBUTE
9142: -- assignment inFORmation FROM psb_employee_assignments_i
9143: --
9144: FOR C_Assignment_Rec in
9145: (
9151: pea.attribute_value,
9152: pea.effective_start_DATE,
9153: pea.effective_END_DATE
9154: FROM psb_employee_assignments_i pea,
9155: psb_employees pe
9156: WHERE pea.data_extract_id = p_data_extract_id
9157: AND pe.hr_employee_id(+) = pea.hr_employee_id
9158: AND pe.data_extract_id(+) = p_data_extract_id
9159: AND pea.hr_position_id > l_restart_hr_position_id