The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Select all inq records */
CURSOR c_query_instances( b_user_name VARCHAR2, b_tranid NUMBER, b_from_sync VARCHAR2) is
SELECT inq.*
FROM CSM_QUERY_INSTANCES_INQ inq,
CSM_QUERY_B b
WHERE inq.tranid$$ = b_tranid
AND inq.clid$$cs = b_user_name
AND inq.QUERY_ID = b.QUERY_ID
AND ((b.EXECUTION_MODE = 'SYNCHRONOUS' AND b_from_sync ='Y') OR b_from_sync = 'N');
SELECT *
FROM CSM_QUERY_VARIABLE_VALUES_INQ
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name
AND INSTANCE_ID = b_instance_id;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_query_instances%ROWTYPE,
p_error_msg out nocopy VARCHAR2,
x_return_status IN out nocopy VARCHAR2
) IS
CURSOR c_check_query(c_QUERY_ID NUMBER)
IS
SELECT QUERY_NAME,LEVEL_ID,LEVEL_VALUE, QUERY_TYPE
FROM CSM_QUERY_B
WHERE QUERY_ID =c_QUERY_ID
AND NVL(DELETE_FLAG,'N') = 'N';
SELECT vb.VARIABLE_ID,
vb.VARIABLE_VALUE_CHAR,
vb.VARIABLE_VALUE_DATE,
vb.VARIABLE_TYPE
FROM CSM_QUERY_VARIABLES_B vb
WHERE vb.QUERY_ID = c_QUERY_ID;
SELECT vb.VARIABLE_ID,
vb.VARIABLE_VALUE_CHAR,
vb.VARIABLE_VALUE_DATE,
vb.SEQNO$$
FROM CSM_QUERY_VARIABLE_VALUES_INQ vb
WHERE vb.CLID$$CS = c_user_name
AND vb.TRANID$$ = c_tran_id
AND vb.QUERY_ID = c_QUERY_ID
AND vb.INSTANCE_ID = c_instance_id;
SELECT CSM_QUERY_INSTANCES_ACC_S.NEXTVAL
FROM DUAL;
SELECT USER_ID
FROM ASG_USER
WHERE USER_NAME =c_user_name;
SELECT CSM_QUERY_VARIABLE_VAL_ACC_S.NEXTVAL
FROM DUAL;
CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_INSERT for Instance ID ' || p_record.INSTANCE_ID ,
'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
( 'Exception occurred in VALIDATE_ACCESS for Query Name ' || l_instance_name || SQLERRM, 'INSERT_INSTANCE',
FND_LOG.LEVEL_EXCEPTION);
SAVEPOINT INSERT_QUERY_INSTANCE;
INSERT INTO CSM_QUERY_INSTANCES_ACC(ACCESS_ID , USER_ID , QUERY_ID , INSTANCE_ID , INSTANCE_NAME,STATUS,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES (l_instance_id, l_user_id, l_query_id, l_instance_id,l_instance_name,'OPEN',
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id);
ROLLBACK TO INSERT_QUERY_INSTANCE;
CSM_UTIL_PKG.LOG( 'Leaving INSERT_INSTANCE for Query Id : ' || l_user_id , FND_LOG.LEVEL_ERROR);
p_error_msg := 'Leaving INSERT_INSTANCE for Query Id : ' || l_user_id ;
( 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ,
FND_LOG.LEVEL_ERROR);
p_error_msg := 'Variable Count mismatch.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ;
( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ,
FND_LOG.LEVEL_ERROR);
p_error_msg := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ;
( 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ,
FND_LOG.LEVEL_ERROR);
p_error_msg := 'Variable Value sent not matching with Type.Leaving INSERT_INSTANCE for Query Id : ' || l_query_id ;
INSERT INTO CSM_QUERY_VARIABLE_VALUES_ACC(ACCESS_ID , USER_ID , QUERY_ID ,
INSTANCE_ID , VARIABLE_ID,VARIABLE_VALUE_CHAR,VARIABLE_VALUE_DATE,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY, LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,GEN_PK)
VALUES (l_qvariable_id, l_user_id, l_query_id,
l_instance_id, i, l_var_value_char_inq_lst(i), l_var_value_date_inq_lst(i),
l_user_id, SYSDATE, l_user_id, SYSDATE, l_user_id,l_qvariable_id)
RETURNING ACCESS_ID into l_access_id;
ROLLBACK TO INSERT_QUERY_INSTANCE;
CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_INSERT for Instance ID ' || p_record.INSTANCE_ID ,
'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm
|| ' for Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
p_error_msg := 'Exception in APPLY_INSERT for Instance Id : ' || l_instance_id || substr(SQLERRM, 1,2000);
ROLLBACK TO INSERT_QUERY_INSTANCE;
END APPLY_INSERT;
This procedure is called by APPLY_CLIENT_CHANGES when an Delete is to be processed.
***/
PROCEDURE APPLY_DELETE
(
p_record IN c_query_instances%ROWTYPE,
p_error_msg out nocopy VARCHAR2,
x_return_status IN out nocopy VARCHAR2
)
IS
CURSOR c_get_user(c_user_name VARCHAR2)
IS
SELECT USER_ID
FROM ASG_USER
WHERE USER_NAME =c_user_name;
SELECT QUERY_ID
FROM CSM_QUERY_INSTANCES_ACC
WHERE INSTANCE_ID = c_instance_id;
CSM_UTIL_PKG.LOG('Entering CSM_QUERY_INSTANCE_PKG.APPLY_DELETE for Instance ID ' || p_record.INSTANCE_ID ,
'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG( 'Query Instance Delete Failed for : ' || l_instance_id ,FND_LOG.LEVEL_ERROR);
p_error_msg := 'Delete request failed for Instance Id : ' || l_instance_id || ' as it does not exist.';
CSM_QUERY_PKG.DELETE_INSTANCE
( p_USER_ID => l_user_id,
p_QUERY_ID => l_query_id,
p_INSTANCE_ID => l_instance_id,
p_commit => fnd_api.G_FALSE,
x_return_status => x_return_status,
x_error_message => p_error_msg) ;
CSM_UTIL_PKG.LOG( 'Query Instance Delete Failed for : ' || l_instance_id ,FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.LOG('Leaving CSM_QUERY_INSTANCE_PKG.APPLY_DELETE for Instance ID ' || p_record.INSTANCE_ID ,
'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_PROCEDURE);
p_error_msg := 'Delete Successful for the Instance Id : ' || l_instance_id ;
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_DELETE', sqlerrm);
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_DELETE: ' || sqlerrm
|| ' for Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_DELETE',FND_LOG.LEVEL_EXCEPTION);
p_error_msg := 'Exception in APPLY_DELETE for Instance Id : ' || l_instance_id || substr(SQLERRM, 1,2000);
END APPLY_DELETE;
APPLY_INSERT
(
p_record,
p_error_msg,
x_return_status
);
APPLY_DELETE
(
p_record,
p_error_msg,
x_return_status
);
ELSE -- update is not supported for this PI
-- invalid dml type
CSM_UTIL_PKG.LOG
( 'Invalid DML type: ' || p_record.dmltype$$ || ' is not supported for this entity'
|| ' for Query Instance ID ' || p_record.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
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,
p_from_sync IN VARCHAR2 DEFAULT 'N',
x_return_status IN out nocopy VARCHAR2
) IS
l_process_status VARCHAR2(1);
/*** Yes -> delete record from inqueue ***/
CSM_UTIL_PKG.REJECT_RECORD
(
p_user_name,
p_tranid,
r_qi_rec.seqno$$,
r_qi_rec.INSTANCE_ID,
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
/*** was Instance delete successful? ***/
IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
/*** Yes -> delete Variable Values record from inqueue ***/
FOR r_qvv_rec IN c_query_variable_values( p_user_name, p_tranid, r_qi_rec.INSTANCE_ID) LOOP
CSM_UTIL_PKG.REJECT_RECORD
(
p_user_name,
p_tranid,
r_qvv_rec.seqno$$,
r_qvv_rec.GEN_PK,
g_object_name,
g_pub_qvv_name,
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
CSM_UTIL_PKG.LOG
( 'Deleting from inqueue failed, rolling back to savepoint'
|| ' for Instance ID ' || r_qi_rec.INSTANCE_ID ,'CSM_QUERY_INSTANCE_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here