The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM CSL_MTL_MAT_TRANSACTIONS_INQ
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
SELECT *
FROM CSL_MTL_TRANS_LOT_NUM_INQ
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name
AND transaction_id = b_transaction_id;
SELECT *
FROM CSL_MTL_UNIT_TRANS_INQ
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name
AND transaction_id = b_transaction_id;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_material_transaction%ROWTYPE,
p_error_msg OUT NOCOPY VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_lot_number c_lot_number%ROWTYPE;
SELECT TRANSACTION_ID
, INVENTORY_ITEM_ID
, ORGANIZATION_ID
, SUBINVENTORY_CODE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SET_ID = b_transaction_set_id;
SELECT RESOURCE_ID
FROM ASG_USER
WHERE USER_NAME = b_client_name;
, v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
JTM_HOOK_UTIL_PKG.Insert_Acc
( p_publication_item_names => l_pub_item_name_mat
,p_acc_table_name => 'JTM_MTL_MAT_TRANS_ACC'
,p_resource_id => r_resource.resource_id
,p_pk1_name => 'TRANSACTION_ID'
,p_pk1_num_value => r_transaction.TRANSACTION_ID
);
CSL_MTL_TRANS_LOT_NUM_ACC_PKG.Insert_MTL_trans_lot_num(
p_resource_id => r_resource.resource_id,
p_transaction_id => r_transaction.TRANSACTION_ID,
p_inventory_item_id => r_transaction.inventory_item_id,
p_organization_id => r_transaction.organization_id
);
CSL_MTL_UNIT_TRANS_ACC_PKG.Insert_MTL_Unit_Trans(
p_resource_id => r_resource.resource_id,
p_transaction_id => r_transaction.TRANSACTION_ID,
p_inventory_item_id => r_transaction.inventory_item_id,
p_organization_id => r_transaction.organization_id,
p_subinventory_code => r_transaction.subinventory_code
);
, 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_material_transaction%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.TRANSACTION_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_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);
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 ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_material_transaction.TRANSACTION_ID
, 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_material_transaction.seqno$$,
r_material_transaction.TRANSACTION_ID,
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 ***/
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_material_transaction.TRANSACTION_ID
, 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);
/* Delete matching contact record(s) */
CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_lot_number.seqno$$,
r_lot_number.tranid$$,
g_object_name,
'MTL_TRANS_LOT_NUMBERS',
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_lot_number.tranid$$
, v_object_name => g_object_name || 'MTL_TRANS_LOT_NUMBERS'
, v_message => 'Deleting from inqueue failed, Defer and reject record'
, v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
/* Delete matching contact record(s) */
CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
(
p_user_name,
p_tranid,
r_unit_transaction.seqno$$,
r_unit_transaction.tranid$$,
g_object_name,
'MTL_UNIT_TRANSACTIONS',
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
jtm_message_log_pkg.Log_Msg
( v_object_id => r_unit_transaction.tranid$$
, v_object_name => g_object_name || 'MTL_UNIT_TRANSACTIONS'
, v_message => 'Deleting from inqueue failed, Defer and reject record'
, 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_material_transaction.TRANSACTION_ID
, 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);