[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mp.organization_id
INTO x_trading_partner_ID
FROM mtl_parameters mp, hr_organization_information hoi
WHERE mp.organization_id = hoi.organization_id and
hoi.org_information1 = 'INV' and
hoi.org_information2 = 'Y' and
hoi.org_information_context = 'CLASS' and
mp.organization_code = p_trading_partner_Code;
SELECT 'X'
INTO p_duplicate
FROM wsh_transactions_history wth
WHERE wth.document_type = p_doc_type
AND wth.document_number = p_doc_number
AND wth.action_type = p_action_type
AND wth.trading_partner_id = x_trading_partner_id
--R12.1.1 STANDALONE PROJECT
-- LSP PROJECT : consider LSP mode also by checking the profile as well as client_code value on WNDI.
AND ((l_wms_deployment_mode <> 'D' AND l_wms_deployment_mode <> 'L') OR ((l_wms_deployment_mode = 'D' AND wth.document_revision = p_doc_revision))
OR (l_wms_deployment_mode = 'L' AND wth.document_revision = p_doc_revision AND p_client_code IS NOT NULL))
AND wth.document_direction = p_doc_direction;
SELECT 'X'
INTO p_940_exists
FROM wsh_transactions_history
WHERE document_number = p_doc_number
AND document_type = 'SR'
AND document_direction = 'I'
AND action_type in ('A', 'C')
AND rownum = 1;
SELECT 'X'
INTO p_940_exists
FROM wsh_transactions_history
WHERE document_number = p_orig_document_number
AND document_type = 'SR'
AND document_direction = 'I'
AND action_type = 'A';
SELECT 'X'
INTO p_940_exists
FROM wsh_transactions_history
WHERE document_number = p_orig_document_number
AND document_type = 'SR'
AND document_direction = 'O'
AND action_type = 'A';
SELECT party_type
INTO P_trading_partner_type
FROM ecx_tp_headers
WHERE party_id = P_trading_partner_id
AND party_site_id = P_trading_partner_site_id;
SELECT client_code
INTO p_client_code
FROM
mtl_client_parameters
WHERE trading_partner_site_id = P_trading_partner_site_id;
SELECT DISTINCT wdd.delivery_detail_id
FROM wsh_del_details_interface wdd,
wsh_del_assgn_interface wda
WHERE wdd.delivery_detail_interface_id = wda.delivery_detail_interface_id
AND wdd.container_flag = 'N'
AND wda.delivery_id = p_delivery_id
AND wda.interface_action_code = '94X_INBOUND'
AND wdd.interface_action_code = '94X_INBOUND';
SELECT 'X'
INTO x_delivery_exists
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT 'X'
INTO x_delivery_detail_exists
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.delivery_detail_id = delivery_detail_int_rec.delivery_detail_id
AND wda.delivery_id = p_delivery_id;
SELECT DISTINCT wdd.source_header_number, wdd.delivery_detail_id, wdd.line_direction
FROM wsh_del_details_interface wdd,
wsh_del_assgn_interface wda
WHERE wdd.delivery_detail_interface_id = wda.delivery_detail_interface_id
AND wdd.container_flag <> 'Y'
AND wda.delivery_interface_id = p_delivery_interface_id
AND wda.interface_action_code = '94X_INBOUND'
AND wdd.interface_action_code = '94X_INBOUND';
SELECT distinct 'X'
INTO l_delivery_detail_exists
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
oe_order_lines_all ol,
po_requisition_lines_all pl,
po_requisition_headers_all ph
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.released_status in ('R','B','X')
AND wdd.source_code = 'OE'
AND wdd.source_line_id = ol.line_id
AND ol.source_document_line_id = pl.requisition_line_id
AND ol.source_document_id = pl.requisition_header_id
AND pl.requisition_header_id = ph.requisition_header_id
AND pl.line_num = delivery_detail_int_rec.delivery_detail_id
AND ph.segment1 = delivery_detail_int_rec.source_header_number;
SELECT distinct 'X'
INTO l_delivery_detail_exists
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_shipment_batches wsb,
wsh_transactions_history wth
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.shipment_line_number = delivery_detail_int_rec.delivery_detail_id
AND wdd.released_status in ('R','B','X')--Fulfillment Batch XML Gateway Project
AND wdd.source_code = 'OE'
AND wdd.shipment_batch_id = wsb.batch_id
AND wsb.name = wth.entity_number
AND wth.document_number = delivery_detail_int_rec.source_header_number
AND wth.entity_type = 'BATCH'
AND wth.document_type = 'SR'
AND wth.document_direction = 'O';
SELECT customer_id, initial_pickup_location_id,
intmed_ship_to_location_id, organization_id,
ultimate_dropoff_location_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT DISTINCT delivery_detail_id, customer_id, customer_item_id,
deliver_to_location_id, intmed_ship_to_location_id,
inventory_item_id, organization_id, ship_from_location_id,
ship_to_location_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT customer_id, initial_pickup_location_id,
intmed_ship_to_location_id, organization_id,
ultimate_dropoff_location_id
FROM wsh_new_del_interface
WHERE delivery_id = p_delivery_id
AND INTERFACE_ACTION_CODE ='94X_INBOUND';
SELECT DISTINCT wdd.delivery_detail_id, wdd.customer_id, wdd.customer_item_id,
wdd.deliver_to_location_id, wdd.intmed_ship_to_location_id,
wdd.inventory_item_id, wdd.organization_id,
wdd.ship_from_location_id, wdd.ship_to_location_id
FROM wsh_del_assgn_interface wda, wsh_del_details_interface wdd
WHERE wda.delivery_id = p_delivery_id
AND wda.delivery_detail_interface_id = wdd.delivery_detail_interface_id
AND wdd.container_flag = 'N'
AND WDD.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND WDA.INTERFACE_ACTION_CODE ='94X_INBOUND';
wsh_debug_sv.log (l_module_name, 'Inserting into wsh_interface_errors');
INSERT INTO wsh_interface_errors
(interface_error_id,
interface_table_name,
interface_id, error_message, creation_date,
created_by, last_update_date, last_updated_by,
last_update_login,INTERFACE_ACTION_CODE)
VALUES (wsh_interface_errors_s.NEXTVAL,
p_interface_errors_rec.p_interface_table_name,
p_interface_errors_rec.p_interface_id, l_text, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
fnd_global.user_id,'94X_INBOUND');
INSERT INTO wsh_interface_errors(
interface_error_id,
interface_table_name,
interface_id,
error_message,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
interface_action_code)
VALUES (wsh_interface_errors_s.NEXTVAL,
l_interface_table_name_tab(i),
l_interface_id_tab(i),
l_text_tab(i),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
fnd_global.conc_request_id,
p_interface_action_code);
INSERT INTO wsh_interface_errors(
interface_error_id,
interface_table_name,
interface_id,
error_message,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
interface_action_code)
VALUES (wsh_interface_errors_s.NEXTVAL,
l_interface_table_name_tab(i),
l_interface_id_tab(i),
l_text_tab(i),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
fnd_global.conc_request_id,
p_interface_action_code);
wsh_debug_sv.logmsg (l_module_name, 'Inserted '||l_text_tab.COUNT||' Interface Error Records');