[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 'Y'
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 pav.accepted_flag='Y';
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') <> 'Y'))
-- 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,'N')='N' -- Bug 3498816
-- 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 (POL.item_description <>
POLA.item_description)
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 (POL.item_description <>
POLA.item_description)
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
WHERE POD.po_header_id = p_doc_id
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
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)
/* OR (POD.distribution_num <> PODA.distribution_num)*/
));--Bug7286203 REMOVED THE CHECK FOR RECOVERY_RATE
Select 'Y'
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 pav.accepted_flag='Y';
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 NOT
(nvl(POR.acceptance_required_flag,'X') ='N' AND
nvl(PORA.acceptance_required_flag,'X') = 'Y' AND
nvl(l_accepted_flag,'X')='Y'))
OR (POR.acceptance_required_flag = 'Y' AND
PORA.acceptance_required_flag ='Y' AND
nvl(l_accepted_flag,'X')='Y')
-- 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,'N')='N') -- Bug 3498816
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
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)
/* OR (POD.distribution_num <> PODA.distribution_num) */
));--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;