APPS.WSH_DELIVERY_UTIL SQL Statements

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

Line: 7

| PROCEDURE NAME   Update_Dlvy_Status                                       |
|                                                                           |
| DESCRIPTION      This procedure  is used to update the delivery status    |
|                  appropriately depending on the parameters passed.        |
|                                                                           |
| MODIFICATION HISTORY                                                      |
|                                                                           |
|    02/20/02      Ravikiran  Vishnuvajhala  Created                        |
|                                                                           |
============================================================================*/
--
G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_UTIL';

Line: 20

procedure Update_Dlvy_Status(	p_delivery_id	IN NUMBER,
				p_action_code IN VARCHAR2 ,
				p_document_type IN VARCHAR2 ,
				x_return_status OUT NOCOPY  VARCHAR2
			    )
IS

--pragma AUTONOMOUS_TRANSACTION;

Line: 42

l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DLVY_STATUS';

Line: 54

  wsh_debug_sv.push(l_module_name, 'Update_Dlvy_Status');

Line: 68

     update wsh_new_deliveries
     set status_code ='OP'
     where delivery_id = p_delivery_id
     and status_code IN ('SR','SC');

Line: 104

	 select planned_flag into l_planned_flag
	 from wsh_new_deliveries
	 where delivery_id = p_delivery_id;

Line: 137

         update wsh_new_deliveries
         set status_code ='SR'
         where delivery_id = p_delivery_id
         and status_code = 'OP';

Line: 142

	 update wsh_new_deliveries
	 set status_code ='SA'
	 where delivery_id = p_delivery_id
	 and status_code IN ('SR','SC');

Line: 150

     update wsh_new_deliveries
     set status_code ='SC'
     where delivery_id = p_delivery_id
     and status_code = 'SR';

Line: 205

END Update_Dlvy_Status;

Line: 215

select status_code, planned_flag
from wsh_new_deliveries
where delivery_id =p_delivery_id;

Line: 220

select 'X'
from wsh_delivery_details wdd,
     wsh_delivery_assignments_v wda
where wdd.delivery_detail_id = wda.delivery_detail_id
and   wdd.container_flag='N'
and   wdd.source_code ='WSH'
and   wda.delivery_id=p_delivery_id
and   rownum = 1;

Line: 235

select wth.event_key, wth.item_type
from wsh_new_deliveries wnd,
wsh_transactions_history wth
where delivery_id =v_delivery_id
and wnd.name = wth.entity_number
and wth.document_direction = 'O'
and wth.document_type = 'SR'
and wth.action_type = 'A'
and wth.entity_type = 'DLVY'
order by transaction_id desc;

Line: 247

select 'X'
from wsh_transactions_history
where event_key = v_event_key
and   item_type = v_item_type
and   document_direction = 'I'
and   action_type = 'A'
and   document_type = 'SA'
order by transaction_id desc;

Line: 397

PROCEDURE Check_Updates_Allowed( p_changed_attributes IN WSH_INTERFACE.ChangedAttributeTabType,
				 p_source_code IN VARCHAR2,
				 x_update_allowed OUT NOCOPY  VARCHAR2,
				 x_return_status OUT NOCOPY  VARCHAR2)
IS

cursor	del_cur ( p_source_line_id IN NUMBER ) is
select	count(*)
from	wsh_new_deliveries wnd,
	wsh_delivery_assignments_v wda,
	wsh_delivery_details wdd
where	wnd.status_code in ('SR','SC')
--and	wnd.planned_flag = 'Y'
and	wda.delivery_id = wnd.delivery_id
and	wda.delivery_detail_id = wdd.delivery_detail_id
and	wdd.container_flag ='N'
and	wdd.source_line_id = p_source_line_id
and	wdd.source_code = p_source_code;

Line: 417

select	organization_id,
	delivery_detail_id
from	wsh_delivery_details
where	source_code= p_source_code
and	source_line_id = p_source_line_id
and 	rownum = 1;

Line: 438

wsh_update_not_allowed EXCEPTION;

Line: 443

l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_UPDATES_ALLOWED';

Line: 455

  wsh_debug_sv.push(l_module_name, 'Check_Updates_Allowed');

Line: 503

	            raise wsh_update_not_allowed;

Line: 514

     x_update_allowed := 'Y';

Line: 521

	WHEN wsh_update_not_allowed THEN
	   x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;

Line: 523

	   FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_NOT_ALLOWED');

Line: 525

	   x_update_allowed := 'N';

Line: 527

           WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_not_allowed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);

Line: 528

           WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_not_allowed');

Line: 532

	   x_update_allowed := 'N';

Line: 539

	   x_update_allowed := 'N';

Line: 540

	   WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_DELIVERY_UTIL.Check_Updates_Allowed',l_module_name);

Line: 546

END Check_Updates_Allowed;

Line: 560

select  wnd.organization_id,
        wnd.status_code,
        wnd.planned_flag,
	wnd.delivery_id
from    wsh_new_deliveries wnd,
        wsh_delivery_legs wdl,
        wsh_trip_stops wts1,
        wsh_trip_stops wts2,
        wsh_trips wt
where   wnd.delivery_id = wdl.delivery_id
and     wts1.stop_id = wdl.PICK_UP_STOP_ID
and     wts2.stop_id = wdl.DROP_OFF_STOP_ID
and     wts1.trip_id = wt.trip_id
and     wts2.trip_id = wt.trip_id
and     wt.trip_id   = p_trip_id
and     rownum = 1;

Line: 578

select  wnd.organization_id,
        wnd.status_code,
        wnd.planned_flag,
	wnd.delivery_id
from    wsh_new_deliveries wnd,
        wsh_delivery_legs wdl
where   wnd.delivery_id = wdl.delivery_id
and     wdl.pick_up_stop_id = p_stop_id
and     rownum = 1;

Line: 590

select	wnd.organization_id,
	wnd.status_code,
	wnd.planned_flag,
	wnd.delivery_id
from    wsh_new_deliveries wnd,
	wsh_delivery_assignments_v wda
where 	wnd.delivery_id = wda.delivery_id
and	wda.delivery_detail_id = p_del_det_id;

Line: 600

select	organization_id,
	status_code,
	planned_flag
from	wsh_new_deliveries
where	delivery_id=p_delivery_id;

Line: 610

select	organization_id,
	source_code,
	container_flag
from	wsh_delivery_details
where	delivery_detail_id = p_del_det_id;

Line: 618

select	distinct 'X'
from	wsh_delivery_details wdd,
	wsh_delivery_assignments_v wda
where	wda.delivery_id = p_delivery_id
and	wdd.delivery_detail_id = wda.delivery_detail_id
and	wdd.source_code = 'WSH'
and	wdd.container_flag = 'N';

Line: 628

select	'X'
from	wsh_delivery_details wdd,
	wsh_delivery_assignments_v wda,
	wsh_delivery_legs wdl
where	wdl.pick_up_stop_id = p_stop_id
and     wda.delivery_id is not null
and	wda.delivery_id = wdl.delivery_id
and	wdd.delivery_detail_id = wda.delivery_detail_id
and	wdd.source_code = 'WSH'
and	wdd.container_flag = 'N'
and     rownum=1;

Line: 641

select	distinct 'X'
from	wsh_delivery_details wdd,
	wsh_new_deliveries wnd,
	wsh_delivery_assignments_v wda,
	wsh_delivery_legs wdl,
	wsh_trip_stops wts1,
	wsh_trip_stops wts2,
	wsh_trips wt
where	wt.trip_id   = p_trip_id
and	wts1.trip_id = wt.trip_id
and     wts2.trip_id = wt.trip_id
and 	wts1.stop_id = wdl.pick_up_stop_id
and     wts2.stop_id = wdl.drop_off_stop_id
and	wnd.delivery_id = wdl.delivery_id
and	wda.delivery_id = wnd.delivery_id
and	wdd.delivery_detail_id = wda.delivery_detail_id
and	wdd.source_code = 'WSH'
and	wdd.container_flag = 'N';

Line: 661

select	distinct 'X'
from	wsh_delivery_details wdd,
	wsh_delivery_assignments_v wda
where	wdd.source_code = 'WSH'
and	wdd.container_flag = 'N'
and	wdd.delivery_detail_id = wda.delivery_detail_id
and	wda.delivery_id = p_delivery_id;

Line: 672

select	'X'
from	wsh_transactions_history
where	transaction_id = (
                        select	max(transaction_id)
                        from	wsh_transactions_history wth,
				wsh_new_deliveries wnd
                        where	wth.entity_number = wnd.name
                        and	wth.trading_partner_id = p_tp_id
			and 	wnd.delivery_id	= p_delivery_id
                        )
and	document_direction='I'
and	action_type = 'A';

Line: 754

	 'UPDATE_STATUS')) THEN
     l_entity_ids := x_entity_ids;