[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO WSH_ITM_SERIAL_NUMBERS
(request_control_id,
delivery_id,
delivery_detail_id,
serial_number)
VALUES
( p_request_control_id,
p_delivery_id,
p_sn_range_rec_type.delivery_detail_id,
l_from_serial_num
);
INSERT INTO WSH_ITM_SERIAL_NUMBERS
(request_control_id,
delivery_id,
delivery_detail_id,
serial_number)
VALUES
( p_request_control_id,
p_delivery_id,
p_sn_range_rec_type.delivery_detail_id,
l_new_serial_number
);
SELECT wdd.delivery_detail_id, wdd.transaction_temp_id, wdd.inventory_item_id
FROM WSH_DELIVERY_DETAILS wdd,wsh_delivery_assignments wda
WHERE wda.delivery_id = c_delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id;
SELECT wdd.delivery_detail_id,wdd.transaction_temp_id,mt.fm_serial_number,
mt.to_serial_number , to_number(mt.serial_prefix) quantity
FROM mtl_serial_numbers_temp mt , wsh_delivery_details wdd
WHERE mt.transaction_temp_id = c_transaction_temp_id
AND wdd.transaction_temp_id = mt.transaction_temp_id
AND wdd.delivery_detail_id = c_delivery_detail_id;
SELECT wdd.delivery_detail_id,null,wdd.serial_number,wdd.to_serial_number,shipped_quantity
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = c_delivery_detail_id;
| x_return_status => This is updated with the process status which |
| could either be a success of warning or an error.|
| p_organization_id => This parameter is used to filter the deliveries |
| based on organization. |
| |
| p_delivery_from_id => This parameter indicates the starting of the |
| range of deliveries to be processed. |
| p_delivery_to_id => This parameter indicates the ending of the |
| range of deliveries to be processed. |
| p_event_name => This parameter indicates the event on which the |
| the export screening was initiated. |
| p_ship_method_code => This paramter indicates the ship method of the |
| delivery |
| p_pickup_date_from => This parameter indicates the initail pickup date | | |
| p_pickup_date_to => This parameter indicates the last pick up date |
| |
| p_event_name => This parameter indicates the event on which the |
| the export screening was initiated. |
| DESCRIPTION |
| This procedure is called For the deliveries of a |
| Specific event. It Logs and Handles Appropriate |
| Exceptions which hold the delivery until the export |
| screening is done for the delivery ad populates data into |
| WSH_ITM_REQUEST_CONTROL Table. |
| |
+===========================================================================*/
PROCEDURE SCREEN_EVENT_DELIVERIES (
x_return_status OUT NOCOPY VARCHAR2,
p_organization_id IN NUMBER,
p_delivery_from_id IN NUMBER,
p_delivery_to_id IN NUMBER,
p_event_name IN VARCHAR2,
p_ship_method_code IN VARCHAR2,
p_pickup_date_from IN VARCHAR2,
p_pickup_date_to IN VARCHAR2
)IS
-- Declaration Section For Log/close Exception Section
i NUMBER;
' SELECT '||
' WE.EXCEPTION_ID AS EXCEPTION_ID, '||
' WE.EXCEPTION_LOCATION_ID AS LOCATION_ID, '||
' WND.DELIVERY_ID AS DELIVERY_ID, '||
' WE.STATUS AS STATUS, '||
' WND.NAME AS DELIVERY_NAME, '||
' WND.ORGANIZATION_ID AS ORGANIZATION_ID, '||
' MTL.MASTER_ORGANIZATION_ID AS MASTER_ORGANIZATION_ID '||
' FROM '||
' WSH_EXCEPTIONS WE , '||
' WSH_NEW_DELIVERIES WND, '||
' MTL_PARAMETERS MTL '||
' WHERE '||
' WE.STATUS <> ''CLOSED'' '||
' AND WND.ORGANIZATION_ID = MTL.ORGANIZATION_ID '||
' AND WND.DELIVERY_ID = WE.DELIVERY_ID '||
' AND WND.DELIVERY_ID = (SELECT '||
' WDA.DELIVERY_ID '||
' FROM '||
' WSH_DELIVERY_ASSIGNMENTS WDA '||
' WHERE '||
' WDA.DELIVERY_ID = WND.DELIVERY_ID '||
' AND ROWNUM = 1) ' ;
WSH_DEBUG_SV.log(l_module_name,' No Of Deliveries Selected For Screening ',l_num_delivery_id_tab.count);
SELECT LANGUAGE_CODE INTO l_LanguageCode FROM
FND_LANGUAGES WHERE INSTALLED_FLAG = 'B';
SELECT 1 INTO l_rec_found
FROM WSH_TRANSACTIONS_HISTORY
WHERE DOCUMENT_TYPE = 'SS' AND
ENTITY_NUMBER = l_varchar_delivery_name_tab(k);
SELECT ORGANIZATION_ID INTO l_tranx_history_rec.trading_partner_id
FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID = l_num_delivery_id_tab(k);
SELECT WSH_DOCUMENT_NUMBER_S.NEXTVAL
INTO l_tranx_history_rec.document_number FROM DUAL;
WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History(
l_tranx_history_rec,
x_txns_id,
x_return_status
);
WSH_DEBUG_SV.log(l_module_name,'Create_Update_Txns_History failed ' || x_return_status);
SELECT WSH_ITM_REQUEST_CONTROL_S.NEXTVAL
INTO l_request_control_id_s FROM DUAL;
INSERT INTO WSH_ITM_REQUEST_CONTROL(
REQUEST_CONTROL_ID,
APPLICATION_ID,
APPLICATION_USER_ID,
SERVICE_TYPE_CODE,
TRANSACTION_DATE,
ORIGINAL_SYSTEM_REFERENCE,
PROCESS_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE_CODE,
TRIGGERING_POINT,
ORGANIZATION_ID,
MASTER_ORGANIZATION_ID
)
VALUES(
l_request_control_id_s,
G_APPLICATION_ID,
l_user_id,
G_SERVICE_TYPE_CODE,
SYSDATE,
l_num_delivery_id_tab(k),
0,
SYSDATE,
l_user_id,
l_user_id,
SYSDATE,
l_login_id,
l_LanguageCode,
p_event_name,
l_num_organization_id_tab(k),
l_num_master_org_id_tab(k)
);
| ret_code => This is updated with the process status which |
| could either be a success or error. |
| p_organization_id => This parameter is used to filter the deliveries |
| based on organization. |
| |
| p_delivery_from_id => This parameter indicates the starting of the |
| range of deliveries to be processed. |
| p_delivery_to_id => This parameter indicates the ending of the |
| range of deliveries to be processed. |
| |
| DESCRIPTION |
| This procedure is called when the concurrent program is |
| Launched. It invokes the screen_event_deliveries Procedure |
| for handling the export screening requests for both |
| Pick Release and Ship Conifirm Events. |
| |
+===========================================================================*/
PROCEDURE SCREEN_DELIVERIES (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_organization_id IN NUMBER,
p_delivery_from_id IN NUMBER,
p_delivery_to_id IN NUMBER,
p_ship_method_code IN VARCHAR2,
p_pickup_date_from IN VARCHAR2,
p_pickup_date_to IN VARCHAR2
)IS
l_return_status VARCHAR2(1);