[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select name, customer_id from wsh_new_deliveries
where delivery_id = p_entity_id;
select distinct 'X'
from wsh_delivery_details wdd1,
wsh_delivery_assignments_v wda1,
wsh_delivery_details wdd2,
wsh_delivery_assignments_v wda2
where
wdd1.source_line_id = wdd2.source_line_id
and wdd1.delivery_detail_id = wda1.delivery_detail_id
and wdd1.container_flag='N'
and wda1.delivery_id = p_delivery_id
and wdd2.delivery_detail_id = wda2.delivery_detail_id
and wdd2.container_flag='N'
and (wda2.delivery_id <> p_delivery_id
or wda2.delivery_id is null);
select distinct '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.released_status not in ('X', 'R', 'B')
and wda.delivery_id =p_delivery_id;
select distinct '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.released_status not in ('X','Y')
and wda.delivery_id =p_delivery_id;
select document_number,transaction_status
from wsh_transactions_history
where transaction_id = (
select max(transaction_id)
from wsh_transactions_history
where entity_number = p_entity_number
and trading_partner_id = p_tp_id
and document_direction = 'O'
--and transaction_status = 'ST'
and action_type = 'A'
);
select distinct 'X'
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wda.delivery_id = p_delivery_id
and wda.parent_delivery_detail_id is null
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.container_flag='N';
select wdd.delivery_detail_id,
wdd.source_header_id,
wdd.source_line_id
from wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
where wda.delivery_id = p_delivery_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.container_flag = 'N'
and wda.delivery_id IS NOT NULL;
wsh_insert_history_error EXCEPTION;
select organization_code
into l_org_code
from mtl_parameters
where organization_id = p_organization_id;
select to_char(WSH_DOCUMENT_NUMBER_S.nextval) into l_txns_history_rec.document_number from dual;
WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
p_action_type,
p_document_type,
x_return_status);
select 1 into l_tmp
from wsh_new_deliveries
where delivery_id = p_entity_id
for update nowait;
wsh_debug_sv.log (l_module_name,'Update_Dlvy_Status-> x_return_status: ',x_return_status);
raise wsh_insert_history_error;
select 1 into l_tmp
from wsh_new_deliveries
where delivery_id = p_entity_id
for update nowait;
WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
p_action_type,
p_document_type,
x_return_status);
wsh_debug_sv.log (l_module_name,'Update_Dlvy_Status-> x_return_status: ',x_return_status);
WHEN wsh_insert_history_error THEN
IF ( p_action_type = 'A' ) THEN
WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
NULL,
p_document_type,
x_return_status);
WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
'A',
p_document_type,
x_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'wsh_insert_history_error exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_insert_history_error');
WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
NULL,
p_document_type,
x_return_status);
WSH_DELIVERY_UTIL.Update_Dlvy_Status (p_entity_id,
'A',
p_document_type,
x_return_status);
select event_key
from wsh_transactions_history
where item_type = p_item_type
and document_number = p_orig_doc_number
and trading_partner_id = p_organization_id;
select delivery_id
from wsh_new_deliveries
where name = p_delivery_name
and organization_id = p_organization_id;
select wsh_transaction_s.nextval into l_temp from dual;
select wsh_transaction_s.nextval into l_temp from dual;
SELECT wnd.delivery_id
from wsh_new_deliveries wnd,
wsh_transactions_history wth
where wnd.name = wth.entity_number
and entity_type='DLVY'
and wth.event_key = item_key
and wth.item_type = item_type
and wth.document_direction='O';
WSH_DELIVERY_UTIL.Update_Dlvy_Status(l_delivery_id,
NULL,
NULL,
l_return_status);
PROCEDURE update_atnms( p_transaction_id IN number)
IS
pragma AUTONOMOUS_TRANSACTION;
|| 'UPDATE_ATNMS';
UPDATE wsh_transactions_history
SET transaction_status = 'ER'
WHERE transaction_id = p_transaction_id;
END update_atnms;
SELECT wth1.entity_number , wth1.transaction_id
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2
WHERE wth1.action_type = 'A'
AND wth1.entity_type = 'DLVY'
AND wth1.document_type = 'SR'
AND wth1.document_direction = 'O'
AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
AND wth1.event_key = wth2.event_key
AND wth2.document_direction = 'I'
AND wth2.transaction_id = v_trx_id
AND wth2.document_type = 'SA'
ORDER BY wth1.transaction_id desc;
SELECT wth2.transaction_id ,
wth2.document_type ,
wth2.document_direction ,
wth2.document_number ,
wth2.orig_document_number ,
wth2.entity_number ,
wth2.entity_type ,
wth2.trading_partner_id ,
wth2.action_type ,
wth2.transaction_status ,
wth2.ecx_message_id ,
wth2.event_name ,
wth2.event_key ,
wth2.item_type ,
wth2.internal_control_number ,
wth2.attribute_category ,
wth2.attribute1 ,
wth2.attribute2 ,
wth2.attribute3 ,
wth2.attribute4 ,
wth2.attribute5 ,
wth2.attribute6 ,
wth2.attribute7 ,
wth2.attribute8 ,
wth2.attribute9 ,
wth2.attribute10 ,
wth2.attribute11 ,
wth2.attribute12 ,
wth2.attribute13 ,
wth2.attribute14 ,
wth2.attribute15
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2
WHERE wth1.transaction_id = v_sr_trx_id
AND wth2.entity_number = wth1.entity_number
AND wth2.document_direction = 'O'
AND wth2.document_type = 'SR'
AND wth2.action_type = 'D'
ORDER BY wth2.transaction_id desc;
update_atnms(l_txns_history_rec.transaction_id);
PROCEDURE Update_Txn_Hist_Err_WF( Item_type IN VARCHAR2,
Item_key IN VARCHAR2,
Actid IN NUMBER,
Funcmode IN VARCHAR2,
Resultout OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1);
wsh_update_history EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_ERR_WF';
wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Err_WF');
Update_Txn_History ( Item_type,
Item_key,
'ER',
l_return_status
);
wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
raise wsh_update_history;
WHEN wsh_update_history THEN
resultout := 'COMPLETE:FAILURE';
WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
END Update_Txn_Hist_Err_WF;
PROCEDURE Update_Txn_Hist_Success_WF( Item_type IN VARCHAR2,
Item_key IN VARCHAR2,
Actid IN NUMBER,
Funcmode IN VARCHAR2,
Resultout OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1);
wsh_update_history EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HIST_SUCCESS_WF';
wsh_debug_sv.push(l_module_name, 'Update_Txn_Hist_Err_WF');
Update_Txn_History ( Item_type,
Item_key,
'ST',
l_return_status
);
wsh_debug_sv.log(l_module_name, 'Update_Txn_History l_return_status ',l_return_status);
raise wsh_update_history;
WHEN wsh_update_history THEN
resultout := 'COMPLETE:FAILURE';
WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
END Update_Txn_Hist_Success_WF;
PROCEDURE Update_Txn_History ( p_item_type IN VARCHAR2,
p_item_key IN VARCHAR2,
p_transaction_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
pragma AUTONOMOUS_TRANSACTION;
wsh_update_history EXCEPTION;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HISTORY';
raise wsh_update_history;
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txns_history_rec,
l_txn_id,
x_return_status );
wsh_debug_sv.log(l_module_name, 'Create_Update_Txns_History x_return_status',x_return_status);
raise wsh_update_history;
WHEN wsh_update_history THEN
x_return_status := wsh_util_core.g_ret_sts_error;
WSH_DEBUG_SV.logmsg(l_module_name,'wsh_update_history exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:wsh_update_history');
END Update_Txn_History;
PROCEDURE WSHSUPI_SELECTOR( Item_type IN VARCHAR2,
Item_key IN VARCHAR2,
Actid IN NUMBER,
Funcmode IN VARCHAR2,
Resultout IN OUT NOCOPY VARCHAR2
) IS
l_user_id NUMBER;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'WSHSUPI_SELECTOR';
wsh_debug_sv.push(l_module_name, 'WSHSUPI_SELECTOR');
END WSHSUPI_SELECTOR;
SELECT status_code
FROM wsh_new_deliveries
WHERE name = v_delivery_name;
SELECT event_key
FROM wsh_transactions_history
WHERE ENTITY_NUMBER = v_delivery_name
AND ENTITY_TYPE = 'DLVY'
AND ACTION_TYPE = 'D'
AND document_direction = 'O'
ORDER BY transaction_id DESC;
SELECT wth2.entity_number , wth1.transaction_id
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2
where wth1.entity_type='DLVY_INT'
and wth1.event_key = v_item_key
and wth1.item_type = v_item_type
and wth1.document_type = 'SA'
and wth1.document_direction='I'
and wth1.action_type = 'A'
AND wth2.entity_type = 'DLVY'
AND wth2.document_type = 'SR'
AND wth2.action_type = 'A'
AND wth2.document_direction = 'O'
AND wth2.item_type = v_item_type
AND wth2.event_key = v_item_key
ORDER BY wth1.transaction_id desc;
UPDATE wsh_transactions_history
SET TRANSACTION_STATUS = 'AP'
WHERE transaction_id = l_transaction_id;
SELECT wth2.transaction_id, wth2.transaction_status,
to_number(wth2.entity_number)
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2
WHERE wth1.entity_number = v_name
AND wth1.action_type = 'A'
AND wth1.entity_type = 'DLVY'
AND wth1.document_type = 'SR'
AND wth1.document_direction = 'O'
AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
AND wth1.event_key = wth2.event_key
AND wth2.document_direction = 'I'
AND wth2.document_type = 'SA';
SELECT transaction_id,
document_type,
document_direction,
document_number,
orig_document_number,
entity_number,
entity_type,
trading_partner_id,
action_type,
transaction_status,
ecx_message_id,
event_name,
event_key ,
item_type,
internal_control_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM wsh_transactions_history
WHERE
entity_number = v_entity_number
AND action_type = 'A'
AND entity_type = 'DLVY'
and document_direction = 'O'
and document_type = 'SR'
ORDER BY transaction_id DESC;
SELECT status_code
FROM wsh_new_deliveries
WHERE name = v_delivery_name;
SELECT entity_number
FROM wsh_transactions_history
WHERE item_type = v_item_type
AND event_key = v_item_key
AND document_direction = 'O'
AND document_type = 'SR'
AND ACTION_TYPE = 'D'
ORDER BY transaction_id desc;
SELECT 1
FROM wsh_transactions_history
WHERE item_type = v_item_type
AND event_key = v_item_key
AND document_direction = 'O'
AND document_type = 'SR'
AND ACTION_TYPE = 'A'
ORDER BY transaction_id desc;
WSH_PROCESS_INTERFACED_PKG.delete_interface_records (
p_delivery_interface_id => l_del_interface_id,
x_return_status => l_Return_Status
) ;
UPDATE wsh_transactions_history
SET transaction_status= 'SX',
entity_number = l_entity_number,
entity_type = 'DLVY'
WHERE
transaction_id = l_trx_id; --bmso