[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wth.Event_Key,
wth.Item_Type,
wth.Internal_Control_Number
FROM wsh_transactions_history wth
WHERE wth.Transaction_ID = (SELECT MAX (Transaction_ID)
FROM wsh_transactions_history
WHERE Document_Number = c_Orig_Document_Number
AND Trading_Partner_ID = c_Trading_Partner_ID
AND Document_Direction = 'I'
AND Action_Type = 'A');
SELECT wth.Entity_Number,
wth.Trading_Partner_ID,
wth.Transaction_Status,
wndi.client_code
FROM wsh_transactions_history wth,
wsh_new_del_interface wndi
WHERE wth.Transaction_ID = (SELECT MAX (Transaction_ID)
FROM wsh_transactions_history
WHERE Document_Number = c_Orig_Document_Number
AND Trading_Partner_ID = c_Trading_Partner_ID
AND Document_Direction = 'I'
AND Action_Type = 'A')
AND wth.entity_number = wndi.delivery_interface_id (+);
SELECT status_code, Delivery_ID
FROM wsh_new_deliveries
WHERE Organization_id = c_Organization_ID
AND Name = c_Delivery_Name;
SELECT Delivery_Interface_id
FROM wsh_new_del_interface
WHERE Name = c_Name
AND Organization_ID = c_Org_ID;
wsh_update_history EXCEPTION;
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
l_txn_hist_record.transaction_id,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
RAISE wsh_update_history;
WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
l_txn_hist_record.transaction_id,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
RAISE wsh_update_history;
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
l_txn_hist_record.transaction_id,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
RAISE wsh_update_history;
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
l_txn_hist_record.transaction_id,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after Create_Update_Txns_History ', l_Return_Status);
RAISE wsh_update_history;
WSH_PROCESS_INTERFACED_PKG.delete_interface_records ( l_New_Del_Interface_ID,
l_Return_Status );
wsh_debug_sv.log (l_module_name, 'Return status after delete_interface_records ', l_Return_Status);
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');
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History ( l_txn_hist_record,
l_txn_hist_record.transaction_id,
l_Return_Status );
SELECT wth1.Transaction_ID,
wth1.Document_Type,
wth1.Document_Direction,
wth1.Document_Number,
wth1.Orig_Document_Number,
wth1.Entity_Number,
wth1.Entity_Type,
wth1.Trading_Partner_ID,
wth1.Action_Type,
wth1.Transaction_Status,
wth1.ECX_Message_ID,
wth1.Event_Name,
wth1.Event_Key,
wth1.Item_Type,
wth1.Internal_Control_Number,
-- R12.1.1 STANDALONE PROJECT
wth1.document_revision,
wth1.Attribute_Category,
wth1.Attribute1,
wth1.Attribute2,
wth1.Attribute3,
wth1.Attribute4,
wth1.Attribute5,
wth1.Attribute6,
wth1.Attribute7,
wth1.Attribute8,
wth1.Attribute9,
wth1.Attribute10,
wth1.Attribute11,
wth1.Attribute12,
wth1.Attribute13,
wth1.Attribute14,
wth1.Attribute15,
NULL -- LSP PROJECT
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2
WHERE wth2.Item_Type = c_Item_Type
AND wth2.Event_Key = c_Item_Key
AND wth1.Document_Number = wth2.Orig_Document_Number
AND wth1.Trading_Partner_ID = wth2.Trading_Partner_ID
AND wth1.Document_Direction = 'I'
AND wth1.Action_Type = 'A';
SELECT wth1.Transaction_ID,
wth1.Document_Type,
wth1.Document_Direction,
wth1.Document_Number,
wth1.Document_Number Orig_Document_Number,
wth1.Entity_Number,
wth1.Entity_Type,
wth1.Trading_Partner_ID,
wth1.Action_Type,
wth1.Transaction_Status,
wth1.ECX_Message_ID,
wth1.Event_Name,
wth1.Event_Key,
wth1.Item_Type,
wth1.Internal_Control_Number,
wth1.document_revision,
wth1.Attribute_Category,
wth1.Attribute1,
wth1.Attribute2,
wth1.Attribute3,
wth1.Attribute4,
wth1.Attribute5,
wth1.Attribute6,
wth1.Attribute7,
wth1.Attribute8,
wth1.Attribute9,
wth1.Attribute10,
wth1.Attribute11,
wth1.Attribute12,
wth1.Attribute13,
wth1.Attribute14,
wth1.Attribute15,
NULL -- LSP PROJECT
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2
WHERE wth2.Item_Type = c_Item_Type
AND wth2.Event_Key = c_Item_Key
AND wth1.Document_Number = wth2.Document_Number
AND wth1.Trading_Partner_ID = wth2.Trading_Partner_ID
AND wth1.Document_Revision < wth2.Document_Revision
AND wth1.Document_Direction = 'I'
AND wth2.Document_Direction = 'I'
AND wth1.Transaction_Status in ('AP', 'ER')
AND wth1.Event_Name is not null
AND wth1.Event_Key is not null;
SELECT wth1.Document_Number,
wth1.Transaction_Status,
wth1.Event_Key
FROM wsh_transactions_history wth1
WHERE Transaction_ID = (SELECT MAX (Transaction_ID)
FROM wsh_transactions_history
WHERE Entity_Number = c_Entity_Number
AND Trading_Partner_ID = c_Org_ID
AND Entity_Type = 'DLVY'
AND Action_Type = 'A'
AND Document_Direction = 'I'
AND Document_Type = 'SR');
SELECT Name, status_code,client_id
FROM wsh_new_deliveries
WHERE Organization_id = c_Organization_ID
AND Delivery_ID = c_Delivery_ID;
SELECT 'X'
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wdd.container_flag = 'N'
AND wdd.source_code = 'WSH'
AND wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
AND wda.Delivery_ID = wnd.Delivery_ID
AND wnd.Delivery_ID = c_Delivery_ID
AND wnd.Organization_ID = c_Org_ID
AND rownum = 1;
select 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_org_id
and document_direction = 'O'
and document_type ='SA'
and entity_type ='DLVY'
and action_type = 'A');
SELECT 'X'
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_system_items msi
WHERE wdd.container_flag = 'N'
AND wdd.released_status <> 'D'
AND nvl(wdd.inv_interfaced_flag,'N') <> 'Y'
AND wdd.Delivery_Detail_ID = wda.Delivery_Detail_ID
AND msi.inventory_item_id = wdd.inventory_item_id
AND msi.organization_id = wdd.organization_id
AND msi.serial_number_control_code IN (2,5,6)
AND wda.Delivery_ID = cp_entity_id
AND rownum = 1;
UPDATE wsh_new_deliveries
SET pending_advice_flag = NULL,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE,
request_id = fnd_global.conc_request_id
WHERE delivery_id = P_Entity_ID;
SELECT party_id
INTO l_curr_txn_hist_record.Trading_Partner_ID
FROM hz_cust_accounts_all
WHERE cust_account_id = l_client_id;