[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dv.vendor_id C_VENDOR_ID,
dv.vendor_site_id C_VENDOR_SITE_ID,
dv.duplicate_vendor_id C_DUP_VENDOR_ID,
dv.duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
dv.entry_id C_ENTRY_ID,
dv.org_id C_ORG_ID,
a.vendor_name C_VENDOR_NAME,
b.vendor_name C_DUP_VENDOR_NAME,
a.party_id C_PARTY_ID,
b.party_id C_DUP_PARTY_ID,
c.vendor_site_code C_VENDOR_SITE_CODE,
d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
c.party_site_id C_PARTY_SITE_ID,
d.party_site_id C_DUP_PARTY_SITE_ID,
dv.keep_site_flag C_KEEP_SITE_FLAG,
dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
a.segment1 C_NEW_VENDOR_NUMBER,
b.segment1 C_OLD_VENDOR_NUMBER
FROM ap_duplicate_vendors_all dv,
ap_suppliers a,
ap_suppliers b,
ap_supplier_sites_all c,
ap_supplier_sites_all d
WHERE dv.process_flag='S'
AND a.vendor_id=dv.vendor_id
AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
AND b.vendor_id=dv.duplicate_vendor_id
AND d.vendor_site_id=dv.duplicate_vendor_site_id
AND d.org_id = dv.org_id
AND dv.process<>'P'
-- Added for Bug 5641382
AND dv.duplicate_vendor_id = NVL(v_dup_vendor_id, dv.duplicate_vendor_id);
SELECT dv.vendor_id C_VENDOR_ID,
dv.vendor_site_id C_VENDOR_SITE_ID,
dv.duplicate_vendor_id C_DUP_VENDOR_ID,
dv.duplicate_vendor_site_id C_DUP_VENDOR_SITE_ID,
dv.entry_id C_ENTRY_ID,
dv.org_id C_ORG_ID,
a.vendor_name C_VENDOR_NAME,
b.vendor_name C_DUP_VENDOR_NAME,
a.party_id C_PARTY_ID,
b.party_id C_DUP_PARTY_ID,
c.vendor_site_code C_VENDOR_SITE_CODE,
d.vendor_site_code C_DUP_VENDOR_SITE_CODE,
c.party_site_id C_PARTY_SITE_ID,
d.party_site_id C_DUP_PARTY_SITE_ID,
dv.keep_site_flag C_KEEP_SITE_FLAG,
dv.paid_invoices_flag C_PAID_INVOICES_FLAG,
a.segment1 C_NEW_VENDOR_NUMBER,
b.segment1 C_OLD_VENDOR_NUMBER
FROM ap_duplicate_vendors_all dv,
ap_suppliers a,
ap_suppliers b,
ap_supplier_sites_all c,
ap_supplier_sites_all d
WHERE dv.process_flag in ('S','D')
AND a.vendor_id=dv.vendor_id
AND c.vendor_site_id=nvl(dv.vendor_site_id,duplicate_vendor_site_id)
AND b.vendor_id=dv.duplicate_vendor_id
AND d.vendor_site_id=dv.duplicate_vendor_site_id
AND d.org_id = dv.org_id
AND dv.process<>'I'
-- Added for Bug 5641382
AND dv.duplicate_vendor_id = NVL(v_dup_vendor_id, dv.duplicate_vendor_id);
SELECT vendor_site_id
INTO l_invoice_row.C_VENDOR_SITE_ID
FROM ap_supplier_sites_all
WHERE vendor_id = l_invoice_row.C_VENDOR_ID
AND vendor_site_code = l_invoice_row.C_VENDOR_SITE_CODE
AND org_id = l_invoice_row.C_ORG_ID; --Bug#7307532
SELECT vendor_site_id
INTO l_po_row.C_VENDOR_SITE_ID
FROM ap_supplier_sites_all
WHERE vendor_id = l_po_row.C_VENDOR_ID
AND vendor_site_code = l_po_row.C_VENDOR_SITE_CODE
AND org_id = l_po_row.C_ORG_ID; --Bug#7307532
select
hcpp.contact_point_id contact_point_id,
hcpp.owner_table_id contact_point_owner_id,
hcpp.owner_table_name contact_point_owner_name
from
hz_parties hp, fnd_user fu , hz_relationships hzr,
hz_party_usg_assignments hpua , HZ_CONTACT_POINTS hcpp
where hp.party_id = hzr.subject_id
and hzr.object_id = l_from_party_id ---:1 -- party of supplier
and hzr.relationship_type = 'CONTACT'
and hzr.relationship_code = 'CONTACT_OF'
and hzr.subject_type ='PERSON'
and hzr.object_type = 'ORGANIZATION'
and hzr.status = 'A'
and fu.person_party_id (+) = hp.party_id
and hp.party_id not in
(
select contact_party_id from pos_contact_requests pcr,
pos_supplier_mappings psm
where pcr.request_status='PENDING'
and psm.mapping_id = pcr.mapping_id
and psm.PARTY_ID = l_from_party_id --:2
and contact_party_id is not null
)
and hpua.party_id = hp.party_id
and hpua.status_flag = 'A'
and hpua.party_usage_code = 'SUPPLIER_CONTACT'
And hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
And hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
And hcpp.status (+)='A'
;
( SELECT vendor_contact_id, per_party_id, org_party_site_id, org_contact_id, party_site_id
FROM ap_supplier_contacts
WHERE org_party_site_id = p_from_party_site_id
)
LOOP
--Initialization of new relationship rec - relationship between 'To Party' and 'Contact'
l_relationship_rec.subject_id := contact_loop_c.per_party_id;
select comments, contact_number, department_code,
department, title, job_title,
decision_maker_flag, job_title_code,
reference_use_flag, rank
into l_org_contact_rec.comments, l_org_contact_rec.contact_number,
l_org_contact_rec.department_code, l_org_contact_rec.department,
l_org_contact_rec.title, l_org_contact_rec.job_title,
l_org_contact_rec.decision_maker_flag, l_org_contact_rec.job_title_code,
l_org_contact_rec.reference_use_flag, l_org_contact_rec.rank
from hz_org_contacts
where org_contact_id = contact_loop_c.org_contact_id;
select location_id
into l_location_id
from hz_party_sites
where party_site_id = contact_loop_c.party_site_id;
SELECT HZ_PARTY_SITE_NUMBER_S.Nextval
INTO l_party_site_rec.party_site_number
FROM DUAL;
select
contact_point_type,
status,
owner_table_name,
primary_flag,
orig_system_reference,
content_source_type,
contact_point_purpose,
primary_by_purpose,
edi_transaction_handling,
edi_id_number,
edi_payment_method,
edi_payment_format,
edi_remittance_method,
edi_remittance_instruction,
edi_tp_header_id,
edi_ece_tp_location_code,
email_format,
email_address,
phone_calling_calendar,
last_contact_dt_time,
timezone_id,
phone_area_code,
phone_country_code,
phone_number,
phone_extension,
phone_line_type,
telex_number,
web_type,
url,
application_id
into
l_contact_point_rec.contact_point_type,
l_contact_point_rec.status,
l_contact_point_rec.owner_table_name,
l_contact_point_rec.primary_flag,
l_contact_point_rec.orig_system_reference,
l_contact_point_rec.content_source_type,
l_contact_point_rec.contact_point_purpose,
l_contact_point_rec.primary_by_purpose,
l_edi_rec.edi_transaction_handling,
l_edi_rec.edi_id_number,
l_edi_rec.edi_payment_method,
l_edi_rec.edi_payment_format,
l_edi_rec.edi_remittance_method,
l_edi_rec.edi_remittance_instruction,
l_edi_rec.edi_tp_header_id,
l_edi_rec.edi_ece_tp_location_code,
l_email_rec.email_format,
l_email_rec.email_address,
l_phone_rec.phone_calling_calendar,
l_phone_rec.last_contact_dt_time,
l_phone_rec.timezone_id,
l_phone_rec.phone_area_code,
l_phone_rec.phone_country_code,
l_phone_rec.phone_number,
l_phone_rec.phone_extension,
l_phone_rec.phone_line_type,
l_telex_rec.telex_number,
l_web_rec.web_type,
l_web_rec.url,
l_contact_point_rec.application_id
from hz_contact_points
where contact_point_id = contact_point_loop_c.contact_point_id;
UPDATE ap_supplier_contacts
SET org_party_site_id = p_to_party_site_id,
rel_party_id = l_party_id,
relationship_id = l_relationship_id,
party_site_id = l_party_site_id
WHERE vendor_contact_id = contact_loop_c.vendor_contact_id;