[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*procedure called for update job api */
procedure update_job(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end update_job;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
/*procedure called for delete job api*/
procedure delete_job(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end delete_job;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
/*procedure called for update location api*/
procedure update_location(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end update_location;
/*procedure called for delete location api*/
procedure delete_location(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end delete_location;
select 'x'
from hr_all_organization_units org
,hr_organization_information hrorg
where hrorg.organization_id = org.organization_id
and hrorg.org_information1 = 'HR_ORG'
and org.organization_id = p_org_id;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
/*procedure called for update Organization api*/
procedure update_organization(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select 'x'
from hr_all_organization_units org
,hr_organization_information hrorg
where hrorg.organization_id = org.organization_id
and hrorg.org_information1 = 'HR_ORG'
and org.organization_id = p_org_id;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end update_organization;
/*procedure called for delete Organization api*/
procedure delete_organization(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select 'x'
from hr_all_organization_units org
,hr_organization_information hrorg
where hrorg.organization_id = org.organization_id
and hrorg.org_information1 = 'HR_ORG'
and org.organization_id = p_org_id;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end delete_organization;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
/*procedure called for update_person api*/
procedure update_person(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select distinct person_id
from per_periods_of_service
where period_of_service_id = p_prd_service_id
and p_date between date_start and nvl(actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end update_person;
/*procedure called for create or update address api*/
procedure cre_or_upd_address(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
is select person_id from per_addresses
where address_id = p_addr_id
and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
/*procedure called for create or update phone api*/
procedure cre_or_upd_phone(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
is select parent_id from per_phones
where phone_id = p_phone_id
and PARENT_TABLE = 'PER_ALL_PEOPLE_F'
and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
/*procedure called for update workforce api*/
procedure update_workforce(itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out NOCOPY varchar2)
is
p_event_key varchar2(100);
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
end update_workforce;
qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
pas.business_group_id as "BUSINESS_GROUP_ID",
pas.person_id as "PERSON_ID",
pas.assignment_id as "ASSIGNMENT_ID",
pas.assignment_number as "ASSIGNMENT_NUMBER",
to_char(pas.effective_start_date,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
to_char(pas.effective_end_date,''YYYY-MM-DD'') as "EFFECTIVE_END_DARE",
pas.organization_id as "ORGANIZATION_ID",
pas.probation_period as "PROBATION_PERIOD",
pas.probation_unit as "PROBATION_UNITS",
pas.job_id as "JOB_ID",
pas.assignment_status_type_id as "ASSIGNMENT_STATUS_TYPE_ID",
pas.location_id as "LOCATION_ID",
pas.employment_category as "EMPLOYMENT_CATEGORY",
pas.normal_hours as "NORMAL_HOURS",
pas.frequency as "FREQUENCY",
pas.grade_id as "GRADE_ID",
pas.position_id as "POSITION_ID",
pas.supervisor_id as "SUPERVISOR_ID",
case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
to_char(pos.final_process_date,''YYYY-MM-DD'')
when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.final_process_date,''YYYY-MM-DD'') end as "FINAL_PROCFESS_DATE",
case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
then to_char(pos.ACTUAL_TERMINATION_DATE,''YYYY-MM-DD'')
when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.ACTUAL_TERMINATION_DATE,''YYYY-MM-DD'') end as "ACTUAL_TERMINATION_DATE"
FROM per_all_assignments_f pas,
per_periods_of_service pos,
per_periods_of_placement pop
WHERE pas.person_id = pop.person_id (+)
AND pas.person_id = pos.person_id (+)
AND pas.assignment_id = :1
AND :2 between pas.effective_start_date and pas.effective_end_date');
'SELECT ''U'' as "OPERATION_FLAG",
null as "BUSINESS_GROUP_ID",
:1 as "ORGANIZATION_ID",
to_char(trunc(sysdate),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''I'' as "EFFECTIVE_STATUS" ,
cursor(select null lang_code ,null data from dual) as "ORGANIZATIONNAME",
null as "LOCATION_ID",
null as "MANAGER_ID"
from dual
');
'SELECT :2 as "OPERATION_FLAG",
ORG.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
ORG.ORGANIZATION_ID as "ORGANIZATION_ID",
to_char(nvl(date_to,trunc(sysdate)),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''I'' as "EFFECTIVE_STATUS",
cursor(select language lang_code,name data from hr_all_organization_units_tl TL where tl.organization_id = org.organization_id) as "ORGANIZATIONNAME",
ORG.LOCATION_ID as "LOCATION_ID",
(select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
where ppf.person_id = hrorg1.ORG_INFORMATION2
and ppf.business_group_id = org.business_group_id
and hrorg1.org_information_context = ''Organization Name Alias''
and hrorg1.organization_id = org.organization_id
and nvl(org.date_to,to_date(''31/12/4712'',''DD/MM/YYYY'')) between fnd_date.canonical_to_date(hrorg1.org_information3)
and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date(''31/12/4712'',''DD/MM/YYYY''))
and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) as "MANAGER_ID"
from hr_all_organization_units org,
hr_organization_information hrorg
where hrorg.organization_id = org.organization_id
and hrorg.org_information1 = ''HR_ORG''
and org.last_update_date = (select max(last_update_date) from hr_all_organization_units where organization_id = :1)');
'SELECT :2 as "OPERATION_FLAG",
ORG.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
ORG.ORGANIZATION_ID as "ORGANIZATION_ID",
to_char(DATE_FROM,''YYYY-MM-DD'') as "EFFECTIVE_DATE" ,
''A'' as "EFFECTIVE_STATUS" ,
cursor(select language lang_code,name data from hr_all_organization_units_tl TL where tl.organization_id = org.organization_id) as "ORGANIZATIONNAME",
ORG.LOCATION_ID as "LOCATION_ID",
(select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
where ppf.person_id = hrorg1.ORG_INFORMATION2
and ppf.business_group_id = org.business_group_id
and hrorg1.org_information_context = ''Organization Name Alias''
and hrorg1.organization_id = org.organization_id
and nvl(org.date_to,to_date(''31/12/4712'',''DD/MM/YYYY'')) between fnd_date.canonical_to_date(hrorg1.org_information3)
and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date(''31/12/4712'',''DD/MM/YYYY''))
and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) as "MANAGER_ID"
from hr_all_organization_units org,
hr_organization_information hrorg
where hrorg.organization_id = org.organization_id
and hrorg.org_information1 = ''HR_ORG''
and org.last_update_date = (select max(last_update_date) from hr_all_organization_units where organization_id = :1)');
qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
ppf.business_group_id as "BUSINESS_GROUP_ID",
ppf.person_id as "PERSON_ID",
(select org_information9 from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = ''Business Group Information'') as "LEGISLATION_CODE",
EMPLOYEE_NUMBER as "EMPLOYEE_NUMBER",
APPLICANT_NUMBER as "APPLICANT_NUMBER",
NPW_NUMBER as "CWK_NUMBER",
PERSON_TYPE_ID as "PERSON_TYPE_ID",
to_char(DATE_OF_BIRTH,''YYYY-MM-DD'') as "DATE_OF_BIRTH",
TOWN_OF_BIRTH as "TOWN_OF_BIRTH",
COUNTRY_OF_BIRTH as "COUNTRY_OF_BIRTH",
to_char(DATE_OF_DEATH,''YYYY-MM-DD'') as "DATE_OF_DEATH",
to_char(ORIGINAL_DATE_OF_HIRE,''YYYY-MM-DD'') as "ORIGINAL_DATE_OF_HIRE",
to_char(EFFECTIVE_START_DATE,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
to_char(EFFECTIVE_END_DATE,''YYYY-MM-DD'') as "EFFECTIVE_END_DATE",
SEX as "SEX",
FULL_NAME as "FULL_NAME",
SUFFIX as "SUFFIX",
TITLE as "TITLE",
LAST_NAME as "LAST_NAME",
FIRST_NAME as "FIRST_NAME",
MIDDLE_NAMES as "MIDDLE_NAMES",
NATIONALITY as "NATIONALITY",
NATIONAL_IDENTIFIER as "NATIONAL_IDENTIFIER",
EMAIL_ADDRESS as "EMAIL_ADDRESS",
null as "ADDRESS_TYPE",
null as "DATE_FROM",
null as "DATE_TO",
null as "ADDRESS_STYLE",
null as "COUNTRY",
null as "ADDRESS_LINE1",
null as "ADDRESS_LINE2",
null as "ADDRESS_LINE3",
null as "TOWN_OR_CITY",
null as "TELEPHONE_NUMBER_1",
null as "REGION_1",
null as "REGION_2",
null as "POSTAL_CODE",
null as "PHONE_DATE_FROM",
null as "PHONE_DATE_TO",
null as "PHONE_TYPE",
null as "PHONE_NUMBER",
(select message_text from fnd_new_messages where message_name = ''HR_NATIONAL_ID_NUMBER_''||
(select to_char(org_information9) from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = ''Business Group Information'')
and language_code = USERENV(''LANG'') ) as "NATIONAL_IDENTIFIER_LABEL",
hr_hrhd_initial_load.hr_hrhd_encrypt(:1) as "ENCRYPTED_PERSON_ID",
hr_hrhd_initial_load.hr_hrhd_encrypt(ppf.business_group_id) as "ENCRYPTED_BUS_GRP_ID"
FROM PER_ALL_PEOPLE_F ppf
where ppf.person_id = :1
AND :2 between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date(''31/12/4712'',''DD/MM/YYYY''))');
qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
ppf.business_group_id as "BUSINESS_GROUP_ID",
ppf.person_id as "PERSON_ID",
(select org_information9 from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = ''Business Group Information'') as "LEGISLATION_CODE",
EMPLOYEE_NUMBER as "EMPLOYEE_NUMBER",
APPLICANT_NUMBER as "APPLICANT_NUMBER",
NPW_NUMBER as "CWK_NUMBER",
PERSON_TYPE_ID as "PERSON_TYPE_ID",
to_char(DATE_OF_BIRTH,''YYYY-MM-DD'') as "DATE_OF_BIRTH",
TOWN_OF_BIRTH as "TOWN_OF_BIRTH",
COUNTRY_OF_BIRTH as "COUNTRY_OF_BIRTH",
to_char(DATE_OF_DEATH,''YYYY-MM-DD'') as "DATE_OF_DEATH",
to_char(ORIGINAL_DATE_OF_HIRE,''YYYY-MM-DD'') as "ORIGINAL_DATE_OF_HIRE",
to_char(EFFECTIVE_START_DATE,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
to_char(EFFECTIVE_END_DATE,''YYYY-MM-DD'') as "EFFECTIVE_END_DATE",
SEX as "SEX",
FULL_NAME as "FULL_NAME",
SUFFIX as "SUFFIX",
TITLE as "TITLE",
LAST_NAME as "LAST_NAME",
FIRST_NAME as "FIRST_NAME",
MIDDLE_NAMES as "MIDDLE_NAMES",
NATIONALITY as "NATIONALITY",
NATIONAL_IDENTIFIER as "NATIONAL_IDENTIFIER",
EMAIL_ADDRESS as "EMAIL_ADDRESS",
Address_Type as "ADDRESS_TYPE",
To_Char(paddr.Date_From,''YYYY-MM-DD'') as "DATE_FROM",
To_Char(paddr.Date_To,''YYYY-MM-DD'') as "DATE_TO",
Style as "ADDRESS_STYLE",
Country as "COUNTRY",
Address_Line1 as "ADDRESS_LINE1",
Address_Line2 as "ADDRESS_LINE2",
Address_Line3 as "ADDRESS_LINE3",
Town_Or_City as "TOWN_OR_CITY",
Telephone_Number_1 as "TELEPHONE_NUMBER_1",
Region_1 as "REGION_1",
Region_2 as "REGION_2",
Postal_Code as "POSTAL_CODE",
null as "PHONE_DATE_FROM",
null as "PHONE_DATE_TO",
null as "PHONE_TYPE",
null as "PHONE_NUMBER",
(select message_text from fnd_new_messages where message_name = ''HR_NATIONAL_ID_NUMBER_''||
(select to_char(org_information9) from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = ''Business Group Information'')
and language_code = USERENV(''LANG'') ) as "NATIONAL_IDENTIFIER_LABEL",
hr_hrhd_initial_load.hr_hrhd_encrypt(:1) as "ENCRYPTED_PERSON_ID",
hr_hrhd_initial_load.hr_hrhd_encrypt(ppf.business_group_id) as "ENCRYPTED_BUS_GRP_ID"
FROM PER_ALL_PEOPLE_F ppf,per_addresses paddr
where ppf.person_id = :1
and ppf.person_id = paddr.person_id
and paddr.address_id = :4
AND :2 between nvl(paddr.date_from,:2) and nvl(paddr.date_to,to_date(''31/12/4712'',''DD/MM/YYYY''))
AND :2 between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date(''31/12/4712'',''DD/MM/YYYY''))');
qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
ppf.business_group_id as "BUSINESS_GROUP_ID",
ppf.person_id as "PERSON_ID",
(select org_information9 from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = ''Business Group Information'') as "LEGISLATION_CODE",
EMPLOYEE_NUMBER as "EMPLOYEE_NUMBER",
APPLICANT_NUMBER as "APPLICANT_NUMBER",
NPW_NUMBER as "CWK_NUMBER",
PERSON_TYPE_ID as "PERSON_TYPE_ID",
to_char(DATE_OF_BIRTH,''YYYY-MM-DD'') as "DATE_OF_BIRTH",
TOWN_OF_BIRTH as "TOWN_OF_BIRTH",
COUNTRY_OF_BIRTH as "COUNTRY_OF_BIRTH",
to_char(DATE_OF_DEATH,''YYYY-MM-DD'') as "DATE_OF_DEATH",
to_char(ORIGINAL_DATE_OF_HIRE,''YYYY-MM-DD'') as "ORIGINAL_DATE_OF_HIRE",
to_char(EFFECTIVE_START_DATE,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
to_char(EFFECTIVE_END_DATE,''YYYY-MM-DD'') as "EFFECTIVE_END_DATE",
SEX as "SEX",
FULL_NAME as "FULL_NAME",
SUFFIX as "SUFFIX",
TITLE as "TITLE",
LAST_NAME as "LAST_NAME",
FIRST_NAME as "FIRST_NAME",
MIDDLE_NAMES as "MIDDLE_NAMES",
NATIONALITY as "NATIONALITY",
NATIONAL_IDENTIFIER as "NATIONAL_IDENTIFIER",
EMAIL_ADDRESS as "EMAIL_ADDRESS",
null as "ADDRESS_TYPE",
null as "DATE_FROM",
null as "DATE_TO",
null as "ADDRESS_STYLE",
null as "COUNTRY",
null as "ADDRESS_LINE1",
null as "ADDRESS_LINE2",
null as "ADDRESS_LINE3",
null as "TOWN_OR_CITY",
null as "TELEPHONE_NUMBER_1",
null as "REGION_1",
null as "REGION_2",
null as "POSTAL_CODE",
to_char(ppn.date_from,''YYYY-MM-DD'') as "PHONE_DATE_FROM",
to_char(ppn.date_to,''YYYY-MM-DD'') as "PHONE_DATE_TO",
PHONE_TYPE as "PHONE_TYPE",
PHONE_NUMBER as "PHONE_NUMBER",
(select message_text from fnd_new_messages where message_name = ''HR_NATIONAL_ID_NUMBER_''||
(select to_char(org_information9) from
hr_organization_information where organization_id = ppf.business_group_id
and org_information_context = ''Business Group Information'')
and language_code = USERENV(''LANG'') ) as "NATIONAL_IDENTIFIER_LABEL",
hr_hrhd_initial_load.hr_hrhd_encrypt(:1) as "ENCRYPTED_PERSON_ID",
hr_hrhd_initial_load.hr_hrhd_encrypt(ppf.business_group_id) as "ENCRYPTED_BUS_GRP_ID"
FROM PER_ALL_PEOPLE_F ppf,per_phones ppn
where ppf.person_id = :1
and ppf.person_id = ppn.PARENT_ID
and ppn.phone_id = :5
AND PPN.PARENT_TABLE = ''PER_ALL_PEOPLE_F''
AND :2 between nvl(ppn.date_from,:2) and nvl(ppn.date_to,to_date(''31/12/4712'',''DD/MM/YYYY''))
AND :2 between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date(''31/12/4712'',''DD/MM/YYYY''))');
'SELECT ''U'' as "OPERATION_FLAG",
''*'' as "BUSINESS_GROUP_ID",
to_char(trunc(sysdate),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''I'' as "EFFECTIVE_STATUS" ,
:1 as "LOCATION_ID",
cursor(select ''*'' lang_code ,''*'' data from dual) as "LOCATIONNAME",
''*'' as "STYLE",
''*'' as "COUNTRY",
''*'' as "ADDRESS_LINE_1",
''*'' as "ADDRESS_LINE_2",
''*'' as "ADDRESS_LINE_3",
''*'' as "TOWN_OR_CITY",
''*'' as "REGION_1",
''*'' as "REGION_2",
''*'' as "REGION_3",
''*'' as "POSTAL_CODE",
''*'' as "TELEPHONE_NUMBER_1",
''*'' as "TELEPHONE_NUMBER_2",
''*'' as "TELEPHONE_NUMBER_3",
''*'' as "LOC_INFORMATION13",
''*'' as "LOC_INFORMATION14",
''*'' as "LOC_INFORMATION15",
''*'' as "LOC_INFORMATION16",
''*'' as "LOC_INFORMATION17",
''*'' as "LOC_INFORMATION18",
''*'' as "LOC_INFORMATION19",
''*'' as "LOC_INFORMATION20"
from dual
');
'SELECT :2 as "OPERATION_FLAG",
hloc.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
to_char(nvl(inactive_date,trunc(sysdate)),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''I'' as "EFFECTIVE_STATUS",
hloc.LOCATION_ID as "LOCATION_ID",
cursor(select language lang_code,location_code data from hr_locations_all_tl hltl where hltl.location_id= hloc.location_id) as "LOCATIONNAME",
STYLE as "STYLE",
COUNTRY as "COUNTRY",
ADDRESS_LINE_1 as "ADDRESS_LINE_1",
ADDRESS_LINE_2 as "ADDRESS_LINE_2",
ADDRESS_LINE_3 as "ADDRESS_LINE_3",
TOWN_OR_CITY as "TOWN_OR_CITY",
REGION_1 as "REGION_1",
REGION_2 as "REGION_2",
REGION_3 as "REGION_3",
POSTAL_CODE as "POSTAL_CODE",
TELEPHONE_NUMBER_1 as "TELEPHONE_NUMBER_1",
TELEPHONE_NUMBER_2 as "TELEPHONE_NUMBER_2",
TELEPHONE_NUMBER_3 as "TELEPHONE_NUMBER_3",
LOC_INFORMATION13 as "LOC_INFORMATION13",
LOC_INFORMATION14 as "LOC_INFORMATION14",
LOC_INFORMATION15 as "LOC_INFORMATION15",
LOC_INFORMATION16 as "LOC_INFORMATION16",
LOC_INFORMATION17 as "LOC_INFORMATION17",
LOC_INFORMATION18 as "LOC_INFORMATION18",
LOC_INFORMATION19 as "LOC_INFORMATION19",
LOC_INFORMATION20 as "LOC_INFORMATION20"
from hr_locations_all hloc
where location_id = :1
and last_update_date = (select max(last_update_date) from hr_locations_all where location_id = :1)');
'SELECT :2 as "OPERATION_FLAG",
hloc.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
hloc.LOCATION_ID as "LOCATION_ID",
to_char(hloc.creation_date,''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''A'' as "EFFECTIVE_STATUS" ,
cursor(select language lang_code,location_code data from hr_locations_all_tl hltl where hltl.location_id= hloc.location_id) as "LOCATIONNAME",
STYLE as "STYLE",
COUNTRY as "COUNTRY",
ADDRESS_LINE_1 as "ADDRESS_LINE_1",
ADDRESS_LINE_2 as "ADDRESS_LINE_2",
ADDRESS_LINE_3 as "ADDRESS_LINE_3",
TOWN_OR_CITY as "TOWN_OR_CITY",
REGION_1 as "REGION_1",
REGION_2 as "REGION_2",
REGION_3 as "REGION_3",
POSTAL_CODE as "POSTAL_CODE",
TELEPHONE_NUMBER_1 as "TELEPHONE_NUMBER_1",
TELEPHONE_NUMBER_2 as "TELEPHONE_NUMBER_2",
TELEPHONE_NUMBER_3 as "TELEPHONE_NUMBER_3",
LOC_INFORMATION13 as "LOC_INFORMATION13",
LOC_INFORMATION14 as "LOC_INFORMATION14",
LOC_INFORMATION15 as "LOC_INFORMATION15",
LOC_INFORMATION16 as "LOC_INFORMATION16",
LOC_INFORMATION17 as "LOC_INFORMATION17",
LOC_INFORMATION18 as "LOC_INFORMATION18",
LOC_INFORMATION19 as "LOC_INFORMATION19",
LOC_INFORMATION20 as "LOC_INFORMATION20"
from hr_locations_all hloc
where location_id = :1
and last_update_date = (select max(last_update_date) from hr_locations_all where location_id = :1)');
'SELECT ''U'' as "OPERATION_FLAG",
''*'' as "BUSINESS_GROUP_ID",
:1 as "JOB_ID",
to_char(trunc(sysdate),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''I'' as "EFFECTIVE_STATUS",
cursor(select ''*'' lang_code ,''*'' data from dual) as "JOBNAME"
from dual
');
'SELECT :2 as "OPERATION_FLAG",
pj.Business_group_id as "BUSINESS_GROUP_ID",
pj.job_id as "JOB_ID",
to_char(nvl(pj.date_to,trunc(sysdate)),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''I'' as "EFFECTIVE_STATUS",
cursor(select language lang_code ,name data from per_jobs_tl pjtl where pjtl.job_id=pj.job_id) as "JOBNAME"
from per_jobs pj
where job_id = :1
and last_update_date = (select max(last_update_date) from per_jobs where job_id = :1)
');
'SELECT :2 as "OPERATION_FLAG",
pj.Business_group_id as "BUSINESS_GROUP_ID",
pj.JOB_ID as "JOB_ID",
to_char(pj.date_from,''YYYY-MM-DD'') as "EFFECTIVE_DATE",
''A'' as "EFFECTIVE_STATUS",
cursor(select language lang_code ,name data from per_jobs_tl pjtl where pjtl.job_id=pj.job_id) as "JOBNAME"
from per_jobs pj
where job_id = :1
and last_update_date = (select max(last_update_date) from per_jobs where job_id = :1)
');
SELECT pas.person_id,
pas.assignment_id,
pas.assignment_number,
pas.effective_start_date,
pas.effective_end_date,
ppf.original_date_of_hire,
pas.probation_period,
pas.probation_unit,
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,
case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
to_char(pos.final_process_date,'YYYY-MM-DD')
when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.final_process_date,'YYYY-MM-DD') end ,
case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
then to_char(pos.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD')
when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD') end
FROM per_all_people_f ppf,
per_all_assignments_f pas,
per_periods_of_service pos,
per_periods_of_placement pop
WHERE pas.assignment_id = p_ass_id
AND pas.person_id = ppf.person_id
AND pas.person_id = pop.person_id (+)
AND pas.person_id = pos.person_id (+)
AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
AND p_effst_date between pas.effective_start_date and pas.effective_end_date
AND pas.effective_start_date BETWEEN ppf.effective_start_date AND
ppf.effective_end_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
SELECT
ppf.business_group_id,
EMPLOYEE_NUMBER,
APPLICANT_NUMBER,
NPW_NUMBER,
PERSON_TYPE_ID ,
DATE_OF_BIRTH,
TOWN_OF_BIRTH,
COUNTRY_OF_BIRTH,
DATE_OF_DEATH,
ORIGINAL_DATE_OF_HIRE,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
SEX,
FULL_NAME,
SUFFIX,
TITLE,
LAST_NAME,
FIRST_NAME,
MIDDLE_NAMES,
NATIONALITY,
NATIONAL_IDENTIFIER,
EMAIL_ADDRESS
FROM PER_ALL_PEOPLE_F ppf
where person_id = p_person_id
and p_eff_st_date between effective_start_date and effective_end_date;
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
Select
Address_Type,
Date_From,
Date_To,
Country,
Address_Line1,
Address_Line2,
Address_Line3,
Town_Or_City,
Telephone_Number_1,
Region_1,
Region_2,
Postal_Code
FROM per_addresses
where person_id = p_person_id
and address_id = p_address_id
and P_Eff_St_Dt between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
Select
ppn.date_from ,
ppn.date_to ,
PHONE_TYPE,
PHONE_NUMBER
FROM per_phones ppn
where ppn.PARENT_ID = P_PERSON_ID
AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
and P_Eff_St_Dt between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
select hrhd_delta_sync_seq.nextval into p_unique_key from dual;