[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update po_headers
set submit_date = NULL
where po_header_id = p_doc_id;
update po_releases
set submit_date = NULL
where po_release_id = p_doc_id;
SELECT 'N'
INTO l_keep_summary
FROM dual
WHERE exists (SELECT 'approved document'
FROM po_headers
WHERE po_header_id = p_doc_id
AND NVL(approved_flag, 'N') IN ('R', 'Y'));
Algr: Selects the revision number of the po_header and the latest
archived version (when it exists) and compares them.
If current revision_num = latest revision_num
return FALSE
Else
return TRUE
In case of a sql error need_to_check will be FALSE
Referenced by :
parameters : p_doc_Id IN NUMBER - Document Id.
CHANGE History: Created 30-Sep-2002 pparthas
*******************************************************************/
FUNCTION PO_Archive_Check(p_doc_id IN NUMBER)
RETURN BOOLEAN IS
l_revision_num po_headers_all.revision_num%type;
SELECT POH.revision_num, nvl(POHA.revision_num, -1)
into l_revision_num, l_archived_number
FROM PO_HEADERS POH,
PO_HEADERS_ARCHIVE POHA
WHERE POH.po_header_id = p_doc_id
AND POH.po_header_id = POHA.po_header_id (+)
AND POHA.latest_external_flag (+) = 'Y';
Algr: Selects the revision number of the po_header and the latest
archived version (when it exists) and compares them.
If current revision_num <> latest revision_num
return FALSE
Else
return TRUE
In case of a sql error need_to_check will be FALSE
Referenced by :
parameters : p_doc_Id IN NUMBER - Document Id.
CHANGE History: Created 30-Sep-2002 pparthas
*******************************************************************/
FUNCTION Release_Archive_Check(p_doc_id IN NUMBER)
RETURN BOOLEAN IS
l_revision_num po_headers_all.revision_num%type;
SELECT POR.revision_num, nvl(PORA.revision_num, -1)
INTO l_revision_num, l_archived_number
FROM PO_RELEASES POR,
PO_RELEASES_ARCHIVE PORA
WHERE POR.po_release_id = p_doc_id
AND POR.po_release_id = PORA.po_release_id (+)
AND PORA.latest_external_flag (+) = 'Y';
Select pav.accepted_flag
into l_accepted_flag
from po_acceptances_v pav,
po_headers poh
where poh.po_header_id=p_doc_id
and poh.po_header_id=pav.po_header_id
and pav.revision_num= poh.revision_num
and poh.acceptance_required_flag='N'
and rownum=1;
Mass update buyer program is run before to update buyer name.
Hence donot use the agent_id comparision for cancel flow*/
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_HEADERS POH,
PO_HEADERS_ARCHIVE POHA
WHERE POH.po_header_id = p_doc_id
AND POH.po_header_id = POHA.po_header_id (+)
AND POHA.latest_external_flag (+) = 'Y'
AND (
( POHA.po_header_id IS NULL)
OR ( (POH.agent_id <> POHA.agent_id) AND (p_chk_cancel_flag='Y'))
OR (POH.vendor_site_id <> POHA.vendor_site_id)
OR (POH.vendor_site_id IS NULL
AND POHA.vendor_site_id IS NOT NULL)
OR (POH.vendor_site_id IS NOT NULL
AND POHA.vendor_site_id IS NULL)
OR (POH.vendor_contact_id <> POHA.vendor_contact_id)
OR (POH.vendor_contact_id IS NULL
AND POHA.vendor_contact_id IS NOT NULL)
OR (POH.vendor_contact_id IS NOT NULL
AND POHA.vendor_contact_id IS NULL)
OR (POH.ship_to_location_id <> POHA.ship_to_location_id)
OR (POH.ship_to_location_id IS NULL
AND POHA.ship_to_location_id IS NOT NULL)
OR (POH.ship_to_location_id IS NOT NULL
AND POHA.ship_to_location_id IS NULL)
OR (POH.bill_to_location_id <> POHA.bill_to_location_id)
OR (POH.bill_to_location_id IS NULL
AND POHA.bill_to_location_id IS NOT NULL)
OR (POH.bill_to_location_id IS NOT NULL
AND POHA.bill_to_location_id IS NULL)
OR (POH.terms_id <> POHA.terms_id)
OR (POH.terms_id IS NULL
AND POHA.terms_id IS NOT NULL)
OR (POH.terms_id IS NOT NULL
AND POHA.terms_id IS NULL)
OR (POH.ship_via_lookup_code <>
POHA.ship_via_lookup_code)
OR (POH.ship_via_lookup_code IS NULL
AND POHA.ship_via_lookup_code IS NOT NULL)
OR (POH.ship_via_lookup_code IS NOT NULL
AND POHA.ship_via_lookup_code IS NULL)
OR (POH.fob_lookup_code <> POHA.fob_lookup_code)
OR (POH.fob_lookup_code IS NULL
AND POHA.fob_lookup_code IS NOT NULL)
OR (POH.fob_lookup_code IS NOT NULL
AND POHA.fob_lookup_code IS NULL)
OR (POH.freight_terms_lookup_code <>
POHA.freight_terms_lookup_code)
OR (POH.freight_terms_lookup_code IS NULL
AND POHA.freight_terms_lookup_code IS NOT NULL)
OR (POH.freight_terms_lookup_code IS NOT NULL
AND POHA.freight_terms_lookup_code IS NULL)
--
OR (POH.shipping_control <>
POHA.shipping_control)
OR (POH.shipping_control IS NULL
AND POHA.shipping_control IS NOT NULL)
OR (POH.shipping_control IS NOT NULL
AND POHA.shipping_control IS NULL)
--
OR (POH.blanket_total_amount <>
POHA.blanket_total_amount)
OR (POH.blanket_total_amount IS NULL
AND POHA.blanket_total_amount IS NOT NULL)
OR (POH.blanket_total_amount IS NOT NULL
AND POHA.blanket_total_amount IS NULL)
OR (POH.note_to_vendor <> POHA.note_to_vendor)
OR (POH.note_to_vendor IS NULL
AND POHA.note_to_vendor IS NOT NULL)
OR (POH.note_to_vendor IS NOT NULL
AND POHA.note_to_vendor IS NULL)
OR (POH.confirming_order_flag <>
POHA.confirming_order_flag)
OR (POH.confirming_order_flag IS NULL
AND POHA.confirming_order_flag IS NOT NULL)
OR (POH.confirming_order_flag IS NOT NULL
AND POHA.confirming_order_flag IS NULL)
-- Start Bug 3659223: Clean up logic, and correctly handle
-- revisioning for PO rejected during signature process.
-- Replaced bug fix for bug 3388218
OR ((POH.acceptance_required_flag <> POHA.acceptance_required_flag)
AND (POH.acceptance_required_flag <> 'N'))
OR (POHA.acceptance_required_flag in ('Y','D')
AND POH.acceptance_required_flag ='N'
AND (nvl(l_accepted_flag,'X') not in ('N', 'Y'))) --Bug# 5943064
-- End Bug 3659223
OR (POH.acceptance_required_flag IS NULL
AND POHA.acceptance_required_flag IS NOT NULL)
OR (POH.acceptance_required_flag IS NOT NULL
AND POHA.acceptance_required_flag IS NULL)
OR (POH.acceptance_due_date <> POHA.acceptance_due_date)
OR (POH.acceptance_due_date IS NULL
AND POHA.acceptance_due_date IS NOT NULL
AND nvl(l_accepted_flag,'X') not in ('N','Y') -- Bug 3498816, Bug# 5943064
-- Bug 3659223: Do not revision for Doc and Sig, as
-- accepting/rejecting will null out the date.
AND nvl(POH.acceptance_required_flag, 'X') <> 'S')
OR (POH.acceptance_due_date IS NOT NULL
AND POHA.acceptance_due_date IS NULL)
OR (POH.amount_limit <> POHA.amount_limit)
OR (POH.amount_limit IS NULL
AND POHA.amount_limit IS NOT NULL)
OR (POH.amount_limit IS NOT NULL
AND POHA.amount_limit IS NULL)
OR (POH.start_date <> POHA.start_date)
OR (POH.start_date IS NULL
AND POHA.start_date IS NOT NULL)
OR (POH.start_date IS NOT NULL
AND POHA.start_date IS NULL)
OR (POH.end_date <> POHA.end_date)
OR (POH.end_date IS NULL
AND POHA.end_date IS NOT NULL)
OR (POH.end_date IS NOT NULL
AND POHA.end_date IS NULL)
OR (p_chk_cancel_flag = 'Y' AND --
((POH.cancel_flag <> POHA.cancel_flag)
OR (POH.cancel_flag IS NULL
AND POHA.cancel_flag IS NOT NULL)
OR (POH.cancel_flag IS NOT NULL
AND POHA.cancel_flag IS NULL)))
-- dependency popo.odf , poarc.odf
OR (POH.conterms_articles_upd_date <> POHA.conterms_articles_upd_date)
OR (POH.conterms_articles_upd_date IS NULL
AND POHA.conterms_articles_upd_date IS NOT NULL)
OR (POH.conterms_articles_upd_date IS NOT NULL
AND POHA.conterms_articles_upd_date IS NULL)
OR (POH.conterms_deliv_upd_date <> POHA.conterms_deliv_upd_date)
OR (POH.conterms_deliv_upd_date IS NULL
AND POHA.conterms_deliv_upd_date IS NOT NULL)
OR (POH.conterms_deliv_upd_date IS NOT NULL
AND POHA.conterms_deliv_upd_date IS NULL)
--
));
SELECT 'Y'
INTO x_different
FROM po_ga_org_assignments pgoa,
po_ga_org_assignments_archive pgoaa
WHERE pgoa.po_header_id = p_doc_id
AND pgoa.po_header_id = pgoaa.po_header_id (+)
AND pgoa.organization_id = pgoaa.organization_id (+)
AND pgoaa.latest_external_flag (+) = 'Y'
AND ( (pgoaa.po_header_id IS NULL)
OR (pgoaa.organization_id <> pgoa.organization_id)
OR (pgoaa.purchasing_org_id <> pgoa.purchasing_org_id)
OR (pgoaa.vendor_site_id <> pgoa.vendor_site_id)
OR (pgoaa.enabled_flag <> pgoa.enabled_flag)
)
AND ROWNUM = 1;
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_LINES POL,
PO_LINES_ARCHIVE POLA
WHERE POL.po_header_id = p_doc_id
AND (p_line_id IS NULL OR POL.po_line_id = p_line_id) --
AND POL.po_line_id = POLA.po_line_id (+)
AND POLA.latest_external_flag (+) = 'Y'
AND (
(POLA.po_line_id is NULL)
OR (POL.line_num <> POLA.line_num)
OR (POL.item_id <> POLA.item_id)
OR (POL.item_id IS NULL
AND POLA.item_id IS NOT NULL)
OR (POL.item_id IS NOT NULL
AND POLA.item_id IS NULL)
-- SERVICES FPJ Start
OR (POL.job_id <> POLA.job_id)
OR (POL.job_id IS NULL
AND POLA.job_id IS NOT NULL)
OR (POL.job_id IS NOT NULL
AND POLA.job_id IS NULL)
OR (POL.amount <> POLA.amount)
OR (POL.amount IS NULL
AND POLA.amount IS NOT NULL)
OR (POL.amount IS NOT NULL
AND POLA.amount IS NULL)
-- SERVICES FPJ Start
OR (POL.item_revision <> POLA.item_revision)
OR (POL.item_revision IS NULL
AND POLA.item_revision IS NOT NULL)
OR (POL.item_revision IS NOT NULL
AND POLA.item_revision IS NULL)
OR (TRIM(POL.item_description) <>
TRIM(POLA.item_description)) --Bug14214404
OR (POL.item_description IS NULL
AND POLA.item_description IS NOT NULL)
OR (POL.item_description IS NOT NULL
AND POLA.item_description IS NULL)
OR (POL.unit_meas_lookup_code <>
POLA.unit_meas_lookup_code)
OR (POL.unit_meas_lookup_code IS NULL
AND POLA.unit_meas_lookup_code IS NOT NULL)
OR (POL.unit_meas_lookup_code IS NOT NULL
AND POLA.unit_meas_lookup_code IS NULL)
OR (POL.quantity_committed <>
POLA.quantity_committed)
OR (POL.quantity_committed IS NULL
AND POLA.quantity_committed IS NOT NULL)
OR (POL.quantity_committed IS NOT NULL
AND POLA.quantity_committed IS NULL)
OR (POL.committed_amount <>
POLA.committed_amount)
OR (POL.committed_amount IS NULL
AND POLA.committed_amount IS NOT NULL)
OR (POL.committed_amount IS NOT NULL
AND POLA.committed_amount IS NULL)
OR (POL.unit_price <> POLA.unit_price)
OR (POL.unit_price IS NULL
AND POLA.unit_price IS NOT NULL)
OR (POL.unit_price IS NOT NULL
AND POLA.unit_price IS NULL)
-- Bug 3471211
OR (POL.not_to_exceed_price <> POLA.not_to_exceed_price)
OR (POL.not_to_exceed_price IS NULL
AND POLA.not_to_exceed_price IS NOT NULL)
OR (POL.not_to_exceed_price IS NOT NULL
AND POLA.not_to_exceed_price IS NULL)
OR (POL.un_number_id <> POLA.un_number_id)
OR (POL.un_number_id IS NULL
AND POLA.un_number_id IS NOT NULL)
OR (POL.un_number_id IS NOT NULL
AND POLA.un_number_id IS NULL)
OR (POL.hazard_class_id <> POLA.hazard_class_id)
OR (POL.hazard_class_id IS NULL
AND POLA.hazard_class_id IS NOT NULL)
OR (POL.hazard_class_id IS NOT NULL
AND POLA.hazard_class_id IS NULL)
OR (POL.note_to_vendor <> POLA.note_to_vendor)
OR (POL.note_to_vendor IS NULL
AND POLA.note_to_vendor IS NOT NULL)
OR (POL.note_to_vendor IS NOT NULL
AND POLA.note_to_vendor IS NULL)
OR (POL.note_to_vendor <> POLA.note_to_vendor)
OR (POL.note_to_vendor IS NULL
AND POLA.note_to_vendor IS NOT NULL)
OR (POL.note_to_vendor IS NOT NULL
AND POLA.note_to_vendor IS NULL)
OR (POL.from_header_id <> POLA.from_header_id)
OR (POL.from_header_id IS NULL
AND POLA.from_header_id IS NOT NULL)
OR (POL.from_header_id IS NOT NULL
AND POLA.from_header_id IS NULL)
OR (POL.from_line_id <> POLA.from_line_id)
OR (POL.from_line_id IS NULL
AND POLA.from_line_id IS NOT NULL)
OR (POL.from_line_id IS NOT NULL
AND POLA.from_line_id IS NULL)
-- Bug 3305753: Closed code need not be compared
-- Since close action is an internal action and
-- should not affect the document revision.
-- ((POL.closed_code <> POLA.closed_code)
--OR (POL.closed_code IS NULL
-- AND POLA.closed_code IS NOT NULL)
--OR (POL.closed_code IS NOT NULL
-- AND POLA.closed_code IS NULL))
OR (POL.vendor_product_num <>
POLA.vendor_product_num)
OR (POL.vendor_product_num IS NULL
AND POLA.vendor_product_num IS NOT NULL)
OR (POL.vendor_product_num IS NOT NULL
AND POLA.vendor_product_num IS NULL)
--
-- Removing CONTRACT_NUM check because
-- Blanket line cannot reference a contract
OR (POL.price_type_lookup_code <>
POLA.price_type_lookup_code)
OR (POL.price_type_lookup_code IS NULL
AND POLA.price_type_lookup_code IS NOT NULL)
OR (POL.price_type_lookup_code IS NOT NULL
AND POLA.price_type_lookup_code IS NULL)
OR (POL.expiration_date IS NULL
AND POLA.expiration_date IS NOT NULL)
OR (POL.expiration_date IS NOT NULL
AND POLA.expiration_date IS NULL)
OR (trunc(POL.expiration_date) <>
trunc(POLA.expiration_date))
OR (p_chk_cancel_flag = 'Y' AND --
((POL.cancel_flag <> POLA.cancel_flag)
OR (POL.cancel_flag IS NULL
AND POLA.cancel_flag IS NOT NULL)
OR (POL.cancel_flag IS NOT NULL
AND POLA.cancel_flag IS NULL)))));
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_LINES POL,
PO_LINES_ARCHIVE POLA
WHERE POL.po_header_id = p_doc_id
AND (p_line_id IS NULL OR POL.po_line_id = p_line_id) --
AND POL.po_line_id = POLA.po_line_id (+)
AND POLA.latest_external_flag (+) = 'Y'
AND (
(POLA.po_line_id is NULL)
OR (POL.line_num <> POLA.line_num)
OR (POL.item_id <> POLA.item_id)
OR (POL.item_id IS NULL
AND POLA.item_id IS NOT NULL)
OR (POL.item_id IS NOT NULL
AND POLA.item_id IS NULL)
-- SERVICES FPJ Start
OR (POL.job_id <> POLA.job_id)
OR (POL.job_id IS NULL
AND POLA.job_id IS NOT NULL)
OR (POL.job_id IS NOT NULL
AND POLA.job_id IS NULL)
OR (POL.amount <> POLA.amount)
OR (POL.amount IS NULL
AND POLA.amount IS NOT NULL)
OR (POL.amount IS NOT NULL
AND POLA.amount IS NULL)
OR (POL.expiration_date IS NULL
AND POLA.expiration_date IS NOT NULL)
OR (POL.expiration_date IS NOT NULL
AND POLA.expiration_date IS NULL)
OR (trunc(POL.expiration_date) <>
trunc(POLA.expiration_date))
OR (POL.start_date IS NULL
AND POLA.start_date IS NOT NULL)
OR (POL.start_date IS NOT NULL
AND POLA.start_date IS NULL)
OR (trunc(POL.start_date) <>
trunc(POLA.start_date))
OR (POL.contractor_first_name <>
POLA.contractor_first_name)
OR (POL.contractor_first_name IS NULL
AND POLA.contractor_first_name IS NOT NULL)
OR (POL.contractor_first_name IS NOT NULL
AND POLA.contractor_first_name IS NULL)
OR (POL.contractor_last_name <>
POLA.contractor_last_name)
OR (POL.contractor_last_name IS NULL
AND POLA.contractor_last_name IS NOT NULL)
OR (POL.contractor_last_name IS NOT NULL
AND POLA.contractor_last_name IS NULL)
-- SERVICES FPJ Start
OR (POL.item_revision <> POLA.item_revision)
OR (POL.item_revision IS NULL
AND POLA.item_revision IS NOT NULL)
OR (POL.item_revision IS NOT NULL
AND POLA.item_revision IS NULL)
OR (TRIM(POL.item_description) <>
TRIM(POLA.item_description)) --Bug14214404
OR (POL.item_description IS NULL
AND POLA.item_description IS NOT NULL)
OR (POL.item_description IS NOT NULL
AND POLA.item_description IS NULL)
OR (POL.unit_meas_lookup_code <>
POLA.unit_meas_lookup_code)
OR (POL.unit_meas_lookup_code IS NULL
AND POLA.unit_meas_lookup_code IS NOT NULL)
OR (POL.unit_meas_lookup_code IS NOT NULL
AND POLA.unit_meas_lookup_code IS NULL)
OR (p_chk_cancel_flag = 'Y' AND POL.quantity <> POLA.quantity) --
OR (POL.quantity IS NULL
AND POLA.quantity IS NOT NULL)
OR (POL.quantity_committed <>
POLA.quantity_committed)
OR (POL.quantity_committed IS NULL
AND POLA.quantity_committed IS NOT NULL)
OR (POL.quantity_committed IS NOT NULL
AND POLA.quantity_committed IS NULL)
OR (POL.committed_amount <>
POLA.committed_amount)
OR (POL.committed_amount IS NULL
AND POLA.committed_amount IS NOT NULL)
OR (POL.committed_amount IS NOT NULL
AND POLA.committed_amount IS NULL)
OR (POL.unit_price <> POLA.unit_price)
OR (POL.unit_price IS NULL
AND POLA.unit_price IS NOT NULL)
OR (POL.unit_price IS NOT NULL
AND POLA.unit_price IS NULL)
-- Bug 3471211
OR (POL.not_to_exceed_price <> POLA.not_to_exceed_price)
OR (POL.not_to_exceed_price IS NULL
AND POLA.not_to_exceed_price IS NOT NULL)
OR (POL.not_to_exceed_price IS NOT NULL
AND POLA.not_to_exceed_price IS NULL)
OR (POL.un_number_id <> POLA.un_number_id)
OR (POL.un_number_id IS NULL
AND POLA.un_number_id IS NOT NULL)
OR (POL.un_number_id IS NOT NULL
AND POLA.un_number_id IS NULL)
OR (POL.hazard_class_id <>
POLA.hazard_class_id)
OR (POL.hazard_class_id IS NULL
AND POLA.hazard_class_id IS NOT NULL)
OR (POL.hazard_class_id IS NOT NULL
AND POLA.hazard_class_id IS NULL)
OR (POL.note_to_vendor <> POLA.note_to_vendor)
OR (POL.note_to_vendor IS NULL
AND POLA.note_to_vendor IS NOT NULL)
OR (POL.note_to_vendor IS NOT NULL
AND POLA.note_to_vendor IS NULL)
OR (POL.note_to_vendor <> POLA.note_to_vendor)
OR (POL.note_to_vendor IS NULL
AND POLA.note_to_vendor IS NOT NULL)
OR (POL.note_to_vendor IS NOT NULL
AND POLA.note_to_vendor IS NULL)
OR (POL.from_header_id <> POLA.from_header_id)
OR (POL.from_header_id IS NULL
AND POLA.from_header_id IS NOT NULL)
OR (POL.from_header_id IS NOT NULL
AND POLA.from_header_id IS NULL)
OR (POL.from_line_id <> POLA.from_line_id)
OR (POL.from_line_id IS NULL
AND POLA.from_line_id IS NOT NULL)
OR (POL.from_line_id IS NOT NULL
AND POLA.from_line_id IS NULL)
-- Bug 3305753:Closed code need not be compared
-- Since close action is an internal action and
-- should not affect the document revision.
-- ((POL.closed_code <> POLA.closed_code)
-- OR (POL.closed_code IS NULL
-- AND POLA.closed_code IS NOT NULL)
-- OR (POL.closed_code IS NOT NULL
-- AND POLA.closed_code IS NULL))
OR (POL.vendor_product_num <>
POLA.vendor_product_num)
OR (POL.vendor_product_num IS NULL
AND POLA.vendor_product_num IS NOT NULL)
OR (POL.vendor_product_num IS NOT NULL
AND POLA.vendor_product_num IS NULL)
--
-- Compare contract_id instead of contract_num
OR (POL.contract_id <> POLA.contract_id)
OR (POL.contract_id IS NULL
AND POLA.contract_id IS NOT NULL)
OR (POL.contract_id IS NOT NULL
AND POLA.contract_id IS NULL)
OR (POL.price_type_lookup_code <>
POLA.price_type_lookup_code)
OR (POL.price_type_lookup_code IS NULL
AND POLA.price_type_lookup_code IS NOT NULL)
OR (POL.price_type_lookup_code IS NOT NULL
AND POLA.price_type_lookup_code IS NULL)
OR (p_chk_cancel_flag = 'Y' AND --
((POL.cancel_flag <> POLA.cancel_flag)
OR (POL.cancel_flag IS NULL
AND POLA.cancel_flag IS NOT NULL)
OR (POL.cancel_flag IS NOT NULL
AND POLA.cancel_flag IS NULL)))
--
OR (POL.retainage_rate <> POLA.retainage_rate)
OR (POL.retainage_rate IS NULL
AND POLA.retainage_rate IS NOT NULL)
OR (POL.retainage_rate IS NOT NULL
AND POLA.retainage_rate IS NULL)
OR (POL.max_retainage_amount <> POLA.max_retainage_amount)
OR (POL.max_retainage_amount IS NULL
AND POLA.max_retainage_amount IS NOT NULL)
OR (POL.max_retainage_amount IS NOT NULL
AND POLA.max_retainage_amount IS NULL)
OR (POL.progress_payment_rate <> POLA.progress_payment_rate)
OR (POL.progress_payment_rate IS NULL
AND POLA.progress_payment_rate IS NOT NULL)
OR (POL.progress_payment_rate IS NOT NULL
AND POLA.progress_payment_rate IS NULL)
OR (POL.recoupment_rate <> POLA.recoupment_rate)
OR (POL.recoupment_rate IS NULL
AND POLA.recoupment_rate IS NOT NULL)
OR (POL.recoupment_rate IS NOT NULL
AND POLA.recoupment_rate IS NULL)
--
));
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_LINE_LOCATIONS POLL,
PO_LINE_LOCATIONS_ARCHIVE POLLA
WHERE POLL.po_header_id = p_doc_id
AND POLL.po_release_id is null -- Bug 3876235
AND (p_line_id IS NULL OR POLL.po_line_id = p_line_id) --
AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --
AND POLL.line_location_id = POLLA.line_location_id (+)
AND POLLA.latest_external_flag (+) = 'Y'
AND (
(POLLA.line_location_id is NULL)
OR (POLL.quantity <> POLLA.quantity)
OR (POLL.quantity IS NULL AND POLLA.quantity IS NOT NULL)
OR (POLL.quantity IS NOT NULL AND POLLA.quantity IS NULL)
-- SERVICES FPJ Start
OR (POLL.amount <> POLLA.amount)
OR (POLL.amount IS NULL AND POLLA.amount IS NOT NULL)
OR (POLL.amount IS NOT NULL AND POLLA.amount IS NULL)
-- SERVICES FPJ Start
OR (POLL.ship_to_location_id <>
POLLA.ship_to_location_id)
OR (POLL.ship_to_location_id IS NULL
AND POLLA.ship_to_location_id IS NOT NULL)
OR (POLL.ship_to_location_id IS NOT NULL
AND POLLA.ship_to_location_id IS NULL)
OR (POLL.need_by_date <> POLLA.need_by_date)
OR (POLL.need_by_date IS NULL
AND POLLA.need_by_date IS NOT NULL)
OR (POLL.need_by_date IS NOT NULL
AND POLLA.need_by_date IS NULL)
OR (POLL.promised_date <> POLLA.promised_date)
OR (POLL.promised_date IS NULL
AND POLLA.promised_date IS NOT NULL)
OR (POLL.promised_date IS NOT NULL
AND POLLA.promised_date IS NULL)
OR (POLL.last_accept_date <> POLLA.last_accept_date)
OR (POLL.last_accept_date IS NULL
AND POLLA.last_accept_date IS NOT NULL)
OR (POLL.last_accept_date IS NOT NULL
AND POLLA.last_accept_date IS NULL)
OR (POLL.price_override <> POLLA.price_override)
OR (POLL.price_override IS NULL
AND POLLA.price_override IS NOT NULL)
OR (POLL.price_override IS NOT NULL
AND POLLA.price_override IS NULL) --BUG7286203 REMOVED THE CHECK FOR TAXCODE ID
--
OR (POLL.payment_type <> POLLA.payment_type)
OR (POLL.payment_type IS NULL
AND POLLA.payment_type IS NOT NULL)
OR (POLL.payment_type IS NOT NULL
AND POLLA.payment_type IS NULL)
OR (POLL.description <> POLLA.description)
OR (POLL.description IS NULL
AND POLLA.description IS NOT NULL)
OR (POLL.description IS NOT NULL
AND POLLA.description IS NULL)
OR (POLL.work_approver_id <> POLLA.work_approver_id)
OR (POLL.work_approver_id IS NULL
AND POLLA.work_approver_id IS NOT NULL)
OR (POLL.work_approver_id IS NOT NULL
AND POLLA.work_approver_id IS NULL)
--
OR (POLL.shipment_num <> POLLA.shipment_num)
OR (POLL.shipment_num IS NULL
AND POLLA.shipment_num IS NOT NULL)
OR (POLL.shipment_num IS NOT NULL
AND POLLA.shipment_num IS NULL)
OR (POLL.sales_order_update_date <> POLLA.sales_order_update_date)
OR (POLL.sales_order_update_date IS NULL
AND POLLA.sales_order_update_date IS NOT NULL)
OR (POLL.sales_order_update_date IS NOT NULL
AND POLLA.sales_order_update_date IS NULL)
OR (p_chk_cancel_flag = 'Y' AND --
((POLL.cancel_flag <> POLLA.cancel_flag)
OR (POLL.cancel_flag IS NULL
AND POLLA.cancel_flag IS NOT NULL)
OR (POLL.cancel_flag IS NOT NULL
AND POLLA.cancel_flag IS NULL)))));
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_LINE_LOCATIONS POLL,
PO_LINE_LOCATIONS_ARCHIVE POLLA
WHERE POLL.po_header_id = p_doc_id
AND POLL.po_release_id is null -- Bug 3876235
AND (p_line_id IS NULL OR POLL.po_line_id = p_line_id) --
AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --
AND POLL.line_location_id = POLLA.line_location_id (+)
AND POLLA.latest_external_flag (+) = 'Y'
AND (
(POLLA.line_location_id is NULL)
OR (POLL.quantity <> POLLA.quantity)
OR (POLL.quantity IS NULL AND POLLA.quantity IS NOT NULL)
OR (POLL.quantity IS NOT NULL AND POLLA.quantity IS NULL)
OR (POLL.ship_to_location_id <>
POLLA.ship_to_location_id)
OR (POLL.ship_to_location_id IS NULL
AND POLLA.ship_to_location_id IS NOT NULL)
OR (POLL.ship_to_location_id IS NOT NULL
AND POLLA.ship_to_location_id IS NULL)
OR (POLL.price_override <> POLLA.price_override)
OR (POLL.price_override IS NULL
AND POLLA.price_override IS NOT NULL)
OR (POLL.price_override IS NOT NULL
AND POLLA.price_override IS NULL)
OR (POLL.shipment_num <> POLLA.shipment_num)
OR (POLL.shipment_num IS NULL
AND POLLA.shipment_num IS NOT NULL)
OR (POLL.shipment_num IS NOT NULL
AND POLLA.shipment_num IS NULL)
/* */
OR (POLL.start_date <> POLLA.start_date)
OR (POLL.start_date is null AND POLLA.start_date is not null)
OR (POLL.start_date is not null AND POLLA.start_date is null)
OR (POLL.end_date <> POLLA.end_date)
OR (POLL.end_date is null AND POLLA.end_date is not null)
OR (POLL.end_date is not null AND POLLA.end_date is null)));
SELECT 'Y'
INTO x_different
FROM po_price_differentials pdf,
po_price_differentials_archive pdfa,
po_lines_all pol
WHERE pol.po_header_id = p_doc_id
AND pol.po_line_id = pdf.entity_id
AND pdf.entity_type in ('PO LINE', 'BLANKET LINE')
AND pdf.price_differential_id = pdfa.price_differential_id (+)
AND pdfa.latest_external_flag (+) = 'Y'
AND (
( pdfa.price_differential_id IS NULL )
OR ( pdf.price_differential_num <> pdfa.price_differential_num )
OR ( pdf.price_type <> pdfa.price_type )
OR ( ( pdf.multiplier <> pdfa.multiplier )
OR ( pdf.multiplier IS NULL AND pdfa.multiplier IS NOT NULL )
OR ( pdf.multiplier IS NOT NULL AND pdfa.multiplier IS NULL ) )
OR ( ( pdf.max_multiplier <> pdfa.max_multiplier )
OR ( pdf.max_multiplier IS NULL AND pdfa.max_multiplier IS NOT NULL )
OR ( pdf.max_multiplier IS NOT NULL AND pdfa.max_multiplier IS NULL ) )
OR ( ( pdf.min_multiplier <> pdfa.min_multiplier)
OR ( pdf.min_multiplier IS NULL AND pdfa.min_multiplier IS NOT NULL )
OR ( pdf.min_multiplier IS NOT NULL AND pdfa.min_multiplier IS NULL ) )
OR ( ( pdf.enabled_flag <> pdfa.enabled_flag )
OR ( pdf.enabled_flag IS NULL AND pdfa.enabled_flag IS NOT NULL )
OR ( pdf.enabled_flag IS NOT NULL AND pdfa.enabled_flag IS NULL ) )
);
SELECT 'Y'
INTO x_different
FROM po_price_differentials pdf,
po_price_differentials_archive pdfa,
po_line_locations_all poll
WHERE poll.po_header_id = p_doc_id
AND poll.line_location_id = pdf.entity_id
AND pdf.entity_type = 'PRICE BREAK'
AND pdf.price_differential_id = pdfa.price_differential_id (+)
AND pdfa.latest_external_flag (+) = 'Y'
AND (
( pdfa.price_differential_id IS NULL )
OR ( pdf.price_differential_num <> pdfa.price_differential_num )
OR ( pdf.price_type <> pdfa.price_type )
OR ( ( pdf.multiplier <> pdfa.multiplier )
OR ( pdf.multiplier IS NULL AND pdfa.multiplier IS NOT NULL )
OR ( pdf.multiplier IS NOT NULL AND pdfa.multiplier IS NULL ) )
OR ( ( pdf.max_multiplier <> pdfa.max_multiplier )
OR ( pdf.max_multiplier IS NULL AND pdfa.max_multiplier IS NOT NULL )
OR ( pdf.max_multiplier IS NOT NULL AND pdfa.max_multiplier IS NULL ) )
OR ( ( pdf.min_multiplier <> pdfa.min_multiplier)
OR ( pdf.min_multiplier IS NULL AND pdfa.min_multiplier IS NOT NULL )
OR ( pdf.min_multiplier IS NOT NULL AND pdfa.min_multiplier IS NULL ) )
OR ( ( pdf.enabled_flag <> pdfa.enabled_flag )
OR ( pdf.enabled_flag IS NULL AND pdfa.enabled_flag IS NOT NULL )
OR ( pdf.enabled_flag IS NOT NULL AND pdfa.enabled_flag IS NULL ) )
);
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_DISTRIBUTIONS POD,
PO_DISTRIBUTIONS_ARCHIVE PODA,
PO_LINE_LOCATIONS POLL --Bug 13960467
WHERE POD.po_header_id = p_doc_id
AND (POD.line_location_id = POLL.line_location_id) --Bug 13960467
AND (p_line_id IS NULL OR POD.po_line_id = p_line_id) --
AND (p_line_location_id IS NULL OR POD.line_location_id = p_line_location_id) --
AND POD.po_distribution_id =
PODA.po_distribution_id (+)
AND PODA.latest_external_flag (+) = 'Y'
AND (
(PODA.po_distribution_id is NULL)
OR (POD.quantity_ordered <> PODA.quantity_ordered)
OR (POD.quantity_ordered IS NULL
AND PODA.quantity_ordered IS NOT NULL)
OR (POD.quantity_ordered IS NOT NULL
AND PODA.quantity_ordered IS NULL)
-- SERVICES FPJ
OR (POD.amount_ordered <> PODA.amount_ordered)
OR (POD.amount_ordered IS NULL
AND PODA.amount_ordered IS NOT NULL)
OR (POD.amount_ordered IS NOT NULL
AND PODA.amount_ordered IS NULL)
-- SERVICES FPJ
/*Bug 12529922 start
OR (POD.deliver_to_person_id <>
PODA.deliver_to_person_id)
OR (POD.deliver_to_person_id IS NULL
AND PODA.deliver_to_person_id IS NOT NULL)
OR (POD.deliver_to_person_id IS NOT NULL
AND PODA.deliver_to_person_id IS NULL)
end Bug 12529922*/
/* OR (POD.distribution_num <> PODA.distribution_num)*/
-- BUG 9766489: Since The Document is allowed to be canceled when its in requires
-- Reapproval state, But if the document is unreserved and have the backing
-- document then its not possible to manage the cancel action on the Main Document.
-- Disabling the cancel action on requires reapproval action when document is
-- unreserved.
OR (p_chk_cancel_flag = 'N'
AND NVL(POLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED' --Bug 13960467
AND POD.BUDGET_ACCOUNT_ID IS NOT NULL
AND Nvl(POD.ENCUMBERED_FLAG,'P') <> Nvl(PODA.ENCUMBERED_FLAG,'P')
-- to handle the null encumbered_flag
)
));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
Select pav.accepted_flag
into l_accepted_flag
from po_acceptances_v pav,
po_releases por
where por.po_release_id=p_doc_id
and por.po_release_id=pav.po_release_id
and pav.revision_num= por.revision_num
and por.acceptance_required_flag='N'
and rownum=1;
out on which Mass update buyer program is run to update buyer name.
Hence donot use the agent_id comparision for cancel flow*/
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_RELEASES POR,
PO_RELEASES_ARCHIVE PORA
WHERE POR.po_release_id = p_doc_id
AND POR.po_release_id = PORA.po_release_id
AND PORA.latest_external_flag (+) = 'Y'
AND (
(PORA.po_release_id IS NULL)
OR (POR.release_num <> PORA.release_num)
OR((POR.agent_id <> PORA.agent_id) AND (p_chk_cancel_flag='Y'))
OR (POR.release_date <> PORA.release_date)
--
OR (POR.shipping_control <>
PORA.shipping_control)
OR (POR.shipping_control IS NULL
AND PORA.shipping_control IS NOT NULL)
OR (POR.shipping_control IS NOT NULL
AND PORA.shipping_control IS NULL)
--
-- Start Bug 3388218
OR ((POR.acceptance_required_flag <> PORA.acceptance_required_flag)
AND (POR.acceptance_required_flag <> 'N'))
OR (PORA.acceptance_required_flag in ('Y')
AND POR.acceptance_required_flag ='N'
AND (nvl(l_accepted_flag,'X') not in ('N', 'Y'))) --Bug# 6066670
-- End Bug 3388218
OR (POR.acceptance_required_flag IS NULL
AND PORA.acceptance_required_flag IS NOT NULL)
OR (POR.acceptance_required_flag IS NOT NULL
AND PORA.acceptance_required_flag IS NULL)
OR (POR.acceptance_due_date <>
PORA.acceptance_due_date)
OR (POR.acceptance_due_date IS NULL
AND PORA.acceptance_due_date IS NOT NULL
AND nvl(l_accepted_flag,'X') not in ('N','Y')) -- Bug#3498816,Bug#6066670
OR (POR.acceptance_due_date IS NOT NULL
AND PORA.acceptance_due_date IS NULL)));
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_LINE_LOCATIONS POLL,
PO_LINE_LOCATIONS_ARCHIVE POLLA
WHERE POLL.po_release_id = p_doc_id
AND (p_line_location_id IS NULL OR POLL.line_location_id = p_line_location_id) --
AND POLL.line_location_id = POLLA.line_location_id (+)
AND POLLA.latest_external_flag (+) = 'Y'
AND (
(POLLA.line_location_id is NULL)
OR (POLL.quantity <> POLLA.quantity)
OR (POLL.quantity IS NULL
AND POLLA.quantity IS NOT NULL)
OR (POLL.quantity IS NOT NULL
AND POLLA.quantity IS NULL)
-- SERVICES FPJ Start
OR (POLL.amount <> POLLA.amount)
OR (POLL.amount IS NULL AND POLLA.amount IS NOT NULL)
OR (POLL.amount IS NOT NULL AND POLLA.amount IS NULL)
-- SERVICES FPJ Start
OR (POLL.ship_to_location_id <>
POLLA.ship_to_location_id)
OR (POLL.ship_to_location_id IS NULL
AND POLLA.ship_to_location_id IS NOT NULL)
OR (POLL.ship_to_location_id IS NOT NULL
AND POLLA.ship_to_location_id IS NULL)
OR (POLL.need_by_date <> POLLA.need_by_date)
OR (POLL.need_by_date IS NULL
AND POLLA.need_by_date IS NOT NULL)
OR (POLL.need_by_date IS NOT NULL
AND POLLA.need_by_date IS NULL)
OR (POLL.promised_date <> POLLA.promised_date)
OR (POLL.promised_date IS NULL
AND POLLA.promised_date IS NOT NULL)
OR (POLL.promised_date IS NOT NULL
AND POLLA.promised_date IS NULL)
OR (POLL.last_accept_date <> POLLA.last_accept_date)
OR (POLL.last_accept_date IS NULL
AND POLLA.last_accept_date IS NOT NULL)
OR (POLL.last_accept_date IS NOT NULL
AND POLLA.last_accept_date IS NULL)
OR (POLL.price_override <> POLLA.price_override)
OR (POLL.price_override IS NULL
AND POLLA.price_override IS NOT NULL)
OR (POLL.price_override IS NOT NULL
AND POLLA.price_override IS NULL) --BUG7286203 REMOVED THE CHECK FOR TAXCODE ID
OR (POLL.shipment_num <> POLLA.shipment_num)
OR (POLL.shipment_num IS NULL
AND POLLA.shipment_num IS NOT NULL)
OR (POLL.shipment_num IS NOT NULL
AND POLLA.shipment_num IS NULL)
OR (POLL.sales_order_update_date <> POLLA.sales_order_update_date)
OR (POLL.sales_order_update_date IS NULL
AND POLLA.sales_order_update_date IS NOT NULL)
OR (POLL.sales_order_update_date IS NOT NULL
AND POLLA.sales_order_update_date IS NULL)
OR (p_chk_cancel_flag = 'Y' AND --
((POLL.cancel_flag <> POLLA.cancel_flag)
OR (POLL.cancel_flag IS NULL
AND POLLA.cancel_flag IS NOT NULL)
OR (POLL.cancel_flag IS NOT NULL
AND POLLA.cancel_flag IS NULL)))));
Select 'Y'
INTO x_different
from sys.dual
where exists(
select null
FROM PO_DISTRIBUTIONS POD,
PO_DISTRIBUTIONS_ARCHIVE PODA
WHERE POD.po_release_id = p_doc_id
AND (p_line_location_id IS NULL OR POD.line_location_id = p_line_location_id) --
AND POD.po_distribution_id =
PODA.po_distribution_id (+)
AND PODA.latest_external_flag (+) = 'Y'
AND (
(PODA.po_distribution_id is NULL)
OR (POD.quantity_ordered <> PODA.quantity_ordered)
OR (POD.quantity_ordered IS NULL
AND PODA.quantity_ordered IS NOT NULL)
OR (POD.quantity_ordered IS NOT NULL
AND PODA.quantity_ordered IS NULL)
-- SERVICES FPJ
OR (POD.amount_ordered <> PODA.amount_ordered)
OR (POD.amount_ordered IS NULL
AND PODA.amount_ordered IS NOT NULL)
OR (POD.amount_ordered IS NOT NULL
AND PODA.amount_ordered IS NULL)
-- SERVICES FPJ
/* Bug 12529922. Remove deliver_to_person_id check
OR (POD.deliver_to_person_id <>
PODA.deliver_to_person_id)
OR (POD.deliver_to_person_id IS NULL
AND PODA.deliver_to_person_id IS NOT NULL)
OR (POD.deliver_to_person_id IS NOT NULL
AND PODA.deliver_to_person_id IS NULL)
end Bug 12529922*/
/* OR (POD.distribution_num <> PODA.distribution_num) */
-- BUG: 9766489 Since The Document is allowed to be canceled when its in requires
-- Reapproval state, But if the document is unreserved and have the backing
-- document then its not possible to manage the cancel action on the Main Document.
-- Disabling the cancel action on requires reapproval action when document is
-- unreserved.
OR (p_chk_cancel_flag = 'N'
AND POD.BUDGET_ACCOUNT_ID IS NOT NULL
AND Nvl(POD.ENCUMBERED_FLAG,'P') <> Nvl(PODA.ENCUMBERED_FLAG,'P')
-- to handle the null encumbered_flag
)
));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
select 'Y'
into l_Archive_Record_Exists
from po_headers_archive
where po_header_id = p_doc_id and rownum = 1;
select 'Y'
into l_Archive_Record_Exists
from po_releases_archive
where po_release_id = p_doc_id and rownum = 1;
SELECT NVL(fsp.purch_encumbrance_flag, 'N')
INTO l_po_encumbrance_flag
FROM financials_system_params_all fsp
WHERE org_id = (SELECT org_id
FROM po_releases_all
WHERE po_release_id = p_doc_id
AND p_doc_type = PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
UNION ALL
SELECT org_id
FROM po_headers_all
WHERE po_header_id = p_doc_id
AND p_doc_type <> PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE);
SELECT 'PO_CHANGED_CANT_CANCEL_WARN',
'DOC_LINE_SHIP_DIST_NUM',
l_doc_token||''||segment1||','|| l_line_token||pol.LINE_NUM||','||l_ship_token||poll.SHIPMENT_NUM,
'PRICE_TOKEN',
Decode(Nvl(poall.price_override,0),Nvl(poll.price_override,0),
l_no_chg_token,
(poall.price_override||' '||l_to_token ||' '|| poll.price_override)),
'AMT_QTY_TOKEN',
DECODE(poll.amount,NULL,l_qty_token,l_amt_token),
'QTY_AMT',
Decode(poll.amount,NULL,
Decode(poall.quantity,poll.quantity,
l_no_chg_token,
(poall.quantity||' '||l_to_token ||' '|| poll.quantity)),
Decode(poall.amount,poll.amount,
l_no_chg_token,
(poall.amount||' '||l_to_token ||' '|| poll.amount))
),
'NEED_BY_PRM_DATE',
Decode(poll.promised_date,NULL,
Decode(poall.need_by_date,poll.need_by_date,
l_no_chg_token,
(Nvl(To_Char(poall.need_by_date),'Null')||' '||l_to_token ||' '|| Nvl(To_Char(poll.need_by_date),'Null'))),
Decode(poall.promised_date,poll.promised_date,
l_no_chg_token,
(Nvl(To_Char(poall.promised_date),'Null')||' '||l_to_token ||' '|| Nvl(To_Char(poll.promised_date),'Null'))
) )
INTO x_msg_name,
x_token_name_tbl(1),
x_token_value_tbl(1),
x_token_name_tbl(2),
x_token_value_tbl(2),
x_token_name_tbl(3),
x_token_value_tbl(3),
x_token_name_tbl(4),
x_token_value_tbl(4),
x_token_name_tbl(5),
x_token_value_tbl(5)
FROM
po_line_locations_archive_all poall,
po_line_locations_all poll,
po_headers_all poh,
po_lines_all pol,
po_distributions_all pod -----
WHERE
poll.line_location_id = pod.line_location_id -----
AND poll.po_line_id = pol.po_line_id
AND poll.po_header_id = pol.po_header_id
AND Nvl(poll.approved_flag,'N')<>'Y'
AND poll.line_location_id=poall.line_location_id
AND poh.po_header_id=poll.po_header_id
AND poall.latest_external_flag ='Y'
AND ((l_po_encumbrance_flag = 'Y' AND pod.encumbered_flag = 'Y')
OR l_po_encumbrance_flag = 'N') -----
AND (Nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)
OR Nvl(poll.promised_date,sysdate) <> Nvl(poall.promised_date,sysdate)
OR Nvl(poll.need_by_date,sysdate) <> Nvl(poall.need_by_date,sysdate))
AND poll.line_location_id IN
( SELECT line_location_id
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_SHIPMENT
AND 0= (SELECT Count(1)
FROM po_distributions_all pod
WHERE pod.line_location_id=p_line_location_id
AND NOT EXISTS (SELECT po_distribution_id
FROM po_distributions_archive_all poad
WHERE pod.po_distribution_id=poad.po_distribution_id))
UNION ALL
SELECT line_location_id
FROM po_line_locations_all
WHERE po_line_id = p_line_id
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_LINE
AND 0= (SELECT Count(1)
FROM po_line_locations_all poll
WHERE po_line_id=p_line_id
AND NOT EXISTS (SELECT line_location_id
FROM po_line_locations_archive_all poall
WHERE poll.line_location_id=poall.line_location_id))
AND 0= (SELECT Count(1)
FROM po_distributions_all pod
WHERE pod.po_line_id=p_line_id
AND NOT EXISTS (SELECT po_distribution_id
FROM po_distributions_archive_all poad
WHERE pod.po_distribution_id=poad.po_distribution_id))
UNION ALL
SELECT line_location_id
FROM po_line_locations_all
WHERE po_header_id = p_doc_id
AND p_doc_type <> PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER
AND 0= (SELECT Count(1)
FROM po_line_locations_all poll
WHERE po_header_id = p_doc_id
AND NOT EXISTS (SELECT line_location_id
FROM po_line_locations_archive_all poall
WHERE poll.line_location_id=poall.line_location_id))
AND 0= (SELECT Count(1)
FROM po_distributions_all pod
WHERE pod.po_header_id=p_doc_id
AND NOT EXISTS (SELECT po_distribution_id
FROM po_distributions_archive_all poad
WHERE pod.po_distribution_id=poad.po_distribution_id))
UNION ALL
SELECT line_location_id
FROM po_line_locations_all
WHERE po_release_id = p_doc_id
AND p_doc_type = PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER
AND 0= (SELECT Count(1)
FROM po_line_locations_all poll
WHERE po_release_id = p_doc_id
AND NOT EXISTS (SELECT line_location_id
FROM po_line_locations_archive_all poall
WHERE poll.line_location_id=poall.line_location_id))
AND 0= (SELECT Count(1)
FROM po_distributions_all pod
WHERE pod.po_release_id=p_doc_id
AND NOT EXISTS (SELECT po_distribution_id
FROM po_distributions_archive_all poad
WHERE pod.po_distribution_id=poad.po_distribution_id)));
SELECT 'PO_CHANGED_CANT_CANCEL'
INTO x_msg_name
FROM po_line_locations_all poll,
po_distributions_all pod -----
WHERE poll.line_location_id = pod.line_location_id
AND (l_po_encumbrance_flag = 'Y' AND pod.encumbered_flag = 'N')
-----
AND Nvl(poll.approved_flag,'N') <>'Y'
AND ((NOT EXISTS (SELECT 'Archive Exists'
FROM po_line_locations_archive_all poall
WHERE poll.line_location_id=poall.line_location_id)
OR (0 <> (SELECT Count(1)
FROM po_distributions_all pod
WHERE pod.line_location_id=poll.line_location_id
AND NOT EXISTS (SELECT po_distribution_id
FROM po_distributions_archive_all poad
WHERE pod.po_distribution_id=poad.po_distribution_id)
)
))
OR(l_po_encumbrance_flag = 'Y'
AND (EXISTS (SELECT 'Enc Columns Changed'
FROM po_line_locations_archive_all poall
WHERE poll.line_location_id=poall.line_location_id
AND poall.latest_external_flag ='Y'
AND (nvl(poll.price_override,0) <> Nvl(poall.price_override,0)
OR Nvl(poll.quantity,0) <> Nvl(poall.quantity,0)
OR Nvl(poll.amount,0) <> Nvl(poall.amount,0)))
OR EXISTS (SELECT 'Enc Amount Changed'
FROM po_distributions_all pod,
po_distributions_archive_all poad
WHERE pod.po_distribution_id=poad.po_distribution_id
AND pod.line_location_id=poll.line_location_id
AND poad.latest_external_flag ='Y'
AND (Nvl(poad.encumbered_amount,0)<>Nvl(pod.encumbered_amount,0)
OR Nvl(poad.rate,0)<>Nvl(pod.rate,0)
OR Nvl(poad.quantity_ordered,0)<>Nvl(pod.quantity_ordered,0)
OR Nvl(poad.amount_ordered,0)<>Nvl(pod.amount_ordered,0)
OR Nvl(poad.nonrecoverable_tax,0)<>Nvl(pod.nonrecoverable_tax,0))
)
)
)
)
AND ROWNUM<2
AND poll.line_location_id IN
( SELECT line_location_id
FROM po_line_locations_all
WHERE line_location_id = p_line_location_id
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_SHIPMENT
UNION ALL
SELECT line_location_id
FROM po_line_locations_all
WHERE po_line_id = p_line_id
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_LINE
UNION ALL
SELECT line_location_id
FROM po_line_locations_all
WHERE po_header_id = p_doc_id
AND p_doc_type <> PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER
UNION ALL
SELECT line_location_id
FROM po_line_locations_all
WHERE po_release_id = p_doc_id
AND p_doc_type = PO_DOCUMENT_CANCEL_PVT.c_doc_type_RELEASE
AND p_action_level = PO_DOCUMENT_CANCEL_PVT.c_entity_level_HEADER);