The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct pad.autosource_rule_id,
pad.sequence_num,
pad.document_line_id
from po_autosource_documents pad
where pad.vendor_id = p_dup_vendor_id;
l_last_updated_by number;
l_last_updated_by := FND_GLOBAL.user_id;
UPDATE po_headers_draft_all pohd
SET pohd.vendor_id = p_vendor_id,
pohd.vendor_site_id = p_vendor_site_id,
pohd.vendor_contact_id = (SELECT vendor_contact_id
FROM ap_supplier_contacts
WHERE org_party_site_id = (SELECT party_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_vendor_site_id)
AND per_party_id = (SELECT per_party_id
FROM ap_supplier_contacts
WHERE vendor_contact_id = pohd.vendor_contact_id)),
pohd.last_updated_by = l_last_updated_by,
pohd.last_update_date = sysdate
WHERE pohd.vendor_id = p_dup_vendor_id
AND pohd.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'Exclude CLM Document'
FROM po_doc_style_headers pods
WHERE pods.style_id = pohd.style_id
AND NVL(pods.CLM_FLAG,'N') = 'Y')
AND EXISTS ( SELECT 'Exclude draft_type is not equal to MOD'
FROM po_drafts dft
WHERE dft.document_id=pohd.po_header_id
AND dft.draft_id= pohd.draft_id
AND dft.draft_type='MOD');
UPDATE PO_GA_ORG_ASSIGN_DRAFT PGOA
SET PGOA.vendor_site_id = p_vendor_site_id,
PGOA.last_update_date = SYSDATE,
PGOA.last_updated_by = l_last_updated_by,
PGOA.last_update_login = FND_GLOBAL.login_id
WHERE PGOA.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'Exclude CLM Document'
FROM po_doc_style_headers pods,
po_headers ph
WHERE pods.style_id = ph.style_id
AND ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
AND NVL(pods.CLM_FLAG,'N') = 'Y')
AND EXISTS ( SELECT 'Exclude draft_type is not equal to MOD'
FROM po_drafts dft
WHERE dft.document_id=pgoa.po_header_id
AND dft.draft_id= pgoa.draft_id
AND dft.draft_type='MOD');
UPDATE po_headers ph
SET ph.vendor_id = p_vendor_id,
ph.vendor_site_id = p_vendor_site_id,
ph.vendor_contact_id = (SELECT vendor_contact_id
FROM ap_supplier_contacts
WHERE org_party_site_id = (SELECT party_site_id
FROM ap_supplier_sites_all
WHERE vendor_site_id = p_vendor_site_id)
AND per_party_id = (SELECT per_party_id
FROM ap_supplier_contacts
WHERE vendor_contact_id = ph.vendor_contact_id)),
last_updated_by = l_last_updated_by,
last_update_date = SYSDATE
WHERE ph.vendor_id = p_dup_vendor_id
AND ph.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'CLM Document' --
FROM po_doc_style_headers pods
WHERE pods.style_id = ph.style_id
AND NVL(pods.CLM_FLAG,'N') = 'Y');
UPDATE po_rfq_vendors
SET vendor_id = p_vendor_id,
vendor_site_id = p_vendor_site_id,
last_updated_by = l_last_updated_by,
last_update_date = sysdate
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id = p_dup_vendor_site_id ;
DELETE from po_rfq_vendors prv
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id = p_dup_vendor_site_id;
UPDATE po_headers_archive pha
SET pha.vendor_id = p_vendor_id,
pha.vendor_site_id = p_vendor_site_id
WHERE pha.vendor_id = p_dup_vendor_id
AND pha.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'CLM Document' --
FROM po_doc_style_headers pods
WHERE pods.style_id = pha.style_id
AND NVL(pods.CLM_FLAG,'N') = 'Y');
PO_LOG.stmt(d_module, d_progress, 'updated PO_HEADERS_ARCHIVE');
UPDATE po_vendor_list_entries pv1
SET pv1.vendor_id = p_vendor_id,
pv1.vendor_site_id = p_vendor_site_id
WHERE pv1.vendor_id = p_dup_vendor_id
AND pv1.vendor_site_id = p_dup_vendor_site_id
AND not exists
(select vendor_id
from po_vendor_list_entries pv2
where pv2.vendor_id = p_vendor_id
and pv2.vendor_site_id = p_vendor_site_id
and pv2.vendor_list_header_id =
pv1.vendor_list_header_id);
DELETE from po_vendor_list_entries pvl
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id = p_dup_vendor_site_id ;
PO_LOG.stmt(d_module, d_progress, 'updated PO_VENDOR_LIST_ENTRIES');
UPDATE po_autosource_vendors pav1
SET pav1.vendor_id = p_vendor_id
WHERE pav1.vendor_id = p_dup_vendor_id
AND not exists
(select vendor_id
from po_autosource_vendors pav2
where pav2.vendor_id = p_vendor_id
and pav2.autosource_rule_id =
pav1.autosource_rule_id) ;
UPDATE po_autosource_vendors pav1
SET pav1.split = (SELECT sum (pav3.split)
FROM po_autosource_vendors pav3
WHERE pav3.autosource_rule_id =
pav1.autosource_rule_id
AND pav3.vendor_id IN
(p_vendor_id, p_dup_vendor_id))
WHERE pav1.vendor_id = p_vendor_id
AND exists
(select pav2.vendor_id
from po_autosource_vendors pav2
where pav2.vendor_id = p_dup_vendor_id
and pav2.autosource_rule_id =
pav1.autosource_rule_id) ;
DELETE from po_autosource_vendors pavl
WHERE vendor_id = p_dup_vendor_id ;
PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_VENDORS');
select nvl(max(sequence_num),0)
into l_max_seq_num
from po_autosource_documents
where autosource_rule_id = l_rule_id
and vendor_id = p_vendor_id;
update po_autosource_documents
set vendor_id = p_vendor_id,
sequence_num = l_new_seq_num
where autosource_rule_id = l_rule_id
and vendor_id = p_dup_vendor_id
and sequence_num = l_seq_num
and not exists
(select 'already have PAD for this rule, vendor, doc line'
from po_autosource_documents
where autosource_rule_id = l_rule_id
and vendor_id = p_vendor_id
and document_line_id = l_doc_line_id);
delete from po_autosource_documents
where vendor_id = p_dup_vendor_id;
PO_LOG.stmt(d_module, d_progress, 'updated PO_AUTOSOURCE_DOCUMENTS');
UPDATE po_ga_org_assignments PGOA
SET PGOA.vendor_site_id = p_vendor_site_id,
PGOA.last_update_date = SYSDATE,
PGOA.last_updated_by = l_last_updated_by,
PGOA.last_update_login = FND_GLOBAL.login_id
WHERE PGOA.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'CLM Document' --
FROM po_doc_style_headers pods,
po_headers ph
WHERE pods.style_id = ph.style_id
AND ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
AND NVL(pods.CLM_FLAG,'N') = 'Y');
UPDATE po_ga_org_assignments_archive PGOA
SET PGOA.vendor_site_id = p_vendor_site_id,
PGOA.last_update_date = SYSDATE,
PGOA.last_updated_by = l_last_updated_by,
PGOA.last_update_login = FND_GLOBAL.login_id
WHERE PGOA.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'CLM Document' --
FROM po_doc_style_headers pods,
po_headers ph
WHERE pods.style_id = ph.style_id
AND ph.PO_HEADER_ID= pgoa.PO_HEADER_ID
AND NVL(pods.CLM_FLAG,'N') = 'Y');
PO_LOG.stmt(d_module, d_progress, 'updated PO_GA_ORG_ASSIGNMENTS');
UPDATE PO_REQUISITION_LINES prl
SET prl.suggested_vendor_name = (select pov1.vendor_name
from po_vendors pov1
where pov1.vendor_id =
p_vendor_id),
prl.suggested_vendor_location = (select pvs1.vendor_site_code
from po_vendor_sites pvs1
where pvs1.vendor_site_id =
p_vendor_site_id)
WHERE prl.suggested_vendor_name in (select pov2.vendor_name
from po_vendors pov2
where pov2.vendor_id =
p_dup_vendor_id)
AND prl.suggested_vendor_location in (select pvs2.vendor_site_code
from po_vendor_sites pvs2
where vendor_site_id =
p_dup_vendor_site_id)
AND NOT EXISTS (SELECT 'CLM Document'
FROM Po_Requisition_Headers_All prha
WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
UPDATE po_requisition_lines prl
SET prl.vendor_id = p_vendor_id,
prl.vendor_site_id = p_vendor_site_id,
prl.last_update_date = sysdate,
prl.last_updated_by = l_last_updated_by
WHERE prl.vendor_id = p_dup_vendor_id
AND prl.vendor_site_id = p_dup_vendor_site_id
AND NOT EXISTS (SELECT 'CLM Document'
FROM Po_Requisition_Headers_All prha
WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
UPDATE po_requisition_lines prl
SET prl.vendor_id = p_vendor_id,
prl.last_update_date = sysdate,
prl.last_updated_by = l_last_updated_by
WHERE prl.vendor_id = p_dup_vendor_id
AND prl.vendor_site_id is null
AND exists
( select vendor_id
from po_vendors
where vendor_id = p_dup_vendor_id
and nvl(end_date_active, sysdate+1) <= sysdate)
AND NOT EXISTS (SELECT 'Exclude CLM Document'
FROM Po_Requisition_Headers_All prha
WHERE prha.Requisition_Header_Id = prl.Requisition_Header_Id
AND NVL(PRHA.FEDERAL_FLAG,'N')='Y');
PO_LOG.stmt(d_module, d_progress, 'updated PO_REQUISITION_LINES');
UPDATE po_reqexpress_lines_all PRL
SET PRL.suggested_vendor_id = p_vendor_id,
PRL.suggested_vendor_site_id = p_vendor_site_id,
PRL.last_update_date = SYSDATE,
PRL.last_updated_by = l_last_updated_by
WHERE PRL.suggested_vendor_id = p_dup_vendor_id
AND PRL.suggested_vendor_site_id = p_dup_vendor_site_id;
UPDATE po_reqexpress_lines_all PRL
SET PRL.suggested_vendor_id = p_vendor_id,
last_update_date = SYSDATE,
last_updated_by = l_last_updated_by
WHERE PRL.suggested_vendor_id = p_dup_vendor_id
AND PRL.suggested_vendor_site_id IS NULL
AND EXISTS (
SELECT NULL
FROM po_vendors PV
WHERE PV.vendor_id = p_dup_vendor_id
AND NVL(PV.end_date_active, SYSDATE + 1) <= SYSDATE);
PO_LOG.stmt(d_module, d_progress, 'updated PO_REQEXPRESS_LINES_ALL');
UPDATE po_approved_supplier_list poasl1
SET poasl1.vendor_id = p_vendor_id,
poasl1.vendor_site_id = p_vendor_site_id
WHERE poasl1.vendor_id = p_dup_vendor_id
AND poasl1.vendor_site_id = p_dup_vendor_site_id
AND not exists
( select vendor_id
from po_approved_supplier_list poasl2
where poasl2.vendor_id = p_vendor_id
and poasl2.vendor_site_id = p_vendor_site_id
and nvl(poasl2.item_id, -99) =
nvl(poasl1.item_id, -99)
and nvl(poasl2.category_id, -99) =
nvl(poasl1.category_id, -99)
and poasl2.using_organization_id =
poasl1.using_organization_id) ;
DELETE from po_approved_supplier_list poasl
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id = p_dup_vendor_site_id ;
UPDATE po_approved_supplier_list poasl1
SET poasl1.vendor_id = p_vendor_id
WHERE poasl1.vendor_id = p_dup_vendor_id
AND poasl1.vendor_site_id is null
AND exists
( select vendor_id
from po_vendors
where vendor_id = p_dup_vendor_id
and nvl(end_date_active, sysdate+1) <= sysdate)
AND not exists
( select vendor_id
from po_approved_supplier_list poasl2
where poasl2.vendor_id = p_vendor_id
and poasl2.vendor_site_id is null
and nvl(poasl2.item_id, -99) =
nvl(poasl1.item_id, -99)
and nvl(poasl2.category_id, -99) =
nvl(poasl1.category_id, -99)
and poasl2.using_organization_id =
poasl1.using_organization_id);
DELETE from po_approved_supplier_list poasl
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id is null
AND exists
( select vendor_id
from po_vendors
where vendor_id = p_dup_vendor_id
and nvl(end_date_active, sysdate+1) <= sysdate);
PO_LOG.stmt(d_module, d_progress, 'updated PO_APPROVED_SUPPLIER_LIST');
UPDATE po_asl_attributes poasl1
SET poasl1.vendor_id = p_vendor_id,
poasl1.vendor_site_id = p_vendor_site_id
WHERE poasl1.vendor_id = p_dup_vendor_id
AND poasl1.vendor_site_id = p_dup_vendor_site_id
AND not exists
(select vendor_id
from po_asl_attributes poasl2
where poasl2.vendor_id = p_vendor_id
and poasl2.vendor_site_id = p_vendor_site_id
and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
and poasl2.using_organization_id =
poasl1.using_organization_id);
DELETE from po_asl_attributes poasl
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id = p_dup_vendor_site_id ;
UPDATE po_asl_attributes poasl1
SET poasl1.vendor_id = p_vendor_id
WHERE poasl1.vendor_id = p_dup_vendor_id
AND poasl1.vendor_site_id is null
AND exists
( select vendor_id
from po_vendors
where vendor_id = p_dup_vendor_id
and nvl(end_date_active, sysdate+1) <= sysdate)
AND not exists
(select vendor_id
from po_asl_attributes poasl2
where poasl2.vendor_id = p_vendor_id
and poasl2.vendor_site_id is null
and nvl(poasl2.item_id, -99) = nvl(poasl1.item_id, -99)
and nvl(poasl2.category_id, -99) = nvl(poasl1.category_id, -99)
and poasl2.using_organization_id =
poasl1.using_organization_id);
DELETE from po_asl_attributes poasl
WHERE vendor_id = p_dup_vendor_id
AND vendor_site_id is null
AND exists
( select vendor_id
from po_vendors
where vendor_id = p_dup_vendor_id
and nvl(end_date_active, sysdate+1) <= sysdate) ;
PO_LOG.stmt(d_module, d_progress, 'updated PO_ASL_ATTRIBUTES');
FND_MESSAGE.SET_TOKEN('ROWS_DELETED',l_row_count);