[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'unique'
INTO l_dummy
FROM dual
WHERE NOT EXISTS
( SELECT 1
FROM po_distributions
WHERE line_location_id = p_line_location_id
AND distribution_num = p_distribution_num
AND (rowid <> p_rowid
OR p_rowid IS NULL));
select nvl(max(distribution_num),0)
from po_distributions
where line_location_id = x_line_location_id;
PROCEDURE NAME: select_summary()
===========================================================================*/
PROCEDURE select_summary(x_line_location_id IN OUT NOCOPY NUMBER,
x_total IN OUT NOCOPY NUMBER) IS
x_progress VARCHAR2(3) := NULL;
select nvl(sum(decode(pol.order_type_lookup_code,'RATE',pod.amount_ordered,
'FIXED PRICE',pod.amount_ordered,pod.quantity_ordered)),0)
into x_total
from po_distributions pod,
po_lines pol
where pod.po_line_id = pol.po_line_id
and pod.line_location_id = x_line_location_id;
po_message_s.sql_error('select_summary', x_progress, sqlcode);
END select_summary;
select location_code from hr_locations
where nvl(inventory_organization_id,x_ship_to_org_id) = x_ship_to_org_id
and location_id = x_deliver_to_location_id
UNION
select (substrb(rtrim(address1)||'-'||rtrim(city),1,20)) location_code from hz_locations
where location_id = x_deliver_to_location_id ;
select segment1
from pa_projects_all
where project_id = x_project_id
union
select project_number
from mrp_seiban_numbers
where project_id = x_project_id;
select task_number
from pa_tasks
where task_id = x_task_id;
select name
from HR_ORGANIZATION_UNITS --bug 3342946 (used to be pa_organizations_expend_v)
where organization_id = x_org_id;
select line_number
from okc_k_lines_b
--Bug# 1633032, togeorge 02/21/2001
-- Just line id enough to pick the line num.
-- where dnz_chr_id =x_oke_contract_header_id
-- and id = x_oke_contract_line_id;
select deliverable_num
from oke_k_deliverables_b
where k_line_id = x_oke_contract_line_id
and deliverable_id = x_oke_contract_deliverable_id;
select displayed_field
into x_dest_type
from po_destination_types_all_v
where lookup_code = x_destination_type_code;
PROCEDURE NAME: delete_distributions()
===========================================================================*/
PROCEDURE delete_distributions(x_delete_id NUMBER,
x_delete_entity VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
IF (X_delete_entity = 'RELEASE') THEN
delete po_distributions_all /*Bug6632095: using base table instead of view */
where line_location_id in
(select line_location_id
from po_line_locations_all
where po_release_id = x_delete_id);
ELSIF (X_delete_entity = 'SHIPMENT') THEN
delete po_distributions
where line_location_id = x_delete_id;
ELSIF (X_delete_entity = 'LINE') THEN
delete po_distributions
where line_location_id in
(select line_location_id
from po_line_locations
where po_line_id = x_delete_id
and shipment_type in ('STANDARD', 'PLANNED'));
ELSIF (X_delete_entity = 'HEADER') THEN
--
--We can have encumbrance distributions tied to a BPA header.
--Allow distributions with distribution_type 'AGREEMENT' to be deleted.
DELETE PO_DISTRIBUTIONS_ALL /*Bug6632095: using base table instead of view */
WHERE po_header_id = x_delete_id
AND distribution_type in ('STANDARD', 'PLANNED', 'AGREEMENT');
po_message_s.sql_error('delete_distributions', x_progress, sqlcode);
END delete_distributions;
SELECT sum(POD.quantity_ordered)
FROM po_distributions POD
WHERE POD.po_distribution_id = X_po_line_location_id;
SELECT max(POD.po_distribution_id)
INTO X_max_distribution_id
FROM po_distributions POD
WHERE POD.line_location_id = X_po_line_location_id;
select 'Y'
into l_exists
from dual
where exists
(select 'rcv transaction records'
from rcv_transactions
where po_line_location_id = p_line_location_id);
select 'Y'
into l_exists
from dual
where exists
(select 'transaction interface records'
from rcv_transactions_interface
where po_line_location_id = p_line_location_id
and transaction_status_code = 'PENDING');
select 'Y'
into l_exists
from dual
where exists
(select 'Active invoice distributions'
from ap_invoice_distributions
where po_distribution_id = p_distribution_id
and nvl(cancellation_flag,'N') <> 'Y'
and nvl(reversal_flag,'N') <> 'Y');
PROCEDURE validate_delete_distribution(p_po_distribution_id IN NUMBER
,p_line_loc_id IN NUMBER
,p_approved_date IN VARCHAR2
,p_style_disp_name IN VARCHAR2
,x_message_text OUT NOCOPY VARCHAR2) IS
l_creation_date po_distributions_all.creation_date%TYPE;
l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_distribution';
d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_DISTRIBUTIONS_SV.validate_delete_distribution';
SELECT creation_date,
req_distribution_id,
po_header_id,
nvl(quantity_delivered, 0),
nvl(amount_delivered, 0),
nvl(encumbered_flag, 'N'),
nvl(quantity_billed, 0),
nvl(amount_billed, 0)
INTO l_creation_date,
l_req_distribution_id,
l_po_header_id,
l_quantity_delivered,
l_amount_delivered,
l_encumbered_flag,
l_quantity_billed,
l_amount_billed
FROM po_distributions_all
WHERE po_distribution_id = p_po_distribution_id;
select po_distribution_id
into l_dummy
FROM po_distributions_all pod
WHERE pod.line_location_id = p_line_loc_id
AND pod.req_distribution_id = l_req_distribution_id;
x_message_text := PO_CORE_S.get_translated_text('PO_PO_DELETE_DEL_DIST_NA');
x_message_text := PO_CORE_S.get_translated_text('PO_PO_DELETE_DIST_BILLED_NA');
select po_distribution_id
into l_dummy
FROM po_distributions_all pod
WHERE pod.line_location_id = p_line_loc_id;
x_message_text := PO_CORE_S.get_translated_text('PO_CANT_DELETE_ONLY_DIST');
END validate_delete_distribution;