APPS.OEXPURGE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 4

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;

Line: 25

	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;

Line: 49

	DELETE FROM SO_PURGE_ORDERS
	WHERE REQUEST_ID IS NULL;

Line: 87

	        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);

Line: 127

END select_purge_orders;

Line: 134

	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;

Line: 169

	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';

Line: 202

	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;

Line: 236

	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';

Line: 310

CURSOR purging IS SELECT header_id FROM so_purge_orders;

Line: 328

         *                  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);

Line: 397

             DELETE FROM so_purge_orders    --    Delete the purge record
		   WHERE  header_id = v_header_id;

Line: 441

	SELECT picking_header_id
	FROM   so_freight_charges
	WHERE  picking_header_id = p_picking_header_id
	FOR UPDATE NOWAIT;

Line: 449

	DELETE FROM so_freight_charges       -- Delete all rows to be purged
	WHERE  picking_header_id = p_picking_header_id;

Line: 477

	SELECT header_id            --  Lock all rows to be purged
	FROM   so_headers
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 485

	DELETE FROM so_headers
	WHERE  header_id = p_header_id;

Line: 513

	SELECT hold_release_id      --  Lock rows to be purged
	FROM   so_hold_releases
	WHERE  hold_release_id = p_release_id
	FOR UPDATE NOWAIT;

Line: 521

	DELETE FROM so_hold_releases
	WHERE  hold_release_id = p_release_id;

Line: 549

	SELECT hold_source_id       --  Lock rows to be purged
	FROM   so_hold_sources
	WHERE  hold_source_id = p_source_id
	FOR UPDATE NOWAIT;

Line: 557

	DELETE FROM   so_hold_sources
	WHERE  hold_source_id = p_source_id;

Line: 585

	SELECT header_id            --  Lock rows to be purged
	FROM   so_line_approvals
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 593

	DELETE FROM   so_line_approvals
	WHERE  header_id = p_header_id;

Line: 621

	SELECT line_id
	FROM   so_line_details
	WHERE  line_id = p_line_id
	FOR UPDATE NOWAIT;

Line: 629

	DELETE FROM   so_line_details
	WHERE  line_id = p_line_id;

Line: 657

	SELECT line_id
	FROM   so_line_service_details
	WHERE  line_id = p_line_id
	FOR UPDATE NOWAIT;

Line: 665

	DELETE FROM   so_line_service_details
	WHERE  line_id = p_line_id;

Line: 694

	SELECT DISTINCT line_id   -- Select the unique line_ids to be purged
	FROM   so_lines
	WHERE  header_id = p_header_id;

Line: 706

	    SELECT line_id
	    INTO   syntax_required
	    FROM   so_lines
	    WHERE  line_id = v_line_id
	    FOR UPDATE NOWAIT;

Line: 731

	        DELETE FROM   so_lines
	        WHERE  line_id = v_line_id;

Line: 765

	SELECT header_id            --  Lock rows to be purged
	FROM   so_note_references
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 773

	DELETE FROM   so_note_references
	WHERE  header_id = p_header_id;

Line: 801

	SELECT header_id            --  Lock rows to be purged
	FROM   so_order_approvals
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 809

	DELETE FROM   so_order_approvals
	WHERE  header_id = p_header_id;

Line: 843

	SELECT line_id            --  Lock rows to be purged
	FROM   so_order_cancellations
	WHERE  line_id = p_line_id
	FOR UPDATE NOWAIT;

Line: 851

	DELETE FROM   so_order_cancellations
	WHERE  line_id = p_line_id;

Line: 875

 * 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;

Line: 888

	SELECT rma_interface_id            --  Lock rows to be purged
	FROM   mtl_so_rma_interface
	WHERE  rma_line_id = p_line_id;

Line: 901

         	    DELETE FROM mtl_so_rma_interface
        	    WHERE  rma_interface_id = v_rma_interface_id;

Line: 932

 *    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;

Line: 945

	SELECT rma_receipt_id            --  Lock rows to be purged
	FROM   mtl_so_rma_receipts
	WHERE  rma_interface_id = p_rma_interface_id;

Line: 955

            DELETE FROM mtl_so_rma_receipts
        	WHERE  rma_receipt_id = v_rma_receipt_id;

Line: 984

	SELECT header_id            --  Lock rows to be purged
	FROM   so_order_cancellations
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 992

	DELETE FROM   so_order_cancellations
	WHERE  header_id = p_header_id;

Line: 1022

	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;

Line: 1048

		SELECT header_id
		INTO   syntax_required
		FROM   so_order_holds
		WHERE  order_hold_id = v_order_hold_id
		FOR UPDATE NOWAIT;

Line: 1054

	        DELETE FROM   so_order_holds
	        WHERE  order_hold_id = v_order_hold_id;

Line: 1089

	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;

Line: 1098

	DELETE FROM   so_picking_batches
	WHERE  batch_id  = p_batch_id
	AND    header_id = p_header_id;

Line: 1126

	SELECT picking_line_id      --  Lock rows to be purged
	FROM   so_picking_cancellations
	WHERE  picking_line_id = p_picking_line_id
	FOR UPDATE NOWAIT;

Line: 1134

	DELETE FROM   so_picking_cancellations
	WHERE  picking_line_id = p_picking_line_id;

Line: 1164

	SELECT picking_header_id,
	       batch_id
	FROM   so_picking_headers
	WHERE  order_header_id = p_header_id;

Line: 1177

	    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;

Line: 1199

	        DELETE FROM   so_picking_headers
	        WHERE  picking_header_id = v_picking_header_id
	        AND    batch_id          = v_batch_id;

Line: 1233

	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;

Line: 1241

	DELETE FROM   so_picking_line_details
	WHERE  picking_line_id = p_picking_line_id;

Line: 1270

	SELECT picking_line_id
	FROM   so_picking_lines
	WHERE  picking_header_id = p_picking_header_id;

Line: 1284

	    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;

Line: 1298

	        DELETE FROM   so_picking_lines
	        WHERE  picking_line_id = v_picking_line_id;

Line: 1333

        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;

Line: 1349

            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;

Line: 1362

                DELETE FROM   so_picking_lines
                WHERE  picking_line_id = v_picking_line_id;

Line: 1396

	SELECT header_id            --  Lock rows to be purged
	FROM   so_picking_rules
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 1404

	DELETE FROM   so_picking_rules
	WHERE  header_id = p_header_id;

Line: 1432

	SELECT header_id            --  Lock rows to be purged
	FROM   so_price_adjustments
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 1440

	DELETE FROM   so_price_adjustments
	WHERE  header_id = p_header_id;

Line: 1468

	SELECT header_id            --  Lock rows to be purged
	FROM   so_sales_credits
	WHERE  header_id = p_header_id
	FOR UPDATE NOWAIT;

Line: 1476

	DELETE FROM so_sales_credits
	WHERE  header_id = p_header_id;

Line: 1517

	SELECT so_exceptions_s.NEXTVAL
	INTO   v_exception_id
	FROM   DUAL;

Line: 1521

	SELECT UID
	INTO   v_uid
	FROM   dual;

Line: 1525

	SELECT application_id
	INTO   v_application_id
	FROM   fnd_application
	WHERE  application_short_name = 'OE';

Line: 1531

        SELECT concurrent_program_id
        INTO v_program_id
        FROM fnd_concurrent_programs
        WHERE application_id=v_application_id
        AND concurrent_program_name='OEXPURGE';

Line: 1537

	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