APPS.XDP_PURGE SQL Statements

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

Line: 137

	SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_HEADERS
		WHERE ORDER_ID = p_order_id;

Line: 147

	SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_RELATIONSHIPS
		WHERE ORDER_ID = p_order_id;

Line: 153

	SELECT COUNT(*) INTO l_no_records FROM XNP_MSGS
		WHERE ORDER_ID = p_order_id;

Line: 159

	SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_PARAMETERS
		WHERE ORDER_ID = p_order_id;

Line: 165

	SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_BUNDLES
		WHERE ORDER_ID = p_order_id;

Line: 171

	SELECT COUNT(*) INTO l_no_records FROM XNP_CALLBACK_EVENTS
		WHERE ORDER_ID = p_order_id;

Line: 176

	SELECT COUNT(*) INTO l_no_records FROM XNP_TIMER_REGISTRY
		WHERE ORDER_ID = p_order_id;

Line: 181

	SELECT COUNT(*) INTO l_no_records FROM XNP_SYNC_REGISTRATION
		WHERE ORDER_ID = p_order_id;

Line: 187

		SELECT wf_item_type,wf_item_key INTO l_wi_type,l_wi_key
			FROM XDP_ORDER_HEADERS
			WHERE ORDER_ID = p_order_id;

Line: 191

		SELECT count(*) INTO l_no_records
		FROM WF_ITEMS
		START WITH item_type = l_wi_type AND item_key = l_wi_key
    		CONNECT BY PRIOR item_key = parent_item_key
		AND PRIOR item_type = parent_item_type;

Line: 200

	SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_LINE_ITEMS
		WHERE ORDER_ID = p_order_id;

Line: 207

		FOR c_item IN (SELECT LINE_ITEM_ID FROM XDP_ORDER_LINE_ITEMS
			WHERE ORDER_ID = p_order_id)
		LOOP
			SELECT COUNT(*) INTO l_no_records FROM XDP_ORDER_LINEITEM_DETS
				WHERE LINE_ITEM_ID = c_item.LINE_ITEM_ID;

Line: 216

			SELECT COUNT(*) INTO l_no_records FROM XDP_LINE_RELATIONSHIPS
				WHERE LINE_ITEM_ID = c_item.LINE_ITEM_ID;

Line: 222

			SELECT COUNT(*) INTO l_no_records FROM XDP_FULFILL_WORKLIST
				WHERE LINE_ITEM_ID = c_item.LINE_ITEM_ID;

Line: 229

				FOR c_fw IN (SELECT WORKITEM_INSTANCE_ID FROM XDP_FULFILL_WORKLIST
						WHERE LINE_ITEM_ID = C_ITEM.LINE_ITEM_ID)
				LOOP
					SELECT COUNT(*) INTO l_temp FROM XDP_WI_RELATIONSHIPS
						WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;

Line: 239

					SELECT COUNT(*) INTO l_temp FROM XDP_WORKLIST_DETAILS
						WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;

Line: 246

					SELECT COUNT(*) INTO l_temp FROM XDP_FA_RUNTIME_LIST
						WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;

Line: 254

						FOR c_fa IN (SELECT FA_INSTANCE_ID FROM XDP_FA_RUNTIME_LIST
							WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID)
						LOOP
							SELECT COUNT(*) INTO l_temp FROM XDP_FA_DETAILS WHERE
								FA_INSTANCE_ID = c_fa.FA_INSTANCE_ID;

Line: 262

							SELECT COUNT(*) INTO l_temp FROM XDP_FE_CMD_AUD_TRAILS
								WHERE FA_INSTANCE_ID = c_fa.FA_INSTANCE_ID;

Line: 270

					SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUDIT_TRAILS
						WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID;

Line: 278

						SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUD_TRAIL_DETS WHERE FMC_ID IN
							(SELECT FMC_ID FROM XDP_FMC_AUDIT_TRAILS
							WHERE WORKITEM_INSTANCE_ID = c_fw.WORKITEM_INSTANCE_ID);

Line: 311

		FOR l_sv_soa IN (SELECT SV_SOA_ID FROM XNP_SV_SOA A, XNP_SV_STATUS_TYPES_B B
       					WHERE a.STATUS_TYPE_CODE = b.STATUS_TYPE_CODE
					AND b.PHASE_INDICATOR ='OLD'
					AND a.MODIFIED_DATE < p_time_to
					AND a.MODIFIED_DATE > p_time_from)
		LOOP
			SELECT COUNT(*) INTO l_no_records FROM XNP_SV_SOA_JN
				WHERE SV_SOA_ID = l_sv_soa.SV_SOA_ID;

Line: 323

			SELECT COUNT(*) INTO l_no_records FROM XNP_SV_FAILURES
				WHERE SV_SOA_ID = l_sv_soa.SV_SOA_ID;

Line: 329

			SELECT COUNT(*) INTO l_no_records FROM XNP_SV_EVENT_HISTORY
				WHERE SV_SOA_ID = l_sv_soa.SV_SOA_ID;

Line: 338

	SELECT COUNT(*) INTO l_no_records FROM XNP_SV_SOA WHERE SV_SOA_ID = p_sv_soa_id;

Line: 342

	SELECT COUNT(*) INTO l_no_records FROM XNP_SV_EVENT_HISTORY
       		WHERE SV_SOA_ID = p_sv_soa_id;

Line: 346

	SELECT COUNT(*) INTO l_no_records FROM XNP_SV_FAILURES
               	WHERE SV_SOA_ID = p_sv_soa_id;

Line: 350

	SELECT COUNT(*) INTO l_no_records FROM XNP_SV_SOA_JN
       		WHERE SV_SOA_ID = p_sv_soa_id;

Line: 380

    SELECT RELATED_ORDER_ID
    FROM XDP_ORDER_RELATIONSHIPS
    START WITH ORDER_ID = p_order_id
    CONNECT BY PRIOR RELATED_ORDER_ID = ORDER_ID;

Line: 395

        	    /* SELECT status_code,
                           completion_date
                      INTO l_order_state,
                           l_completion_date
        	      FROM XDP_ORDER_HEADERS
                     WHERE order_id = l_order_relation.related_order_id; */

Line: 402

		SELECT status_code,
                    NVL(completion_date, CANCEL_PROVISIONING_DATE)
                INTO l_order_state,
                    l_completion_date
        	FROM XDP_ORDER_HEADERS
                WHERE order_id = l_order_relation.related_order_id;

Line: 429

PROCEDURE DELETE_FULFILL_WORKLIST(
	p_line_item_id	IN XDP_ORDER_LINE_ITEMS.LINE_ITEM_ID%TYPE,
	p_run_mode	IN VARCHAR2
) IS
CURSOR c_fulfill_worklist IS
	SELECT WORKITEM_INSTANCE_ID FROM XDP_FULFILL_WORKLIST WHERE
	line_item_id = p_line_item_id
	FOR UPDATE NOWAIT;

Line: 439

	SELECT FA_INSTANCE_ID FROM XDP_FA_RUNTIME_LIST
	WHERE WORKITEM_INSTANCE_ID = p_workitem_instance_id
	FOR UPDATE NOWAIT;

Line: 455

				SELECT COUNT(*) INTO l_temp FROM XDP_FA_DETAILS
					WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;

Line: 460

				SELECT COUNT(*) INTO l_temp FROM XDP_FE_CMD_AUD_TRAILS
					WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;

Line: 467

				DELETE FROM XDP_FA_DETAILS WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;

Line: 468

				DELETE FROM XDP_FE_CMD_AUD_TRAILS WHERE FA_INSTANCE_ID = l_fa.FA_INSTANCE_ID;

Line: 473

			SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUD_TRAIL_DETS
				WHERE FMC_ID IN
					(SELECT FMC_ID FROM XDP_FMC_AUDIT_TRAILS
						WHERE WORKITEM_INSTANCE_ID
						= l_worklist.WORKITEM_INSTANCE_ID);

Line: 481

				SELECT COUNT(*) INTO l_temp FROM XDP_FA_RUNTIME_LIST
					WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 486

				SELECT COUNT(*) INTO l_temp FROM XDP_FMC_AUDIT_TRAILS
					WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 491

				SELECT COUNT(*) INTO l_temp FROM XDP_WI_RELATIONSHIPS
					WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 496

				SELECT COUNT(*) INTO l_temp FROM XDP_WORKLIST_DETAILS
					WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 503

			DELETE FROM XDP_FMC_AUD_TRAIL_DETS
				WHERE FMC_ID IN
					(SELECT FMC_ID FROM XDP_FMC_AUDIT_TRAILS
						WHERE WORKITEM_INSTANCE_ID
	                	    		= l_worklist.WORKITEM_INSTANCE_ID);

Line: 509

			DELETE FROM XDP_FA_RUNTIME_LIST
				WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 512

			DELETE FROM XDP_FMC_AUDIT_TRAILS
				WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 515

			DELETE FROM XDP_WI_RELATIONSHIPS
				WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 518

			DELETE FROM XDP_WORKLIST_DETAILS
				WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 521

			DELETE FROM XDP_FULFILL_WORKLIST
				WHERE WORKITEM_INSTANCE_ID = l_worklist.WORKITEM_INSTANCE_ID;

Line: 534

END DELETE_FULFILL_WORKLIST;

Line: 540

PROCEDURE DELETE_LINE_ITEMS
(
	p_order_id			IN XDP_ORDER_HEADERS.ORDER_ID%TYPE,
	p_run_mode			IN VARCHAR2
) IS

CURSOR c_line_item IS
	SELECT LINE_ITEM_ID
          FROM XDP_ORDER_LINE_ITEMS
         WHERE order_id = p_order_id
	   FOR UPDATE OF LINE_ITEM_ID NOWAIT;

Line: 562

		Delete_Fulfill_Worklist(l_line_item.line_item_id,p_run_mode);

Line: 565

				SELECT COUNT(*)
                                  INTO l_temp
                                  FROM XDP_ORDER_LINEITEM_DETS
				 WHERE line_item_id = l_line_item.line_item_id;

Line: 573

				SELECT COUNT(*)
                                  INTO l_temp
                                  FROM XDP_LINE_RELATIONSHIPS
				 WHERE line_item_id = l_line_item.line_item_id;

Line: 584

			DELETE FROM XDP_ORDER_LINEITEM_DETS
				WHERE line_item_id = l_line_item.line_item_id;

Line: 587

			DELETE FROM XDP_LINE_RELATIONSHIPS
				WHERE line_item_id = l_line_item.line_item_id;

Line: 590

			DELETE FROM XDP_ORDER_LINE_ITEMS WHERE CURRENT OF c_line_item;

Line: 599

END DELETE_LINE_ITEMS;

Line: 615

PROCEDURE DELETE_WF_ITEMS
(
	p_order_id			IN VARCHAR2,
	p_run_mode			IN VARCHAR2
) IS
CURSOR c_wi(p_wi_type IN VARCHAR2,p_wi_key IN VARCHAR2) IS
	SELECT level,item_type,item_key
	  FROM WF_ITEMS
	 START WITH item_type = p_wi_type
           AND item_key = p_wi_key
    	CONNECT BY PRIOR item_key = parent_item_key
  	AND PRIOR item_type = parent_item_type;

Line: 655

	SELECT wf_item_type,
               wf_item_key
          INTO l_wi_type,l_wi_key
	  FROM XDP_ORDER_HEADERS
         WHERE ORDER_ID = p_order_id;

Line: 674

			SELECT persistence_type INTO l_PType
				FROM wf_item_types
				WHERE NAME = l_wi.item_type;

Line: 694

END DELETE_WF_ITEMS;

Line: 738

	-- to be sure that the order can be deleted
    	-- On exceptions, FALSE will be returned, a message will be logged
    	-- and committed to database
    	--
	IS_ORDER_DELETABLE(p_order_id,p_time_from,p_time_to,l_order_deletable);

Line: 751

    	-- To delete the order
        -- On exceptions, a message will be logged and committed to database
        -- carries on to next order
        --
		IF l_order_deletable = 'TRUE' THEN
			BEGIN

				-- Lock the record for update

	      		FND_MESSAGE.SET_NAME('XDP','XDP_PURGE_ORD');

Line: 764

       			SELECT 1 INTO l_dummy FROM XDP_ORDER_HEADERS
	              		WHERE order_id = p_order_id for update nowait;

Line: 767

				DELETE_LINE_ITEMS(p_order_id,p_run_mode);

Line: 768

	            	DELETE_WF_ITEMS(p_order_id,p_run_mode);

Line: 771

		        		SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_BUNDLES
                       			WHERE order_id = p_order_id;

Line: 776

	        			SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_RELATIONSHIPS
                       			WHERE order_id = p_order_id;

Line: 780

	        			SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_PARAMETERS
                       			WHERE order_id = p_order_id;

Line: 791

   	                		SELECT COUNT(*) INTO l_temp FROM XNP_MSGS
           		       		WHERE order_id = p_order_id;

Line: 794

                  			SELECT COUNT(*) INTO l_temp FROM XNP_MSGS
        	                		WHERE order_id = p_order_id
						AND msg_code IN
							(SELECT MSG_CODE FROM XNP_MSG_TYPES_B
								WHERE PROTECTED_FLAG='Y');

Line: 803

		        		SELECT COUNT(*) INTO l_temp FROM XNP_CALLBACK_EVENTS
       		                	WHERE order_id = p_order_id;

Line: 807

	        			SELECT COUNT(*) INTO l_temp FROM XNP_TIMER_REGISTRY
	                        	WHERE order_id = p_order_id;

Line: 811

					SELECT COUNT(*) INTO l_temp FROM XNP_SYNC_REGISTRATION
                       			WHERE order_id = p_order_id;

Line: 818

		        		DELETE FROM XDP_ORDER_BUNDLES
                        		WHERE order_id = p_order_id;

Line: 820

		        		DELETE FROM XDP_ORDER_RELATIONSHIPS
               	        		WHERE order_id = p_order_id;

Line: 822

		        		DELETE FROM XDP_ORDER_PARAMETERS
                       			WHERE order_id = p_order_id;

Line: 830

               					DELETE FROM XNP_MSGS
   		        			WHERE order_id = p_order_id;

Line: 833

           					DELETE FROM XNP_MSGS
               					WHERE order_id = p_order_id
						AND msg_code IN
							(SELECT MSG_CODE FROM XNP_MSG_TYPES_B
							WHERE PROTECTED_FLAG='Y');

Line: 840

        				DELETE FROM XNP_CALLBACK_EVENTS
               				WHERE order_id = p_order_id;

Line: 843

	        			DELETE FROM XNP_TIMER_REGISTRY
	                		WHERE order_id = p_order_id;

Line: 845

					DELETE FROM XNP_SYNC_REGISTRATION
                  			WHERE order_id = p_order_id ;

Line: 847

	       				DELETE FROM XDP_ORDER_HEADERS
	               			WHERE order_id = p_order_id ;

Line: 891

	SELECT ORDER_ID FROM XDP_ORDER_HEADERS
        WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
	   AND COMPLETION_DATE < p_time_to
	   AND COMPLETION_DATE > p_time_from; */

Line: 897

	SELECT ORDER_ID FROM XDP_ORDER_HEADERS
        WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
	   AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) < p_time_to
	   AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) > p_time_from;

Line: 925

		/* SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_HEADERS
			WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
			AND COMPLETION_DATE < p_time_to
			AND COMPLETION_DATE > p_time_from; */

Line: 930

		SELECT COUNT(*) INTO l_temp FROM XDP_ORDER_HEADERS
		WHERE STATUS_CODE IN ('SUCCESS','SUCCESS_WITH_OVERRIDE','ABORTED','CANCELED')
		AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) < p_time_to
		AND NVL(COMPLETION_DATE, CANCEL_PROVISIONING_DATE) > p_time_from;

Line: 1034

	DELETE FROM xnp_timer_registry WHERE timer_id = p_msg_id;

Line: 1063

	SELECT MSG_ID FROM XNP_MSGS M
    WHERE
        (M.MSG_STATUS IN ('PROCESSED','TIME_OUT'))
        AND MSG_CREATION_DATE < p_time_to
    	AND MSG_CREATION_DATE > p_time_from
    	FOR UPDATE OF MSG_ID NOWAIT;

Line: 1072

	SELECT MSG_ID FROM XNP_MSGS M
    WHERE
        (M.MSG_STATUS IN ('PROCESSED','TIME_OUT'))
        AND MSG_CREATION_DATE < p_time_to
    	AND MSG_CREATION_DATE > p_time_from
        AND NOT Exists (SELECT ORDER_ID from xdp_order_headers where (order_id=m.order_id))
    	FOR UPDATE OF MSG_ID NOWAIT;

Line: 1094

        	SELECT COUNT(*) INTO l_no_msgs FROM XNP_MSGS
            WHERE
                MSG_STATUS IN ('PROCESSED','TIME_OUT')
                AND MSG_CREATION_DATE < p_time_to
            	AND MSG_CREATION_DATE > p_time_from;

Line: 1100

        	SELECT COUNT(*) INTO l_no_msgs FROM XNP_MSGS M
            WHERE
                -- 03/30/2001. Modified. Replaced CREATION_DATE with MSG_CREATION_DATE. rnyberg
                MSG_STATUS IN ('PROCESSED','TIME_OUT')
                AND MSG_CREATION_DATE < p_time_to
            	AND MSG_CREATION_DATE > p_time_from
                AND NOT Exists (SELECT ORDER_ID from xdp_order_headers where (order_id=m.order_id));

Line: 1139

			     	DELETE FROM XNP_MSGS WHERE CURRENT OF c_xnp_msgs_1;

Line: 1150

					DELETE FROM XNP_MSGS WHERE CURRENT OF c_xnp_msgs_2;

Line: 1207

	SELECT SV_SOA_ID FROM XNP_SV_SOA A, XNP_SV_STATUS_TYPES_B B
       		WHERE a.STATUS_TYPE_CODE = b.STATUS_TYPE_CODE
		AND b.PHASE_INDICATOR ='OLD'
		AND a.MODIFIED_DATE < p_time_to
		AND a.MODIFIED_DATE > p_time_from FOR UPDATE OF A.SV_SOA_ID NOWAIT;

Line: 1232

		SELECT COUNT(*) INTO l_temp FROM XNP_SV_SOA A, XNP_SV_STATUS_TYPES_B B
       			WHERE a.STATUS_TYPE_CODE = b.STATUS_TYPE_CODE
			AND b.PHASE_INDICATOR ='OLD'
			AND a.MODIFIED_DATE < p_time_to
			AND a.MODIFIED_DATE > p_time_from;

Line: 1252

       			SELECT COUNT(*) INTO l_temp FROM XNP_SV_EVENT_HISTORY
               		WHERE SV_SOA_ID = l_xnp_soa.sv_soa_id;

Line: 1256

       			SELECT COUNT(*) INTO l_temp FROM XNP_SV_FAILURES
               		WHERE SV_SOA_ID = l_xnp_soa.sv_soa_id;

Line: 1260

			SELECT COUNT(*) INTO l_temp FROM XNP_SV_SOA_JN
               		WHERE SV_SOA_ID = l_xnp_soa.sv_soa_id;

Line: 1284

					DELETE FROM XNP_SV_EVENT_HISTORY WHERE
						SV_SOA_ID = l_xnp_soa_id;

Line: 1286

    	       				DELETE FROM XNP_SV_FAILURES WHERE
                				SV_SOA_ID = l_xnp_soa_id;

Line: 1288

        	   			DELETE FROM XNP_SV_SOA_JN WHERE
                				SV_SOA_ID = l_xnp_soa_id;

Line: 1290

					DELETE FROM XNP_SV_SOA WHERE CURRENT OF c_xnp_soa;

Line: 1344

    SELECT DEBUG_TYPE FROM XDP_DEBUG
    WHERE LAST_UPDATE_DATE < p_time_to
    AND LAST_UPDATE_DATE > p_time_from FOR UPDATE OF DEBUG_TYPE NOWAIT;

Line: 1349

    SELECT ERROR_ID FROM XDP_ERROR_LOG
    WHERE LAST_UPDATE_DATE < p_time_to
    AND LAST_UPDATE_DATE > p_time_from FOR UPDATE OF ERROR_ID NOWAIT;

Line: 1355

    SELECT DEBUG_ID FROM XNP_DEBUG
    WHERE LAST_UPDATE_DATE < p_time_to
    AND LAST_UPDATE_DATE > p_time_from FOR UPDATE OF DEBUG_ID NOWAIT;

Line: 1376

	SELECT COUNT(*) INTO l_no_reords FROM XDP_DEBUG
		WHERE LAST_UPDATE_DATE < p_time_to
		AND LAST_UPDATE_DATE > p_time_from;

Line: 1396

        			DELETE FROM XDP_DEBUG WHERE CURRENT OF c_xdp_debug;

Line: 1415

	SELECT COUNT(*) INTO l_no_reords FROM XDP_ERROR_LOG
		WHERE LAST_UPDATE_DATE < p_time_to
		AND LAST_UPDATE_DATE > p_time_from;

Line: 1437

					DELETE FROM XDP_ERROR_LOG WHERE CURRENT OF c_xdp_errors;

Line: 1454

	SELECT COUNT(*) INTO l_no_reords FROM XNP_DEBUG
		WHERE LAST_UPDATE_DATE < p_time_to
		AND LAST_UPDATE_DATE > p_time_from;

Line: 1474

        			DELETE FROM XNP_DEBUG WHERE CURRENT OF c_xnp_debug;

Line: 1491

	SELECT COUNT(*) INTO l_no_reords FROM WF_ITEMS
		WHERE ITEM_TYPE='XDPRECOV' AND END_DATE < p_time_to;

Line: 1499

			SELECT persistence_type INTO WF_PURGE.PERSISTENCE_TYPE
			FROM wf_item_types WHERE NAME = 'XDPRECOV';