[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Procedure to update the record into hr_psft_sync_run table begins*/
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;
/*Procedure to update the record into hr_psft_sync_run table ends*/
/*Procedure to insert the record into hr_psft_sync_run table begins*/
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;
/*Procedure to insert the record into hr_psft_sync_run table ends*/
/*Procedure to extract the delta synch data for country begins here*/
PROCEDURE hr_country_delta_sync(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY VARCHAR2
,p_party_site_id in NUMBER)
is
p_cntry_code fnd_territories_vl.territory_code%type;
p_last_update_date date;
select ft.territory_code,
ft.territory_short_name ,
ft.territory_code,ft.obsolete_flag,ft.row_id,ft.last_update_date
from fnd_territories_vl ft
where ft.last_update_date > p_max_run_date
and (ft.territory_code,ft.row_id) not in (select cntry.country_code,cntry.row_id
from hr_country_delta_sync cntry
where ft.territory_code = cntry.country_code
and ft.row_id = cntry.row_id
and ft.last_update_date <= cntry.last_update_date
and cntry.status in ('QUEUED','SENT'));
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select country_code,row_id,event_key from
hr_country_delta_sync
where status = 'QUEUED';
select ft.territory_code,
ft.territory_short_name ,
ft.territory_code,ft.obsolete_flag,ft.row_id,ft.last_update_date
from fnd_territories_vl ft
where territory_code = p_country_code
and row_id = p_row1_id;
select max(run_date)
from hr_psft_sync_run
where process = 'COUNTRY_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'COUNTRY_FULL_SYNC'
and status = 'COMPLETED';
select count('x')
from hr_psft_sync_run
where process = 'COUNTRY_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_delta_sync_messages.insert_psft_sync_run(2,'COUNTRY_DELTA_SYNC',errbuf,retcode);
update hr_country_delta_sync
set status = 'SENT'
where event_key = p_event_key_gen;
fetch fet_cntry_sync into p_cntry_code,p_cntry_desc,p_cntry_code,p_obs_flag,p_row_id,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_country_delta_sync(COUNTRY_CODE,COUNTRY_DESCRIPTION,ROW_ID,
COUNTRY_2CHAR,OBSOLETE_FLAG,LAST_UPDATE_DATE,STATUS,EFFECTIVE_STATUS_DATE,EVENT_KEY)
values(p_cntry_code,p_cntry_desc,p_row_id,p_cntry_code,p_obs_flag,p_last_update_date,'QUEUED',p_effective_date,
p_cntry_code||'-'||to_char(p_unique_key));
fetch fet_cntry_fsync into p_cntry_code,p_cntry_desc,p_cntry_code,p_obs_flag,p_row_id,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_country_delta_sync(COUNTRY_CODE,COUNTRY_DESCRIPTION,ROW_ID,
COUNTRY_2CHAR,OBSOLETE_FLAG,LAST_UPDATE_DATE,STATUS,EFFECTIVE_STATUS_DATE,EVENT_KEY)
values(p_cntry_code,p_cntry_desc,p_row_id,p_cntry_code,p_obs_flag,p_last_update_date,'QUEUED',p_effective_date,
p_cntry_code||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'COUNTRY_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'COUNTRY_DELTA_SYNC',p_effective_date,errbuf,retcode);
p_last_update_date date;
select max(run_date)
from hr_psft_sync_run
where process = 'STATE_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'STATE_FULL_SYNC'
and status = 'COMPLETED';
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select ft.territory_code,fcl.lookup_code,fcl.meaning,fcl.enabled_flag,fcl.last_update_date
from fnd_common_lookups fcl,fnd_territories_vl ft
where fcl.lookup_type = (ft.territory_code ||'_STATE')
and fcl.last_update_date > p_max_run_date
and (ft.territory_code ,fcl.lookup_code) not in (select state.country_code,state.state_code
from hr_state_delta_sync state
where ft.territory_code = state.country_code
and fcl.lookup_code = state.state_code
and ft.last_update_date <= state.last_update_date
and state.status in ('QUEUED','SENT'));
select country_code,state_code,event_key from
hr_state_delta_sync
where status = 'QUEUED';
select ft.territory_code,fcl.lookup_code,fcl.meaning,fcl.enabled_flag,fcl.last_update_date
from fnd_common_lookups fcl,fnd_territories_vl ft
where fcl.lookup_type = (ft.territory_code ||'_STATE')
and fcl.lookup_type = (p_country_code||'_STATE')
and fcl.lookup_code = p_stt_code;
select count('x')
from hr_psft_sync_run
where process = 'STATE_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_delta_sync_messages.insert_psft_sync_run(2,'STATE_DELTA_SYNC',errbuf,retcode);
update hr_state_delta_sync
set status = 'SENT'
where event_key = p_event_key_gen;
fetch fet_state_qsync into p_cntry_code,p_state_code,p_state_desc,p_enable_flag,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_state_delta_sync(COUNTRY_CODE,STATE_CODE,
STATE_DESCRIPTION,ENABLE_FLAG,STATUS,EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,EVENT_KEY )
values(p_cntry_code,p_state_code,p_state_desc,p_enable_flag,'QUEUED',p_effective_date,p_last_update_date,p_state_code||'-'||to_char(p_unique_key));
fetch fet_state_sync into p_cntry_code,p_state_code,p_state_desc,p_enable_flag,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_state_delta_sync(COUNTRY_CODE,STATE_CODE,
STATE_DESCRIPTION,ENABLE_FLAG,STATUS,EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,EVENT_KEY ) values(p_cntry_code,p_state_code,p_state_desc,p_enable_flag,'QUEUED',p_effective_date,p_last_update_date,p_state_code||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'STATE_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'STATE_DELTA_SYNC',p_effective_date,errbuf,retcode);
p_last_update_date date;
select count('x')
from hr_psft_sync_run
where process = 'LOC_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
select max(run_date)
from hr_psft_sync_run
where process = 'LOC_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'LOC_FULL_SYNC'
and status = 'COMPLETED';
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
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,
last_update_date
from
hr_locations_all loc
where last_update_date > p_max_run_date
and (loc.location_id,loc.business_group_id)not in(
select sync.location_id,sync.business_group_id
from hr_locn_delta_sync sync
where loc.location_id = sync.location_id
and loc.business_group_id = sync.business_group_id
and loc.last_update_date <= sync.last_update_date
and sync.status in ('QUEUED','SENT'));
select location_id,business_group_id,event_key from
hr_locn_delta_sync
where status = 'QUEUED';
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,
last_update_date
from
hr_locations_all loc
where LOC.location_id = p_location_id
and nvl(LOC.business_group_id,0) = nvl(p_business_group_id,0);
hr_delta_sync_messages.insert_psft_sync_run(2,'LOC_DELTA_SYNC',errbuf,retcode);
update hr_locn_delta_sync
set status = 'SENT'
where event_key = p_event_key_gen;
p_loc_info_19,p_loc_info_20,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_locn_delta_sync(BUSINESS_GROUP_ID,
LOCATION_ID,
EFFECTIVE_DATE,
EFFECTIVE_STATUS,
LOCATION_CODE,
LOCATION_DESCRIPTION,
LOCATION_STYLE,
COUNTRY,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
TOWN_OR_CITY,
REGION_1,
REGION_2,
REGION_3,
POSTAL_CODE,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
LOCATION_INFORMATION13,
LOCATION_INFORMATION14,
LOCATION_INFORMATION15,
LOCATION_INFORMATION16,
LOCATION_INFORMATION17,
LOCATION_INFORMATION18,
LOCATION_INFORMATION19,
LOCATION_INFORMATION20,
STATUS,
EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,
EVENT_KEY
)
values(p_bg_id,p_loc_id,p_active_date,p_effecive_status,
p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
p_tel_no_3,p_loc_info_13, p_loc_info_14,p_loc_info_15,p_loc_info_16,p_loc_info_17,p_loc_info_18,
p_loc_info_19,p_loc_info_20,'QUEUED',p_effective_date,p_last_update_date,p_loc_id||'-'||to_char(p_unique_key));
p_loc_info_19,p_loc_info_20,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_locn_delta_sync(BUSINESS_GROUP_ID,
LOCATION_ID,
EFFECTIVE_DATE,
EFFECTIVE_STATUS,
LOCATION_CODE,
LOCATION_DESCRIPTION,
LOCATION_STYLE,
COUNTRY,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
TOWN_OR_CITY,
REGION_1,
REGION_2,
REGION_3,
POSTAL_CODE,
TELEPHONE_NUMBER_1,
TELEPHONE_NUMBER_2,
TELEPHONE_NUMBER_3,
LOCATION_INFORMATION13,
LOCATION_INFORMATION14,
LOCATION_INFORMATION15,
LOCATION_INFORMATION16,
LOCATION_INFORMATION17,
LOCATION_INFORMATION18,
LOCATION_INFORMATION19,
LOCATION_INFORMATION20,
STATUS,
EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,
EVENT_KEY
) values(p_bg_id,p_loc_id,p_active_date,p_effecive_status,
p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
p_tel_no_3,p_loc_info_13, p_loc_info_14,p_loc_info_15,p_loc_info_16,p_loc_info_17,p_loc_info_18,
p_loc_info_19,p_loc_info_20,'QUEUED',p_effective_date,p_last_update_date,p_loc_id||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'LOC_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'LOC_DELTA_SYNC',p_effective_date,errbuf,retcode);
p_last_update_date date;
/*Select state ment modified for the employee number
not getting displayed for Ex-Employee*/
cursor csr_person_delta_sync (P_SYNC_DATE DATE) is
SELECT DECODE ( ppf.CURRENT_NPW_FLAG , 'Y',NPW_NUMBER,EMPLOYEE_NUMBER ) EMPLOYEE_NUMBER,
-- HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(P_SYNC_DATE , PPF.PERSON_ID) , bug 6891949
HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(ppf.last_update_date , 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,
ppf.business_group_id,
ppf.LAST_UPDATE_DATE
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 pps.last_update_date > P_SYNC_DATE
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 ppp.last_update_date > P_SYNC_DATE
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 ( (ppf.last_update_date > P_SYNC_DATE
AND sysdate BETWEEN effective_start_date AND effective_end_date )
OR (padr.last_update_date > P_SYNC_DATE) )
AND (ppf.employee_number,ppf.business_group_id)
not in (select per.employee_number,per.business_group_id
from hr_person_delta_sync per
where ppf.employee_number = per.employee_number
and ppf.business_group_id = per.business_group_id
and ppf.last_update_date = per.last_update_date
and per.status in ('QUEUED','SENT'));
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select employee_number,business_group_id,record_key from
hr_person_delta_sync
where status = 'QUEUED';
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,
ppf.business_group_id,
ppf.LAST_UPDATE_DATE
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 padr.last_update_date > P_SYNC_DATE
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 =
(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 pps.business_group_id = p_business_group_id
--AND pps.last_update_date > P_SYNC_DATE
and ACTUAL_TERMINATION_DATE is not null
))
OR ( ppf.CURRENT_NPW_FLAG = 'Y'
OR ppf.person_id =
(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 ppp.business_group_id = p_business_group_id
--AND ppp.last_update_date > P_SYNC_DATE
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 ppf.employee_number = p_employee_number
--AND ( (ppf.last_update_date > P_SYNC_DATE
AND sysdate BETWEEN effective_start_date AND effective_end_date;
select count('x')
from hr_psft_sync_run
where process = 'PERSON_DELTA_SYNC'
and run_date < sysdate
and status = 'STARTED';
select max (run_date)
from hr_psft_sync_run
where process = 'PERSON_DELTA_SYNC'
and run_date < sysdate
and status = 'COMPLETED';
select ruN_date
from hr_psft_sync_run
where process = 'PERSON_FULL_SYNC'
and status = 'COMPLETED';
hr_delta_sync_messages.insert_psft_sync_run(2,'PERSON_DELTA_SYNC',errbuf,retcode);
update hr_person_delta_sync
set status = 'SENT'
where record_key = p_event_key_gen;
l_business_group_id,p_last_update_date ;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_person_delta_sync
(EMPLOYEE_NUMBER,USER_PERSON_TYPE ,
DATE_OF_BIRTH,TOWN_OF_BIRTH,
COUNTRY_OF_BIRTH,BUSINESS_GROUP_ID,
DATE_OF_DEATH,ORIGINAL_DATE_OF_HIRE,
EFFECTIVE_START_DATE,SEX,MARITAL_STATUS ,
FULL_NAME,PRE_NAME_ADJUNCT,SUFFIX,
TITLE,LAST_NAME,FIRST_NAME ,
MIDDLE_NAMES,ADDRESS_TYPE ,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,
NATIONALITY,NATIONAL_IDENTIFIER ,STATUS,
EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,
RECORD_KEY )
values (L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,
L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH,l_business_group_id,
L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE,
L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX,
L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE,
L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER,'QUEUED',l_current_date,
p_last_update_date,L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key));
l_business_group_id,p_last_update_date ;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_person_delta_sync
(EMPLOYEE_NUMBER,USER_PERSON_TYPE ,
DATE_OF_BIRTH,TOWN_OF_BIRTH,
COUNTRY_OF_BIRTH,BUSINESS_GROUP_ID,
DATE_OF_DEATH,ORIGINAL_DATE_OF_HIRE,
EFFECTIVE_START_DATE,SEX,MARITAL_STATUS ,
FULL_NAME,PRE_NAME_ADJUNCT,SUFFIX,
TITLE,LAST_NAME,FIRST_NAME ,
MIDDLE_NAMES,ADDRESS_TYPE ,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,
NATIONALITY,NATIONAL_IDENTIFIER ,STATUS,
EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,
RECORD_KEY )
values (L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,
L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH,l_business_group_id,
L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE,
L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX,
L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE,
L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER,'QUEUED',l_current_date,
p_last_update_date,L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'PERSON_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'PERSON_DELTA_SYNC',p_effective_date,errbuf,retcode);
TYPE LSTUPDDTTYPE IS TABLE OF per_all_assignments_f.last_update_date%type INDEX BY BINARY_INTEGER;
,LAST_UPDATE_DATE LSTUPDDTTYPE
);
select max(run_date)
from hr_psft_sync_run
where process = 'WORKFORCE_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'WORKFORCE_FULL_SYNC'
and status = 'COMPLETED';
select count('x')
from hr_psft_sync_run
where process = 'WORKFORCE_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select employee_number,business_group_id,job_id,event_key,last_update_date
from HR_WORKFORCE_DELTA_SYNC
where status = 'QUEUED';
hr_delta_sync_messages.insert_psft_sync_run(2,'WORKFORCE_DELTA_SYNC',errbuf,retcode);
update HR_WORKFORCE_DELTA_SYNC
set status = 'SENT'
where event_key = p_event_key_gen;
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,pas.last_update_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 ppf.employee_number = p_emp_num
AND pas.business_group_id = p_bg_id
AND pas.job_id = p_job_id
AND pas.last_update_date >= p_lst_upd_date;
,WorkForcedeltaType.LAST_UPDATE_DATE(1);
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into HR_WORKFORCE_DELTA_SYNC
(EMPLOYEE_NUMBER,
EMPL_RCD ,
ORIGINAL_DATE_OF_HIRE,
PROBATION_PERIOD,
EFFDT,
ORGANIZATION_ID,
JOB_ID,
ASSIGNMENT_STATUS_TYPE_ID,
LOCATION_ID,
EMPLOYMENT_CATEGORY,
BUSINESS_GROUP_ID,
NORMAL_HOURS,
FREQUENCY,
GRADE_ID ,
SUPERVISOR_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
STEP_ID,
FINAL_PROCESS_DATE,
ACCEPTED_TERMINATION_DATE,
STATUS,
EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,
EVENT_KEY)
values(
WorkForceDeltaType.EMPLID(1)
,WorkForceDeltaType.EMPL_RCD(1)
,WorkForceDeltaType.ORIG_HIRE_DT(1)
,WorkForceDeltaType.PROBATION_DT(1)
,WorkForceDeltaType.EFFDT(1)
,WorkForceDeltaType.BUSINESS_UNIT(1)
,WorkForceDeltaType.JOBCODE(1)
,WorkForceDeltaType.EMPL_STATUS(1)
,WorkForceDeltaType.LOCATION(1)
,WorkForceDeltaType.FULL_PART_TIME(1)
,WorkForceDeltaType.COMPANY(1)
,WorkForceDeltaType.STD_HOURS(1)
,WorkForceDeltaType.STD_HRS_FREQUENCY(1)
,WorkForceDeltaType.GRADE(1)
,WorkForceDeltaType.SUPERVISOR_ID(1)
,WorkForceDeltaType.ASGN_START_DT(1)
,WorkForceDeltaType.ASGN_END_DT(1)
,WorkForceDeltaType.STEP(1)
,WorkForceDeltaType.TERMINATION_DT(1)
,WorkForceDeltaType.LAST_DATE_WORKED(1)
,'QUEUED'
,sysdate
,WorkForceDeltaType.LAST_UPDATE_DATE(1)
,WorkForceDeltaType.EMPLID(1)||WorkForceDeltaType.BUSINESS_UNIT(1)||
WorkForceDeltaType.JOBCODE(1)||'-'||to_char(p_unique_key));
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,pas.last_update_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 >= run_date
AND (ppf.employee_number,pas.business_group_id,pas.job_id) not in (
select wfrc.employee_number,wfrc.business_group_id,wfrc.job_id
from HR_WORKFORCE_DELTA_SYNC wfrc
where wfrc.employee_number = ppf.employee_number
and wfrc.business_group_id = pas.business_group_id
and wfrc.job_id = pas.job_id
and pas.last_update_date <= wfrc.last_update_date
and wfrc.status in ('QUEUED','SENT')) ;
,WorkForcedeltaType.LAST_UPDATE_DATE;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into HR_WORKFORCE_DELTA_SYNC
(EMPLOYEE_NUMBER,
EMPL_RCD ,
ORIGINAL_DATE_OF_HIRE,
PROBATION_PERIOD,
EFFDT,
ORGANIZATION_ID,
JOB_ID,
ASSIGNMENT_STATUS_TYPE_ID,
LOCATION_ID,
EMPLOYMENT_CATEGORY,
BUSINESS_GROUP_ID,
NORMAL_HOURS,
FREQUENCY,
GRADE_ID ,
SUPERVISOR_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
STEP_ID,
FINAL_PROCESS_DATE,
ACCEPTED_TERMINATION_DATE,
STATUS,
EFFECTIVE_STATUS_DATE,
LAST_UPDATE_DATE,
EVENT_KEY)
values(
WorkForceDeltaType.EMPLID(I)
,WorkForceDeltaType.EMPL_RCD(I)
,WorkForceDeltaType.ORIG_HIRE_DT(I)
,WorkForceDeltaType.PROBATION_DT(I)
,WorkForceDeltaType.EFFDT(I)
,WorkForceDeltaType.BUSINESS_UNIT(I)
,WorkForceDeltaType.JOBCODE(I)
,WorkForceDeltaType.EMPL_STATUS(I)
,WorkForceDeltaType.LOCATION(I)
,WorkForceDeltaType.FULL_PART_TIME(I)
,WorkForceDeltaType.COMPANY(I)
,WorkForceDeltaType.STD_HOURS(I)
,WorkForceDeltaType.STD_HRS_FREQUENCY(I)
,WorkForceDeltaType.GRADE(I)
,WorkForceDeltaType.SUPERVISOR_ID(I)
,WorkForceDeltaType.ASGN_START_DT(I)
,WorkForceDeltaType.ASGN_END_DT(I)
,WorkForceDeltaType.STEP(I)
,WorkForceDeltaType.TERMINATION_DT(I)
,WorkForceDeltaType.LAST_DATE_WORKED(I)
,'QUEUED'
,sysdate
,WorkForceDeltaType.LAST_UPDATE_DATE(I)
,WorkForceDeltaType.EMPLID(I)||WorkForceDeltaType.BUSINESS_UNIT(I)||
WorkForceDeltaType.JOBCODE(I)||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'WORKFORCE_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'WORKFORCE_DELTA_SYNC',p_effective_date,errbuf,retcode);
TYPE lstupddtType IS TABLE OF per_jobs.last_update_date%type INDEX BY BINARY_INTEGER;
select max(run_date)
from hr_psft_sync_run
where process = 'JOBCODE_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'JOBCODE_FULL_SYNC'
and status = 'COMPLETED';
select count('x')
from hr_psft_sync_run
where process = 'JOBCODE_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
select setid,jobcode,event_key,last_update_date from
HR_JOBCODE_DELTA_SYNC
where status = 'QUEUED';
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
hr_delta_sync_messages.insert_psft_sync_run(2,'JOBCODE_DELTA_SYNC',errbuf,retcode);
update HR_JOBCODE_DELTA_SYNC
set status = 'SENT'
where event_key = p_event_key_gen;
SELECT BUSINESS_GROUP_ID SETID,
JOB_ID JOBCODE,
DATE_FROM EFFDT,
DECODE(DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
NAME DESCR,
LAST_UPDATE_DATE LAST_UPD_DATE
FROM PER_JOBS
WHERE last_update_date >= p_lst_upddt
AND BUSINESS_GROUP_ID = p_set_id
AND JOB_ID = p_job_id;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into HR_JOBCODE_DELTA_SYNC(
SETID ,
JOBCODE ,
EFFDT,
EFF_STATUS ,
DESCR ,
STATUS ,
EFFECTIVE_STATUS_DATE ,
LAST_UPDATE_DATE ,
EVENT_KEY)
values(
Jobcodedeltatype.SETID(1)
,Jobcodedeltatype.JOBCODE(1)
,Jobcodedeltatype.EFFDT(1)
,Jobcodedeltatype.EFF_STATUS(1)
,Jobcodedeltatype.DESCR(1)
,'QUEUED'
,sysdate
,Jobcodedeltatype.LAST_UPD_DATE(1)
,Jobcodedeltatype.SETID(1)||Jobcodedeltatype.JOBCODE(1)||'-'||to_char(p_unique_key));
SELECT job.BUSINESS_GROUP_ID SETID,
job.JOB_ID JOBCODE,
job.DATE_FROM EFFDT,
DECODE(job.DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
job.NAME DESCR,
job.LAST_UPDATE_DATE LAST_UPD_DATE
FROM PER_JOBS job
WHERE job.last_update_date >= run_date
and (job.business_group_id,job.job_id)not in
(select setid,jobcode from HR_JOBCODE_DELTA_SYNC jbcd
where job.BUSINESS_GROUP_ID = jbcd.setid
and job.JOB_ID = jbcd.jobcode
and job.last_update_date <= jbcd.last_update_date
and jbcd.status in ('QUEUED','SENT'));
select SETID,
JOBCODE,
EFFDT,
EFF_STATUS,
DESCR
FROM HR.HR_JOBCODE_DELTA_SYNC
WHERE STATUS = 'QUEUED';*/
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into HR_JOBCODE_DELTA_SYNC(
SETID ,
JOBCODE ,
EFFDT,
EFF_STATUS ,
DESCR ,
STATUS ,
EFFECTIVE_STATUS_DATE ,
LAST_UPDATE_DATE ,
EVENT_KEY)
values(
Jobcodedeltatype.SETID(I)
,Jobcodedeltatype.JOBCODE(I)
,Jobcodedeltatype.EFFDT(I)
,Jobcodedeltatype.EFF_STATUS(I)
,Jobcodedeltatype.DESCR(I)
,'QUEUED'
,sysdate
,Jobcodedeltatype.LAST_UPD_DATE(I)
,Jobcodedeltatype.SETID(I)||Jobcodedeltatype.JOBCODE(I)||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'JOBCODE_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'JOBCODE_DELTA_SYNC',p_effective_date,errbuf,retcode);
p_last_update_date date;
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,
org.last_update_date
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 sysdate between org.date_from
and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
and sysdate between mgr.start_date(+) and mgr.end_date(+)
and org.last_update_date > p_max_run_date
and (org.business_group_id,org.organization_id) not in (select orgn.business_group_id,orgn.organization_id
from hr_organization_delta_sync orgn
where org.business_group_id = orgn.business_group_id
and org.organization_id = orgn.organization_id
and org.last_update_date <= orgn.last_update_date
and orgn.status in ('QUEUED','SENT'));
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select business_group_id,organization_id,event_key,last_update_date from
hr_organization_delta_sync
where status = 'QUEUED';
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,
org.last_update_date
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 sysdate between org.date_from
and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
and sysdate between mgr.start_date(+) and mgr.end_date(+)
and org.organization_id = p_orgn_id
and org.business_group_id = p_bgrp_id
and org.last_update_date >= p_lstupd_date ;
select max(run_date)
from hr_psft_sync_run
where process = 'ORG_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'ORG_FULL_SYNC'
and status = 'COMPLETED';
select count('x')
from hr_psft_sync_run
where process = 'ORG_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_delta_sync_messages.insert_psft_sync_run(2,'ORG_DELTA_SYNC',errbuf,retcode);
update hr_organization_delta_sync
set status = 'SENT'
where event_key = p_event_key_gen;
fetch fet_orgn_sync into p_bg_id,p_dept_id,p_eff_date,p_eff_status,p_bg_name,p_loc_id,p_person_id,p_full_name,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_organization_delta_sync(BUSINESS_GROUP_ID,
ORGANIZATION_ID,
BUSINESS_GROUP_NAME ,
EFFECTIVE_DATE,
EFFECTIVE_STATUS ,
COMPANY,
SETID_LOCATION,
LOCATION_ID,
MANAGER_ID ,
MANAGER_FULL_NAME,
LAST_UPDATE_DATE ,
STATUS,
EFFECTIVE_STATUS_DATE ,
EVENT_KEY
)
values(p_bg_id,p_dept_id,p_bg_name,p_eff_date,p_eff_status,p_bg_id,p_bg_id,p_loc_id,p_person_id,p_full_name,p_last_update_date,'QUEUED',p_effective_date,
to_char(p_dept_id)||'-'||to_char(p_unique_key));
fetch fet_orgn_fsync into p_bg_id,p_dept_id,p_eff_date,p_eff_status,p_bg_name,p_loc_id,p_person_id,p_full_name,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_organization_delta_sync(BUSINESS_GROUP_ID,
ORGANIZATION_ID,
BUSINESS_GROUP_NAME ,
EFFECTIVE_DATE,
EFFECTIVE_STATUS ,
COMPANY,
SETID_LOCATION,
LOCATION_ID,
MANAGER_ID ,
MANAGER_FULL_NAME,
LAST_UPDATE_DATE ,
STATUS,
EFFECTIVE_STATUS_DATE ,
EVENT_KEY
)
values(p_bg_id,p_dept_id,p_bg_name,p_eff_date,p_eff_status,p_bg_id,p_bg_id,p_loc_id,p_person_id,p_full_name,p_last_update_date,'QUEUED',p_effective_date,
to_char(p_dept_id)||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'ORG_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'ORG_DELTA_SYNC',p_effective_date,errbuf,retcode);
p_last_update_date date;
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,
last_update_date
from hr_all_organization_units org
where last_update_date > p_max_run_date
and org.organization_id = org.business_group_id
and (business_group_id) not in (select business_group_id
from hr_bgrp_delta_sync bg
where org.business_group_id = bg.business_group_id
and org.last_update_date <= bg.last_update_date
and bg.status in ('QUEUED','SENT'));
select business_group_id,event_key,last_update_date from
hr_bgrp_delta_sync
where status = 'QUEUED';
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,
last_update_date
from hr_all_organization_units org
where business_group_id = p_bgrp_id
and business_group_id = organization_id
and last_update_date >= p_lstupd_date;
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select max(run_date)
from hr_psft_sync_run
where process = 'BG_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'BG_FULL_SYNC'
and status = 'COMPLETED';
select count('x')
from hr_psft_sync_run
where process = 'BG_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_delta_sync_messages.insert_psft_sync_run(2,'BG_DELTA_SYNC',errbuf,retcode);
update hr_bgrp_delta_sync
set status = 'SENT'
where event_key = p_event_key_gen;
fetch fet_bg_sync into p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_bgrp_delta_sync(BUSINESS_GROUP_ID,BUSINESS_GROUP_NAME,
EFFECTIVE_DATE,EFFECTIVE_STATUS,LAST_UPDATE_DATE ,STATUS,EFFECTIVE_STATUS_DATE,
EVENT_KEY)
values(p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
to_char(p_bg_id)||'-'||to_char(p_unique_key));
fetch fet_bg_fsync into p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_bgrp_delta_sync(BUSINESS_GROUP_ID,BUSINESS_GROUP_NAME,
EFFECTIVE_DATE,EFFECTIVE_STATUS,LAST_UPDATE_DATE ,STATUS,EFFECTIVE_STATUS_DATE,
EVENT_KEY)
values(p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
to_char(p_bg_id)||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'BG_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'BG_DELTA_SYNC',p_effective_date,errbuf,retcode);
p_last_update_date date;
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,
last_update_date
from pay_all_payrolls_f pay
where last_update_date > p_max_run_date
and p_effective_date between effective_start_date and effective_end_date
and (payroll_id,business_group_id) not in (select pyrl.payroll_id,pyrl.business_group_id
from hr_pyrl_delta_sync pyrl
where pay.payroll_id = pyrl.payroll_id
and pay.business_group_id = pyrl.business_group_id
and pay.last_update_date <= pyrl.last_update_date
and pyrl.status in ('QUEUED','SENT'));
is select generation_status,generation_message
from ecx_out_process_v prcs
where document_id = p_evn_key;
select payroll_id,business_group_id,event_key,last_update_date from
hr_pyrl_delta_sync
where status = 'QUEUED';
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,
last_update_date
from pay_all_payrolls_f pay
where pay.payroll_id = p_payroll_id
and pay.business_group_id = p_bgrp_id
and p_effective_date between effective_start_date and effective_end_date
and last_update_date >= p_lstupd_date;
select max(run_date)
from hr_psft_sync_run
where process = 'PYRL_DELTA_SYNC'
and run_date < p_effective_date
and status = 'COMPLETED';
select max(run_date)
from hr_psft_sync_run
where process = 'PYRL_FULL_SYNC'
and status = 'COMPLETED';
select count('x')
from hr_psft_sync_run
where process = 'PYRL_DELTA_SYNC'
and run_date < p_effective_date
and status = 'STARTED';
hr_delta_sync_messages.insert_psft_sync_run(2,'PYRL_DELTA_SYNC',errbuf,retcode);
update hr_pyrl_delta_sync
set status = 'SENT'
where event_key = p_event_key_gen;
fetch fet_pyrl_sync into p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_pyrl_delta_sync(PAYROLL_ID,
PAYROLL_NAME,
BUSINESS_GROUP_ID ,
EFFECTIVE_DATE,
EFFECTIVE_STATUS ,
LAST_UPDATE_DATE,
STATUS ,
EFFECTIVE_STATUS_DATE,
EVENT_KEY
)
values(p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
to_char(p_pyrl_id)||'-'||to_char(p_unique_key));
fetch fet_pyrl_fsync into p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
insert into hr_pyrl_delta_sync(PAYROLL_ID,
PAYROLL_NAME,
BUSINESS_GROUP_ID ,
EFFECTIVE_DATE,
EFFECTIVE_STATUS ,
LAST_UPDATE_DATE,
STATUS ,
EFFECTIVE_STATUS_DATE,
EVENT_KEY
)
values(p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
to_char(p_pyrl_id)||'-'||to_char(p_unique_key));
hr_delta_sync_messages.update_psft_sync_run(1,'PYRL_DELTA_SYNC',p_effective_date,errbuf,retcode);
hr_delta_sync_messages.update_psft_sync_run(3,'PYRL_DELTA_SYNC',p_effective_date,errbuf,retcode);
/*common procedure to update the status of the sync data from message designer starts here*/
PROCEDURE update_delta_msg_status(p_event_key varchar2,
p_process_name varchar2)
is
begin
if p_process_name = 'STATE_DELTA_SYNCH'
then
update hr_state_delta_sync
set status = 'SENT'
where event_key = p_event_key;
update hr_country_delta_sync
set status = 'SENT'
where event_key = p_event_key;
update hr_locn_delta_sync
set status = 'SENT'
where event_key = p_event_key;
update hr_person_delta_sync
set status = 'SENT'
where record_key = p_event_key;
update HR_WORKFORCE_DELTA_SYNC
set status = 'SENT'
where event_key = p_event_key;
update HR_JOBCODE_DELTA_SYNC
set status = 'SENT'
where event_key = p_event_key;
update hr_organization_delta_sync
set status = 'SENT'
where event_key = p_event_key;
update hr_bgrp_delta_sync
set status = 'SENT'
where event_key = p_event_key;
update hr_pyrl_delta_sync
set status = 'SENT'
where event_key = p_event_key;
/*common procedure to update the status of the sync data from message designer ends here*/
end hr_delta_sync_messages;