The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO X_change_status
FROM PO_HEADERS POH
WHERE (poh.po_header_id = X_po_header_id)
AND ( (poh.agent_id <> X_agent_id)
OR
(poh.agent_id is NULL AND
X_agent_id is NOT NULL)
OR (poh.agent_id is NOT NULL AND
X_agent_id is NULL)
OR (poh.vendor_site_id <> X_vendor_site_id)
OR (poh.vendor_site_id is NULL AND
X_vendor_site_id is NOT NULL)
OR (poh.vendor_site_id is NOT NULL AND
X_vendor_site_id is NULL)
OR (poh.vendor_contact_id <> X_vendor_contact_id )
OR (poh.vendor_contact_id is NULL AND
X_vendor_contact_id is NOT NULL)
OR (poh.vendor_contact_id is NOT NULL AND
X_vendor_contact_id is NULL)
OR (poh.confirming_order_flag <> X_confirming_order_flag)
OR (poh.confirming_order_flag is NULL AND
X_confirming_order_flag is NOT NULL)
OR (poh.confirming_order_flag is NOT NULL AND
X_confirming_order_flag is NULL)
OR (poh.ship_to_location_id <> X_ship_to_location_id)
OR (poh.ship_to_location_id is NULL AND
X_ship_to_location_id is NOT NULL)
OR (poh.ship_to_location_id is NOT NULL AND
X_ship_to_location_id is NULL)
OR (poh.bill_to_location_id <> X_bill_to_location_id)
OR (poh.bill_to_location_id is NULL AND
X_bill_to_location_id is NOT NULL)
OR (poh.bill_to_location_id is NOT NULL AND
X_bill_to_location_id is NULL)
OR (poh.terms_id <> X_terms_id )
OR (poh.terms_id is NULL AND
X_terms_id is NOT NULL)
OR (poh.terms_id is NOT NULL AND
X_terms_id is NULL)
OR ( poh.ship_via_lookup_code <> X_ship_via_lookup_code)
OR ( poh.ship_via_lookup_code is NOT NULL AND
X_ship_via_lookup_code is NULL)
OR ( poh.ship_via_lookup_code is NULL AND
X_ship_via_lookup_code is NOT NULL)
OR (poh.fob_lookup_code <> X_fob_lookup_code )
OR (poh.fob_lookup_code is NULL AND
X_fob_lookup_code is NOT NULL)
OR (poh.fob_lookup_code is NOT NULL AND
X_fob_lookup_code is NULL)
OR (poh.freight_terms_lookup_code <> X_freight_terms_lookup_code )
OR (poh.freight_terms_lookup_code is NULL AND
X_freight_terms_lookup_code is NOT NULL)
OR (poh.freight_terms_lookup_code is NOT NULL AND
X_freight_terms_lookup_code is NULL)
OR (poh.note_to_vendor <> X_note_to_vendor )
OR (poh.note_to_vendor is NULL AND
X_note_to_vendor is NOT NULL)
OR (poh.note_to_vendor is NOT NULL AND
X_note_to_vendor is NULL)
OR (poh.acceptance_required_flag <> X_acceptance_required_flag)
OR (poh.acceptance_required_flag is NULL AND
X_acceptance_required_flag is NOT NULL)
OR (poh.acceptance_required_flag is NOT NULL AND
X_acceptance_required_flag is NULL)
OR (poh.acceptance_due_date <> X_acceptance_due_date )
OR (poh.acceptance_due_date is NULL AND
X_acceptance_due_date is NOT NULL)
OR (poh.acceptance_due_date is NOT NULL AND
X_acceptance_due_date is NULL)
OR (poh.blanket_total_amount <> X_blanket_total_amount)
OR (poh.blanket_total_amount is NULL AND
X_blanket_total_amount is NOT NULL)
OR (poh.blanket_total_amount is NOT NULL AND
X_blanket_total_amount is NULL)
OR (poh.start_date <> X_start_date)
OR (poh.start_date is NULL AND
X_start_date is NOT NULL)
OR (poh.start_date is NOT NULL AND
X_start_date is NULL)
OR (poh.end_date <> X_end_date )
OR (poh.end_date is NULL AND
X_end_date is NOT NULL)
OR (poh.end_date is NOT NULL AND
X_end_date is NULL)
OR (poh.amount_limit <> X_amount_limit)
OR (poh.amount_limit is NULL AND
X_amount_limit is NOT NULL)
OR (poh.amount_limit is NOT NULL AND
X_amount_limit is NULL)
--
OR (poh.conterms_articles_upd_date <> p_kterms_art_upd_date)
OR (poh.conterms_articles_upd_date is NULL AND
p_kterms_art_upd_date is NOT NULL)
OR (poh.conterms_articles_upd_date is NOT NULL AND
p_kterms_art_upd_date is NULL)
OR (poh.conterms_deliv_upd_date <> p_kterms_deliv_upd_date)
OR (poh.conterms_deliv_upd_date is NULL AND
p_kterms_deliv_upd_date is NOT NULL)
OR (poh.conterms_deliv_upd_date is NOT NULL AND
p_kterms_deliv_upd_date is NULL)
--
--
OR (POH.shipping_control <> p_shipping_control)
OR (POH.shipping_control IS NULL AND
p_shipping_control IS NOT NULL)
OR ( POH.shipping_control IS NOT NULL AND
p_shipping_control IS NULL)
--
);
PROCEDURE NAME: update_children()
===========================================================================*/
/*
PROCEDURE update_children() IS
x_progress VARCHAR2(3) := NULL;
po_message_s.sql_error('update_children', x_progress, sqlcode);
END update_children; */
SELECT 'release exist prior to the effective date'
FROM po_releases
WHERE release_date < X_start_date
AND po_header_id = X_po_header_id;
SELECT 'release exist after the expiration date'
FROM po_releases
--
WHERE TRUNC(release_date) > X_end_date
AND po_header_id = X_po_header_id;
PROCEDURE NAME : update_req_link()
===========================================================================*/
PROCEDURE update_req_link(X_po_header_id IN number) is
X_progress varchar2(3) := '';
/* Update po_requisition_lines to remove the link to
the shipment if you are deleting a standard or planned PO */
-- : added update of reqs_in_pool_flag and of
-- WHO columns.
UPDATE po_requisition_lines_all
SET line_location_id = NULL
, reqs_in_pool_flag = 'Y'
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE line_location_id in (SELECT line_location_id
FROM po_line_locations_all --bug 8777237: changed po_line_locations to po_line_locations_all
WHERE po_header_id = X_po_header_id);
po_message_s.sql_error('update_req_link', X_progress, sqlcode);
END update_req_link;
SELECT POH.currency_code,
POH.type_lookup_code,
POH.revision_num,
POH.vendor_id,
POH.vendor_site_id,
POH.ship_to_location_id
FROM PO_HEADERS POH
WHERE POH.po_header_id = X_po_header_id;
select polc.displayed_field
into X_document_status
from po_lookup_codes polc
where polc.lookup_type = 'AUTHORIZATION STATUS'
and polc.lookup_code = X_lookup_code ;
select polc.displayed_field
into X_document_status
from po_lookup_codes polc
where lookup_type = 'RFQ/QUOTE STATUS'
and lookup_code = X_lookup_code;
SELECT 'F'
INTO l_invalid
FROM po_headers_all POH,
po_lines_all POL
WHERE (POL.contract_id = p_po_header_id
-- Bug # 13550798 Added below condition to apply check for GBPAs as well.
OR POL.from_header_id = p_po_header_id)
AND POL.po_header_id = POH.po_header_id
-- Bug # 13550798 Changed comparision based on approval date
/* AND TRUNC(POL.creation_date) NOT BETWEEN
NVL(TRUNC(p_start_date), POL.creation_date - 1)
AND NVL(TRUNC(p_end_date), POL.creation_date + 1) */
AND POH.approved_date IS NOT NULL
AND TRUNC(POH.approved_date) > TRUNC(p_end_date+ nvl(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0))
AND NVL(POH.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(POH.cancel_flag, 'N') <> 'Y';