[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)
AND dv.duplicate_vendor_site_id = NVL(v_dup_vendor_site_id, dv.duplicate_vendor_site_id); /* Added for bug 9501188 */
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)
AND dv.duplicate_vendor_site_id = NVL(v_dup_vendor_site_id, dv.duplicate_vendor_site_id); /* Added for bug 9501188 */
select org_id
into l_org_id
from ap_supplier_sites_all
where vendor_site_id = v_dup_vendor_site_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 count(apps.vendor_site_id)
INTO l_active_site_count
FROM ap_suppliers aps, ap_supplier_sites_all apps
WHERE aps.vendor_id = l_invoice_row.C_DUP_VENDOR_ID
AND aps.vendor_id = apps.vendor_id
AND apps.vendor_site_id <> l_invoice_row.C_DUP_VENDOR_SITE_ID
AND apps.pay_site_flag = 'Y'
AND apps.inactive_date is not null;
SELECT set_of_books_id into l_xla_ledger_id
FROM ap_system_parameters_all
WHERE org_id = l_invoice_row.C_ORG_ID
and rownum = 1;
select count(1) into l_count_xla_gt from xla_events_gt;
DELETE FROM xla_events_gt;
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
Add last update values to the ap_supplier_contacts update */
-- Bug 7297864- End
CURSOR c_contact_point (l_dup_from_party_id NUMBER, l_per_party_id NUMBER) /* bug 9604355 */
IS
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,
hz_relationships hzr,
hz_party_usg_assignments hpua,
HZ_CONTACT_POINTS hcpp
where hp.party_id = hzr.subject_id
and hzr.subject_id = l_per_party_id /* bug 9604355 */
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 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_dup_from_party_id /* :2 bug 9604355 */
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';
FOR contact_loop_c IN ( 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
FND_FILE.Put_Line(FND_FILE.Log,'Inside the loop of merge vendor_contact :'|| contact_loop_c.vendor_contact_id);
SELECT vendor_contact_id
INTO l_new_vendor_contact_id
FROM ap_supplier_contacts apc
WHERE per_party_id = l_relationship_rec.subject_id
--AND NVL(org_party_site_id, -1) = contact_loop_c.to_party_site_id;
SELECT relationship_id,
party_id
INTO l_relationship_id,l_party_id
FROM hz_relationships
WHERE subject_id = l_relationship_rec.subject_id
AND subject_type = 'PERSON'
AND object_id = l_relationship_rec.object_id
AND object_type = 'ORGANIZATION'
AND status = 'A'
AND directional_flag = 'F';
SELECT org_contact_id
INTO l_org_contact_id
FROM hz_org_contacts
WHERE party_relationship_id = l_relationship_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 party_site_id
INTO l_party_site_id
FROM hz_party_sites
WHERE location_id = l_location_id
AND party_id = l_party_id;
SELECT hz_party_site_number_s.nextval
INTO l_party_site_rec.party_site_number
FROM dual;
SELECT owner_table_id
INTO l_contact_point_rec.owner_table_id
FROM hz_contact_points
WHERE owner_table_id = l_party_id
AND owner_table_name = 'HZ_PARTIES'
AND status = 'A'
AND ROWNUM < 2;
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;
/* Bug 9559145 -- commenting below and writing new insert statement*/
--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,
-- org_contact_id = l_org_contact_id,
-- last_update_date = sysdate,
-- last_updated_by = FND_GLOBAL.USER_ID,
-- last_update_login = FND_GLOBAL.LOGIN_ID,
-- request_id = FND_GLOBAL.conc_request_id,
-- program_application_id = FND_GLOBAL.prog_appl_id,
-- program_id = FND_GLOBAL.conc_program_id
--WHERE vendor_contact_id = contact_loop_c.vendor_contact_id;
INSERT INTO AP_SUPPLIER_CONTACTS
( VENDOR_CONTACT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
VENDOR_SITE_ID,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
INACTIVE_DATE,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
PREFIX,
TITLE,
MAIL_STOP,
AREA_CODE,
PHONE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
CONTACT_NAME_ALT,
FIRST_NAME_ALT,
LAST_NAME_ALT,
DEPARTMENT,
EMAIL_ADDRESS,
URL,
ALT_AREA_CODE,
ALT_PHONE,
FAX_AREA_CODE,
FAX,
PER_PARTY_ID,
RELATIONSHIP_ID,
REL_PARTY_ID,
PARTY_SITE_ID,
ORG_CONTACT_ID,
ORG_PARTY_SITE_ID)
SELECT
PO_VENDOR_CONTACTS_S.NEXTVAL, --bug13743686
sysdate,
FND_GLOBAL.USER_ID,
VENDOR_SITE_ID,
FND_GLOBAL.LOGIN_ID,
CREATION_DATE,
CREATED_BY,
INACTIVE_DATE,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
PREFIX,
TITLE,
MAIL_STOP,
AREA_CODE,
PHONE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
PROGRAM_UPDATE_DATE,
CONTACT_NAME_ALT,
FIRST_NAME_ALT,
LAST_NAME_ALT,
DEPARTMENT,
EMAIL_ADDRESS,
URL,
ALT_AREA_CODE,
ALT_PHONE,
FAX_AREA_CODE,
FAX,
PER_PARTY_ID,
l_relationship_id,
l_party_id,
l_party_site_id,
l_org_contact_id,
p_to_party_site_id
FROM AP_SUPPLIER_CONTACTS
WHERE vendor_contact_id = contact_loop_c.vendor_contact_id;
FND_FILE.Put_Line(FND_FILE.Log,'No.of rows inserted '||SQL%ROWCOUNT);
SELECT contact_point_id
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND owner_table_id = p_from_party_site_id
) LOOP
FND_FILE.Put_Line(FND_FILE.Log,'inside party site contact points oop');
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,
200
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;