APPS.WSH_PURGE SQL Statements

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

Line: 72

				p_delete_beyond_x_ship_days number,
				p_dummy_x_ship_days varchar2,
				p_purge_intransit_trips varchar2,
				p_delete_empty_records varchar2,
				p_create_date_from varchar2,
				p_create_date_to varchar2,
				p_dummy_create_date varchar2,
				p_del_beyond_creation_days number,
				p_dummy_x_create_days varchar2,
				p_sort_per_criteria varchar2,
				p_print_detail varchar2
		     )IS

l_return_status VARCHAR2(1);

Line: 123

	    WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);

Line: 125

	    WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);

Line: 145

			p_delete_beyond_x_ship_days	=> p_delete_beyond_x_ship_days ,
			p_purge_intransit_trips 	=> p_purge_intransit_trips ,
			p_delete_empty_records 		=> p_delete_empty_records ,
			p_create_date_from     		=> p_create_date_from ,
			p_create_date_to       		=> p_create_date_to ,
			p_del_beyond_creation_days 	=> p_del_beyond_creation_days ,
			x_tbl_trip_purge_set		=> l_tbl_trip_purge_set  ,
			x_tbl_delivery_purge_set	=> l_tbl_delivery_purge_set,
			x_tbl_del_details_purge_set 	=> l_tbl_del_detail_purge_set,
			x_tbl_del_legs_purge_set	=> l_tbl_del_leg_purge_set ,
			x_tbl_trip_stops_purge_set	=> l_tbl_trip_stop_purge_set,
			x_tbl_containers_purge_set   	=> l_tbl_container_purge_set,
			x_return_status    		=> l_return_status
	              );

Line: 188

				p_delete_beyond_x_ship_days	=> p_delete_beyond_x_ship_days ,
				p_purge_intransit_trips		=> p_purge_intransit_trips ,
				p_delete_empty_records		=> p_delete_empty_records ,
				p_create_date_from   		=> p_create_date_from ,
				p_create_date_to     		=> p_create_date_to ,
				p_del_beyond_creation_days 	=> p_del_beyond_creation_days ,
				p_sort_per_criteria		=> p_sort_per_criteria ,
				p_print_detail   		=> p_print_detail ,
				p_tbl_trip_purge_set		=> l_tbl_trip_purge_set  ,
				p_tbl_delivery_purge_set	=> l_tbl_delivery_purge_set,
				p_tbl_container_purge_set	=> l_tbl_container_purge_set,
				p_count_legs    		=> l_tbl_del_leg_purge_set.COUNT ,
				p_count_stops      		=> l_tbl_trip_stop_purge_set.COUNT,
				p_count_details    		=> l_tbl_del_detail_purge_set.COUNT,
				p_count_containers        	=> l_tbl_container_purge_set.COUNT,
				x_return_status			=> l_return_status
			     );

Line: 254

	l_tbl_trip_purge_set.DELETE;

Line: 255

	l_tbl_delivery_purge_set.DELETE;

Line: 256

	l_tbl_del_detail_purge_set.DELETE;

Line: 257

	l_tbl_del_leg_purge_set.DELETE;

Line: 258

	l_tbl_trip_stop_purge_set.DELETE;

Line: 259

	l_tbl_container_purge_set.DELETE;

Line: 284

            p_delete_beyond_x_ship_days  Only the deliveries having initial_pickup_date greater
            than the specified date would be considered eligible for Purge
            p_purge_intransit_trips Decides whether to purge In Transit Trips or not
            p_delete_empty_records Decides whether to delete empty record or not.
            The empty records can be Empty Trips, Orphaned Empty Deliveries,
            Delivery with Empty containers, Empty Containers
            p_create_date_from Only Empty records having creation_date greater than this
            date would be purged
            p_create_date_to Only Empty records having creation_date less than this
            date would be purged
            p_del_beyond_creation_days Only Empty records having creation_date greater than
            this date would be purged
            x_tbl_trip_purge_set - pl/sql table of trip id's eligible for purge
            x_tbl_delivery_purge_set -  pl/sql table of delivery id's eligible for purge
            x_tbl_del_details_purge_set - pl/sql table of delivery detail id's eligible for purge
            x_tbl_del_legs_purge_set - pl/sql table of delivery leg id's eligible for purge
            x_tbl_trip_stops_purge_set - pl/sql table of trip stop id's eligible for purge
            x_tbl_containers_purge_set - pl/sql table of container id's eligible for purge
            x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success

Description: This API gets all the Shipping Data from the view WSH_PURGE_SET_V
             and puts it into the plsql tables for respective entities after validating it
             with the user given parameters
==============================================================================
Input: Parameters as given in the FDD.
Output: Table of Record Types for Trips, Stops, Legs,
        Deliveries, Containers, Details
================================================================================
Logic: i) Build Trip SQL for purge set types:
          NON_EMPTY - Complete Shipping Entities

      ii) Build Delivery SQL for purge set type:
          EMPTYDELS - Orphaned empty deliveries without any details

          only Delete Empty Records, creation date from, to and delete beyond
          x creation days will be honoured.

     iii) Execute the Trip ,Delivery and Container dynamically built SQLs and populate the
          Table of record types for trip,delivery and containers.

	  Add Trip IDs for purge set types :
	  EMPTYTRIPS - Orphaned Trips without any deliveries assigned to them

      iv) For NON_EMPTY purge sets get the deliveries for all
          trips and add delivery ids to the purge set for deliveries.

       v) For a given trip get all the stops and create a stops purge set.

      vi) For a given delivery get all the delivery legs and details and create
          a purge set.

     vii) From NON_EMPTY get all the container ids(wdd.container_flag='Y') and
          populate them in container purge set
-----------------------------------------------------------------------------*/

PROCEDURE Get_Purge_Set(p_source_system varchar2,
			p_ship_from_org number,
			p_order_number_from varchar2,
			p_order_number_to varchar2,
			p_order_type number,
			p_ship_date_from varchar2,
			p_ship_date_to varchar2,
			p_delete_beyond_x_ship_days number,
			p_purge_intransit_trips varchar2,
			p_delete_empty_records varchar2,
			p_create_date_from varchar2,
			p_create_date_to varchar2,
			p_del_beyond_creation_days number,
			x_tbl_trip_purge_set OUT  NOCOPY Trip_ID_Tbl_Type ,
			x_tbl_delivery_purge_set OUT  NOCOPY Delivery_ID_Tbl_Type,
			x_tbl_del_details_purge_set OUT  NOCOPY Del_Detail_ID_Tbl_Type,
			x_tbl_del_legs_purge_set OUT  NOCOPY Del_Leg_ID_Tbl_Type,
			x_tbl_trip_stops_purge_set OUT  NOCOPY Trip_Stop_ID_Tbl_Type,
			x_tbl_containers_purge_set OUT  NOCOPY Container_ID_Tbl_Type,
			x_return_status OUT  NOCOPY VARCHAR2
			)IS

	l_debug_on BOOLEAN;

Line: 403

	SELECT	distinct wnd.delivery_id, wnd.name
	FROM	wsh_trips wt,
		wsh_trip_stops wts,
		wsh_delivery_legs wdl,
		wsh_new_deliveries wnd
	WHERE	wt.trip_id = wts.trip_id
	AND	wts.stop_id = wdl.pick_up_stop_id
	AND	wdl.delivery_id = wnd.delivery_id
	AND	wts.trip_id = p_tripid;

Line: 414

	SELECT	stop_id
	FROM	wsh_trip_stops
	WHERE	trip_id= p_tripid;

Line: 419

	SELECT	delivery_leg_id
	FROM	wsh_delivery_legs
	WHERE	delivery_id = p_deliveryid;

Line: 425

	SELECT	wda.delivery_detail_id,
		wdd.container_flag
	FROM	wsh_delivery_assignments_v wda,
		wsh_delivery_details wdd
	WHERE	wda.delivery_id = p_deliveryid
	AND	wda.delivery_detail_id = wdd.delivery_detail_id
	UNION
    SELECT  wdd_can.delivery_detail_id,
            'N'
    FROM    wsh_delivery_assignments_v wda,
            wsh_delivery_details wdd,
            wsh_delivery_details wdd_can
    WHERE   wda.delivery_id = p_deliveryid
    AND     wda.delivery_detail_id  = wdd.delivery_detail_id
    AND     wdd_can.source_line_id  = wdd.source_line_id
    AND     wdd.source_code         = 'OE'
    AND     wdd_can.source_code     = 'OE'
    AND     wdd_can.released_status = 'D';

Line: 445

	--cursor to select empty trips would come from dynamic sql
	--cursor to select empty dels would come from dynamic sql
	--(based on create date from and to)

	/*
	--cursor to select orphaned deliveries with only empty containers
	CURSOR c_delivery_empty_containers IS
	SELECT	wnd.delivery_id
		--wda.delivery_detail_id
	FROM	wsh_new_deliveries wnd,
		wsh_delivery_assignments_v wda,
		wsh_delivery_legs wdl
	WHERE	wnd.delivery_id = wda.delivery_id
	AND	wnd.delivery_id = wdl.delivery_id(+)
	AND	wdl.delivery_leg_id IS NULL
	AND	NOT EXISTS (
			SELECT	1
			FROM	wsh_delivery_details wdd,
				wsh_delivery_assignments_v wda1
			WHERE	wdd.delivery_Detail_id = wda1.delivery_detail_id
			AND	wda1.delivery_id = wnd.delivery_id
			AND	wdd.container_flag = 'N'
         ) ;

Line: 470

	--cursor to select orphaned empty containers
/*	CURSOR c_empty_containers IS
	SELECT
	wdd.delivery_detail_id,
	'EMPTYLPNS'
	--wdd.container_name dd_lpn_number,
	FROM
	wsh_delivery_assignments_v wda,
	wsh_delivery_details wdd
	WHERE
	wda.delivery_detail_id = wdd.delivery_detail_id AND
	wdd.container_flag = 'Y'AND
	wda.delivery_id IS NULL AND
	NOT EXISTS (
			SELECT 1
			FROM
			wsh_delivery_assignments_v wda2
			WHERE
			wda2.parent_delivery_detail_id = wda.delivery_detail_id
		    ) ;

Line: 513

	    WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);

Line: 515

	    WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);

Line: 526

	                                    (SELECT  inner.trip_id trip_id,
										 COUNT ( inner.dd_id ) dd_cnt
                                         FROM
                                         wsh_purge_set_v inner ';

Line: 551

		|| to_date(SYSDATE - p_delete_beyond_x_ship_days,'DD-MM-YYYY') ||'''' ;

Line: 607

                       ''OE'',(DECODE((SELECT count(oe.order_number)
                                       FROM oe_order_headers_all oe
                                       WHERE oe.header_id= inner.dd_source_header_id),0,''FALSE'',''TRUE'')),
                       ''PO'',( DECODE((SELECT count(po.po_header_id)
                                        FROM po_headers_all po
                                        WHERE po.po_header_id= inner.dd_source_header_id),0,''FALSE'',''TRUE'')),
                       ''RTV'',(Decode(inner.dd_po_shipment_line_id,NULL,
                                        decode((select count(mmt.transaction_id)
                                                from mtl_material_transactions mmt
                                                WHERE mmt.picking_line_id = inner.dd_id
                                                ),0,''FALSE'',''TRUE''
                                               ),
                                         decode((select count(rt.interface_source_line_id)
                                                 from rcv_transactions rt
                                                 WHERE rt.interface_source_line_id= inner.dd_id
                                                 ),0,''FALSE'',''TRUE''
                                                )
                                          )
                                 ),
                       ''FALSE'') = ''FALSE''
		       ';

Line: 648

                            (  SELECT 1 FROM
                               wsh_delivery_details dd
                               WHERE
                               dd.delivery_detail_id = inner.dd_id AND
                               (
                                  Nvl(dd.released_status,''N'') NOT IN (''C'',''L'') OR
                                  dd.oe_interfaced_flag IN (''N'',''P'') OR
                                  dd.inv_interfaced_flag IN (''N'',''P'')
                               )
                            ) ';

Line: 660

    trip_sql :=	trip_sql ||' SELECT outer.trip_id , outer.trip_name , ''NON_EMPTY'' purge_set_type
                                  FROM wsh_purge_set_v outer ,trip_inner_query
                                  where outer.trip_id = trip_inner_query.trip_id
								  GROUP BY outer.trip_id , outer.trip_name
								  HAVING count ( outer.dd_id ) = (select dd_cnt from trip_inner_query where trip_id = outer.trip_id)';

Line: 670

	--check whether to delete empty records
        IF (p_delete_empty_records ='Y') THEN
		--construct delivery_sql for empty deliveries
		delivery_sql := 'SELECT
				wnd.delivery_id,wnd.name
				FROM
				wsh_new_deliveries wnd,
				wsh_delivery_assignments_v wda,
				wsh_delivery_legs wdl
				WHERE
				wda.delivery_id(+) = wnd.delivery_id AND
				wnd.delivery_id = wdl.delivery_id(+) AND
				wdl.delivery_leg_id IS NULL AND
				wda.delivery_detail_id IS NULL ' ;

Line: 711

		empty_trip_sql := '	SELECT	distinct wt.trip_id, wt.name
					FROM	wsh_trips wt,
						wsh_trip_stops wts,
						wsh_delivery_legs wdl1,
						wsh_delivery_legs wdl2
					WHERE	';

Line: 738

					(	SELECT	1
						FROM	wsh_trip_stops wtss,
							wsh_delivery_legs wdl1s
						WHERE	wtss.trip_id = wt.trip_id
						AND	wdl1s.pick_up_stop_id = wtss.stop_id
					)' ;

Line: 747

		empty_container_sql := 'SELECT 	wdd.delivery_detail_id,
					''EMPTYLPNS''
					--wdd.container_name dd_lpn_number,
					FROM
					wsh_delivery_assignments_v wda,
					wsh_delivery_details wdd
					WHERE	wda.delivery_detail_id = wdd.delivery_detail_id
					AND	wdd.container_flag = ''Y''
					AND	wda.delivery_id IS NULL ';

Line: 772

							SELECT 1
							FROM
							wsh_delivery_assignments_v wda2
							WHERE
							wda2.parent_delivery_detail_id = wda.delivery_detail_id
						    ) ' ;

Line: 779

	END IF; --end check whether to delete empty records

Line: 781

	--fetch trip ids for non empty trips and insert into PL/SQL table
	OPEN c_trip_purge_cur FOR trip_sql;

Line: 811

	IF (p_delete_empty_records ='Y') THEN
		--fetch trip ids for empty trips and insert into PL/SQL table
		OPEN c_empty_trip_cur FOR empty_trip_sql;

Line: 927

        cancel_wdd_sql := 'SELECT wdd.delivery_detail_id
                           FROM   wsh_delivery_details wdd
                           WHERE  wdd.source_code = ''OE''
                           AND    wdd.released_status = ''D''
                           AND    NOT EXISTS
                                ( SELECT 1
                                  FROM   wsh_delivery_details det
                                  WHERE  det.source_code = ''OE''
                                  AND    det.source_line_id = wdd.source_line_id
                                  AND    det.released_status <> ''D'' )
                           AND    NOT EXISTS
                                ( SELECT 1
                                  FROM   oe_order_headers_all oeh
                                  WHERE  oeh.header_id = wdd.source_header_id ) ';

Line: 1002

Description:	This API calls the individual APIs to delete the data in
		Shipping and Transportation tables
=============================================================================
   Input: Table of Record Types for Trips, Stops, Legs, Deliveries, Containers, Details
   Output: Return Status - success or failure
==============================================================================
   Logic: i) Call Purge_Delivery_Details
         ii) Call Purge_Containers
        iii) Call Purge_Delivery_Legs
         iv) Call Purge_Trip_Stops
          v) Call Purge_Deliveries
         vi) Call Purge_Trips
-----------------------------------------------------------------------------*/

PROCEDURE Purge_Entities(	p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
				p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
				p_tbl_del_details_purge_set Del_Detail_ID_Tbl_Type,
				p_tbl_del_legs_purge_set Del_Leg_ID_Tbl_Type,
				p_tbl_trip_stops_purge_set Trip_Stop_ID_Tbl_Type,
				p_tbl_containers_purge_set Container_ID_Tbl_Type,
				x_return_status OUT  NOCOPY VARCHAR2
			)IS
	l_debug_on BOOLEAN;

Line: 1212

Description:	This API delete the data in Shipping and Transportation
		related to trip
==============================================================================
Input: Table of Record Types for Trips
Output: Return Status - success or failure
==============================================================================
Logic: i) Delete records from the following tables:
WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_DOCUMENT_INSTANCES,WSH_TRIPS
-----------------------------------------------------------------------------*/

PROCEDURE Purge_Trips(		p_tbl_trip_purge_set Trip_ID_Tbl_Type,
				x_return_status OUT  NOCOPY VARCHAR2
		     )IS

	l_debug_on	BOOLEAN;

Line: 1251

		DELETE
		FROM	wsh_exceptions
		WHERE	trip_id = l_trip_id;

Line: 1257

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1261

		DELETE
		FROM	wsh_freight_costs
		WHERE	trip_id = l_trip_id;

Line: 1267

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1271

		DELETE
		FROM	wsh_document_instances
		WHERE	entity_id = l_trip_id
		AND	entity_name = 'WSH_TRIPS';

Line: 1278

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1282

		DELETE
		FROM	wsh_trips
		WHERE	trip_id = l_trip_id;

Line: 1288

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trips: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1315

Description: This API delete the data in Shipping and Transportation
related to delivery
=============================================================================+
Input: Table of Record Types for Deliveries
Output: Return Status - success or failure
==============================================================================
Logic: i) Delete records from the following tables:
          WSH_EXCEPTIONS, WSH_TRANSACTIONS_HISTORY, WSH_DOCUMENT_INSTANCES,
          WSH_FREIGHT_COSTS

	  If FTE is installed,
             FTE_SHIPMENT_STATUS_DETAILS, FTE_SHIPMENT_STATUS_EXCEPTIONS,
             FTE_MESSAGE_PARTNER, FTE_MESSAGE_CONTACT, FTE_MESSAGE_LOCATION,
             FTE_DELIVERY_PROOF, FTE_SHIPMENT_STATUS_HEADERS

          If ITM Screening is done,
             WSH_ITM_RESPONSE_LINES, WSH_ITM_RESPONSE_HEADERS, WSH_ITM_REQUEST_CONTROL,
             WSH_INBOUND_TXN_HISTORY
	  and finally WSH_NEW_DELIVERIES.
-----------------------------------------------------------------------------*/

PROCEDURE Purge_Deliveries(	p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
				x_return_status OUT  NOCOPY VARCHAR2
			)IS
	l_debug_on	BOOLEAN;

Line: 1365

		DELETE
		FROM	wsh_exceptions
		WHERE	delivery_id = l_delivery_id;

Line: 1371

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1375

		DELETE
		FROM	wsh_transactions_history
		WHERE	entity_number = to_char(l_delivery_id)
		AND	entity_type   = 'DLVY';

Line: 1382

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_transactions_history: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1386

		DELETE
		FROM	wsh_freight_costs
		WHERE	delivery_id = l_delivery_id;

Line: 1392

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1396

		DELETE
		FROM	wsh_document_instances
		WHERE	entity_id = l_delivery_id
		AND	entity_name = 'WSH_NEW_DELIVERIES';

Line: 1403

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1409

			DELETE
			FROM	fte_shipment_status_details
			WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1417

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_details: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1421

			DELETE
			FROM	fte_shipment_status_exceptions
			WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1429

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_exceptions: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1433

			DELETE
			FROM	fte_message_partner
			WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1441

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_partner: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1445

			DELETE
			FROM	fte_message_address
			WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1453

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_address: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1457

			DELETE
			FROM	fte_message_contact
			WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1465

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_contact: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1469

			DELETE
			FROM	fte_message_location
			WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1477

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_location: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1482

			DELETE
			FROM	fte_delivery_proof
                        WHERE 	transaction_id IN (SELECT transaction_id
                                                   FROM   fte_shipment_status_headers
						   WHERE  delivery_id = l_delivery_id);

Line: 1490

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_delivery_proof: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1495

			DELETE
			FROM	fte_shipment_status_headers
			WHERE 	delivery_id = l_delivery_id;

Line: 1502

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_headers: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1507

		DELETE
		FROM	wsh_itm_response_lines
		WHERE 	response_header_id IN (	SELECT	wirh.response_header_id
						FROM	wsh_itm_response_headers wirh,
							wsh_itm_request_control wirc
						WHERE	wirc.original_system_reference = l_delivery_id
						AND	wirc.request_control_id = wirh.request_control_id
						AND	wirc.service_type_code = 'WSH_EXPORT_COMPLIANCE' );

Line: 1518

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_response_lines: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1522

		DELETE
		FROM	wsh_itm_response_headers
		WHERE 	request_control_id IN (	SELECT	request_control_id
						FROM	wsh_itm_request_control
						WHERE	original_system_reference = l_delivery_id
						AND	service_type_code = 'WSH_EXPORT_COMPLIANCE' )  ;

Line: 1531

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_response_headers: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1535

		DELETE
		FROM	wsh_itm_request_control
		WHERE 	original_system_reference = l_delivery_id
		AND	service_type_code = 'WSH_EXPORT_COMPLIANCE';

Line: 1542

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_request_control: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1546

		DELETE
		FROM	wsh_inbound_txn_history
		WHERE	shipment_header_id IN (	SELECT rcv_shipment_header_id
						FROM wsh_new_deliveries
						WHERE delivery_id = l_delivery_id) ;

Line: 1554

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_inbound_txn_history: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1558

		DELETE
		FROM	wsh_new_deliveries
		WHERE	delivery_id = l_delivery_id;

Line: 1564

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_new_deliveries: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1592

Description: This API delete the data in Shipping and Transportation
related to trip stop
==============================================================================
Input: Table of Record Types for Trip Stops
Output: Return Status - success or failure
==============================================================================
Logic: i) Delete records from the following tables:
          WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_TRIP_STOPS
-----------------------------------------------------------------------------*/

PROCEDURE Purge_Trip_Stops(	p_tbl_trip_stop_purge_set Trip_Stop_ID_Tbl_Type,
				x_return_status OUT  NOCOPY VARCHAR2
			)IS
	l_debug_on	BOOLEAN;

Line: 1630

		DELETE
		FROM	wsh_exceptions
		WHERE	trip_stop_id = l_stop_id;

Line: 1636

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1640

		DELETE
		FROM	wsh_freight_costs
		WHERE	stop_id = l_stop_id;

Line: 1646

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1650

		DELETE
		FROM	wsh_trip_stops
		WHERE	stop_id = l_stop_id;

Line: 1656

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trip_stops: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1683

Description: This API delete the data in Shipping and Transportation
             related to delivery leg
==============================================================================
   Input: Table of Record Types for Delivery Legs
   Output: Return Status - success or failure
==============================================================================
   Logic: i) Delete records from the following tables:
             WSH_FREIGHT_COSTS, WSH_DOCUMENT_INSTANCES, WSH_DELIVERY_LEG_ACTIVITIES,
             WSH_DELIVERY_LEG_DETAILS, WSH_DELIVERY_LEGS

             If FTE is installed,
             FTE_INVOICE_LINES, FTE_INVOICE_HISTORY, FTE_INVOICE_HEADERS,
             FTE_FAILURE_REASONS
-----------------------------------------------------------------------------*/

PROCEDURE Purge_Delivery_Legs(	p_tbl_del_leg_purge_set Del_Leg_ID_Tbl_Type,
				x_return_status OUT  NOCOPY VARCHAR2
			)IS
	l_debug_on	BOOLEAN;

Line: 1725

		DELETE
		FROM	wsh_freight_costs
		WHERE	delivery_leg_id = l_leg_id;

Line: 1731

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1737

			DELETE
			FROM	fte_invoice_lines
			WHERE	invoice_header_id IN (	SELECT	fih.invoice_header_id
							FROM	fte_invoice_headers fih,
								wsh_document_instances wdi
							WHERE	wdi.entity_id = l_leg_id
							AND	wdi.entity_name = 'WSH_DELIVERY_LEGS'
							AND	wdi.sequence_number = fih.bol
						    ) ;

Line: 1748

				WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_lines: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1752

			DELETE
			FROM	fte_invoice_headers
			WHERE	bol IN (SELECT	sequence_number
					FROM	wsh_document_instances
					WHERE	entity_id = l_leg_id
					AND	entity_name = 'WSH_DELIVERY_LEGS') ;

Line: 1761

				WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_headers: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1765

			DELETE
			FROM	fte_invoice_history
			WHERE	bol IN (SELECT	sequence_number
					FROM	wsh_document_instances
					WHERE	entity_id = l_leg_id
					AND	entity_name = 'WSH_DELIVERY_LEGS') ;

Line: 1774

				WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_history: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1778

			DELETE
			FROM	fte_failure_reasons
			WHERE	bol IN (	SELECT	sequence_number
					FROM	wsh_document_instances
					WHERE	entity_id = l_leg_id
					AND	entity_name = 'WSH_DELIVERY_LEGS') ;

Line: 1787

				WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_failure_reasons: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1793

		DELETE
		FROM	wsh_document_instances
		WHERE	entity_id = l_leg_id
		AND	entity_name = 'WSH_DELIVERY_LEGS';

Line: 1800

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1804

		DELETE
		FROM	wsh_delivery_leg_activities
		WHERE	delivery_leg_id = l_leg_id ;

Line: 1810

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_leg_activities: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1814

		DELETE
		FROM	wsh_delivery_leg_details
		WHERE	delivery_leg_id = l_leg_id;

Line: 1820

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_leg_details: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1825

		DELETE
		FROM	wsh_delivery_legs
		WHERE	delivery_leg_id = l_leg_id ;

Line: 1831

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_legs: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1858

Description: This API delete the data in Shipping and Transportation
             related to delivery detail
=============================================================================
Input: Table of Record Types for Delivery Details
Output: Return Status - success or failure
==============================================================================
Logic: i) Delete records from the following tables:
          WSH_FREIGHT_COSTS, WSH_SERIAL_NUMBERS, WSH_EXCEPTIONS,
          wsh_delivery_assignments_v, WSH_DELIVERY_DETAILS
-----------------------------------------------------------------------------*/
PROCEDURE Purge_Delivery_Details(p_tbl_del_detail_purge_set Del_Detail_ID_Tbl_Type,
				 x_return_status OUT  NOCOPY VARCHAR2
				)IS
	l_debug_on	BOOLEAN;

Line: 1895

		DELETE
		FROM	wsh_serial_numbers
		WHERE	delivery_detail_id = l_detail_id;

Line: 1901

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_serial_numbers: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1905

		DELETE
		FROM	wsh_exceptions
		WHERE	delivery_detail_id = l_detail_id;

Line: 1911

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1915

		DELETE
		FROM	wsh_freight_costs
		WHERE	delivery_detail_id = l_detail_id;

Line: 1921

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1925

		DELETE
		FROM	wsh_delivery_assignments_v
		WHERE	delivery_detail_id = l_detail_id;

Line: 1931

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_assignments_v: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1935

		DELETE
		FROM	wsh_delivery_details
		WHERE	delivery_detail_id = l_detail_id;

Line: 1941

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_details: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 1967

Description: This API calls the WMS API to delete the data in WMS
             related to Containers
==============================================================================
Input: Table of Record Types for Container Ids
Output: Return Status - success or failure
==============================================================================
Logic: i) Delete records from the following tables:
          wsh_delivery_assignments_v, WSH_DELIVERY_DETAILS
-----------------------------------------------------------------------------*/
PROCEDURE Purge_Containers(p_tbl_containers_purge_set Container_ID_Tbl_Type,
			   x_return_status OUT  NOCOPY VARCHAR2
		           )IS
	l_debug_on	BOOLEAN;

Line: 2011

		DELETE
		FROM	wsh_exceptions
		WHERE	delivery_detail_id = l_container_id;

Line: 2017

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2021

		DELETE
		FROM	wsh_freight_costs
		WHERE	delivery_detail_id = l_container_id;

Line: 2027

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2031

		DELETE
		FROM	wsh_delivery_assignments_v
		WHERE	delivery_detail_id = l_container_id;

Line: 2037

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_assignments_v: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2041

		DELETE
		FROM	wsh_delivery_details
		WHERE	delivery_detail_id = l_container_id
		RETURNING lpn_id INTO l_lpn_id;

Line: 2048

			WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_details: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2057

	--Call the WMS API to DELETE the LPNs
	IF l_debug_on THEN
	    WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.LPN_PURGE_ACTIONS', WSH_DEBUG_SV.C_PROC_LEVEL);

Line: 2069

						p_action	=>WMS_Container_GRP.G_LPN_PURGE_ACTION_DELETE,
						p_lpn_purge_rec	=> l_wms_lpn_record
					   );

Line: 2159

				p_delete_beyond_x_ship_days number,
				p_purge_intransit_trips varchar2,
				p_delete_empty_records varchar2,
				p_create_date_from varchar2,
				p_create_date_to varchar2,
				p_del_beyond_creation_days number,
				p_sort_per_criteria varchar2,
				p_print_detail varchar2,
				p_tbl_trip_purge_set  Trip_ID_Tbl_Type ,
				p_tbl_delivery_purge_set  Delivery_ID_Tbl_Type,
				p_tbl_container_purge_set Container_ID_Tbl_Type,
				p_count_legs NUMBER,
				p_count_stops NUMBER,
				p_count_details NUMBER,
				p_count_containers NUMBER,
				x_return_status OUT  NOCOPY VARCHAR2
				)IS
	l_debug_on BOOLEAN;

Line: 2213

	SELECT	distinct del_id,
		del_name,
		del_waybill,
		del_gross_weight,
		del_ui_location_code,
		del_customer_name,
		del_pickup_date,
		del_dropoff_date,
		del_ship_date,
		bol
	FROM	wsh_purge_set_v
	WHERE	trip_id =  p_tripid;

Line: 2228

	SELECT	/*+use_nl(v.wda, v.wnd, v.wdl)*/ distinct v.bol
	FROM	wsh_purge_set_v v
	WHERE	v.del_id = p_delivery_id
	AND	v.bol is not null;

Line: 2236

	SELECT	/*+use_nl(v.wda, v.wnd, v.wdl)*/ DISTINCT v.dd_source_header_number,
		v.dd_source_header_type_name--,
--		dd_creation_date
	FROM	wsh_purge_set_v v
	WHERE	v.del_id = p_delivery_id
	AND	v.dd_source_header_number is not null;

Line: 2244

	SELECT	distinct trip_id,
		trip_name,
		del_id,
		del_name,
		del_waybill,
		del_gross_weight,
		del_ui_location_code,
		del_customer_name,
		del_pickup_date,
		del_dropoff_date,
		del_ship_date,
		bol
	FROM	wsh_purge_set_v
	WHERE	dd_source_header_number= p_ordernumber;

Line: 2261

	SELECT	distinct wda.parent_delivery_detail_id
	FROM	wsh_delivery_assignments_v wda,
		wsh_delivery_details wdd
	WHERE	wda.delivery_detail_id = wdd.delivery_detail_id
	AND	wdd.source_header_number = order_number
	AND	wda.parent_delivery_detail_id IS NOT NULL;

Line: 2270

	SELECT DISTINCT wda.parent_delivery_detail_id
	FROM   wsh_delivery_assignments_v wda ,wsh_Delivery_Details wdd
	WHERE  wda.parent_delivery_detail_id is not null
	AND wdd.delivery_Detail_id = wda.delivery_detail_id
	CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id
	START WITH wdd.source_header_number =order_number;

Line: 2279

	SELECT DISTINCT wda.parent_delivery_detail_id
	FROM   wsh_delivery_assignments_v wda
	WHERE  wda.parent_delivery_detail_id is not null
	CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id
	START WITH wda.delivery_id IN (select wda1.delivery_id from
	wsh_delivery_assignments_v wda1, wsh_delivery_Details wdd
	WHERE wda1.delivery_Detail_id = wdd.delivery_Detail_id
	and     wdd.source_header_number = p_order_number);

Line: 2318

	    WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);

Line: 2320

	    WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);

Line: 2337

		FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'View Purge Selection');

Line: 2358

 SELECT meaning
 INTO   l_src_meaning
 FROM   wsh_lookups
 WHERE  lookup_code = p_source_system
        AND lookup_type in ('WSH_PURGE_SOURCE_SYSTEMS','SOURCE_SYSTEM')
        AND LOOKUP_CODE <> 'OKE'
        AND enabled_flag = 'Y'
        AND Trunc(SYSDATE) BETWEEN Nvl(start_date_active, Trunc(SYSDATE)) AND Nvl
                                   (
                                   end_date_active, Trunc(SYSDATE))
        AND ROWNUM < 2;

Line: 2386

	FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '' || p_delete_beyond_x_ship_days || '');

Line: 2394

	IF p_delete_empty_records = 'Y' THEN
		FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Yes');

Line: 2855

	SELECT	wdd.lpn_id
	FROM	wsh_trips wt,
		wsh_trip_stops wts,
		wsh_delivery_legs wdl,
		wsh_new_deliveries wnd,
		wsh_delivery_assignments_v wda,
		wsh_delivery_details wdd
	WHERE	wt.trip_id = wts.trip_id
	AND	wts.stop_id = wdl.pick_up_stop_id
	AND	wdl.delivery_id = wnd.delivery_id
	AND	wda.delivery_id = wnd.delivery_id
	AND	wda.delivery_detail_id = wdd.delivery_detail_id
	AND	wdd.container_flag = 'Y'
	AND	wdd.lpn_id IS NOT NULL
	AND	wts.trip_id = p_tripid;

Line: 2879

	SELECT
	DISTINCT wt1.trip_id
	FROM
	wsh_trips wt1,
	wsh_trip_stops pickup_stop1,
	wsh_trip_stops dropoff_stop1,
	wsh_delivery_legs wdl1
	WHERE
	wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
	wdl1.drop_off_stop_id = dropoff_stop1.stop_id AND
	wt1.trip_id = pickup_stop1.trip_id AND
	wt1.trip_id = dropoff_stop1.trip_id AND
	wdl1.delivery_id IN (SELECT delivery_id
			     FROM   wsh_delivery_legs
			     START WITH delivery_id IN (SELECT delivery_id
						      FROM wsh_delivery_legs
						      WHERE parent_delivery_leg_id IS NULL
						      START WITH delivery_id IN (SELECT wdl.delivery_id
										     FROM
										     wsh_new_deliveries wnd,
										     wsh_delivery_legs wdl,
										     wsh_trip_stops pickup_stop,
										     wsh_trip_stops dropoff_stop,
										     wsh_trips wt
										     WHERE
										     wnd.delivery_id = wdl.delivery_id AND
										     wdl.pick_up_stop_id = pickup_stop.stop_id AND
										     wdl.drop_off_stop_id = dropoff_stop.stop_id AND
										     wt.trip_id = pickup_stop.trip_id AND
										     wt.trip_id = dropoff_stop.trip_id AND
										     ((wnd.delivery_type = 'CONSOLIDATION')
										      OR
										      (wdl.parent_delivery_leg_id IS NULL)
										     ) AND
										     wt.trip_id = p_tripid)
						      CONNECT BY delivery_leg_id = PRIOR parent_delivery_leg_id )
			     CONNECT BY parent_delivery_leg_id = PRIOR delivery_leg_id)
	ORDER BY wt1.trip_id;

Line: 2920

    SELECT
    DISTINCT wt1.trip_id
    FROM
    wsh_trips wt1,
    wsh_trip_stops wts,
    wsh_delivery_legs wdl1
    WHERE
    (wdl1.pick_up_stop_id = wts.stop_id OR
    wdl1.drop_off_stop_id = wts.stop_id) AND
    wt1.trip_id = wts.trip_id AND
    wdl1.delivery_id IN
    (
     SELECT delivery_id
     FROM wsh_delivery_legs
     WHERE parent_delivery_leg_id
     IN
     (
          SELECT wdl.delivery_leg_id
          FROM
          wsh_delivery_legs wdl,
          wsh_trip_stops wts,
          wsh_trips wt
          WHERE
          (wdl.pick_up_stop_id = wts.stop_id OR
          wdl.drop_off_stop_id = wts.stop_id) AND
          wt.trip_id = wts.trip_id AND
          wdl.parent_delivery_leg_id IS NULL AND
          wt.trip_id =  p_tripid
     )
    )
    ORDER BY wt1.trip_id;

Line: 2969

		sql_tripmove := 'SELECT move_id, trip_id
				FROM fte_trip_moves
				WHERE move_id IN
				(SELECT move_id
				FROM fte_trip_moves
				GROUP BY move_id
				HAVING count(trip_id) >1 )
				ORDER BY move_id';