The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE select_purge_orders
(dummy_1 VARCHAR2,
dummy_2 VARCHAR2,
p_low_order_number NUMBER,
p_high_order_number NUMBER,
p_low_cdate DATE,
p_high_cdate DATE,
p_low_ddate DATE,
p_high_ddate DATE,
p_order_category VARCHAR2,
p_order_type_id NUMBER,
p_customer_id NUMBER)
IS
a_id NUMBER := 0;
SELECT so.header_id
, so.order_number
, sot.name
FROM so_order_types sot,
so_headers so
WHERE so.order_number BETWEEN NVL(p_low_order_number,so.order_number)
AND NVL( p_high_order_number, so.order_number )
-- The time component in Order Date and Creation Date has been stripped off
-- before making comparision. Bug# 916858 - propagated from Rel. 11
-- ( Bug# 914321 ).
AND TRUNC(so.creation_date) BETWEEN
NVL( TRUNC(p_low_cdate), TRUNC(so.creation_date) )
AND NVL( TRUNC(p_high_cdate), TRUNC(so.creation_date) )
AND TRUNC(so.date_ordered) BETWEEN
NVL( TRUNC(p_low_ddate), TRUNC(so.date_ordered) )
AND NVL( TRUNC(p_high_ddate), TRUNC(so.date_ordered) )
AND so.order_category = NVL( p_order_category, so.order_category )
AND so.order_type_id = NVL( p_order_type_id, so.order_type_id )
AND so.customer_id = NVL( p_customer_id, so.customer_id )
AND sot.order_type_id = so.order_type_id
AND so.open_flag is null;
DELETE FROM SO_PURGE_ORDERS
WHERE REQUEST_ID IS NULL;
INSERT INTO SO_PURGE_ORDERS
(HEADER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID)
VALUES (a_id,
sysdate,
-1,
sysdate,
-1,
NULL,
NULL,
0,
300);
END select_purge_orders;
SELECT 'Open demand for this sales order'
FROM mtl_sales_orders mso,
mtl_demand md
WHERE mso.segment1 = p_order_number
AND mso.segment2 = p_order_type_name
AND mso.sales_order_id = md.demand_source_header_id
AND md.demand_source_type IN (2,8)
AND md.primary_uom_quantity > NVL( md.completed_quantity, 0 )
AND md.row_status_flag = 1;
SELECT 'Open invoices for this sales order'
FROM ra_customer_trx_lines rctl,
ra_customer_trx rct
WHERE rctl.interface_line_attribute1 = p_order_number
AND rctl.interface_line_attribute2 = p_order_type_name
AND rctl.customer_trx_id = rct.customer_trx_id
AND rct.complete_flag = 'N';
SELECT 'Open work orders for this sales order'
FROM mtl_sales_orders mso,
wip_so_allocations wsa,
wip_discrete_jobs wdj
WHERE mso.segment1 = p_order_number
AND mso.segment2 = p_order_type_name
AND mso.sales_order_id = wsa.demand_source_header_id
AND wsa.wip_entity_id = wdj.wip_entity_id
AND wdj.date_closed IS NULL
AND wdj.status_type = 1;
SELECT 'Open return for this sales order'
FROM so_lines sl1,
so_lines sl2,
so_headers sh,
so_order_types ot
WHERE sh.order_number = p_order_number
AND sh.order_type_id = ot.order_type_id
AND ot.name = p_order_type_name
AND sl1.header_id = sh.header_id
AND sl2.return_reference_id = sl1.line_id
AND sl2.line_type_code = 'RETURN'
AND sl2.return_reference_type_code IN ( 'ORDER', 'PO' )
AND nvl(sl2.open_flag, 'N') = 'Y';
CURSOR purging IS SELECT header_id FROM so_purge_orders;
* 0 = Success, rows deleted/purged *
* 100, 1403 are considered to be successful *
* Failure: Any negative value. Processing is rolled back. *
* Error has already been recorded in so_exceptions *
*********************************************************************/
return_status := OEXPURGE.so_purge_headers( v_header_id,
p_request_id);
DELETE FROM so_purge_orders -- Delete the purge record
WHERE header_id = v_header_id;
SELECT picking_header_id
FROM so_freight_charges
WHERE picking_header_id = p_picking_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_freight_charges -- Delete all rows to be purged
WHERE picking_header_id = p_picking_header_id;
SELECT header_id -- Lock all rows to be purged
FROM so_headers
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_headers
WHERE header_id = p_header_id;
SELECT hold_release_id -- Lock rows to be purged
FROM so_hold_releases
WHERE hold_release_id = p_release_id
FOR UPDATE NOWAIT;
DELETE FROM so_hold_releases
WHERE hold_release_id = p_release_id;
SELECT hold_source_id -- Lock rows to be purged
FROM so_hold_sources
WHERE hold_source_id = p_source_id
FOR UPDATE NOWAIT;
DELETE FROM so_hold_sources
WHERE hold_source_id = p_source_id;
SELECT header_id -- Lock rows to be purged
FROM so_line_approvals
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_line_approvals
WHERE header_id = p_header_id;
SELECT line_id
FROM so_line_details
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_line_details
WHERE line_id = p_line_id;
SELECT line_id
FROM so_line_service_details
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_line_service_details
WHERE line_id = p_line_id;
SELECT DISTINCT line_id -- Select the unique line_ids to be purged
FROM so_lines
WHERE header_id = p_header_id;
SELECT line_id
INTO syntax_required
FROM so_lines
WHERE line_id = v_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_lines
WHERE line_id = v_line_id;
SELECT header_id -- Lock rows to be purged
FROM so_note_references
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_note_references
WHERE header_id = p_header_id;
SELECT header_id -- Lock rows to be purged
FROM so_order_approvals
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_order_approvals
WHERE header_id = p_header_id;
SELECT line_id -- Lock rows to be purged
FROM so_order_cancellations
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_order_cancellations
WHERE line_id = p_line_id;
* 2. Inventory can't delete the item with error message: *
* RMA's exist for this item in the interface table *
* (MTL_SO_RMA_INTERFACE) *
*************************************************************/
FUNCTION so_purge_mtl_so_rma_interface
( p_line_id NUMBER,
p_request_id NUMBER ) RETURN NUMBER IS
return_status NUMBER := 0;
SELECT rma_interface_id -- Lock rows to be purged
FROM mtl_so_rma_interface
WHERE rma_line_id = p_line_id;
DELETE FROM mtl_so_rma_interface
WHERE rma_interface_id = v_rma_interface_id;
* Inventory can't delete the item with error message: *
* RMA's exist for this item in the interface table *
* (MTL_SO_RMA_INTERFACE) *
*************************************************************/
FUNCTION so_purge_mtl_so_rma_receipts
( p_rma_interface_id NUMBER,
p_request_id NUMBER ) RETURN NUMBER IS
return_status NUMBER := 0;
SELECT rma_receipt_id -- Lock rows to be purged
FROM mtl_so_rma_receipts
WHERE rma_interface_id = p_rma_interface_id;
DELETE FROM mtl_so_rma_receipts
WHERE rma_receipt_id = v_rma_receipt_id;
SELECT header_id -- Lock rows to be purged
FROM so_order_cancellations
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_order_cancellations
WHERE header_id = p_header_id;
SELECT DISTINCT NVL( hold_release_id, 0 ),
NVL( hold_source_id, 0 ),
order_hold_id
FROM so_order_holds
WHERE header_id = p_header_id;
SELECT header_id
INTO syntax_required
FROM so_order_holds
WHERE order_hold_id = v_order_hold_id
FOR UPDATE NOWAIT;
DELETE FROM so_order_holds
WHERE order_hold_id = v_order_hold_id;
SELECT batch_id -- Lock rows to be purged
FROM so_picking_batches
WHERE batch_id = p_batch_id
AND header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_batches
WHERE batch_id = p_batch_id
AND header_id = p_header_id;
SELECT picking_line_id -- Lock rows to be purged
FROM so_picking_cancellations
WHERE picking_line_id = p_picking_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_cancellations
WHERE picking_line_id = p_picking_line_id;
SELECT picking_header_id,
batch_id
FROM so_picking_headers
WHERE order_header_id = p_header_id;
SELECT order_header_id -- Lock rows to be purged
INTO syntax_required
FROM so_picking_headers
WHERE picking_header_id = v_picking_header_id
AND batch_id = v_batch_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_headers
WHERE picking_header_id = v_picking_header_id
AND batch_id = v_batch_id;
SELECT picking_line_id -- Lock rows to be purged
FROM so_picking_line_details
WHERE picking_line_id = p_picking_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_line_details
WHERE picking_line_id = p_picking_line_id;
SELECT picking_line_id
FROM so_picking_lines
WHERE picking_header_id = p_picking_header_id;
SELECT picking_line_id -- Lock rows to be purged
INTO syntax_required
FROM so_picking_lines
WHERE picking_line_id = v_picking_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_lines
WHERE picking_line_id = v_picking_line_id;
SELECT picking_line_id
FROM so_picking_lines spl, so_lines sl
WHERE spl.picking_header_id = -1
AND sl.line_id = spl.order_line_id
AND sl.header_id = p_header_id;
SELECT picking_line_id -- Lock rows to be purged
INTO syntax_required
FROM so_picking_lines
WHERE picking_line_id = v_picking_line_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_lines
WHERE picking_line_id = v_picking_line_id;
SELECT header_id -- Lock rows to be purged
FROM so_picking_rules
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_picking_rules
WHERE header_id = p_header_id;
SELECT header_id -- Lock rows to be purged
FROM so_price_adjustments
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_price_adjustments
WHERE header_id = p_header_id;
SELECT header_id -- Lock rows to be purged
FROM so_sales_credits
WHERE header_id = p_header_id
FOR UPDATE NOWAIT;
DELETE FROM so_sales_credits
WHERE header_id = p_header_id;
SELECT so_exceptions_s.NEXTVAL
INTO v_exception_id
FROM DUAL;
SELECT UID
INTO v_uid
FROM dual;
SELECT application_id
INTO v_application_id
FROM fnd_application
WHERE application_short_name = 'OE';
SELECT concurrent_program_id
INTO v_program_id
FROM fnd_concurrent_programs
WHERE application_id=v_application_id
AND concurrent_program_name='OEXPURGE';
INSERT INTO so_exceptions -- record an error
VALUES ( v_exception_id, -- sequence number
SYSDATE, -- creation date
v_uid, -- created by
SYSDATE, -- last updated date
v_uid, -- last updated by
v_uid, -- last update login
p_request_id, -- request id
v_application_id, -- program application id
v_program_id, -- program_id
SYSDATE, -- last date changed by current pgm
p_context, -- object on which exception occurred
p_id_number, -- id number,
v_message_text ); -- SQLERRM