[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.role_id, b.role_type_code
FROM jtf_rs_job_roles a,
jtf_rs_roles_b b
WHERE a.job_id = l_job_id
AND a.role_id = b.role_id ;
SELECT
ppl.employee_number employee_num
,ppl.npw_number
,ppl.full_name
,ppl.first_name
,ppl.middle_names middle_name
,ppl.last_name
,ppl.work_telephone
,ppl.email_address
,ppl.person_id
,job.job_id
,job.name job_title
,ppl.business_group_id org_id
,org.name org_name
,loc.address_line_1 address1
,loc.address_line_2 address2
,loc.address_line_3 address3
,loc.town_or_city city
,loc.country country
,loc.postal_code postal_code
,asg.location_id address_id
,asg.supervisor_id mgr_id
,emp.full_name mgr_name
,asg.assignment_id assignment_id
,asg.assignment_type
,ppl.office_number
,ppl.internal_location
,ppl.mailstop
FROM
per_all_people_f ppl
,hr_all_organization_units org
,per_assignments_f asg
,per_jobs job
,hr_locations_all loc -- Changed to hr_locations_all for performance fix bug # 4956645
-- ,per_all_people_f pep removed the table since we can get the work_telephone from ppl
,per_all_people_f emp
WHERE ppl.person_id = l_person_id
AND trunc(sysdate) between trunc(ppl.effective_start_date) and trunc(ppl.effective_end_date)
-- AND ppl.employee_number is not null
AND (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
AND ppl.business_group_id = org.organization_id
AND ppl.person_id = asg.person_id
AND asg.primary_flag = 'Y'
-- AND asg.assignment_type = 'E'
AND asg.assignment_type in ('E','C')
AND trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
AND asg.job_id = job.job_id (+)
AND asg.location_id = loc.location_id(+)
-- AND ppl.person_id = pep.person_id
-- AND trunc(sysdate) between pep.effective_start_date and pep.effective_end_date
-- AND pep.employee_number is not null
AND asg.supervisor_id = emp.person_id (+)
AND trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+);
SELECT
ppl.employee_number employee_num
,ppl.npw_number
,ppl.full_name
,ppl.first_name
,ppl.middle_names middle_name
,ppl.last_name
,ppl.work_telephone
,ppl.email_address
,ppl.person_id
,job.job_id
,job.name job_title
,ppl.business_group_id org_id
,org.name org_name
,loc.address_line_1 address1
,loc.address_line_2 address2
,loc.address_line_3 address3
,loc.town_or_city city
,loc.country country
,loc.postal_code postal_code
,asg.location_id address_id
,asg.supervisor_id mgr_id
,emp.full_name mgr_name
,asg.assignment_id assignment_id
,asg.assignment_type
,ppl.office_number
,ppl.internal_location
,ppl.mailstop
FROM
per_all_people_f ppl
,hr_all_organization_units org
,per_assignments_f asg
,per_jobs job
,hr_locations_all loc -- Changed to hr_locations_all for performance fix bug # 4956645
-- ,per_all_people_f pep removed the table since we can get the work_telephone from ppl
,per_all_people_f emp
WHERE ppl.person_id = l_person_id
-- AND trunc(sysdate) between trunc(ppl.effective_start_date) and trunc(ppl.effective_end_date)
-- Enh 3947611 2-dec-2004:changed the date check for future dated employees.
AND trunc(sysdate) <= trunc(ppl.effective_end_date)
-- AND ppl.employee_number is not null
AND (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
AND ppl.business_group_id = org.organization_id
AND ppl.person_id = asg.person_id
AND asg.primary_flag = 'Y'
-- AND asg.assignment_type = 'E'
AND asg.assignment_type in ('E','C')
-- AND trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
-- Enh 3947611 2-dec-2004: changed the date check for future dated employees.
AND trunc(sysdate) <= asg.effective_end_date
AND asg.job_id = job.job_id (+)
AND asg.location_id = loc.location_id(+)
-- AND ppl.person_id = pep.person_id
-- AND trunc(sysdate) between pep.effective_start_date and pep.effective_end_date
-- AND pep.employee_number is not null
AND asg.supervisor_id = emp.person_id (+)
-- AND trunc(sysdate) between emp.effective_start_date(+) and emp.effective_end_date(+)
-- Enh 3947611 2-dec-2004:changed the date check for future dated employees who may have future dated employees
-- as manager.
AND trunc(sysdate) <= emp.effective_end_date (+);
SELECT EFFECTIVE_START_DATE
FROM PER_all_assignments_f
WHERE person_id = l_person_id
AND ASSIGNMENT_TYPE = l_assignment_type
AND PRIMARY_FLAG = 'Y'
AND job_id = l_job_id
AND assignment_id = l_assignment_id
ORDER BY EFFECTIVE_START_DATE asc;
SELECT phone_number
FROM per_phones per
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = l_person_id
AND phone_type = 'W1'
AND date_from = (SELECT MAX(date_from)
FROM per_phones p2
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = l_person_id
AND phone_type = 'W1' );
SELECT phone_number
FROM per_phones per
WHERE parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'M'
and date_from = (SELECT MAX(date_from)
FROM per_phones p2
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = l_person_id
AND phone_type = 'M' );
SELECT phone_number
FROM per_phones per
WHERE parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'P'
and date_from = (SELECT MAX(date_from)
FROM per_phones p2
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = l_person_id
AND phone_type = 'P' );
SELECT PARTY.PARTY_NUMBER,
PARTY.PARTY_NAME,
PARTY.EMAIL_ADDRESS,
PARTY.ADDRESS1,
PARTY.ADDRESS2,
PARTY.ADDRESS3,
PARTY.ADDRESS4,
PARTY.CITY,
PARTY.POSTAL_CODE,
PARTY.STATE,
PARTY.PROVINCE,
PARTY.COUNTY,
PARTY.COUNTRY,
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
TO_NUMBER(NULL) ORG_ID,
NULL ORG_NAME,
PARTY.PERSON_FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME,
PARTY.PERSON_LAST_NAME
FROM HZ_PARTIES PARTY,
HZ_CONTACT_POINTS CT_POINT1
WHERE PARTY.PARTY_ID = l_person_id
AND PARTY.PARTY_TYPE NOT IN ('ORGANIZATION', 'GROUP')
AND CT_POINT1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+) = PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+) = 'Y'
AND CT_POINT1.STATUS (+) = 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+) = 'PHONE';
SELECT hzoc.org_contact_id CONTACT_ID
FROM hz_relationships hzr,
hz_org_contacts hzoc
WHERE hzr.party_id = l_party_id
AND hzr.directional_flag = 'F'
AND hzr.relationship_code = 'EMPLOYEE_OF'
AND hzr.subject_table_name ='HZ_PARTIES'
AND hzr.object_table_name ='HZ_PARTIES'
AND hzr.start_date <= SYSDATE
AND (hzr.end_date is null or hzr.end_date > SYSDATE)
AND hzr.status = 'A'
AND hzoc.PARTY_RELATIONSHIP_ID = hzr.relationship_id;
SELECT PARTY.PARTY_NUMBER,
PARTY.PARTY_NAME,
PARTY.EMAIL_ADDRESS,
PARTY.ADDRESS1,
PARTY.ADDRESS2,
PARTY.ADDRESS3,
PARTY.ADDRESS4,
PARTY.CITY,
PARTY.POSTAL_CODE,
PARTY.STATE,
PARTY.PROVINCE,
PARTY.COUNTY,
PARTY.COUNTRY,
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
REL.OBJECT_ID ORG_ID,
PARTY.PARTY_NAME ORG_NAME,
PARTY.PERSON_FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME,
PARTY.PERSON_LAST_NAME
FROM HZ_PARTIES PARTY,
HZ_CONTACT_POINTS CT_POINT1,
-- HZ_PARTY_RELATIONSHIPS REL
HZ_RELATIONSHIPS REL
WHERE PARTY.PARTY_ID = l_person_id
AND (
( PARTY.PARTY_TYPE = 'ORGANIZATION'
AND
PARTY.PARTY_ID = REL.SUBJECT_ID
)
OR
( PARTY.PARTY_TYPE = 'PARTY_REALTIONSHIP'
AND
PARTY.PARTY_ID = REL.PARTY_ID
)
)
-- AND REL.PARTY_RELATIONSHIP_TYPE IN ('PARTNER_OF', 'VAD_OF', 'THIRD_PARTY_OF')
--
AND REL.RELATIONSHIP_CODE IN
('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
AND PARTY.STATUS = 'A'
--
AND CT_POINT1.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+) = PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+) = 'Y'
AND CT_POINT1.STATUS (+) = 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+) = 'PHONE';
SELECT LOC.ADDRESS1,
LOC.ADDRESS2,
LOC.ADDRESS3,
LOC.ADDRESS4,
LOC.CITY,
LOC.POSTAL_CODE,
LOC.STATE,
LOC.PROVINCE,
LOC.COUNTY,
LOC.COUNTRY
FROM HZ_PARTY_SITES SITE,
HZ_LOCATIONS LOC
WHERE SITE.PARTY_SITE_ID = l_address_id
AND SITE.LOCATION_ID = LOC.LOCATION_ID;
SELECT p.segment1 ,
s.address_line1 ,
s.address_line2 ,
s.address_line3 ,
s.CITY,
s.STATE,
s.PROVINCE,
s.COUNTY,
s.COUNTRY,
c.area_code ||' '|| c.phone phone,
s.zip,
s.org_id ,
o.name ,
c.FIRST_NAME,
c.MIDDLE_NAME,
c.last_name
FROM PO_VENDORS P,
po_vendor_contacts c,
po_vendor_sites_all s,
hr_operating_units o
WHERE c.vendor_contact_id = l_person_id
and c.vendor_site_id = s.vendor_site_id
and s.vendor_id = p.vendor_id
and s.org_id = o.organization_id;
SELECT p.segment1 ,
s.address_line1 ,
s.address_line2 ,
s.address_line3 ,
s.CITY,
s.STATE,
s.PROVINCE,
s.COUNTY,
s.COUNTRY,
rel.primary_phone_area_code ||' '|| rel.primary_phone_number phone,
s.zip,
s.org_id ,
o.name ,
per.person_FIRST_NAME first_name,
per.person_MIDDLE_NAME middle_name,
per.person_last_name last_name,
per.email_address
FROM ap_suppliers P,
ap_supplier_contacts c,
ap_supplier_sites_all s,
hr_all_organization_units o,
hz_parties per,
hz_parties rel
WHERE c.vendor_contact_id = l_person_id
-- and c.vendor_site_id = s.vendor_site_id
and s.vendor_site_id = NVL(c.vendor_site_id ,s.vendor_site_id)
and s.vendor_site_id = l_vendor_site_id
and c.org_party_site_id = s.party_site_id
and per.party_id = c.per_party_id
and rel.party_id = c.rel_party_id
and s.vendor_id = p.vendor_id
and s.org_id = o.organization_id;
IF (p_imp_res_tbl(i).selected = 'Y') THEN
IF (p_imp_res_tbl(i).comment_code = 'ROLE') THEN
--if resource with same role does not exist
IF(p_imp_res_tbl(i).role_id IS NOT NULL) THEN
JTF_RS_ROLE_RELATE_PVT.CREATE_RESOURCE_ROLE_RELATE(
p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_role_resource_type => 'RS_INDIVIDUAL',
p_role_resource_id => p_imp_res_tbl(i).resource_id,
p_role_id => p_imp_res_tbl(i).role_id,
p_start_date_active => p_imp_res_tbl(i).role_start_date_active,
p_end_date_active => p_imp_res_tbl(i).role_end_date_active,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_role_relate_id => l_role_relate_id
);
SELECT jtf_rs_transaction_num_s.nextval
INTO l_transaction_number
FROM dual;
p_wh_update_date => null,
p_sales_tax_geocode => null,
p_sales_tax_inside_city_limits => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_salesrep_id => l_salesrep_id
);