[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** In Procedure: chord_hd ***' );
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** Finish: chord_hd ***' );
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** In Procedure check_header_change ***');
/* Each of the following select statement maps to one attribute.
* It is written like this for clarity. Since each statemet consists
* of one index access and one range scan, performance should not
* suffer too much.
* However, they can be combined into one sql statement
* for slight performance gain.
*/
BEGIN
-- SQL What: Select 'Y' if agent id is changed
-- SQL Why: Need the value for routing to reapproval
-- if there is a change
-- SQL Join: po_header_id, agent_id
SELECT DISTINCT 'Y'
INTO x_header_control.agent_id
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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));
SELECT DISTINCT 'Y'
INTO x_header_control.vendor_site_id
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.vendor_contact_id
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.ship_to_location_id
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.bill_to_location_id
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.terms_id
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.ship_via_lookup_code
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.fob_lookup_code
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.freight_terms_lookup_code
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.note_to_vendor
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.confirming_order_flag
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.acceptance_required_flag
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.acceptance_required_flag <> POHA.acceptance_required_flag)
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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.acceptance_due_date
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.acceptance_due_date <> POHA.acceptance_due_date)
OR (POH.acceptance_due_date IS NULL
AND POHA.acceptance_due_date IS NOT NULL)
OR (POH.acceptance_due_date IS NOT NULL
AND POHA.acceptance_due_date IS NULL)
);
SELECT DISTINCT 'Y'
INTO x_header_control.start_date
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.end_date
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
SELECT DISTINCT 'Y'
INTO x_header_control.cancel_flag
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_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.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)
);
** Modified the divisor in the select statement
** from: nvl(POHA.blanket_total_amount,1)
** to: decode(nvl(POHA.blanket_total_amount,0),0,1,
** POHA.blanket_total_amount)
*/
-- SQL What: Retrieving the percentage change in
-- blanket total amount
-- SQL Why: Need the value in tolerance check (i.e reapproval
-- rule validations)
-- SQL Join: po_header_id
SELECT max((nvl(POH.blanket_total_amount,0)
-nvl(POHA.blanket_total_amount,0))
/ decode(nvl(POHA.blanket_total_amount,0),0,1,
POHA.blanket_total_amount)*100)
INTO x_header_control.blanket_total_change
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_id
AND POH.po_header_id = POHA.po_header_id (+)
AND POHA.latest_external_flag (+) = 'Y';
SELECT DISTINCT 'Y'
INTO x_header_control.amount_limit
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_id
AND POH.po_header_id = POHA.po_header_id (+)
AND POHA.latest_external_flag (+) = 'Y'
AND ((POH.amount_limit IS NULL AND
POHA.amount_limit IS NOT NULL)
);
** Modified the divisor in the select statement
** from: nvl(POHA.amount_limit,1)
** to: decode(nvl(POHA.amount_limit,0),0,1,POHA.amount_limit)
*/
-- SQL What: Retrieving the percentage change in
-- amount limit
-- SQL Why: Need the value in tolerance check (i.e reapproval
-- rule validations)
-- SQL Join: po_header_id
SELECT max((nvl(POH.amount_limit,0)
-nvl(POHA.amount_limit,0))
/ decode(nvl(POHA.amount_limit,0),0,1,POHA.amount_limit)
*100)
INTO x_header_control.amount_limit_change
FROM PO_HEADERS_all POH,
PO_HEADERS_ARCHIVE_all POHA
WHERE POH.po_header_id = x_po_header_id
AND POH.po_header_id = POHA.po_header_id (+)
AND POHA.latest_external_flag (+) = 'Y';
SELECT DISTINCT 'Y'
INTO x_header_control.po_acknowledged
FROM PO_ACCEPTANCES PA
WHERE PA.po_header_id = x_po_header_id;
SELECT DISTINCT 'Y'
INTO x_header_control.po_accepted
FROM PO_ACCEPTANCES PA
WHERE PA.po_header_id = x_po_header_id
AND PA.accepted_flag = 'Y';
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** Finish check_header_change ***');
SELECT
nvl(round(round(sum(
DECODE(POLL.matching_basis
, 'AMOUNT',
(nvl(POD.amount_ordered, 0) -
nvl(POD.amount_cancelled, 0)) *
nvl(POD.rate,1) /
nvl(X_min_unit,1)
, --QUANTITY
(nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) *
nvl(POLL.price_override, 0) *
nvl(POD.rate,1) /
nvl(X_min_unit,1))
)
* nvl(X_min_unit,1)/ nvl(X_base_min_unit,1)
)
* nvl(X_base_min_unit,1)) , 0)
INTO x_po_total
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL POLL
WHERE POD.po_header_id = x_po_header_id
AND POLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND POLL.line_location_id = POD.line_location_id;
SELECT
nvl(round(round(sum(
DECODE(POLLA.matching_basis
, 'AMOUNT',
(nvl(PODA.amount_ordered, 0) -
nvl(PODA.amount_cancelled, 0)) *
nvl(PODA.rate,1) /
nvl(X_min_unit,1)
, --QUANTITY
(nvl(PODA.quantity_ordered, 0) -
nvl(PODA.quantity_cancelled, 0)) *
nvl(POLLA.price_override, 0) *
nvl(PODA.rate,1) /
nvl(X_min_unit,1))
)
* nvl(X_min_unit,1)/ nvl(X_base_min_unit,1)
)
* nvl(X_base_min_unit,1)) , 0)
INTO x_po_total_archive
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA,
PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
WHERE PODA.po_header_id = x_po_header_id
AND POLLA.latest_external_flag (+) = 'Y'
AND PODA.latest_external_flag (+) = 'Y'
AND POLLA.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND POLLA.line_location_id = PODA.line_location_id;
SELECT sum(
DECODE(POLL.matching_basis
, 'AMOUNT',
(nvl(POD.amount_ordered, 0) -
nvl(POD.amount_cancelled, 0))
, --QUANTITY
(nvl(POD.quantity_ordered, 0) -
nvl(POD.quantity_cancelled, 0)) *
nvl(POLL.price_override, 0)))
INTO x_po_total
FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL POLL
WHERE POD.po_header_id = x_po_header_id
AND POLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND POLL.line_location_id = POD.line_location_id;
SELECT sum(
DECODE(POLLA.matching_basis
, 'AMOUNT',
(nvl(PODA.amount_ordered, 0) -
nvl(PODA.amount_cancelled, 0))
, --QUANTITY
(nvl(PODA.quantity_ordered, 0) -
nvl(PODA.quantity_cancelled, 0)) *
nvl(POLLA.price_override, 0)))
INTO x_po_total_archive
FROM PO_LINE_LOCATIONS_ARCHIVE_ALL POLLA,
PO_DISTRIBUTIONS_ARCHIVE_ALL PODA
WHERE PODA.po_header_id = x_po_header_id
AND POLLA.latest_external_flag (+) = 'Y'
AND PODA.latest_external_flag (+) = 'Y'
AND POLLA.shipment_type in ('STANDARD','PLANNED','BLANKET')
AND POLLA.line_location_id = PODA.line_location_id
AND PODA.po_header_id = POLLA.po_header_id;
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** In procedure set_wf_header_control ***');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** Finish set_wf_header_control ***');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** In procedure get_wf_header_control ***');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** FINISH: get_wf_header_control ***');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** In procedure get_wf_header_parameters ***');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'po_header_id = '|| to_char(x_header_parameters.po_header_id));
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** FINISH: get_wf_header_parameters ***');
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** In procedure: debug_header_control ***');
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'agent_id : ' ||x_header_control.agent_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'vendor_site_id : ' ||x_header_control.vendor_site_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'vendor_contact_id : ' ||x_header_control.vendor_contact_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'ship_to_location_id : ' ||x_header_control.ship_to_location_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'bill_to_location_id : ' ||x_header_control.bill_to_location_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'terms_id : ' ||x_header_control.terms_id);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'ship_via_lookup_code : ' ||x_header_control.ship_via_lookup_code);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'fob_lookup_code : ' ||x_header_control.fob_lookup_code);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'freight_terms_lookup_code: ' ||x_header_control.freight_terms_lookup_code);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'note_to_vendor : ' ||x_header_control.note_to_vendor);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'confirming_order_flag : ' ||x_header_control.confirming_order_flag);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'acceptance_required_flag : ' ||x_header_control.acceptance_required_flag);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'acceptance_due_date : ' ||x_header_control.acceptance_due_date);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'start_date : ' ||x_header_control.start_date);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'end_date : ' ||x_header_control.end_date);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'cancel_flag : ' ||x_header_control.cancel_flag);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'blanket_total_change : ' ||to_char(x_header_control.blanket_total_change));
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'amount_limit_change : ' ||to_char(x_header_control.amount_limit_change));
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'po_acknowledged : ' ||x_header_control.po_acknowledged);
PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey,
'po_accepted : ' ||x_header_control.po_accepted);
PO_WF_DEBUG_PKG.INSERT_DEBUG(ITEMTYPE, ITEMKEY,
'*** Finished: debug_header_control ***');