[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wth3.transaction_id,
wth3.document_type,
wth3.document_direction,
wth3.document_number,
wth3.orig_document_number,
wth3.entity_number,
wth3.entity_type,
wth3.trading_partner_id,
wth3.action_type,
wth3.transaction_status,
wth3.ecx_message_id,
wth3.event_name,
wth3.event_key ,
wth3.item_type,
wth3.internal_control_number,
--R12.1.1 STANDALONE PROJECT
wth3.document_revision,
wth3.attribute_category,
wth3.attribute1,
wth3.attribute2,
wth3.attribute3,
wth3.attribute4,
wth3.attribute5,
wth3.attribute6,
wth3.attribute7,
wth3.attribute8,
wth3.attribute9,
wth3.attribute10,
wth3.attribute11,
wth3.attribute12,
wth3.attribute13,
wth3.attribute14,
wth3.attribute15,
NULL -- LSP PROJECT : just added for dependency for client_id
FROM wsh_transactions_history wth1,
wsh_transactions_history wth2,
wsh_transactions_history wth3
WHERE
wth2.entity_number = v_entity_number
AND wth2.document_direction = 'I'
AND wth2.document_type = 'SA'
AND wth1.event_key = wth2.event_key
AND wth1.document_number = wth2.ORIG_DOCUMENT_NUMBER
AND wth1.action_type = 'A'
and wth1.document_direction = 'O'
and wth1.document_type = 'SR'
AND wth1.entity_type = 'DLVY'
AND wth3.entity_number = wth1.entity_number
AND wth3.document_type = 'SR'
AND wth3.document_direction = 'O'
AND wth3.action_type = 'D'
ORDER BY wth1.transaction_id DESC;
WSH_INTERFACE_COMMON_ACTIONS.G_Update_Attributes_Tab.delete;
WSH_INTERFACE_COMMON_ACTIONS.G_Packing_Detail_Tab.delete;
WSH_INTERFACE_COMMON_ACTIONS.G_SERIAL_RANGE_TAB.delete;
UPDATE wsh_transactions_history
SET transaction_status = 'SC',
entity_number = x_delivery_id,
entity_type = 'DLVY'
WHERE entity_type = 'DLVY_INT'
AND entity_number = to_char(l_delivery_interface_id)
AND document_type = 'SR';
-- Delete only for 'SR' because for 'SA' delete done in ship_advice_pkg
IF(l_trns_history_rec.document_type = 'SR') THEN
Delete_Interface_Records(
L_Delivery_Interface_ID,
X_Return_Status);
wsh_debug_sv.log (l_module_name, 'Return status after delete interface records', X_Return_Status);
-- Update done only for 'SR' because , for 'SA', update
-- done in ship_advice_pkg
--Fulfillment Batch XML Project
--Moved the code to handle error status in workflow activity, in case the shipment advice handling for batches is carried out through workflows.
IF l_trns_history_rec.document_type = 'SA'
AND l_trns_history_rec.event_name = 'oracle.apps.wsh.batch.bsai'
AND l_trns_history_rec.event_key IS NOT NULL THEN
IF l_debug_on THEN
wsh_debug_sv.log (l_module_name, 'Ship Advice error.Move to error event in workflow.');
UPDATE wsh_transactions_history
SET transaction_status = 'ER'
WHERE entity_type = 'DLVY_INT'
AND entity_number = to_char(l_delivery_interface_id)
-- TPW - Distributed changes
AND document_type in ('SR', 'SA');
UPDATE wsh_transactions_history
SET transaction_status = 'ER'
WHERE entity_type = 'DLVY_INT'
AND entity_number = to_char(l_delivery_interface_id)
AND document_type IN ('SR', 'SA');
to Derive, Validate data in the Interface tables and update ID columns in interface tables.
This procedure includes calls to various APIs available in Shipping.
Only fields which are not being Derived/validated by any of the Public APIs(which will be
called subsequently) are Derived/Validated here.
If all the fields are successfully derived/Validated, the ID columns will be updated in
the Interface tables.
=======================================================================================*/
PROCEDURE derive_ids (
p_delivery_interface_id IN NUMBER,
p_document_type IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
)
IS
CURSOR delivery_cur
IS
SELECT name, organization_code, customer_number,
intmed_ship_to_location_code, initial_pickup_location_code,
ultimate_dropoff_location_code, customer_name,
-- TPW - Distributed changes
ship_to_customer_name,
ship_to_address1, ship_to_address2, ship_to_address3, ship_to_address4,
ship_to_city, ship_to_state, ship_to_postal_code, ship_to_country
FROM wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT 'X'
FROM HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
WSH_LOCATIONS WL,
WSH_NEW_DELIVERIES WND
WHERE wnd.delivery_id = p_delivery_id
AND wnd.ultimate_dropoff_location_id = wl.wsh_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND HCAS.STATUS = 'A'
AND HCA.STATUS = 'A'
AND HCAS.ORG_ID = HCSU.ORG_ID
AND WND.CUSTOMER_ID= HCAS.cust_account_id
AND HCAS.ORG_ID = p_org_id;
SELECT wddi.item_number, wddi.customer_item_number, wddi.organization_code,
wddi.ship_from_location_code, wddi.ship_to_location_code,
wddi.deliver_to_location_code, wddi.customer_number, wddi.subinventory,
wddi.revision, wddi.lot_number, wddi.locator_id,
wddi.intmed_ship_to_location_code, wddi.delivery_detail_interface_id,
wddi.customer_name, wddi.container_flag,
-- TPW - Distributed changes
wddi.locator_code
FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
WHERE wdai.delivery_interface_id = p_delivery_interface_id
AND wddi.delivery_detail_interface_id =
wdai.delivery_detail_interface_id
AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND wdai.INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT delivery_id, ultimate_dropoff_location_id
FROM wsh_new_deliveries
WHERE name=l_del_name;
SELECT to_number(org_information3)
FROM hr_organization_information
WHERE organization_id = p_organization_id
AND org_information_context = 'Accounting Information';
SELECT DECODE(msi.location_control_code, 1, 'N', 'Y') loc_control_flag,
msi.restrict_locators_code,
msi.restrict_subinventories_code,
msi.location_control_code,
msi.reservable_type,
msi.MTL_TRANSACTIONS_ENABLED_FLAG -- Bug 3599363
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.inventory_item_id = v_inventory_item_id
AND msi.organization_id = v_organization_id;
SELECT COUNT (delivery_interface_id)
INTO l_del_count
FROM wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
-- Select the distinct customer_name from this delivery's delivery details
-- If there is more than one distinct customer_name at the delivery detail level,
-- Raise an exception because that is an invalid case
IF(delivery_rec.customer_name IS NULL) THEN
IF l_debug_on THEN
wsh_debug_sv.log (l_module_name, 'Delivery Rec customer name is null');
SELECT DISTINCT customer_name
INTO l_detail_customer_name
FROM wsh_del_details_interface wddi, wsh_del_assgn_interface wdai
WHERE wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wddi.customer_name IS NOT NULL
AND wdai.delivery_interface_id = p_delivery_interface_id
AND wddi.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND wdai.INTERFACE_ACTION_CODE = '94X_INBOUND';
/* Update ID fields in interface table only if all the validations succeeded */
IF l_debug_on THEN
wsh_debug_sv.log (l_module_name, 'l_d_temp_status',l_d_temp_status);
UPDATE wsh_new_del_interface
SET delivery_id = decode(p_document_type, 'SA',l_dlvy_id,delivery_id),
customer_id = l_customer_id,
organization_id = l_org_id,
intmed_ship_to_location_id = l_intmed_ship_to_location_id,
initial_pickup_location_id = l_initial_pickup_location_id,
ultimate_dropoff_location_id = l_ultimate_dropoff_location_id
WHERE delivery_interface_id = p_delivery_interface_id;
UPDATE wsh_del_legs_interface
SET delivery_id = l_dlvy_id
WHERE delivery_interface_id = p_delivery_interface_id;
/* Update ID fields in interface table only if all the validations succeeded */
IF l_debug_on THEN
wsh_debug_sv.log (l_module_name, 'Delivery ID', l_dlvy_id);
UPDATE wsh_del_details_interface
SET inventory_item_id = l_inventory_item_id,
customer_item_id = l_customer_item_id,
organization_id = l_det_org_id,
ship_from_location_id = l_ship_from_location_id,
ship_to_location_id = l_ship_to_location_id,
intmed_ship_to_location_id = l_det_intmed_shipto,
deliver_to_location_id = l_deliver_to_location_id,
customer_id = l_det_customer_id,
ship_to_site_use_id = l_ship_to_site_use_id,
deliver_to_site_use_id = l_deliver_to_site_use_id,
org_id = l_line_op_unit_id,
-- TPW - Distributed changes
locator_id = l_locator_id,
source_header_id = decode(p_document_type, 'SR', l_dlvy_id, source_header_id)
WHERE delivery_detail_interface_id = delivery_detail_rec.delivery_detail_interface_id;
PROCEDURE NAME : Delete_Interface_Records
This Procedure will be used to delete record in the different interface tables, after data
is populated in the base tables ????
=======================================================================================*/
PROCEDURE delete_interface_records (
p_delivery_interface_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_transaction_status wsh_transactions_history.transaction_status%TYPE;
invalid_delete EXCEPTION;
SELECT delivery_detail_interface_id, del_assgn_interface_id
FROM wsh_del_assgn_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT wdli.delivery_leg_interface_id,
wdli.pick_up_stop_interface_id,
wdli.drop_off_stop_interface_id, wtsi.trip_interface_id
FROM wsh_del_legs_interface wdli, wsh_trip_stops_interface wtsi
WHERE wdli.delivery_interface_id = p_delivery_interface_id
AND wdli.pick_up_stop_interface_id = wtsi.stop_interface_id
AND wdli.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND wtsi.INTERFACE_ACTION_CODE = '94X_INBOUND';
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_INTERFACE_RECORDS';
wsh_debug_sv.push (l_module_name, 'DELETE INTERFACE RECORDS');
SELECT COUNT (*)
INTO l_del_count
FROM wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_del_legs_interface
WHERE delivery_leg_interface_id = ids_rec.delivery_leg_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_freight_costs_interface
WHERE delivery_leg_interface_id = ids_rec.delivery_leg_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_trip_stops_interface
WHERE stop_interface_id IN
(ids_rec.pick_up_stop_interface_id,
ids_rec.drop_off_stop_interface_id
)
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_freight_costs_interface
WHERE stop_interface_id IN
(ids_rec.pick_up_stop_interface_id,
ids_rec.drop_off_stop_interface_id
)
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_trips_interface
WHERE trip_interface_id = ids_rec.trip_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_freight_costs_interface
WHERE trip_interface_id = ids_rec.trip_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_del_details_interface
WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_freight_costs_interface
WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_del_assgn_interface
WHERE delivery_detail_interface_id = detail_interface_ids_rec.delivery_detail_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
DELETE wsh_freight_costs_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE = '94X_INBOUND';
WHEN invalid_delete
THEN
x_return_status := wsh_util_core.g_ret_sts_error;
WSH_DEBUG_SV.logmsg(l_module_name,'invalid_delete exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_delete');
END delete_interface_records;
PROCEDURE NAME : Delete_Interface_Records
This Procedure will be used to delete record in the different interface tables, after data
is populated in the base tables
=======================================================================================*/
PROCEDURE delete_interface_records (
p_del_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
p_del_det_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
p_del_assgn_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
p_del_error_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
p_det_error_interface_id_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_INTERFACE_RECORDS';
wsh_debug_sv.push (l_module_name, 'DELETE INTERFACE RECORDS');
DELETE FROM wsh_interface_errors
WHERE interface_error_id = p_det_error_interface_id_tbl(i);
wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_interface_errors');
DELETE FROM wsh_interface_errors
WHERE interface_error_id = p_del_error_interface_id_tbl(i);
wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_interface_errors');
DELETE FROM wsh_del_assgn_interface
WHERE del_assgn_interface_id = p_del_assgn_interface_id_tbl(i);
wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_del_assgn_interface');
DELETE FROM wsh_del_details_interface
WHERE delivery_detail_interface_id = p_del_det_interface_id_tbl(i);
wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_del_details_interface');
DELETE FROM wsh_new_del_interface
WHERE delivery_interface_id = p_del_interface_id_tbl(i);
wsh_debug_sv.logmsg ( l_module_name, 'Deleted '||SQL%ROWCOUNT||' Records from wsh_new_del_interface');
END delete_interface_records;