[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_req_line_vdr_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
);
PROCEDURE update_req_temp_vdr_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
);
PROCEDURE update_fte_vdr_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
);
PROCEDURE update_okc_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
);
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_headers GA,
po_ga_org_assignments PGOA
WHERE GA.global_agreement_flag = 'Y'
AND GA.vendor_id = p_from_vendor_id
AND GA.vendor_site_id = p_from_site_id
AND GA.po_header_id = PGOA.po_header_id
AND PGOA.vendor_site_id <> p_from_site_id );
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_headers GA,
po_lines_all POL
WHERE GA.global_agreement_flag = 'Y'
AND GA.vendor_id = p_from_vendor_id
AND GA.vendor_site_id = p_from_site_id
AND POL.org_id <> GA.org_id
AND GA.po_header_id IN (POL.contract_id,
POL.from_header_id));
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_headers GA,
po_lines POL,
po_headers POH
WHERE GA.global_agreement_flag = 'Y'
AND GA.vendor_id = p_from_vendor_id
AND GA.vendor_site_id = p_from_site_id
AND GA.po_header_id IN (POL.contract_id,
POL.from_header_id)
AND POH.po_header_id = POL.po_header_id
AND POH.vendor_id = p_from_vendor_id
AND POH.vendor_site_id <> p_from_site_id);
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_headers_all GA,
po_ga_org_assignments PGOA
WHERE GA.global_agreement_flag = 'Y'
AND GA.vendor_id = p_from_vendor_id
AND GA.po_header_id = PGOA.po_header_id
AND PGOA.vendor_site_id = p_from_site_id
AND GA.vendor_site_id <> p_from_site_id);
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_headers POH,
po_lines POL,
po_headers_all GA
WHERE POH.vendor_id = p_from_vendor_id
AND POH.vendor_site_id = p_from_site_id
AND POH.po_header_id = POL.po_header_id
AND GA.po_header_id IN (POL.contract_id,
POL.from_header_id)
AND GA.global_agreement_flag = 'Y'
AND GA.vendor_id = p_from_vendor_id
AND GA.vendor_site_id <> POH.vendor_site_id);
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_requisition_lines RL,
po_headers_all GA
WHERE RL.vendor_id = p_from_vendor_id
AND RL.blanket_po_header_id = GA.po_header_id
AND GA.global_agreement_flag = 'Y'
AND GA.org_id <> RL.org_id);
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_requisition_lines_all RL,
po_headers GA
WHERE RL.vendor_id = p_from_vendor_id
AND RL.vendor_site_id = p_from_site_id
AND RL.blanket_po_header_id = GA.po_header_id
AND GA.global_agreement_flag = 'Y'
AND GA.vendor_id = p_from_vendor_id
AND GA.vendor_site_id <> p_from_site_id);
SELECT 'F'
INTO l_pass_val
FROM dual
WHERE EXISTS (
SELECT NULL
FROM po_requisition_lines_all RL,
po_headers GA
WHERE GA.vendor_id = p_from_vendor_id
AND GA.vendor_site_id = p_from_site_id
AND GA.global_agreement_flag = 'Y'
AND GA.po_header_id = RL.blanket_po_header_id
AND RL.vendor_id = p_from_vendor_id
AND RL.vendor_site_id <> p_from_site_id);
* PUBLIC PROCEDURE : update_org_assignments
*
* REQUIRES:
* validate_purge must have been successful.
* The To vendor_site_id must be defined in po_vendor_sites_all.
*
* MODIFIES:
* API Message List - any messages will be appended to the API Message List
*
* EFFECTS:
* Updates the Global Agreements' Org Assignment table - replaces every
* instance of the old Supplier/SiteName with the new vendor_site_id.
*
* RETURNS:
* x_return_status - (a) FND_API.G_RET_STS_SUCCESS if validation successful
* (b) FND_API.G_RET_STS_ERROR if error during validation
* (c) FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
*
*===========================================================================
*/
PROCEDURE update_org_assignments
(
p_api_version IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
p_from_vendor_id IN PO_VENDORS.vendor_id%TYPE,
p_from_site_id IN PO_VENDOR_SITES_ALL.vendor_site_id%TYPE,
p_to_vendor_id IN PO_VENDORS.vendor_id%TYPE,
p_to_site_id IN PO_VENDOR_SITES_ALL.vendor_site_id%TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_org_assignments';
UPDATE po_ga_org_assignments PGOA
SET PGOA.vendor_site_id = p_to_site_id,
-- Bug 3387904 START - Need to update the WHO columns:
PGOA.last_update_date = SYSDATE,
PGOA.last_updated_by = FND_GLOBAL.user_id,
PGOA.last_update_login = FND_GLOBAL.login_id
-- Bug 3387904 END
WHERE PGOA.vendor_site_id = p_from_site_id;
UPDATE po_ga_org_assignments_archive PGOA
SET PGOA.vendor_site_id = p_to_site_id,
-- Bug 3387904 START - Need to update the WHO columns:
PGOA.last_update_date = SYSDATE,
PGOA.last_updated_by = FND_GLOBAL.user_id,
PGOA.last_update_login = FND_GLOBAL.login_id
-- Bug 3387904 END
WHERE PGOA.vendor_site_id = p_from_site_id;
update_req_line_vdr_info
( p_from_vendor_id => p_from_vendor_id,
p_from_site_id => p_from_site_id,
p_to_vendor_id => p_to_vendor_id,
p_to_site_id => p_to_site_id
);
update_req_temp_vdr_info
( p_from_vendor_id => p_from_vendor_id,
p_from_site_id => p_from_site_id,
p_to_vendor_id => p_to_vendor_id,
p_to_site_id => p_to_site_id
);
update_fte_vdr_info
( p_from_vendor_id => p_from_vendor_id,
p_from_site_id => p_from_site_id,
p_to_vendor_id => p_to_vendor_id,
p_to_site_id => p_to_site_id
);
update_okc_info
( p_from_vendor_id => p_from_vendor_id,
p_from_site_id => p_from_site_id,
p_to_vendor_id => p_to_vendor_id,
p_to_site_id => p_to_site_id
);
END update_org_assignments;
SELECT PV.vendor_name,
PVS.vendor_site_code
INTO x_vendor_name,
x_vendor_site_code
FROM po_vendors PV,
po_vendor_sites_all PVS
WHERE PV.vendor_id = p_vendor_id
AND PVS.vendor_site_id = p_vendor_site_id
AND PV.vendor_id = PVS.vendor_id;
PROCEDURE update_req_line_vdr_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_req_line_vdr_info';
UPDATE po_requisition_lines_all
SET suggested_vendor_name =
(SELECT PV.vendor_name
FROM po_vendors PV
WHERE PV.vendor_id = p_to_vendor_id),
suggested_vendor_location =
(SELECT PVS.vendor_site_code
FROM po_vendor_sites PVS
WHERE PVS.vendor_site_id = p_to_site_id),
vendor_id = p_to_vendor_id,
vendor_site_id = p_to_site_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE vendor_id = p_from_vendor_id
AND vendor_site_id = p_from_site_id;
p_message => 'Updated rows: ' || SQL%ROWCOUNT
);
UPDATE po_requisition_lines_all
SET suggested_vendor_name =
(SELECT PV.vendor_name
FROM po_vendors PV
WHERE PV.vendor_id = p_to_vendor_id),
vendor_id = p_to_vendor_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE vendor_id = p_from_vendor_id
AND vendor_site_id IS NULL
AND EXISTS
(SELECT vendor_id
FROM po_vendors PV
WHERE vendor_id = p_from_vendor_id
AND NVL(PV.end_date_active, SYSDATE+1) <= SYSDATE);
p_message => 'Updated rows: ' || SQL%ROWCOUNT
);
END update_req_line_vdr_info;
PROCEDURE update_req_temp_vdr_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_req_temp_vdr_info';
UPDATE po_reqexpress_lines_all PRL
SET PRL.suggested_vendor_id = p_to_vendor_id,
PRL.suggested_vendor_site_id = p_to_site_id,
PRL.last_update_date = SYSDATE,
PRL.last_updated_by = FND_GLOBAL.user_id,
PRL.last_update_login = FND_GLOBAL.login_id
WHERE PRL.suggested_vendor_id = p_from_vendor_id
AND PRL.suggested_vendor_site_id = p_from_site_id;
p_message => 'Updated rows: ' || SQL%ROWCOUNT
);
UPDATE po_reqexpress_lines_all PRL
SET PRL.suggested_vendor_id = p_to_vendor_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE PRL.suggested_vendor_id = p_from_vendor_id
AND PRL.suggested_vendor_site_id IS NULL
AND EXISTS (
SELECT NULL
FROM po_vendors PV
WHERE PV.vendor_id = p_from_vendor_id
AND NVL(PV.end_date_active, SYSDATE + 1) <= SYSDATE);
p_message => 'Updated rows: ' || SQL%ROWCOUNT
);
END update_req_temp_vdr_info;
PROCEDURE update_fte_vdr_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_fte_vdr_info';
END update_fte_vdr_info;
PROCEDURE update_okc_info
( p_from_vendor_id IN NUMBER,
p_from_site_id IN NUMBER,
p_to_vendor_id IN NUMBER,
p_to_site_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_okc_info';
OKC_MANAGE_DELIVERABLES_GRP.updateExtPartyOnDeliverables
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_document_class => 'PO',
p_from_external_party_id => p_from_vendor_id,
p_from_external_party_site_id => p_from_site_id,
p_to_external_party_id => p_to_vendor_id,
p_to_external_party_site_id => p_to_site_id,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status
);
END update_okc_info;