The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT reql.*
FROM CSL_CSP_REQUIREMENT_LNS_inq reql, CSL_CSP_REQUIREMENT_HDR_inq reqh
WHERE reql.tranid$$ = b_tranid
AND reql.clid$$cs = b_user_name
AND reql.requirement_header_id = reqh.requirement_header_id;
This one is executed after all requirement lines with headers have been deleted from the inqueue.
The requirement lines without header remain then.
***/
CURSOR c_requirements_no_headers ( b_user_name VARCHAR2, b_tranid NUMBER) is
SELECT *
FROM CSL_CSP_REQUIREMENT_LNS_inq
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
If p_get_inqueue_header = TRUE => fetch requirement header from the requirement header inqueue.
If p_get_inqueue_header = FALSE => fetch requirement header from Apps.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_requirements%ROWTYPE,
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_get_inqueue_header IN BOOLEAN,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_header_rec CSP_PARTS_REQUIREMENT.HEADER_REC_TYPE;
SELECT *
FROM CSL_CSP_REQUIREMENT_HDR_inq
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name
AND requirement_header_id = b_requirement_header_id;
SELECT *
FROM CSP_REQUIREMENT_HEADERS
WHERE requirement_header_id = b_requirement_header_id;
, v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Exception occurred in APPLY_INSERT:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
, v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
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_requirements%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => p_record.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Exception occurred in APPLY_UPDATE:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
, v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
END APPLY_UPDATE;
APPLY_INSERT
(
p_record,
p_user_name,
p_tranid,
p_get_inqueue_header,
p_error_msg,
x_return_status
);
APPLY_UPDATE
(
p_record,
p_error_msg,
x_return_status
);
, v_message => 'Delete is not supported for this entity'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
This procedure is called by PROCESS_REQS and deletes all requirement headers from the inqueue,
for a given user and transaction.
***/
PROCEDURE DELETE_REQ_HEADERS_FROM_INQ
(
p_user_name IN VARCHAR2,
p_tranid 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_req_headers_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER) is
SELECT *
FROM CSL_CSP_REQUIREMENT_HDR_inq
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
, v_message => 'Entering ' || g_object_name || '.DELETE_REQ_HEADERS_FROM_INQ'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_get_req_headers_from_inq.seqno$$,
r_get_req_headers_from_inq.requirement_header_id, -- put PK column here
g_object_name,
g_pub_name2,
l_error_msg,
x_return_status
);
/*** was delete successful? ***/
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_get_req_headers_from_inq.requirement_header_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Deleting from inqueue failed, rolling back to savepoint'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_get_req_headers_from_inq.requirement_header_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Record not processed successfully, deferring and rejecting record'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
, v_message => 'Leaving ' || g_object_name || '.DELETE_REQ_HEADERS_FROM_INQ'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
, v_message => 'Exception occurred in DELETE_REQ_HEADERS_FROM_INQ:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
END DELETE_REQ_HEADERS_FROM_INQ;
/*** Yes -> delete record from inqueue ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_requirements.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Record successfully processed, deleting from inqueue'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_requirements.seqno$$,
r_requirements.requirement_line_id, -- put PK column here
g_object_name,
g_pub_name,
l_error_msg,
x_return_status
);
/*** was delete successful? ***/
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_requirements.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Deleting from inqueue failed, rolling back to savepoint'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_requirements.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Record not processed successfully, deferring and rejecting record'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
DELETE_REQ_HEADERS_FROM_INQ(p_user_name, p_tranid, x_return_status);
/*** Yes -> delete record from inqueue ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_requirements.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Record successfully processed, deleting from inqueue'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_requirements.seqno$$,
r_requirements.requirement_line_id, -- put PK column here
g_object_name,
g_pub_name,
l_error_msg,
x_return_status
);
/*** was delete successful? ***/
IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_requirements.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Deleting from inqueue failed, rolling back to savepoint'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_requirements.requirement_line_id -- put PK column here
, v_object_name => g_object_name
, v_message => 'Record not processed successfully, deferring and rejecting record'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
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);