[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_entity_updated_date IN DATE,
x_substitute_entity OUT NOCOPY VARCHAR2,
p_transmission_id IN NUMBER,
x_send_allowed OUT NOCOPY BOOLEAN,
x_return_status OUT NOCOPY VARCHAR2
) IS
--Cursor to check whether the entity exists in WOSRD
CURSOR c_entity_exists(p_id NUMBER, p_type VARCHAR2, p_parent_id NUMBER) IS
SELECT sync_ref_id,
substitute_entity,
last_sent_date
FROM wsh_otm_sync_ref_data
WHERE entity_id = p_id
AND entity_type = p_type
AND parent_entity_id = nvl(p_parent_id,0);
SELECT msi.last_update_date,
wosrd.sync_ref_id,
wosrd.substitute_entity
FROM mtl_system_items msi,
wsh_otm_sync_ref_data wosrd
WHERE msi.inventory_item_id = p_item_id
AND wosrd.entity_type = p_entity_type
AND wosrd.entity_id = msi.inventory_item_id
AND msi.last_update_date > wosrd.last_sent_date;
SELECT wl.last_update_date,
wosrd.sync_ref_id,
wosrd.substitute_entity
FROM wsh_locations wl,
wsh_otm_sync_ref_data wosrd
WHERE wl.wsh_location_id = p_location_id
AND wosrd.entity_type = p_entity_type
AND wosrd.entity_id = wl.wsh_location_id
AND wl.last_update_date > wosrd.last_sent_date;
l_last_update_date DATE;
WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_UPDATED_DATE ',p_entity_updated_date);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.INSERT_ROW_SYNC_REF_DATA',WSH_DEBUG_SV.C_PROC_LEVEL);
insert_row_sync_ref_data(p_entity_id => p_entity_id ,
p_parent_entity_id => p_parent_entity_id,
p_entity_type => p_entity_type,
p_transmission_id => p_transmission_id,
x_sync_ref_id => l_sync_ref_id,
x_substitute_entity => l_substitute_entity,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.INSERT_ROW_SYNC_REF_DATA_LOG',WSH_DEBUG_SV.C_PROC_LEVEL);
insert_row_sync_ref_data_log (p_sync_ref_id => l_sync_ref_id,
p_transmission_id => p_transmission_id,
p_entity_type => p_entity_type,
x_return_status => l_return_status
);
IF p_entity_updated_date >= l_last_sent_date OR
to_number(sysdate - l_last_sent_date) <= 1 THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_OTM_SYNC_REF_DATA_PKG.INSERT_ROW_SYNC_REF_DATA_LOG',WSH_DEBUG_SV.C_PROC_LEVEL);
insert_row_sync_ref_data_log (p_sync_ref_id => l_sync_ref_id,
p_transmission_id => p_transmission_id,
p_entity_type => p_entity_type,
x_return_status => l_return_status
);
PROCEDURE update_ref_data( p_transmission_id IN NUMBER,
p_transmission_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
--Cursor to get sync_ref_id's for given transmission_id
CURSOR c_get_sync_ref_id(p_trans_id NUMBER) IS
SELECT distinct entity_type, sync_ref_id
FROM wsh_otm_sync_ref_data_log
WHERE transmission_id = p_trans_id
ORDER BY entity_type, sync_ref_id;
SELECT 1
FROM wsh_otm_sync_ref_data
WHERE sync_ref_id = p_sync_ref_id
FOR UPDATE NOWAIT;
SELECT sent_date
FROM wsh_otm_sync_ref_data_log
WHERE sync_ref_id = p_sync_id
AND rownum = 1;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_REF_DATA';
--Update last_sent_date of WOSRD with sent_date of WOSRDL.sent_date
UPDATE wsh_otm_sync_ref_data
SET last_sent_date = l_sent_date
WHERE sync_ref_id = l_sync_ref_id;
WSH_DEBUG_SV.log(l_module_name,'Updated ', SQL%ROWCOUNT);
--Delete data from WOSRDL for l_sync_ref_id
DELETE
FROM wsh_otm_sync_ref_data_log
WHERE sync_ref_id = l_sync_ref_id;
--Since this is an error case delete all the data from WOSRDL for given transmission_id
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Error case. So deleting all the data from WOSRDL for transmission_id ', p_transmission_id);
DELETE
FROM wsh_otm_sync_ref_data_log
WHERE transmission_id = p_transmission_id;
END update_ref_data;
PROCEDURE insert_row_sync_ref_data( p_entity_id IN NUMBER,
p_parent_entity_id IN NUMBER,
p_entity_type IN VARCHAR2,
p_transmission_id IN NUMBER,
x_sync_ref_id OUT NOCOPY NUMBER,
x_substitute_entity OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION; --since its an autonomous transaction
SELECT entity_id, parent_entity_id
FROM wsh_otm_sync_ref_data
WHERE entity_id = p_id
AND entity_type = p_type
AND substitute_entity IS NULL;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW_SYNC_REF_DATA';
INSERT INTO wsh_otm_sync_ref_data (sync_ref_id,
entity_id,
parent_entity_id,
entity_type,
substitute_entity,
last_sent_date,
called_by_module,
additional_num,
additional_char,
additional_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES (wsh_otm_sync_ref_data_s.NEXTVAL,
p_entity_id,
nvl(p_parent_entity_id,0),
p_entity_type,
l_substitute_entity,
to_date('1900/01/01 00:00:01', 'YYYY/MM/DD HH24:MI:SS'),
'WSH-TXN',
NULL,
NULL,
NULL,
SYSDATE, --creation_date
FND_GLOBAL.USER_ID,
SYSDATE, --last_update_date
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
) returning sync_ref_id into x_sync_ref_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Exception while inserting data into WOSRD ' || SQLERRM);
SELECT substitute_entity
INTO l_substitute_entity
FROM wsh_otm_sync_ref_data
WHERE entity_id = p_entity_id
AND parent_entity_id = nvl(p_parent_entity_id,0)
AND entity_type = p_entity_type;
END insert_row_sync_ref_data;
PROCEDURE insert_row_sync_ref_data_log( p_sync_ref_id NUMBER,
p_transmission_id NUMBER,
p_entity_type VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_debug_on BOOLEAN ;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW_SYNC_REF_DATA_LOG';
INSERT INTO wsh_otm_sync_ref_data_log (transmission_id,
sync_ref_id,
entity_type,
sent_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES (p_transmission_id,
p_sync_ref_id,
p_entity_type,
SYSDATE, --sent_date
SYSDATE, --creation_date
FND_GLOBAL.USER_ID,
SYSDATE, --last_update_date
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
);
END insert_row_sync_ref_data_log;