The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM CSF_M_COUNTER_VALUES_inq
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
This procedure is called by PROCESS_REQS and deletes all requirement headers from the inqueue,
for a given user and transaction.
***/
PROCEDURE DELETE_PROP_READ_FROM_INQ
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_ctr_val_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error_msg VARCHAR2(4000);
This one is to be executed after all requirement lines with headers have been deleted from the inqueue.
***/
CURSOR c_get_ctr_prop_read_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER, b_ctr_val_id NUMBER) is
SELECT *
FROM CSM_COUNTER_PROP_VALUES_INQ inq
WHERE inq.tranid$$ = b_tranid
AND inq.clid$$cs = b_user_name
AND inq.COUNTER_VALUE_ID = b_ctr_val_id;
( 'Entering CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
'CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
FND_LOG.LEVEL_STATEMENT); -- put PK column here
CSM_UTIL_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_get_ctr_prop_read_from_inq.seqno$$,
r_get_ctr_prop_read_from_inq.counter_prop_value_id, -- put PK column here
g_object_name,
l_pub_name,
l_error_msg,
x_return_status
);
/*** was delete successful? ***/
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
CSM_UTIL_PKG.LOG
( 'Deleting from inqueue failed, rolling back to savepoinT'|| 'for PK '||r_get_ctr_prop_read_from_inq.counter_prop_value_id,
'CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
FND_LOG.LEVEL_PROCEDURE); -- put PK column here
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
CSM_UTIL_PKG.LOG
( 'Record not processed successfully, deferring and rejecting record'|| 'for PK '|| r_get_ctr_prop_read_from_inq.counter_prop_value_id,
'CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
FND_LOG.LEVEL_PROCEDURE); -- put PK column here
'CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
FND_LOG.LEVEL_PROCEDURE); -- put PK column here
( 'Leaving CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ ',
'CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
FND_LOG.LEVEL_STATEMENT); -- put PK column here
( 'Exception occurred in DELETE_PROP_READ_FROM_INQ: '|| FND_GLOBAL.LOCAL_CHR(10) || sqlerrm,
'CSM_COUNTER_VALUES_PKG.DELETE_PROP_READ_FROM_INQ',
FND_LOG.LEVEL_EXCEPTION); -- put PK column here
END DELETE_PROP_READ_FROM_INQ;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_CS_COUNTER_VALUES%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
CURSOR c_counter_value_id (b_counter_value_id number)
IS
SELECT counter_value_id
FROM cs_counter_values
WHERE counter_value_id = b_counter_value_id;
SELECT *
FROM CSM_COUNTER_PROP_VALUES_INQ
WHERE counter_value_id = b_counter_value_id
AND tranid$$ = c_tranid
AND clid$$cs = c_user_name;
l_err_msg := 'Entering ' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_VALUES_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
DELETE_PROP_READ_FROM_INQ(
p_record.clid$$cs,
p_record.tranid$$,
l_counter_value_id,
x_return_status
);
l_err_msg:= 'Error' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_VALUES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
l_err_msg:= 'Leaving ' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_VALUES_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
l_err_msg := 'Exception occurred in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm|| ' for PK ' || to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_COUNTER_VALUES_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
l_err_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT' || ' for PK ' || to_char( p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG(l_err_msg,'CSM_COUNTER_VALUES_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
END APPLY_INSERT;
This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
***/
PROCEDURE APPLY_UPDATE
(
p_record IN c_CS_COUNTER_VALUES%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
CURSOR c_counter_value
( b_counter_value_id number
)
IS
SELECT counter_id
, counter_value_id
, counter_grp_log_id
, object_version_number
, last_update_date
FROM cs_ctr_counter_values_v
WHERE counter_value_id = b_counter_value_id;
SELECT counter_reading
FROM cs_counter_values
WHERE counter_value_id = b_counter_value_id;
l_err_msg := 'Entering ' || g_object_name || '.APPLY_UPDATE' || ' for PK ' || to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG(l_err_msg,'CSM_COUNTER_VALUES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE );
IF(r_counter_value.last_update_date <> p_record.server_last_update_date) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
l_err_msg:= 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_COUNTER_VALUES_PKG.APPLY_UPDATE: P_KEY = '
|| p_record.counter_value_id;
CSM_UTIL_PKG.LOG(l_err_msg,'CSM_COUNTER_VALUES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
Cs_Ctr_Capture_Reading_pub.Update_Counter_Reading
( p_api_version_number => 1.0
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, p_ctr_rdg_tbl => l_ctr_rdg_tbl
, p_ctr_grp_log_id => l_ctr_grp_log_id
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
l_err_msg := 'Leaving ' || g_object_name || '.APPLY_UPDATE'|| ' for PK ' || to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG(l_err_msg,'CSM_COUNTER_VALUES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
l_err_msg:= 'Exception occurred in ' || g_object_name || '.APPLY_UPDATE: ' || sqlerrm
|| ' for PK ' || p_record.COUNTER_VALUE_ID;
CSM_UTIL_PKG.LOG(l_err_msg,'CSM_COUNTER_VALUES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
l_err_msg := 'Leaving ' || g_object_name || '.APPLY_UPDATE'|| ' for PK ' ||to_char(p_record.COUNTER_VALUE_ID);
CSM_UTIL_PKG.LOG(l_err_msg,'CSM_COUNTER_VALUES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
END APPLY_UPDATE;
APPLY_INSERT
(
p_record,
p_error_msg,
x_return_status
);
APPLY_UPDATE
(
p_record,
p_error_msg,
x_return_status
);
l_err_msg := 'Delete is not supported for this entity ' || g_object_name;
a fast sync. This procedure will insert the data that came from mobile into the backend tables using
public APIs.
***/
PROCEDURE APPLY_CLIENT_CHANGES
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_debug_level IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_process_status VARCHAR2(1);
/*** Yes -> delete record from inqueue ***/
l_error_msg := 'Record successfully processed, deleting from inqueue ' || g_object_name
|| ' for PK ' || r_CS_COUNTER_VALUES.COUNTER_VALUE_ID;
CSM_UTIL_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_CS_COUNTER_VALUES.seqno$$,
r_CS_COUNTER_VALUES.COUNTER_VALUE_ID, -- put PK column here
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
l_error_msg := 'Deleting from inqueue failed, rolling back to savepoint for entity ' || g_object_name
|| ' and PK ' || r_CS_COUNTER_VALUES.COUNTER_VALUE_ID ;
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
l_error_msg :='Record not processed successfully, deferring and rejecting record for entity ' || g_object_name
|| ' and PK ' || r_CS_COUNTER_VALUES.COUNTER_VALUE_ID ;