[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: delete_line()
===========================================================================*/
PROCEDURE delete_line(X_type_lookup_code IN VARCHAR2,
X_po_line_id IN NUMBER,
X_row_id IN VARCHAR2,
p_skip_validation IN VARCHAR2) --)
IS
x_progress VARCHAR2(3) := '';
X_allow_delete VARCHAR2(1) := '';
x_allow_delete := 'Y';
** Get additional line information for delete verification
*/
SELECT line_num,
po_header_id
INTO X_po_line_num,
X_po_header_id
FROM po_lines pol
WHERE po_line_id = X_po_line_id;
** Verify a line can be deleted
*/
IF (X_type_lookup_code = 'RFQ') THEN
/*
** verify rfq line can be deleted.
*/
po_rfqs_sv.val_line_delete (X_po_line_id,
X_po_header_id,
X_allow_delete);
** verify quotation line can be deleted.
*/
po_quotes_sv.val_line_delete(X_po_line_id,
X_po_line_num,
X_po_header_id,
X_allow_delete);
** verify PO line can be deleted.
*/
--Bug 3453216. Added token values for the message
--'PO_PO_USE_CANCEL_ON_APRVD_PO3'. Deriving the token values here from
--X_type_lookup_code
Begin
IF X_type_lookup_code IN ('STANDARD','PLANNED') THEN
select type_name
into l_type_name
from po_document_types
where document_type_code = 'PO'
and document_subtype=X_type_lookup_code;
select type_name
into l_type_name
from po_document_types
where document_type_code = 'PA'
and document_subtype='BLANKET';
po_lines_sv.val_line_delete(X_po_line_id => X_po_line_id,
X_allow_delete => X_allow_delete,
p_token => 'DOCUMENT_TYPE',
p_token_value => l_type_name);
** If deletion is permitted, call the Lines table handler to delete row.
*/
IF (x_allow_delete = 'Y') THEN
/* call the ATTACHMENTS PKG to delete all attachments*/
fnd_attached_documents2_pkg.delete_attachments('PO_LINES',
x_po_line_id,
'', '', '', '', 'Y');
** Delete all children of the selected line.
*/
po_lines_sv.delete_children(X_type_lookup_code, X_po_line_id);
** Delete the Line.
*/
po_lines_pkg_sud.delete_row(X_row_id);
po_message_s.sql_error('delete_line', x_progress, sqlcode);
END delete_line;
PROCEDURE NAME: delete_all_lines
===========================================================================*/
PROCEDURE delete_all_lines( X_po_header_id IN NUMBER
,p_type_lookup_code IN VARCHAR2) IS
X_progress VARCHAR2(3) := '';
SELECT po_line_id
FROM po_lines_all /*Bug6632095: using base table instead of view */
WHERE po_header_id = X_po_header_id;
fnd_attached_documents2_pkg.delete_attachments('PO_LINES',
x_po_line_id,
'', '', '', '', 'Y');
PO_ATTRIBUTE_VALUES_PVT.delete_attributes_for_header
(
p_doc_type => p_type_lookup_code
, p_po_header_id => x_po_header_id
);
DELETE FROM PO_LINES_ALL /*Bug6632095: using base table instead of view */
WHERE po_header_id = X_po_header_id;
po_message_s.sql_error('delete_all_lines', X_progress, sqlcode);
END delete_all_lines;
PROCEDURE NAME: delete_children
===========================================================================*/
PROCEDURE delete_children(X_type_lookup_code IN VARCHAR2,
X_po_line_id IN NUMBER) IS
X_progress VARCHAR2(3) := '';
/* Delete Distributions for a PO */
X_progress := '010';
po_distributions_sv.delete_distributions(X_po_line_id, 'LINE');
po_shipments_sv4.delete_all_shipments(X_po_line_id, 'LINE',
X_type_lookup_code);
PO_ATTRIBUTE_VALUES_PVT.delete_attributes
(
p_doc_type => x_type_lookup_code
, p_po_line_id => x_po_line_id
);
po_message_s.sql_error('delete_children', X_progress, sqlcode);
END delete_children;
PROCEDURE NAME: test_val_line_delete()
===========================================================================*/
PROCEDURE test_val_line_delete(X_po_line_id IN NUMBER) IS
X_allow_delete VARCHAR2(1) := '';
po_lines_sv.val_line_delete(X_po_line_id, X_allow_delete);
END test_val_line_delete;
X_allow_delete IN OUT NOCOPY VARCHAR2,
p_token IN VARCHAR2,
p_token_value IN VARCHAR2,
x_message_text OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := '';
SELECT POH.type_lookup_code,
POH.approved_flag,
POH.po_header_id,
POH.approved_date,
POL.creation_date
INTO l_type_lookup_code,
l_approved_flag,
l_po_header_id,
l_approved_date,
l_line_creation_date
FROM po_headers_all POH,
po_lines_all POL
WHERE POH.po_header_id = POL.po_header_id
AND POL.po_line_id = X_po_line_id;
X_allow_delete := 'N';
X_allow_delete := 'N';
IF (X_allow_delete = 'N') THEN
x_message_text := PO_CORE_S.get_translated_text(
'PO_PO_USE_CANCEL_ON_APRVD_PO3',
p_token,
p_token_value);
SELECT MAX('N')
INTO X_allow_delete
FROM po_line_locations pll
WHERE pll.po_line_id = X_po_line_id
AND pll.approved_flag IN ('Y','R');
IF (X_allow_delete is NULL) THEN
--
-- Check to see if the Purchase Order line has encumbered shipments.
-- If it does NOT, allow deletion.
-- If it does, put the appropriate message on the stack
--
SELECT POH.type_lookup_code
INTO l_type_lookup_code
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL
WHERE POH.po_header_id = POL.po_header_id
AND POL.po_line_id = x_po_line_id;
X_allow_delete := 'Y';
x_allow_delete := 'Y';
x_allow_delete := 'N';
X_allow_delete := 'Y';
po_message_s.sql_error('val_delete', x_progress, sqlcode);
PROCEDURE NAME: val_line_delete()
===========================================================================*/
/*
** this val line delete is specific to purchase orders.
*/
PROCEDURE val_line_delete
(X_po_line_id IN NUMBER,
X_allow_delete IN OUT NOCOPY VARCHAR2,
p_token IN VARCHAR2, -- Bug 3453216
p_token_value IN VARCHAR2) -- Bug 3453216
IS
x_progress VARCHAR2(3) := '';
SELECT MAX('N')
INTO X_allow_delete
FROM po_line_locations pll
WHERE pll.po_line_id = X_po_line_id
AND pll.approved_flag IN ('Y','R');
IF (X_allow_delete is NULL) THEN
/*
** Check to see if the Purchase Order line has encumbered shipments.
** If it does NOT, allow deletion.
** If it does, display message and prevent deletion.
*/
--
-- Only check for reserved distributions for Standard and Planned PO lines.
SELECT POH.type_lookup_code
INTO l_type_lookup_code
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
WHERE POH.po_header_id = POL.po_header_id
AND POL.po_line_id = x_po_line_id
;
X_allow_delete := 'Y';
x_allow_delete := 'Y';
x_allow_delete := 'N';
X_allow_delete := 'Y';
po_message_s.sql_error('val_delete', x_progress, sqlcode);
END val_line_delete;
PROCEDURE NAME: val_update()
===========================================================================*/
PROCEDURE val_update
(X_po_line_id IN NUMBER,
X_quantity_ordered IN NUMBER) IS
X_progress VARCHAR2(3) := '';
po_message_s.sql_error('val_update', x_progress, sqlcode);
END val_update;
SELECT 'Y'
INTO X_approval_status_changed
FROM po_lines pol
WHERE pol.po_line_id = X_po_line_id
AND ((pol.unit_price <> X_unit_price)
OR (pol.unit_price is NULL
AND
X_unit_price is NOT NULL)
OR (pol.unit_price is NOT NULL
AND
X_unit_price is NULL)
OR (pol.line_num <> X_line_num)
OR (pol.line_num is NULL
AND
X_line_num IS NOT NULL)
OR (pol.line_num IS NOT NULL
AND
X_line_num IS NULL)
OR (pol.item_id <> X_item_id)
OR (pol.item_id is NULL
AND
X_item_id IS NOT NULL)
OR (pol.item_id IS NOT NULL
AND
X_item_id IS NULL)
OR (pol.item_description <> X_item_description)
OR (pol.item_description is NULL
AND
X_item_description IS NOT NULL)
OR (pol.item_description IS NOT NULL
AND
X_item_description IS NULL)
OR (pol.quantity <> X_quantity)
OR (pol.quantity is NULL
AND
X_quantity IS NOT NULL)
OR (pol.quantity IS NOT NULL
AND
X_quantity IS NULL)
OR (pol.unit_meas_lookup_code <> X_unit_meas_lookup_code)
OR (pol.unit_meas_lookup_code is NULL
AND
X_unit_meas_lookup_code IS NOT NULL)
OR (pol.unit_meas_lookup_code IS NOT NULL
AND
X_unit_meas_lookup_code IS NULL)
OR (pol.from_header_id <> X_from_header_id)
OR (pol.from_header_id is NULL
AND
X_from_header_id IS NOT NULL)
OR (pol.from_header_id IS NOT NULL
AND
X_from_header_id IS NULL)
OR (pol.from_line_id <> X_from_line_id)
OR (pol.from_line_id is NULL
AND
X_from_line_id IS NOT NULL)
OR (pol.from_line_id IS NOT NULL
AND
X_from_line_id IS NULL)
OR (pol.hazard_class_id <> X_hazard_class_id)
OR (pol.hazard_class_id is NULL
AND
X_hazard_class_id IS NOT NULL)
OR (pol.hazard_class_id IS NOT NULL
AND
X_hazard_class_id IS NULL)
--
-- Remove the check for contract_num
OR (pol.vendor_product_num <> X_vendor_product_num)
OR (pol.vendor_product_num is NULL
AND
X_vendor_product_num IS NOT NULL)
OR (pol.vendor_product_num IS NOT NULL
AND
X_vendor_product_num IS NULL)
OR (pol.un_number_id <> X_un_number_id)
OR (pol.un_number_id is NULL
AND
X_un_number_id IS NOT NULL)
OR (pol.un_number_id IS NOT NULL
AND
X_un_number_id IS NULL)
OR (pol.note_to_vendor <> X_note_to_vendor)
OR (pol.note_to_vendor is NULL
AND
X_note_to_vendor IS NOT NULL)
OR (pol.note_to_vendor IS NOT NULL
AND
X_note_to_vendor IS NULL)
OR (pol.item_revision <> X_item_revision)
OR (pol.item_revision is NULL
AND
X_item_revision IS NOT NULL)
OR (pol.item_revision IS NOT NULL
AND
X_item_revision IS NULL)
OR (pol.category_id <> X_category_id)
OR (pol.category_id is NULL
AND
X_category_id IS NOT NULL)
OR (pol.category_id IS NOT NULL
AND
X_category_id IS NULL)
OR (pol.price_type_lookup_code <> X_price_type_lookup_code)
OR (pol.price_type_lookup_code is NULL
AND
X_price_type_lookup_code IS NOT NULL)
OR (pol.price_type_lookup_code IS NOT NULL
AND
X_price_type_lookup_code IS NULL)
OR (pol.not_to_exceed_price <> X_not_to_exceed_price)
OR (pol.not_to_exceed_price is NULL
AND
X_not_to_exceed_price IS NOT NULL)
OR (pol.not_to_exceed_price IS NOT NULL
AND
X_not_to_exceed_price IS NULL)
--
OR (pol.contract_id <> p_contract_id)
OR (pol.contract_id IS NOT NULL
AND
p_contract_id IS NULL)
OR (pol.contract_id IS NULL
AND
p_contract_id IS NOT NULL)
--
--
OR (pol.contractor_first_name <> X_contractor_first_name)
OR (pol.contractor_first_name IS NOT NULL
AND
X_contractor_first_name IS NULL)
OR (pol.contractor_first_name IS NULL
AND
X_contractor_first_name IS NOT NULL)
OR (pol.contractor_last_name <> X_contractor_last_name)
OR (pol.contractor_last_name IS NOT NULL
AND
X_contractor_last_name IS NULL)
OR (pol.contractor_last_name IS NULL
AND
X_contractor_first_name IS NOT NULL)
OR (pol.start_date <> X_assignment_start_date)
OR (pol.start_date IS NOT NULL
AND
X_assignment_start_date IS NULL)
OR (pol.start_date IS NULL
AND
X_assignment_start_date IS NOT NULL)
OR (pol.expiration_date <> X_expiration_date)
OR (pol.expiration_date IS NOT NULL
AND
X_expiration_date IS NULL)
OR (pol.expiration_date IS NULL
AND
X_expiration_date IS NOT NULL)
OR (pol.amount <> X_amount_db)
OR (pol.amount IS NOT NULL
AND
X_amount_db IS NULL)
OR (pol.amount IS NULL
AND
X_amount_db IS NOT NULL)
--
);
SELECT 'Y'
INTO X_approval_status_changed
FROM po_lines pol
WHERE pol.po_line_id = X_po_line_id
AND ((pol.unit_price <> X_unit_price)
OR (pol.unit_price is NULL
AND
X_unit_price is NOT NULL)
OR (pol.unit_price is NOT NULL
AND
X_unit_price is NULL)
OR (pol.line_num <> X_line_num)
OR (pol.line_num is NULL
AND
X_line_num IS NOT NULL)
OR (pol.line_num IS NOT NULL
AND
X_line_num IS NULL)
OR (pol.item_id <> X_item_id)
OR (pol.item_id is NULL
AND
X_item_id IS NOT NULL)
OR (pol.item_id IS NOT NULL
AND
X_item_id IS NULL)
OR (pol.item_description <> X_item_description)
OR (pol.item_description is NULL
AND
X_item_description IS NOT NULL)
OR (pol.item_description IS NOT NULL
AND
X_item_description IS NULL)
OR (pol.quantity <> X_quantity)
OR (pol.quantity is NULL
AND
X_quantity IS NOT NULL)
OR (pol.quantity IS NOT NULL
AND
X_quantity IS NULL)
OR (pol.unit_meas_lookup_code <> X_unit_meas_lookup_code)
OR (pol.unit_meas_lookup_code is NULL
AND
X_unit_meas_lookup_code IS NOT NULL)
OR (pol.unit_meas_lookup_code IS NOT NULL
AND
X_unit_meas_lookup_code IS NULL)
OR (pol.from_header_id <> X_from_header_id)
OR (pol.from_header_id is NULL
AND
X_from_header_id IS NOT NULL)
OR (pol.from_header_id IS NOT NULL
AND
X_from_header_id IS NULL)
OR (pol.from_line_id <> X_from_line_id)
OR (pol.from_line_id is NULL
AND
X_from_line_id IS NOT NULL)
OR (pol.from_line_id IS NOT NULL
AND
X_from_line_id IS NULL)
OR (pol.hazard_class_id <> X_hazard_class_id)
OR (pol.hazard_class_id is NULL
AND
X_hazard_class_id IS NOT NULL)
OR (pol.hazard_class_id IS NOT NULL
AND
X_hazard_class_id IS NULL)
--
-- Remove the check for CONTRACT_NUM
OR (pol.vendor_product_num <> X_vendor_product_num)
OR (pol.vendor_product_num is NULL
AND
X_vendor_product_num IS NOT NULL)
OR (pol.vendor_product_num IS NOT NULL
AND
X_vendor_product_num IS NULL)
OR (trunc(pol.expiration_date) <> trunc(X_expiration_date))
OR (pol.expiration_date IS NULL
AND
X_expiration_date IS NOT NULL)
OR (pol.expiration_date IS NOT NULL
AND
X_expiration_date IS NULL)
OR (pol.un_number_id <> X_un_number_id)
OR (pol.un_number_id is NULL
AND
X_un_number_id IS NOT NULL)
OR (pol.un_number_id IS NOT NULL
AND
X_un_number_id IS NULL)
--
OR (pol.contract_id <> p_contract_id)
OR (pol.contract_id IS NOT NULL
AND
p_contract_id IS NULL)
OR (pol.contract_id IS NULL
AND
p_contract_id IS NOT NULL));
SELECT 'Y'
INTO X_approval_status_changed
FROM po_lines pol
WHERE pol.po_line_id = X_po_line_id
AND (
(pol.note_to_vendor <> X_note_to_vendor)
OR (pol.note_to_vendor is NULL
AND
X_note_to_vendor IS NOT NULL)
OR (pol.note_to_vendor IS NOT NULL
AND
X_note_to_vendor IS NULL)
OR (pol.item_revision <> X_item_revision)
OR (pol.item_revision is NULL
AND
X_item_revision IS NOT NULL)
OR (pol.item_revision IS NOT NULL
AND
X_item_revision IS NULL)
OR (pol.category_id <> X_category_id)
OR (pol.category_id is NULL
AND
X_category_id IS NOT NULL)
OR (pol.category_id IS NOT NULL
AND
X_category_id IS NULL)
OR (pol.price_type_lookup_code <> X_price_type_lookup_code)
OR (pol.price_type_lookup_code is NULL
AND
X_price_type_lookup_code IS NOT NULL)
OR (pol.price_type_lookup_code IS NOT NULL
AND
X_price_type_lookup_code IS NULL)
OR (pol.not_to_exceed_price <> X_not_to_exceed_price)
OR (pol.not_to_exceed_price is NULL
AND
X_not_to_exceed_price IS NOT NULL)
OR (pol.not_to_exceed_price IS NOT NULL
AND
X_not_to_exceed_price IS NULL)
OR (pol.quantity_committed <> X_quantity_committed)
OR (pol.quantity_committed is NULL
AND
X_quantity_committed IS NOT NULL)
OR (pol.quantity_committed IS NOT NULL
AND
X_quantity_committed IS NULL)
OR (pol.committed_amount <> X_committed_amount)
OR (pol.committed_amount is NULL
AND
X_committed_amount IS NOT NULL)
OR (pol.committed_amount IS NOT NULL
AND
X_committed_amount IS NULL)
--
OR (pol.amount <> X_amount_db)
OR (pol.amount IS NOT NULL
AND
X_amount_db IS NULL)
OR (pol.amount IS NULL
AND
X_amount_db IS NOT NULL)
--
);
PROCEDURE NAME: update_released_quantity()
===========================================================================*/
PROCEDURE update_released_quantity
(X_event IN VARCHAR2,
X_shipment_type IN VARCHAR2,
X_po_line_id IN NUMBER,
X_original_quantity IN NUMBER,
X_quantity IN NUMBER) IS
x_progress VARCHAR2(3) := '';
/* Bug# 3104460 - PO_LINES.QUANTITY should not be updated. */
IF (X_shipment_type = 'BLANKET') THEN
IF (X_event = 'INSERT') THEN
UPDATE PO_LINES
SET closed_code = 'OPEN'
WHERE po_line_id = X_po_line_id
-- Bug 3202973 Should not update quantity for Services lines:
AND order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE');
po_message_s.sql_error('update_released_quantity', x_progress, sqlcode);
END update_released_quantity;