[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Delete_Shipment_Request
(
p_action_code IN VARCHAR2,
p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2);
ELSIF p_action_code = 'UPDATE' OR p_action_code ='DELETE' THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Update_Delete_Shipment_Request', WSH_DEBUG_SV.C_PROC_LEVEL);
Update_Delete_Shipment_Request(
p_action_code => p_action_code,
p_shipment_request_info => p_shipment_request_info,
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name, 'p_action_code should be CREATE/QUERY/UPDATE/DELETE.The current value is',p_action_code);
SELECT to_char(p_date1,'YYYY/MM/DD HH24:MI:SS'),to_char(p_date2,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
select count(*) into l_exists
FROM wsh_transactions_history
WHERE document_number = to_char(p_shipment_request_info.document_number)
and document_revision = p_shipment_request_info.document_revision
AND document_type = 'SR'
AND document_direction = 'I';
wsh_debug_sv.logmsg(l_module_name, 'Inserting into WSH_NEW_DEL_INTERFACE');
INSERT INTO WSH_NEW_DEL_INTERFACE(
DELIVERY_INTERFACE_ID,
PLANNED_FLAG,
STATUS_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
DELIVERY_TYPE,
ORGANIZATION_CODE,
CARRIER_CODE,
SERVICE_LEVEL,
MODE_OF_TRANSPORT,
CUSTOMER_ID,
CUSTOMER_NAME,
SHIP_TO_CUSTOMER_ID,
SHIP_TO_CUSTOMER_NAME,
SHIP_TO_ADDRESS_ID,
SHIP_TO_ADDRESS1,
SHIP_TO_ADDRESS2,
SHIP_TO_ADDRESS3,
SHIP_TO_ADDRESS4,
SHIP_TO_CITY,
SHIP_TO_STATE,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
SHIP_TO_CONTACT_ID,
SHIP_TO_CONTACT_NAME,
SHIP_TO_CONTACT_PHONE,
INVOICE_TO_CUSTOMER_ID,
INVOICE_TO_CUSTOMER_NAME,
INVOICE_TO_ADDRESS_ID,
INVOICE_TO_ADDRESS1,
INVOICE_TO_ADDRESS2,
INVOICE_TO_ADDRESS3,
INVOICE_TO_ADDRESS4,
INVOICE_TO_CITY,
INVOICE_TO_STATE,
INVOICE_TO_COUNTRY,
INVOICE_TO_POSTAL_CODE,
INVOICE_TO_CONTACT_ID,
INVOICE_TO_CONTACT_NAME,
INVOICE_TO_CONTACT_PHONE,
DELIVER_TO_CUSTOMER_ID,
DELIVER_TO_CUSTOMER_NAME,
DELIVER_TO_ADDRESS_ID,
DELIVER_TO_ADDRESS1,
DELIVER_TO_ADDRESS2,
DELIVER_TO_ADDRESS3,
DELIVER_TO_ADDRESS4,
DELIVER_TO_CITY,
DELIVER_TO_STATE,
DELIVER_TO_COUNTRY,
DELIVER_TO_POSTAL_CODE,
DELIVER_TO_CONTACT_ID,
DELIVER_TO_CONTACT_NAME,
DELIVER_TO_CONTACT_PHONE,
FREIGHT_TERMS_CODE,
FOB_CODE,
TRANSACTION_TYPE_ID,
PRICE_LIST_ID,
CURRENCY_CODE,
CLIENT_CODE, -- LSP PROJECT
INTERFACE_ACTION_CODE)
Values(
wsh_new_del_interface_s.nextval,
'N',
'OP',
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.Conc_Program_Id,
sysdate,
FND_GLOBAL.Conc_Request_Id,
'I',
p_shipment_request_info.organization_code,
p_shipment_request_info.CARRIER_CODE,
p_shipment_request_info.SERVICE_LEVEL,
p_shipment_request_info.MODE_OF_TRANSPORT,
p_shipment_request_info.customer_id,
p_shipment_request_info.customer_name,
p_shipment_request_info.ship_to_customer_id,
p_shipment_request_info.ship_to_customer_name,
p_shipment_request_info.ship_to_address_id,
p_shipment_request_info.ship_to_address1,
p_shipment_request_info.ship_to_address2,
p_shipment_request_info.ship_to_address3,
p_shipment_request_info.ship_to_address4,
p_shipment_request_info.ship_to_city,
p_shipment_request_info.ship_to_state,
p_shipment_request_info.ship_to_country,
p_shipment_request_info.ship_to_postal_code,
p_shipment_request_info.ship_to_contact_id ,
p_shipment_request_info.ship_to_contact_name,
p_shipment_request_info.ship_to_contact_phone,
p_shipment_request_info.invoice_to_customer_id,
p_shipment_request_info.invoice_to_customer_name,
p_shipment_request_info.invoice_to_address_id,
p_shipment_request_info.invoice_to_address1,
p_shipment_request_info.invoice_to_address2,
p_shipment_request_info.invoice_to_address3,
p_shipment_request_info.invoice_to_address4,
p_shipment_request_info.invoice_to_city,
p_shipment_request_info.invoice_to_state,
p_shipment_request_info.invoice_to_country,
p_shipment_request_info.invoice_to_postal_code,
p_shipment_request_info.invoice_to_contact_id,
p_shipment_request_info.invoice_to_contact_name,
p_shipment_request_info.invoice_to_contact_phone,
p_shipment_request_info.deliver_to_customer_id,
p_shipment_request_info.deliver_to_customer_name,
p_shipment_request_info.deliver_to_address_id,
p_shipment_request_info.deliver_to_address1,
p_shipment_request_info.deliver_to_address2,
p_shipment_request_info.deliver_to_address3,
p_shipment_request_info.deliver_to_address4,
p_shipment_request_info.deliver_to_city,
p_shipment_request_info.deliver_to_state,
p_shipment_request_info.deliver_to_country,
p_shipment_request_info.deliver_to_postal_code,
p_shipment_request_info.deliver_to_contact_id,
p_shipment_request_info.deliver_to_contact_name,
p_shipment_request_info.deliver_to_contact_phone,
p_shipment_request_info.freight_terms_code,
p_shipment_request_info.fob_code,
p_shipment_request_info.transaction_type_id,
p_shipment_request_info.price_list_id,
p_shipment_request_info.currency_code,
p_shipment_request_info.client_code, -- LSP PROJECT
'94X_STANDALONE'
)
RETURNING DELIVERY_INTERFACE_ID INTO p_shipment_request_info.delivery_interface_id;
WSH_DEBUG_SV.log(l_module_name, 'Number of records inserted into WSH_NEW_DEL_INTERFACE',l_ins_rows);
wsh_debug_sv.logmsg(l_module_name, 'Inserting into WSH_DEL_DETAILS_INTERFACE and WSH_DEL_ASSGN_INTERFACE');
INSERT INTO WSH_DEL_DETAILS_INTERFACE(
DELIVERY_DETAIL_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CONTAINER_FLAG,
DATE_REQUESTED,
DATE_SCHEDULED,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
CUSTOMER_ITEM_ID,
CUSTOMER_ITEM_NUMBER,
REQUESTED_QUANTITY,
REQUESTED_QUANTITY_UOM,
SRC_REQUESTED_QUANTITY,
SRC_REQUESTED_QUANTITY_UOM,
LINE_NUMBER,
SOURCE_LINE_NUMBER,
SOURCE_HEADER_NUMBER,
EARLIEST_PICKUP_DATE,
LATEST_PICKUP_DATE,
EARLIEST_DROPOFF_DATE,
LATEST_DROPOFF_DATE,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW,
SHIPPING_INSTRUCTIONS,
PACKING_INSTRUCTIONS,
SHIPMENT_PRIORITY_CODE,
CUST_PO_NUMBER,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR_CODE,
LOT_NUMBER,
REVISION,
SHIP_SET_NAME,
UNIT_SELLING_PRICE,
LINE_DIRECTION,
INTERFACE_ACTION_CODE)
VALUES(
wsh_del_details_interface_s.nextval,
'OE',
-1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.Conc_Program_Id,
sysdate,
FND_GLOBAL.Conc_Request_Id,
'N',
p_shipment_request_info.shipment_details_tab(i).date_requested,
p_shipment_request_info.shipment_details_tab(i).date_scheduled,
p_shipment_request_info.shipment_details_tab(i).inventory_item_id,
p_shipment_request_info.shipment_details_tab(i).item_number,
p_shipment_request_info.shipment_details_tab(i).customer_item_id,
p_shipment_request_info.shipment_details_tab(i).customer_item_number,
p_shipment_request_info.shipment_details_tab(i).requested_quantity,
p_shipment_request_info.shipment_details_tab(i).requested_quantity_uom,
p_shipment_request_info.shipment_details_tab(i).src_requested_quantity,
p_shipment_request_info.shipment_details_tab(i).src_requested_quantity_uom,
p_shipment_request_info.shipment_details_tab(i).line_number,
p_shipment_request_info.shipment_details_tab(i).source_line_number,
p_shipment_request_info.shipment_details_tab(i).source_header_number,
p_shipment_request_info.shipment_details_tab(i).earliest_pickup_date,
p_shipment_request_info.shipment_details_tab(i).latest_pickup_date,
p_shipment_request_info.shipment_details_tab(i).earliest_dropoff_date,
p_shipment_request_info.shipment_details_tab(i).latest_dropoff_date,
p_shipment_request_info.shipment_details_tab(i).ship_tolerance_above,
p_shipment_request_info.shipment_details_tab(i).ship_tolerance_below,
p_shipment_request_info.shipment_details_tab(i).shipping_instructions,
p_shipment_request_info.shipment_details_tab(i).packing_instructions,
p_shipment_request_info.shipment_details_tab(i).shipment_priority_code,
p_shipment_request_info.shipment_details_tab(i).cust_po_number,
p_shipment_request_info.shipment_details_tab(i).subinventory,
p_shipment_request_info.shipment_details_tab(i).locator_id,
p_shipment_request_info.shipment_details_tab(i).locator_code,
p_shipment_request_info.shipment_details_tab(i).lot_number,
p_shipment_request_info.shipment_details_tab(i).revision,
p_shipment_request_info.shipment_details_tab(i).ship_set_name,
p_shipment_request_info.shipment_details_tab(i).unit_selling_price,
'O',
'94X_STANDALONE')
RETURNING DELIVERY_DETAIL_INTERFACE_ID INTO p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id;
INSERT INTO WSH_DEL_ASSGN_INTERFACE(
DEL_ASSGN_INTERFACE_ID,
DELIVERY_DETAIL_INTERFACE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
DELIVERY_INTERFACE_ID,
INTERFACE_ACTION_CODE
)
VALUES(
wsh_del_assgn_interface_s.nextval,
p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.Conc_Program_Id,
sysdate,
FND_GLOBAL.Conc_Request_Id,
p_shipment_request_info.delivery_interface_id,
'94X_STANDALONE'
);
wsh_debug_sv.logmsg(l_module_name, 'Done Inserting into WSH_DEL_DETAILS_INTERFACE and WSH_DEL_ASSGN_INTERFACE'||p_shipment_request_info.shipment_details_tab(i).delivery_detail_interface_id);
INSERT INTO WSH_DEL_DETAILS_INTERFACE(
DELIVERY_DETAIL_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
CONTAINER_FLAG,
DATE_REQUESTED,
DATE_SCHEDULED,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
CUSTOMER_ITEM_ID,
CUSTOMER_ITEM_NUMBER,
REQUESTED_QUANTITY,
REQUESTED_QUANTITY_UOM,
SRC_REQUESTED_QUANTITY,
SRC_REQUESTED_QUANTITY_UOM,
LINE_NUMBER,
SOURCE_LINE_NUMBER,
SOURCE_HEADER_NUMBER,
EARLIEST_PICKUP_DATE,
LATEST_PICKUP_DATE,
EARLIEST_DROPOFF_DATE,
LATEST_DROPOFF_DATE,
SHIP_TOLERANCE_ABOVE,
SHIP_TOLERANCE_BELOW,
SHIPPING_INSTRUCTIONS,
PACKING_INSTRUCTIONS,
SHIPMENT_PRIORITY_CODE,
CUST_PO_NUMBER,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR_CODE,
LOT_NUMBER,
REVISION,
SHIP_SET_NAME,
UNIT_SELLING_PRICE,
LINE_DIRECTION,
INTERFACE_ACTION_CODE)
VALUES(
wsh_del_details_interface_s.nextval,
'OE',
-1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.Conc_Program_Id,
sysdate,
FND_GLOBAL.Conc_Request_Id,
'N',
--Bug8784331
l_date_requested(i),
l_date_scheduled(i),
l_inventory_item_id(i),
l_item_number(i),
l_customer_item_id(i),
l_customer_item_number(i),
l_requested_quantity(i),
l_requested_quantity_uom(i),
l_src_requested_quantity(i),
l_src_requested_quantity_uom(i),
l_line_number(i),
l_source_line_number(i),
l_source_header_number(i),
l_earliest_pickup_date(i),
l_latest_pickup_date(i),
l_earliest_dropoff_date(i),
l_latest_dropoff_date(i),
l_ship_tolerance_above(i),
l_ship_tolerance_below(i),
l_shipping_instructions(i),
l_packing_instructions(i),
l_shipment_priority_code(i),
l_cust_po_number(i),
l_subinventory(i),
l_locator_id(i),
l_locator_code(i),
l_lot_number(i),
l_revision(i),
l_ship_set_name(i),
l_unit_selling_price(i),
--Bug8784331
'O',
'94X_STANDALONE')
RETURNING DELIVERY_DETAIL_INTERFACE_ID BULK COLLECT INTO l_del_det_int_tab;
WSH_DEBUG_SV.log(l_module_name, 'Number of records inserted into WSH_DEL_DETAILS_INTERFACE',l_ins_rows );
INSERT INTO WSH_DEL_ASSGN_INTERFACE(
DEL_ASSGN_INTERFACE_ID,
DELIVERY_DETAIL_INTERFACE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
DELIVERY_INTERFACE_ID,
INTERFACE_ACTION_CODE
)
VALUES(
wsh_del_assgn_interface_s.nextval,
l_del_det_int_tab(i), --Bug8784331
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.Conc_Program_Id,
sysdate,
FND_GLOBAL.Conc_Request_Id,
p_shipment_request_info.delivery_interface_id,
'94X_STANDALONE'
);
WSH_DEBUG_SV.log(l_module_name, 'Number of records inserted into WSH_DEL_ASSGN_INTERFACE',l_ins_rows );
WSH_DEBUG_SV.log(l_module_name, 'Inserting into WSH_TRANSACTIONS_HISTORY' );
INSERT INTO WSH_TRANSACTIONS_HISTORY(
TRANSACTION_ID,
DOCUMENT_TYPE,
DOCUMENT_NUMBER,
DOCUMENT_DIRECTION,
TRANSACTION_STATUS,
ACTION_TYPE,
ENTITY_NUMBER,
ENTITY_TYPE,
TRADING_PARTNER_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
DOCUMENT_REVISION
)
VALUES (
wsh_transaction_s.nextval,
'SR',
p_shipment_request_info.Document_Number,
'I',
'AP',
p_shipment_request_info.ACTION_TYPE,
p_shipment_request_info.delivery_interface_id,
'DLVY_INT',
-1,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.Conc_Program_Id,
sysdate,
FND_GLOBAL.Conc_Request_Id,
p_shipment_request_info.document_revision )
RETURNING TRANSACTION_ID INTO p_shipment_request_info.transaction_id;
SELECT p_shipment_request_info.document_number,p_shipment_request_info.document_revision,NULL,wie.error_message
FROM WSH_INTERFACE_ERRORS WIE
WHERE wie.interface_id = p_shipment_request_info.delivery_interface_id
AND wie.interface_table_name = 'WSH_NEW_DEL_INTERFACE'
UNION
SELECT p_shipment_request_info.document_number,p_shipment_request_info.document_revision,wddi.line_number,wie.error_message
FROM WSH_INTERFACE_ERRORS WIE,
wsh_del_assgn_interface WDAI,
WSH_DEL_DETAILS_INTERFACE WDDI
WHERE wie.interface_table_name = 'WSH_DEL_DETAILS_INTERFACE'
AND wie.interface_id = WDAI.delivery_detail_interface_id
AND wddi.delivery_detail_interface_id = WDAI.delivery_detail_interface_id
and WDAI.delivery_interface_id = p_shipment_request_info.delivery_interface_id;
SELECT WTH.transaction_id,
WTH.action_type,
WTH.document_number,
WTH.document_revision,
WNDI.delivery_interface_id,
WNDI.organization_code,
WNDI.customer_id,
WNDI.customer_name,
WNDI.ship_to_customer_id,
WNDI.ship_to_customer_name,
WNDI.ship_to_address_id,
WNDI.ship_to_address1,
WNDI.ship_to_address2,
WNDI.ship_to_address3,
WNDI.ship_to_address4,
WNDI.ship_to_city,
WNDI.ship_to_state,
WNDI.ship_to_country,
WNDI.ship_to_postal_code,
WNDI.ship_to_contact_id,
WNDI.ship_to_contact_name,
WNDI.ship_to_contact_phone,
WNDI.invoice_to_customer_id,
WNDI.invoice_to_customer_name,
WNDI.invoice_to_address_id,
WNDI.invoice_to_address1,
WNDI.invoice_to_address2,
WNDI.invoice_to_address3,
WNDI.invoice_to_address4,
WNDI.invoice_to_city,
WNDI.invoice_to_state,
WNDI.invoice_to_country,
WNDI.invoice_to_postal_code,
WNDI.invoice_to_contact_id,
WNDI.invoice_to_contact_name,
WNDI.invoice_to_contact_phone,
WNDI.deliver_to_customer_id,
WNDI.deliver_to_customer_name,
WNDI.deliver_to_address_id,
WNDI.deliver_to_address1,
WNDI.deliver_to_address2,
WNDI.deliver_to_address3,
WNDI.deliver_to_address4,
WNDI.deliver_to_city,
WNDI.deliver_to_state,
WNDI.deliver_to_country,
WNDI.deliver_to_postal_code,
WNDI.deliver_to_contact_id,
WNDI.deliver_to_contact_name,
WNDI.deliver_to_contact_phone,
WNDI.carrier_code,
WNDI.service_level,
WNDI.mode_of_transport,
WNDI.freight_terms_code,
WNDI.fob_code,
WNDI.currency_code,
WNDI.transaction_type_id,
WNDI.price_list_id,
WNDI.client_code -- LSP PROJECT
FROM WSH_TRANSACTIONS_HISTORY WTH,
WSH_NEW_DEL_INTERFACE WNDI
WHERE WTH.document_number = to_char(p_shipment_request_info.document_number)
AND WTH.document_revision = p_shipment_request_info.document_revision
AND WTH.document_type ='SR'
AND WTH.document_direction ='I'
AND to_number(WTH.entity_number) = WNDI.delivery_interface_id
AND WNDI.Interface_action_code = '94X_STANDALONE' ;
SELECT WDDI.delivery_detail_interface_id,
WDDI.line_number,
WDDI.item_number,
WDDI.inventory_item_id,
WDDI.item_description,
WDDI.requested_quantity,
WDDI.requested_quantity_uom,
WDDI.customer_item_number,
WDDI.customer_item_id,
WDDI.date_requested,
WDDI.date_scheduled,
WDDI.ship_tolerance_above,
WDDI.ship_tolerance_below,
WDDI.packing_instructions,
WDDI.shipping_instructions,
WDDI.shipment_priority_code,
WDDI.ship_set_name,
WDDI.subinventory,
WDDI.revision,
WDDI.locator_code,
WDDI.locator_id,
WDDI.lot_number,
WDDI.unit_selling_price,
WDDI.currency_code,
WDDI.earliest_pickup_date,
WDDI.latest_pickup_date,
WDDI.earliest_dropoff_date,
WDDI.latest_dropoff_date,
WDDI.cust_po_number,
WDDI.source_header_number,
WDDI.source_line_number,
WDDI.src_requested_quantity,
WDDI.src_requested_quantity_uom
FROM WSH_DEL_DETAILS_INTERFACE WDDI ,
WSH_DEL_ASSGN_INTERFACE WDAI
WHERE WDDI.delivery_detail_interface_id = WDAI.delivery_detail_interface_id
AND WDAI.delivery_interface_id = p_shipment_request_info.delivery_interface_id
AND WDDI.Interface_action_code = '94X_STANDALONE';
PROCEDURE Update_Delete_Shipment_Request(
p_action_code IN VARCHAR2 ,
p_shipment_request_info IN OUT NOCOPY Shipment_Request_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2) AS
CURSOR c_get_Details_DN IS
SELECT wth.document_number,wth.document_revision,wndi.delivery_interface_id,wth.transaction_id
FROM WSH_TRANSACTIONS_HISTORY WTH,
WSH_NEW_DEL_INTERFACE WNDI,
wsh_interface_errors wie
WHERE WTH.document_number = to_char(p_shipment_request_info.document_number)
and WTH.document_revision = p_shipment_request_info.document_revision
and wth.document_type = 'SR'
and wth.document_direction = 'I'
and wie.interface_id(+) = WNDI.delivery_interface_id
and wie.interface_table_name(+) = 'WSH_NEW_DEL_INTERFACE'
and to_number(WTH.entity_number) = WNDI.delivery_interface_id
AND WNDI.Interface_action_code = '94X_STANDALONE'
FOR UPDATE OF wth.transaction_id, wndi.delivery_interface_id ,wie.interface_id NOWAIT;
SELECT wdai.delivery_detail_interface_id
FROM WSH_DEL_ASSGN_INTERFACE wdai,
wsh_del_details_interface wddi,
wsh_interface_errors wie
WHERE wdai.delivery_interface_id = p_shipment_request_info.delivery_interface_id
and wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
and wie.interface_id(+) = wdai.delivery_detail_interface_id
and wie.interface_table_name(+) = 'WSH_DEL_DETAILS_INTERFACE'
FOR UPDATE OF WDAI.delivery_detail_interface_id, wddi.delivery_detail_interface_id,wie.interface_id NOWAIT;
SELECT wdai.delivery_detail_interface_id
FROM wsh_del_details_interface wddi,
wsh_del_assgn_interface wdai,
wsh_interface_errors wie
WHERE wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
AND wdai.delivery_interface_id= p_shipment_request_info.delivery_interface_id
AND wddi.line_number = c_line_number
and wie.interface_id(+) = wdai.delivery_detail_interface_id
and wie.interface_table_name(+) = 'WSH_DEL_DETAILS_INTERFACE'
FOR UPDATE OF WDAI.delivery_detail_interface_id, wddi.delivery_detail_interface_id,wie.interface_id NOWAIT;
SELECT Count(*)
from wsh_del_assgn_interface wdai
where wdai.delivery_interface_id= p_shipment_request_info.delivery_interface_id;
l_insert_sr_info Shipment_Request_Rec_Type;
l_update_sr_info Shipment_Request_Rec_Type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_Delete_Shipment_Request';
l_insert_sr_info.document_number := p_shipment_request_info.document_number;
l_insert_sr_info.document_revision := p_shipment_request_info.document_revision;
l_insert_sr_info.delivery_interface_id := p_shipment_request_info.delivery_interface_id;
IF p_action_code = 'UPDATE' THEN
--
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name, 'line number ' || p_shipment_request_info.shipment_details_tab(i).line_number || ' does not exist, so insert into WDDI');
l_insert_sr_info.shipment_details_tab(l_ins_count):= p_shipment_request_info.shipment_details_tab(i);
l_update_sr_info.shipment_details_tab(l_upd_count):= p_shipment_request_info.shipment_details_tab(i);
IF p_action_code ='DELETE' THEN
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name, 'Taking locks on WDDI and WDAI');
IF p_action_code ='UPDATE' THEN
--{
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name, 'Updating WSH_NEW_DEL_INTERFACE');
UPDATE WSH_NEW_DEL_INTERFACE
SET last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.Login_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,
organization_code = p_shipment_request_info.organization_code,
carrier_code = p_shipment_request_info.carrier_code,
service_level = p_shipment_request_info.service_level,
mode_of_transport = p_shipment_request_info.mode_of_transport,
customer_id = p_shipment_request_info.customer_id,
customer_name = p_shipment_request_info.customer_name,
ship_to_customer_id = p_shipment_request_info.ship_to_customer_id,
ship_to_customer_name = p_shipment_request_info.ship_to_customer_name,
ship_to_address_id = p_shipment_request_info.ship_to_address_id,
ship_to_address1 = p_shipment_request_info.ship_to_address1,
ship_to_address2 = p_shipment_request_info.ship_to_address2,
ship_to_address3 = p_shipment_request_info.ship_to_address3,
ship_to_address4 = p_shipment_request_info.ship_to_address4,
ship_to_city = p_shipment_request_info.ship_to_city,
ship_to_state = p_shipment_request_info.ship_to_state,
ship_to_country = p_shipment_request_info.ship_to_country,
ship_to_postal_code = p_shipment_request_info.ship_to_postal_code,
ship_to_contact_id = p_shipment_request_info.ship_to_contact_id ,
ship_to_contact_name = p_shipment_request_info.ship_to_contact_name,
ship_to_contact_phone = p_shipment_request_info.ship_to_contact_phone,
invoice_to_customer_id = p_shipment_request_info.invoice_to_customer_id,
invoice_to_customer_name = p_shipment_request_info.invoice_to_customer_name,
invoice_to_address_id = p_shipment_request_info.invoice_to_address_id,
invoice_to_address1 = p_shipment_request_info.invoice_to_address1,
invoice_to_address2 = p_shipment_request_info.invoice_to_address2,
invoice_to_address3 = p_shipment_request_info.invoice_to_address3,
invoice_to_address4 = p_shipment_request_info.invoice_to_address4,
invoice_to_city = p_shipment_request_info.invoice_to_city,
invoice_to_state = p_shipment_request_info.invoice_to_state,
invoice_to_country = p_shipment_request_info.invoice_to_country,
invoice_to_postal_code = p_shipment_request_info.invoice_to_postal_code,
invoice_to_contact_id = p_shipment_request_info.invoice_to_contact_id,
invoice_to_contact_name = p_shipment_request_info.invoice_to_contact_name,
invoice_to_contact_phone = p_shipment_request_info.invoice_to_contact_phone,
deliver_to_customer_id = p_shipment_request_info.deliver_to_customer_id,
deliver_to_customer_name = p_shipment_request_info.deliver_to_customer_name,
deliver_to_address_id = p_shipment_request_info.deliver_to_address_id,
deliver_to_address1 = p_shipment_request_info.deliver_to_address1,
deliver_to_address2 = p_shipment_request_info.deliver_to_address2,
deliver_to_address3 = p_shipment_request_info.deliver_to_address3,
deliver_to_address4 = p_shipment_request_info.deliver_to_address4,
deliver_to_city = p_shipment_request_info.deliver_to_city,
deliver_to_state = p_shipment_request_info.deliver_to_state,
deliver_to_country = p_shipment_request_info.deliver_to_country,
deliver_to_postal_code = p_shipment_request_info.deliver_to_postal_code,
deliver_to_contact_id = p_shipment_request_info.deliver_to_contact_id,
deliver_to_contact_name = p_shipment_request_info.deliver_to_contact_name,
deliver_to_contact_phone = p_shipment_request_info.deliver_to_contact_phone,
freight_terms_code = p_shipment_request_info.freight_terms_code,
fob_code = p_shipment_request_info.fob_code,
transaction_type_id = p_shipment_request_info.transaction_type_id,
price_list_id = p_shipment_request_info.price_list_id,
currency_code = p_shipment_request_info.currency_code,
client_code = p_shipment_request_info.client_code -- LSP PROJECT
WHERE delivery_interface_id = l_delivery_interface_id;
WSH_DEBUG_SV.log(l_module_name, 'WNDI : Number of records updated',l_ins_rows);
IF l_update_sr_info.shipment_details_tab.count > 0 AND
l_update_sr_info.shipment_details_tab.count < 4 THEN
--{
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name, 'Updating in non-bulk mode', l_update_sr_info.shipment_details_tab.count);
FOR i in 1..l_update_sr_info.shipment_details_tab.count LOOP
--
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name, 'updating delivery_detail_interface_id',l_update_sr_info.shipment_details_tab(i).delivery_detail_interface_id );
UPDATE WSH_DEL_DETAILS_INTERFACE
SET last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.Login_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,
date_requested = l_update_sr_info.shipment_details_tab(i).date_requested,
date_scheduled = l_update_sr_info.shipment_details_tab(i).date_scheduled,
inventory_item_id = l_update_sr_info.shipment_details_tab(i).inventory_item_id,
item_number = l_update_sr_info.shipment_details_tab(i).item_number,
customer_item_id = l_update_sr_info.shipment_details_tab(i).customer_item_id,
customer_item_number = l_update_sr_info.shipment_details_tab(i).customer_item_number,
requested_quantity = l_update_sr_info.shipment_details_tab(i).requested_quantity,
requested_quantity_uom = l_update_sr_info.shipment_details_tab(i).requested_quantity_uom,
src_requested_quantity = l_update_sr_info.shipment_details_tab(i).src_requested_quantity,
src_requested_quantity_uom = l_update_sr_info.shipment_details_tab(i).src_requested_quantity_uom,
line_number = l_update_sr_info.shipment_details_tab(i).line_number,
source_line_number = l_update_sr_info.shipment_details_tab(i).source_line_number,
earliest_pickup_date = l_update_sr_info.shipment_details_tab(i).earliest_pickup_date,
latest_pickup_date = l_update_sr_info.shipment_details_tab(i).latest_pickup_date,
earliest_dropoff_date = l_update_sr_info.shipment_details_tab(i).earliest_dropoff_date,
latest_dropoff_date = l_update_sr_info.shipment_details_tab(i).latest_dropoff_date,
ship_tolerance_above = l_update_sr_info.shipment_details_tab(i).ship_tolerance_above,
ship_tolerance_below = l_update_sr_info.shipment_details_tab(i).ship_tolerance_below,
shipping_instructions = l_update_sr_info.shipment_details_tab(i).shipping_instructions,
packing_instructions = l_update_sr_info.shipment_details_tab(i).packing_instructions,
shipment_priority_code = l_update_sr_info.shipment_details_tab(i).shipment_priority_code,
cust_po_number = l_update_sr_info.shipment_details_tab(i).cust_po_number,
subinventory = l_update_sr_info.shipment_details_tab(i).subinventory,
locator_id = l_update_sr_info.shipment_details_tab(i).locator_id,
locator_code = l_update_sr_info.shipment_details_tab(i).locator_code,
lot_number = l_update_sr_info.shipment_details_tab(i).lot_number,
revision = l_update_sr_info.shipment_details_tab(i).revision,
ship_set_name = l_update_sr_info.shipment_details_tab(i).ship_set_name,
unit_selling_price = l_update_sr_info.shipment_details_tab(i).unit_selling_price
WHERE delivery_detail_interface_id = l_update_sr_info.shipment_details_tab(i).delivery_detail_interface_id;
ELSIF l_update_sr_info.shipment_details_tab.count > 0 THEN
--{
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name, 'Updating in bulk mode', l_update_sr_info.shipment_details_tab.count);
FOR i in 1..l_update_sr_info.shipment_details_tab.count LOOP
l_date_requested(i) := l_update_sr_info.shipment_details_tab(i).date_requested;
l_date_scheduled(i) := l_update_sr_info.shipment_details_tab(i).date_scheduled;
l_inventory_item_id(i) := l_update_sr_info.shipment_details_tab(i).inventory_item_id;
l_item_number(i) := l_update_sr_info.shipment_details_tab(i).item_number;
l_customer_item_id(i) := l_update_sr_info.shipment_details_tab(i).customer_item_id;
l_customer_item_number(i) := l_update_sr_info.shipment_details_tab(i).customer_item_number;
l_requested_quantity(i) := l_update_sr_info.shipment_details_tab(i).requested_quantity;
l_requested_quantity_uom(i) := l_update_sr_info.shipment_details_tab(i).requested_quantity_uom;
l_src_requested_quantity(i) := l_update_sr_info.shipment_details_tab(i).src_requested_quantity;
l_src_requested_quantity_uom(i) := l_update_sr_info.shipment_details_tab(i).src_requested_quantity_uom;
l_line_number(i) := l_update_sr_info.shipment_details_tab(i).line_number;
l_source_line_number(i) := l_update_sr_info.shipment_details_tab(i).source_line_number;
l_earliest_pickup_date(i) := l_update_sr_info.shipment_details_tab(i).earliest_pickup_date;
l_latest_pickup_date(i) := l_update_sr_info.shipment_details_tab(i).latest_pickup_date;
l_earliest_dropoff_date(i) := l_update_sr_info.shipment_details_tab(i).earliest_dropoff_date;
l_latest_dropoff_date(i) := l_update_sr_info.shipment_details_tab(i).latest_dropoff_date;
l_ship_tolerance_above(i) := l_update_sr_info.shipment_details_tab(i).ship_tolerance_above;
l_ship_tolerance_below(i) := l_update_sr_info.shipment_details_tab(i).ship_tolerance_below;
l_shipping_instructions(i) := l_update_sr_info.shipment_details_tab(i).shipping_instructions ;
l_packing_instructions(i) := l_update_sr_info.shipment_details_tab(i).packing_instructions;
l_shipment_priority_code(i) := l_update_sr_info.shipment_details_tab(i).shipment_priority_code;
l_cust_po_number(i) := l_update_sr_info.shipment_details_tab(i).cust_po_number;
l_subinventory(i) := l_update_sr_info.shipment_details_tab(i).subinventory;
l_locator_id(i) := l_update_sr_info.shipment_details_tab(i).locator_id;
l_locator_code(i) := l_update_sr_info.shipment_details_tab(i).locator_code;
l_lot_number(i) := l_update_sr_info.shipment_details_tab(i).lot_number;
l_revision(i) := l_update_sr_info.shipment_details_tab(i).revision;
l_ship_set_name(i) := l_update_sr_info.shipment_details_tab(i).ship_set_name;
l_unit_selling_price(i) := l_update_sr_info.shipment_details_tab(i).unit_selling_price;
l_delivery_detail_interface_id(i):= l_update_sr_info.shipment_details_tab(i).delivery_detail_interface_id;
FORALL i in 1..l_update_sr_info.shipment_details_tab.count
UPDATE WSH_DEL_DETAILS_INTERFACE
SET last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.Login_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,
--Bug8784331
date_requested = l_date_requested(i),
date_scheduled = l_date_scheduled(i) ,
inventory_item_id = l_inventory_item_id(i),
item_number = l_item_number(i),
customer_item_id = l_customer_item_id(i),
customer_item_number = l_customer_item_number(i),
requested_quantity = l_requested_quantity(i),
requested_quantity_uom = l_requested_quantity_uom(i),
src_requested_quantity = l_src_requested_quantity(i) ,
src_requested_quantity_uom = l_src_requested_quantity_uom(i),
line_number = l_line_number(i),
source_line_number = l_source_line_number(i),
earliest_pickup_date = l_earliest_pickup_date(i),
latest_pickup_date = l_latest_pickup_date(i),
earliest_dropoff_date = l_earliest_dropoff_date(i),
latest_dropoff_date = l_latest_dropoff_date(i),
ship_tolerance_above = l_ship_tolerance_above(i) ,
ship_tolerance_below = l_ship_tolerance_below(i),
shipping_instructions = l_shipping_instructions(i) ,
packing_instructions = l_packing_instructions(i),
shipment_priority_code = l_shipment_priority_code(i),
cust_po_number = l_cust_po_number(i),
subinventory = l_subinventory(i),
locator_id = l_locator_id(i),
locator_code = l_locator_code(i),
lot_number = l_lot_number(i),
revision = l_revision(i),
ship_set_name = l_ship_set_name(i),
unit_selling_price = l_unit_selling_price(i)
WHERE delivery_detail_interface_id = l_delivery_detail_interface_id(i) ;
IF l_insert_sr_info.shipment_details_tab.count > 0 THEN
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Create_Shipment_Request with UPDATE', WSH_DEBUG_SV.C_PROC_LEVEL);
Create_Shipment_Request(p_shipment_request_info => l_insert_sr_info,
p_caller => 'UPDATE',
x_return_status => l_return_status);
WSH_DEBUG_SV.log(l_module_name, 'The Action of inserting new del detail interface lines failed');
WSH_DEBUG_SV.log(l_module_name, 'The Action of inserting new del detail interface lines is successful');
delete from wsh_del_assgn_interface
where delivery_detail_interface_id = l_del_det_int_tab(i);
WSH_DEBUG_SV.log(l_module_name,i||' WDAI : Number of records Deleted',l_ins_rows);
delete from WSH_DEL_DETAILS_INTERFACE
where delivery_detail_interface_id = l_del_det_int_tab(i);
WSH_DEBUG_SV.log(l_module_name,i||' WDDI : Number of records Deleted',l_ins_rows);
delete from WSH_INTERFACE_ERRORS
where interface_id = l_del_det_int_tab(i)
and interface_table_name = 'WSH_DEL_DETAILS_INTERFACE';
WSH_DEBUG_SV.log(l_module_name,i||' WIE : Number of records Deleted',l_ins_rows);
delete from wsh_del_assgn_interface
where delivery_detail_interface_id = l_del_det_int_tab(i);
WSH_DEBUG_SV.log(l_module_name, 'WDAI : Number of records Deleted',l_ins_rows);
delete from WSH_DEL_DETAILS_INTERFACE
where delivery_detail_interface_id = l_del_det_int_tab(i);
WSH_DEBUG_SV.log(l_module_name, 'WDDI : Number of records Deleted',l_ins_rows);
delete from WSH_INTERFACE_ERRORS
where interface_id = l_del_det_int_tab(i)
and interface_table_name= 'WSH_DEL_DETAILS_INTERFACE';
WSH_DEBUG_SV.log(l_module_name, 'WIE : Number of records Deleted',l_ins_rows);
delete from wsh_new_del_interface where delivery_interface_id = l_delivery_interface_id;
WSH_DEBUG_SV.log(l_module_name, 'WNDI : Number of records Deleted',l_ins_rows);
delete from wsh_transactions_history where transaction_id = l_transaction_id;
WSH_DEBUG_SV.log(l_module_name, 'WTH : Number of records Deleted',l_ins_rows);
delete from wsh_interface_errors
where interface_id = l_delivery_interface_id
and interface_table_name = 'WSH_NEW_DEL_INTERFACE';
WSH_DEBUG_SV.log(l_module_name, 'WIE : Number of records Deleted',l_ins_rows);
wsh_debug_sv.logmsg(l_module_name, 'Successfully deleted records.');
END Update_Delete_Shipment_Request;