[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Common procedure to update the hr_psft_sync_run table begins here*/
PROCEDURE update_psft_sync_run
(p_status number
,p_process_name varchar2
,p_run_date date
,errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2)
IS
l_status varchar2(10);
update hr_psft_sync_run
set status = l_status
where process = p_process_name
and run_date = p_run_date;
FND_FILE.put_line(fnd_file.log,'Error in update_psft_sync_run: '||SQLCODE);
END update_psft_sync_run;
/*Common procedure to update the hr_psft_sync_run table ends here*/
/*Common procedure to insert into hr_psft_sync_run table begins here*/
PROCEDURE insert_psft_sync_run
(p_status number
,p_process_name varchar2
,errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2)
IS
l_status varchar2(10);
INSERT INTO hr_psft_sync_run(run_date,status,process)
Values(sysdate,l_status,p_process_name);
FND_FILE.put_line(fnd_file.log,'Error in insert_psft_sync_run: '||SQLCODE);
END insert_psft_sync_run;
/*Common procedure to insert into psft_sync_run table ends here*/
/*Procedure to extract state data for Full Synch messages begins*/
PROCEDURE HR_STATE_FULL_SYNC(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2)
is
p_cntry_code fnd_territories_vl.territory_code%type;
select ft.territory_code,fcl.lookup_code,fcl.meaning,fcl.enabled_flag
from fnd_common_lookups fcl,fnd_territories_vl ft
where fcl.lookup_type = (ft.territory_code ||'_STATE')
order by ft.territory_code;
select count('x')
from hr_psft_sync_run
where process = 'STATE_FULL_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'STATE_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'STATE_FULL_SYNC',p_effective_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'STATE_FULL_SYNC',p_effective_date,errbuf,retcode);
select ft.territory_code,
ft.territory_short_name ,
ft.territory_code,ft.obsolete_flag
from fnd_territories_vl ft
order by ft.territory_code;
select count('x')
from hr_psft_sync_run
where process = 'COUNTRY_FULL_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'COUNTRY_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'COUNTRY_FULL_SYNC',p_effective_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'COUNTRY_FULL_SYNC',p_effective_date,errbuf,retcode);
select BUSINESS_GROUP_ID,
LOCATION_ID,
case when inactive_date is not null then inactive_date
else CREATION_DATE end,
case when inactive_date is not null then 'INACTIVE'
else 'ACTIVE' end,
LOCATION_CODE ,
DESCRIPTION,
STYLE,
COUNTRY,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
REGION_1,
REGION_2,
REGION_3,
POSTAL_CODE,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
LOC_INFORMATION13,
LOC_INFORMATION14,
LOC_INFORMATION15,
LOC_INFORMATION16,
LOC_INFORMATION17,
LOC_INFORMATION18,
LOC_INFORMATION19,
LOC_INFORMATION20
from
hr_locations_all;
select count('x')
from hr_psft_sync_run
where process = 'LOC_FULL_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'LOC_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'LOC_FULL_SYNC',p_effective_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'LOC_FULL_SYNC',p_effective_date,errbuf,retcode);
/*Select state ment modified for the employee number
not getting displayed for Ex-Employee*/
cursor csr_person_data is
SELECT DECODE ( ppf.CURRENT_NPW_FLAG , 'Y', NPW_NUMBER,EMPLOYEE_NUMBER ) EMPLOYEE_NUMBER,
HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE , PPF.PERSON_ID) ,
DATE_OF_BIRTH,
TOWN_OF_BIRTH,
COUNTRY_OF_BIRTH,
DATE_OF_DEATH,
ORIGINAL_DATE_OF_HIRE,
EFFECTIVE_START_DATE,
HL1.MEANING SEX,
HL4.MEANING MARITAL_STATUS,
FULL_NAME,
PRE_NAME_ADJUNCT,
SUFFIX,
HL3.MEANING TITLE,
LAST_NAME,
FIRST_NAME,
MIDDLE_NAMES,
ADDRESS_TYPE,
padr.DATE_FROM,
COUNTRY,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
TOWN_OR_CITY,
TELEPHONE_NUMBER_1,
REGION_1,
REGION_2,
POSTAL_CODE,
EMAIL_ADDRESS,
PHONE_TYPE,
PHONE_NUMBER,
HL2.MEANING NATIONALITY,
NATIONAL_IDENTIFIER
FROM PER_ALL_PEOPLE_F ppf,
PER_ADDRESSES padr ,
PER_PHONES ppn ,
hr_lookups HL1 ,
HR_LOOKUPS HL2 ,
HR_LOOKUPS HL3 ,
HR_LOOKUPS HL4
WHERE ppf.person_id = padr.person_id (+)
AND ( padr.person_id is null
OR ( padr.person_id is not null
AND padr.primary_flag ='Y'
AND ppf.person_id = padr.person_id
and sysdate between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
))
AND ppn.PARENT_ID (+) = PPF.PERSON_ID
-- Modified for the bug 6895752 starts here
/*AND ( ppn.parent_id is null
OR ( ppn.parent_id is not null
AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PPN.PHONE_TYPE = 'W1' ))*/
AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
AND PPN.PHONE_TYPE (+) = 'W1'
-- Modified for the bug 6895752 ends here
AND ((ppf.CURRENT_EMPLOYEE_FLAG = 'Y'
OR ppf.person_id in -- modified for bug6873563
(SELECT nvl(pps.person_id , -100)
FROM per_periods_of_service pps
WHERE pps.person_id = ppf.person_id
AND pps.business_group_id = ppf.business_group_id
and ACTUAL_TERMINATION_DATE is not null
))
OR ( ppf.CURRENT_NPW_FLAG = 'Y'
OR ppf.person_id in -- modified for bug6873563
(SELECT nvl(ppp.person_id , -100)
FROM per_periods_of_placement ppp
WHERE ppp.person_id = ppf.person_id
AND ppp.business_group_id = ppf.business_group_id
and ACTUAL_TERMINATION_DATE is not null
)))
AND HL1.LOOKUP_TYPE (+) = 'SEX'
AND HL1.LOOKUP_CODE (+) = ppf.SEX
AND HL2.LOOKUP_TYPE (+) = 'NATIONALITY'
AND HL2.LOOKUP_CODE (+) = Ppf.NATIONALITY
AND HL3.LOOKUP_TYPE (+) = 'TITLE'
AND HL3.LOOKUP_CODE (+) = PPF.TITLE
AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
AND sysdate BETWEEN effective_start_date AND effective_end_date ;
select COUNT ('1')
from hr_psft_sync_run
where process = 'PERSON_FULL_SYNC'
and run_date > sysdate
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'PERSON_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'PERSON_FULL_SYNC',l_current_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'PERSON_FULL_SYNC',l_current_date,errbuf,retcode);
select count('x')
from hr_psft_sync_run
where process = 'JOBCODE_FULL_SYNC'
and run_date < p_eff_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'JOBCODE_FULL_SYNC',errbuf,retcode);
SELECT BUSINESS_GROUP_ID SETID,
JOB_ID JOBCODE,
DATE_FROM EFFDT,
DECODE(DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
NAME DESCR
FROM PER_JOBS
WHERE last_update_date <= p_eff_date;
hr_full_sync_messages.update_psft_sync_run(1,'JOBCODE_FULL_SYNC',l_current_date,errbuf,retcode);
update_psft_sync_run(3,'JOBCODE_FULL_SYNC',l_current_date,errbuf,retcode);
select count('x')
from hr_psft_sync_run
where process = 'WORKFORCE_FULL_SYNC'
and run_date < p_eff_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'WORKFORCE_FULL_SYNC',errbuf,retcode);
SELECT ppf.employee_number,1 AS empl_rcd ,ppf.original_date_of_hire,
pas.probation_period,pas.effective_start_date effdt,pas.organization_id,
pas.job_id,pas.assignment_status_type_id,pas.location_id,
pas.employment_category,pas.business_group_id,pas.normal_hours,
pas.frequency,pas.grade_id,pas.supervisor_id,pas.EFFECTIVE_START_DATE,
nvl(pas.EFFECTIVE_END_DATE,sysdate) EFFECTIVE_END_DATE,
nvl(psf.step_id,0) Step_id
,pos.final_process_date,pos.ACCEPTED_TERMINATION_DATE
FROM per_all_people_f ppf,per_all_assignments_f pas,
per_periods_of_service pos,PER_SPINAL_POINT_PLACEMENTS_F psf
WHERE pas.primary_flag='Y'
AND pos.person_id=pas.person_id
AND ppf.person_id = pos.person_id
AND pas.business_group_id = psf.business_group_id(+)
AND pas.assignment_id = psf.assignment_id(+)
AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
AND pas.effective_start_date BETWEEN ppf.effective_start_date(+) AND
ppf.effective_end_date(+)
AND pas.last_update_date < = sysdate;
hr_full_sync_messages.update_psft_sync_run(1,'WORKFORCE_FULL_SYNC',l_current_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'WORKFORCE_FULL_SYNC',l_current_date,errbuf,retcode);
select org.business_group_id,
org.organization_id,
case when org.date_to is null then org.date_from
else org.date_to end,
case when org.date_to is null then 'ACTIVE'
else 'INACTIVE' end,
org.name,
org.location_id,
mgr.person_id,
mgr.full_name
from hr_all_organization_units org
,per_org_manager_v mgr,hr_organization_information hrorg
where org.business_group_id = mgr.business_group_id(+)
and org.organization_id = mgr.organization_id(+)
and hrorg.organization_id = org.organization_id
and hrorg.org_information1 = 'HR_ORG'
and p_effective_date between org.date_from
and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
and p_effective_date between mgr.start_date(+) and mgr.end_date(+);
select count('x')
from hr_psft_sync_run
where process = 'ORG_FULL_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'ORG_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'ORG_FULL_SYNC',p_effective_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'ORG_FULL_SYNC',p_effective_date,errbuf,retcode);
select business_group_id,
name,
case when date_to is null then date_from
else date_to end,
case when date_to is null then 'ACTIVE'
else 'INACTIVE' end
from PER_BUSINESS_GROUPS
where p_effective_date between date_from and
nvl (date_to, to_date('31-12-4712', 'DD-MM-YYYY'));
select count('x')
from hr_psft_sync_run
where process = 'BG_FULL_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'BG_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'BG_FULL_SYNC',p_effective_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'BG_FULL_SYNC',p_effective_date,errbuf,retcode);
select payroll_id,
payroll_name,
business_group_id,
case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
then add_months(first_period_end_date,NUMBER_OF_YEARS*12) else (select min(effective_start_date) from
pay_all_payrolls_f pay1
where pay1.payroll_id = pay.payroll_id
and pay1.business_group_id = pay.business_group_id) end,
case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
then 'INACTIVE' else 'ACTIVE' end
from pay_all_payrolls_f pay
where p_effective_date between effective_start_date and effective_end_date;
select count('x')
from hr_psft_sync_run
where process = 'PYRL_FULL_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_full_sync_messages.insert_psft_sync_run(2,'PYRL_FULL_SYNC',errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(1,'PYRL_FULL_SYNC',p_effective_date,errbuf,retcode);
hr_full_sync_messages.update_psft_sync_run(3,'PYRL_FULL_SYNC',p_effective_date,errbuf,retcode);