[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION INSERT_TXN_TYPES_RECORD
( p_incident_id IN NUMBER
, p_txn_billing_type_id IN NUMBER
, p_cov_txn_grp_line_id IN NUMBER
, p_bp_id IN NUMBER
, p_up_to_amount IN VARCHAR2
, p_percent_covered IN VARCHAR2
)
RETURN BOOLEAN
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name1
, 'Entering INSERT_TXN_TYPES_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
INSERT INTO CSL_CONTR_BUSS_TXN_TYPES
( INCIDENT_ID
, TXN_BILLING_TYPE_ID
, BUSINESS_PROCESS_ID
, CONTRACT_SERVICE_ID
, UP_TO_AMOUNT
, PERCENT_COVERED
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
VALUES
( p_incident_id
, p_txn_billing_type_id
, p_bp_id
, p_cov_txn_grp_line_id
, p_up_to_amount
, p_percent_covered
, sysdate
, 1
, sysdate
, 1
);
, 'Leaving INSERT_TXN_TYPES_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in INSERT_TXN_TYPES_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END INSERT_TXN_TYPES_RECORD;
** and will insert these into the CSL-Contract txn types table */
FUNCTION INSERT_CONTRACT_TXN_TYPES
( p_incident_id IN NUMBER
, p_cov_txn_grp_line_id IN NUMBER
, p_business_process_id IN NUMBER
, p_resource_id IN NUMBER
)
RETURN NUMBER
IS
--Fix for Bug #3478401
CURSOR c_csl_contr_txn_types ( b_incident_id NUMBER
, b_txn_bill_type_id NUMBER
, b_bp_id NUMBER
, b_cov_txn_grp_line_id NUMBER) IS
SELECT null
FROM CSL_CONTR_BUSS_TXN_TYPES
WHERE INCIDENT_ID = b_incident_id
AND TXN_BILLING_TYPE_ID = b_txn_bill_type_id
AND BUSINESS_PROCESS_ID = b_bp_id
AND CONTRACT_SERVICE_ID = b_cov_txn_grp_line_id;
, 'Entering INSERT_CONTRACT_TXN_TYPES'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
/* Insert the new Txn bill type record into the CSL contract table */
l_success := INSERT_TXN_TYPES_RECORD
( p_incident_id => p_incident_id
, p_txn_billing_type_id => l_oks_out_tbl_bt(i).txn_bill_type_id
, p_cov_txn_grp_line_id => p_cov_txn_grp_line_id
, p_bp_id => p_business_process_id
, p_up_to_amount => l_oks_out_tbl_bt(i).Covered_upto_amount
, p_percent_covered => l_oks_out_tbl_bt(i).percent_covered
);
, 'Inserting ACC record for resource_id = ' || p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
JTM_HOOK_UTIL_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
,P_ACC_TABLE_NAME => g_acc_table_name3
,P_RESOURCE_ID => p_resource_id
,P_PK1_NAME => g_pk1_name3
,P_PK1_NUM_VALUE => p_incident_id
,P_PK2_NAME => g_pk2_name3
,P_PK2_NUM_VALUE => l_oks_out_tbl_bt(i).txn_bill_type_id
,P_PK3_NAME => g_pk3_name3
,P_PK3_NUM_VALUE => p_business_process_id
);
, 'Leaving INSERT_CONTRACT_TXN_TYPES'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in INSERT_CONTRACT_TXN_TYPES'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END INSERT_CONTRACT_TXN_TYPES;
FUNCTION INSERT_BUSS_PROCESSES_RECORD
( p_incident_id IN NUMBER
, p_cov_txn_grp_line_id IN NUMBER
, p_bp_id IN NUMBER
, p_start_date IN DATE
, p_end_date IN DATE
)
RETURN BOOLEAN
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name1
, 'Entering INSERT_BUSS_PROCESSES_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
INSERT INTO CSL_CONTR_BUSS_PROCESSES
( INCIDENT_ID
, BUSINESS_PROCESS_ID
, CONTRACT_SERVICE_ID
, START_DATE
, END_DATE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
VALUES
( p_incident_id
, p_bp_id
, p_cov_txn_grp_line_id
, p_start_date
, p_end_date
, sysdate
, 1
, sysdate
, 1
);
, 'Leaving INSERT_BUSS_PROCESSES_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in INSERT_BUSS_PROCESSES_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END INSERT_BUSS_PROCESSES_RECORD;
** the Contract Line and will insert the details of these buss_processes **
** into the CSL-Contract buss process table */
FUNCTION INSERT_CONTRACT_BUSS_PROCESSES
( p_incident_id IN NUMBER
, p_contract_service_id IN NUMBER
, p_resource_id IN NUMBER
)
RETURN NUMBER
IS
CURSOR c_csl_contr_bus_proc (b_incident_id NUMBER, b_cov_txn_grp_line_id NUMBER, b_bp_id NUMBER) IS
SELECT null
FROM CSL_CONTR_BUSS_PROCESSES
WHERE INCIDENT_ID = b_incident_id
AND BUSINESS_PROCESS_ID = b_bp_id
AND CONTRACT_SERVICE_ID = b_cov_txn_grp_line_id;
, 'Entering INSERT_CONTRACT_BUSS_PROCESSES'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
/* Insert the new bus proc record into the CSL contract table */
l_success := INSERT_BUSS_PROCESSES_RECORD
( p_incident_id => p_incident_id
, p_cov_txn_grp_line_id => l_oks_out_tbl_bp(i).cov_txn_grp_line_id
, p_bp_id => l_oks_out_tbl_bp(i).bp_id
, p_start_date => l_oks_out_tbl_bp(i).start_date
, p_end_date => l_oks_out_tbl_bp(i).end_date
);
/* Insert all the Txn Billing Types for the Bus-proc */
l_rec := INSERT_CONTRACT_TXN_TYPES
( p_incident_id => p_incident_id
, p_cov_txn_grp_line_id => l_oks_out_tbl_bp(i).cov_txn_grp_line_id
, p_business_process_id => l_oks_out_tbl_bp(i).bp_id
, p_resource_id => p_resource_id
);
, 'Inserting ACC record for resource_id = ' || p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
JTM_HOOK_UTIL_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_RESOURCE_ID => p_resource_id
,P_PK1_NAME => g_pk1_name2
,P_PK1_NUM_VALUE => p_incident_id
,P_PK2_NAME => g_pk2_name2
,P_PK2_NUM_VALUE => l_oks_out_tbl_bp(i).bp_id
);
, 'Leaving INSERT_CONTRACT_BUSS_PROCESSES'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in INSERT_CONTRACT_BUSS_PROCESSES'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END INSERT_CONTRACT_BUSS_PROCESSES;
FUNCTION INSERT_CONTRACT_HEADER_RECORD
( p_incident_id IN NUMBER
, p_cov_txn_grp_line_id IN NUMBER
, p_contract_number IN VARCHAR2
, p_service_name IN VARCHAR2
, p_service_description IN VARCHAR2
, p_amount_uom_code IN VARCHAR2
)
RETURN BOOLEAN
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name1
, 'Entering INSERT_CONTRACT_HEADER_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
INSERT INTO CSL_SR_CONTRACT_HEADERS
( INCIDENT_ID
, CONTRACT_SERVICE_ID
, CONTRACT_NUMBER
, SERVICE_NAME
, SERVICE_DESCRIPTION
, AMOUNT_UOM_CODE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
)
VALUES
( p_incident_id
, p_cov_txn_grp_line_id
, p_contract_number
, p_service_name
, p_service_description
, p_amount_uom_code
, sysdate
, 1
, sysdate
, 1
);
, 'Leaving INSERT_CONTRACT_HEADER_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in INSERT_CONTRACT_HEADER_RECORD'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END INSERT_CONTRACT_HEADER_RECORD;
** and will insert the details into the CSL-Contract header table */
FUNCTION INSERT_CONTRACT_HEADER
( p_incident_id IN NUMBER
, p_resource_id IN NUMBER
)
RETURN BOOLEAN
IS
--Added sts_code = 'ACTIVE' to fix bug #3484383
CURSOR c_csl_contract_line_details (b_incident_id NUMBER ) IS
select l.id
, h.contract_number
, s.name
, s.description
, l.currency_code
from okc_k_headers_b h
, okc_k_lines_b l
, Okx_System_Items_V s
, Okc_K_Items IT
, CS_INCIDENTS_ALL_B cs
where cs.INCIDENT_ID = b_incident_id
and cs.CONTRACT_SERVICE_ID = l.id
and h.id = l.dnz_chr_id
and l.id = it.CLE_ID
AND s.Id1 = to_number(IT.Object1_Id1)
AND s.Id2 = to_number(IT.Object1_Id2)
AND h.sts_code = 'ACTIVE';
SELECT NULL
FROM CSL_SR_CONTRACT_HEADERS
WHERE INCIDENT_ID = b_incident_id;
SELECT CONTRACT_SERVICE_ID
FROM CS_INCIDENTS_ALL_B CS
WHERE CS.incident_id = b_incident_id;
, 'Entering INSERT_CONTRACT_HEADER'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
/* Insert the new contract line record into the CSL contract table */
l_success := INSERT_CONTRACT_HEADER_RECORD
( p_incident_id => p_incident_id
, p_cov_txn_grp_line_id => l_contract_service_id
, p_contract_number => l_contract_nuber
, p_service_name => l_service_name
, p_service_description => l_service_description
, p_amount_uom_code => l_amount_uom_code
);
l := INSERT_CONTRACT_BUSS_PROCESSES
( p_incident_id => p_incident_id
, p_contract_service_id => l_contract_service_id
, p_resource_id => p_resource_id
);
, 'Inserting ACC record for resource_id = ' || p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
JTM_HOOK_UTIL_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_RESOURCE_ID => p_resource_id
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_incident_id
);
l_return := CSL_JTF_NOTES_ACC_PKG.PRE_INSERT_CHILDREN
( P_SOURCE_OBJ_ID => l_contract_service_id
, P_SOURCE_OBJ_CODE => 'OKS_COV_NOTE'
, P_RESOURCE_ID => p_resource_id
);
, 'Inserting Contract Notes Failed for resource_id = '
|| p_resource_id
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Leaving INSERT_CONTRACT_HEADER'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in INSERT_CONTRACT_HEADER'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END INSERT_CONTRACT_HEADER;
/* Will delete all the sr-contract acc records for all mobile resources */
-- ER 3168529 - Support for contract notes. Added a new parameter
-- p_contract_service_id
PROCEDURE DELETE_SR_CONTRACT_ACC
( p_incident_id IN NUMBER
, p_resource_id IN NUMBER
, p_contract_service_id IN NUMBER
)
IS
BEGIN
IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
jtm_message_log_pkg.Log_Msg
( p_incident_id
, g_table_name1
, 'Entering DELETE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_incident_id
,P_RESOURCE_ID => p_resource_id
);
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_PK1_NAME => g_pk1_name2
,P_PK1_NUM_VALUE => p_incident_id
,P_RESOURCE_ID => p_resource_id
);
JTM_HOOK_UTIL_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
,P_ACC_TABLE_NAME => g_acc_table_name3
,P_PK1_NAME => g_pk1_name3
,P_PK1_NUM_VALUE => p_incident_id
,P_RESOURCE_ID => p_resource_id
);
CSL_JTF_NOTES_ACC_PKG.POST_DELETE_CHILDREN
(
P_SOURCE_OBJ_ID => p_contract_service_id
, P_SOURCE_OBJ_CODE => 'OKS_COV_NOTE'
, P_RESOURCE_ID => p_resource_id
);
, 'Leaving DELETE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
, 'Exception occured in DELETE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
, 'csl_contract_handling_pkg');
END DELETE_SR_CONTRACT_ACC;
/* Called after SR-ACC Insert */
PROCEDURE POST_INSERT_SR_CONTRACT_ACC (
p_incident_id IN NUMBER
, p_resource_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2)
IS
l_success BOOLEAN := FALSE;
, 'Entering POST_INSERT_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
l_success := INSERT_CONTRACT_HEADER
( p_incident_id => p_incident_id
, p_resource_id => p_resource_id
);
, 'Leaving POST_INSERT_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
END POST_INSERT_SR_CONTRACT_ACC;
/* Called after SR-ACC Update */
PROCEDURE POST_UPDATE_SR_CONTRACT_ACC (
p_incident_id IN NUMBER
, p_old_contract_service_id IN NUMBER
, p_new_contract_service_id IN NUMBER
, p_resource_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR c_csl_contr_record (b_incident_id NUMBER) IS
SELECT CONTRACT_SERVICE_ID
FROM CSL_SR_CONTRACT_HEADERS
WHERE INCIDENT_ID = b_incident_id;
, 'Entering POST_UPDATE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
/*** yes -> delete old contract from csl contract tables ***/
DELETE CSL_CONTR_BUSS_TXN_TYPES WHERE incident_id = p_incident_id;
DELETE CSL_CONTR_BUSS_PROCESSES WHERE incident_id = p_incident_id;
DELETE CSL_SR_CONTRACT_HEADERS WHERE incident_id = p_incident_id;
/* The contract service id is updated for the SR **
** the ACC records has to be deleted for this SR-contract records */
-- ER 3168529
DELETE_SR_CONTRACT_ACC
( p_incident_id => p_incident_id
, p_resource_id => p_resource_id
, p_contract_service_id => p_old_contract_service_id
);
/* The contract service id is updated for the SR **
** the ACC records has to be created for this SR-contract records */
POST_INSERT_SR_CONTRACT_ACC
( p_incident_id => p_incident_id
, p_resource_id => p_resource_id
, x_return_status => x_return_status
);
, 'Leaving POST_UPDATE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
END POST_UPDATE_SR_CONTRACT_ACC;
/* Called before SR-ACC delete */
PROCEDURE PRE_DELETE_SR_CONTRACT_ACC (
p_incident_id IN NUMBER
, p_resource_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_csl_contr_record (b_incident_id NUMBER) IS
SELECT CONTRACT_SERVICE_ID
FROM CSL_SR_CONTRACT_HEADERS
WHERE INCIDENT_ID = b_incident_id;
, 'Entering PRE_DELETE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
DELETE_SR_CONTRACT_ACC
( p_incident_id => p_incident_id
, p_resource_id => p_resource_id
, p_contract_service_id => r_csl_contr_record.contract_service_id
);
, 'Entering PRE_DELETE_SR_CONTRACT_ACC'
, JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
, 'csl_contract_handling_pkg');
END PRE_DELETE_SR_CONTRACT_ACC;