[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** and will insert these into the CSM-Contract txn types table */
PROCEDURE INSERT_CONTRACT_TXN_TYPES
( p_cov_txn_grp_line_id IN NUMBER
, p_business_process_id IN NUMBER
, p_contract_service_id IN VARCHAR2
, p_user_id IN NUMBER
)
IS
CURSOR l_access_id_csr(p_contract_service_id IN VARCHAR2, p_business_process_id in number,
p_txn_billing_type_id in number, p_user_id IN number)
IS
SELECT acc.access_id, acc.counter
FROM csm_contr_buss_txn_types_acc acc
WHERE user_id = p_user_id
AND contract_service_id = p_contract_service_id
AND business_process_id = p_business_process_id
AND txn_billing_type_id = p_txn_billing_type_id;
CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES'
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id = ' || p_user_id
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_STATEMENT);
CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
,P_ACC_TABLE_NAME => g_acc_table_name3
,P_SEQ_NAME => g_sequence_name3
,P_USER_ID => p_user_id
,P_PK1_NAME => g_pk1_name3
-- ,P_PK1_CHAR_VALUE => p_cov_txn_grp_line_id
,P_PK1_CHAR_VALUE => p_contract_service_id
,P_PK2_NAME => g_pk2_name3
,P_PK2_NUM_VALUE => p_business_process_id
,P_PK3_NAME => g_pk3_name3
,P_PK3_NUM_VALUE => l_oks_out_tbl_bt(i).txn_bill_type_id
);
/* l_sql := ' UPDATE '|| g_acc_table_name3 ||
' SET percent_covered = ' || '''' || l_oks_out_tbl_bt(i).percent_covered || '''' ||
' ,up_to_amount = ' || '''' || l_oks_out_tbl_bt(i).Covered_upto_amount || '''' ||
' WHERE contract_service_id = ' || '''' || p_contract_service_id || '''' ||
' AND business_process_id = ' || p_business_process_id ||
' AND txn_billing_type_id = ' || l_oks_out_tbl_bt(i).txn_bill_type_id ;
l_sql := ' UPDATE '|| g_acc_table_name3 ||
' SET percent_covered = :1 ' ||
' ,up_to_amount = :2 ' ||
' WHERE contract_service_id = :3' ||
' AND business_process_id = :4' ||
' AND txn_billing_type_id = :5';
CSM_ACC_PKG.UPDATE_ACC (p_publication_item_names => g_publication_item_name3
,p_acc_table_name => g_acc_table_name3
,p_user_id => p_user_id
,p_access_id => l_access_id
);
CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES'
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES:' || l_sqlerrno || ':' || l_sqlerrmsg
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_ERROR);
END INSERT_CONTRACT_TXN_TYPES;
** the Contract Line and will insert the details of these buss_processes **
** into the CSM-Contract buss process table */
PROCEDURE INSERT_CONTRACT_BUSS_PROCESSES
( p_contract_service_id IN VARCHAR2
, p_user_id IN NUMBER
)
IS
CURSOR l_access_id_csr(p_contract_service_id IN VARCHAR2, p_business_process_id in number, p_user_id IN number)
IS
SELECT acc.access_id, acc.counter
FROM csm_contr_buss_processes_acc acc
WHERE user_id = p_user_id
AND contract_service_id = p_contract_service_id
AND business_process_id = p_business_process_id;
CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES'
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_PROCEDURE);
/* Insert all the Txn Billing Types for the Bus-proc */
INSERT_CONTRACT_TXN_TYPES
( 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_contract_service_id => p_contract_service_id
, p_user_id => p_user_id
);
CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id = ' || p_user_id
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_STATEMENT);
CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
,P_ACC_TABLE_NAME => g_acc_table_name2
,P_SEQ_NAME => g_sequence_name2
,P_PK1_NAME => g_pk1_name2
,P_PK1_CHAR_VALUE => p_contract_service_id
,P_PK2_NAME => g_pk2_name2
,P_PK2_NUM_VALUE => l_oks_out_tbl_bp(i).bp_id
,P_USER_ID => p_user_id
);
/* l_sql := ' UPDATE '|| g_acc_table_name2 ||
' SET start_date = ' || '''' || l_oks_out_tbl_bp(i).start_date || '''' ||
' ,end_date = '|| '''' || l_oks_out_tbl_bp(i).end_date || '''' ||
' WHERE contract_service_id = ' || '''' || p_contract_service_id || '''' ||
' AND business_process_id = ' || l_oks_out_tbl_bp(i).bp_id;
l_sql := ' UPDATE '|| g_acc_table_name2 ||
' SET start_date = :1 ' ||
' ,end_date = :2'||
' WHERE contract_service_id = :3' ||
' AND business_process_id = :4';
CSM_ACC_PKG.UPDATE_ACC (p_publication_item_names => g_publication_item_name2
,p_acc_table_name => g_acc_table_name2
,p_user_id => p_user_id
,p_access_id => l_access_id
);
CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES'
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES:' || l_sqlerrno || ':' || l_sqlerrmsg
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_ERROR);
END INSERT_CONTRACT_BUSS_PROCESSES;
** and will insert the details into the CSM-Contract header table */
PROCEDURE INSERT_CONTRACT_HEADER
( p_incident_id IN NUMBER
, p_user_id IN NUMBER
)
IS
CURSOR l_csm_contract_line_details (b_incident_id NUMBER )
IS
SELECT cs.CONTRACT_SERVICE_ID
, h.contract_number
, s.name
, s.description
, l.currency_code
FROM okc_k_headers_all_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);
SELECT acc.access_id, acc.counter
FROM csm_contr_headers_acc acc
WHERE incident_id = p_incident_id
AND user_id = p_user_id;
CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER'
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_PROCEDURE);
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_EXCEPTION);
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER'
, FND_LOG.LEVEL_STATEMENT);
INSERT_CONTRACT_BUSS_PROCESSES
( p_contract_service_id => r_csm_contract_line_details.contract_service_id
, p_user_id => p_user_id
);
CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id = ' || p_user_id
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_STATEMENT);
CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => p_incident_id
,P_USER_ID => p_user_id
);
/* l_sql := ' UPDATE '|| g_acc_table_name1 ||
' SET contract_service_id = ' || '''' || r_csm_contract_line_details.contract_service_id || '''' ||
', contract_number = '|| '''' || r_csm_contract_line_details.contract_number || '''' ||
', service_name = '|| '''' ||r_csm_contract_line_details.name ||'''' ||
', service_description = '||'''' || r_csm_contract_line_details.description ||'''' ||
', amount_uom_code = '|| '''' ||r_csm_contract_line_details.currency_code ||'''' ||
' WHERE incident_id = '|| p_incident_id;
l_sql := ' UPDATE '|| g_acc_table_name1 ||
' SET contract_service_id = :1' ||
', contract_number = :2 '||
', service_name = :3'||
', service_description = :4'||
', amount_uom_code = :5'||
' WHERE incident_id = :6 ';
CSM_ACC_PKG.UPDATE_ACC (p_publication_item_names => g_publication_item_name1
,p_acc_table_name => g_acc_table_name1
,p_user_id => p_user_id
,p_access_id => l_access_id
);
CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER'
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER' , FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER:' || l_sqlerrno || ':' || l_sqlerrmsg
, 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_ERROR);
END INSERT_CONTRACT_HEADER;
/* Will delete all the sr-contract acc records for all mobile resources */
PROCEDURE DELETE_SR_CONTRACT_ACC
( p_incident_id IN NUMBER
, p_user_id IN NUMBER
)
IS
l_sqlerrno varchar2(20);
SELECT incident_id, contract_service_id
FROM CSM_CONTR_HEADERS_ACC
WHERE user_id = p_user_id
AND incident_id = p_incidentid;
SELECT contract_service_id, business_process_id
FROM CSM_CONTR_BUSS_PROCESSES_ACC
WHERE user_id = p_userid
AND contract_service_id = p_contractserviceid;
SELECT contract_service_id, business_process_id, txn_billing_type_id
FROM csm_contr_buss_txn_types_acc
WHERE user_id = p_userid
AND contract_service_id = p_contractserviceid
AND business_process_id = p_businessprocessid;
CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC'
, 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_PROCEDURE);
, 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_STATEMENT);
CSM_ACC_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_CHAR_VALUE => r_contr_buss_txn_types_rec.contract_service_id
,P_PK2_NAME => g_pk2_name3
,P_PK2_NUM_VALUE => r_contr_buss_txn_types_rec.business_process_id
,P_PK3_NAME => g_pk3_name3
,P_PK3_NUM_VALUE => r_contr_buss_txn_types_rec.txn_billing_type_id
,P_USER_ID => p_user_id
);
, 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_STATEMENT);
CSM_ACC_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_CHAR_VALUE => r_contr_buss_processes_rec.contract_service_id
,P_PK2_NAME => g_pk2_name2
,P_PK2_NUM_VALUE => r_contr_buss_processes_rec.business_process_id
,P_USER_ID => p_user_id
);
, 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_STATEMENT);
CSM_ACC_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 => r_contr_headers_rec.incident_id
,P_USER_ID => p_user_id
);
CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC'
, 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC:' || l_sqlerrno || ':' || l_sqlerrmsg
, 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_ERROR);
END DELETE_SR_CONTRACT_ACC;
PROCEDURE DELETE_OKS_NOTES_ACC(p_contract_service_id IN varchar2,
p_user_id IN number)
IS
l_markdirty BOOLEAN;
SELECT acc.jtf_note_id, acc.user_id
FROM jtf_notes_b notes, csm_notes_acc acc
WHERE notes.source_object_code = p_sourceobjectcode
AND notes.source_object_id = p_sourceobjectid
AND notes.jtf_note_id = acc.jtf_note_id
AND acc.user_id = p_user_id;
--delete for the user
for l_oks_notes_rec in l_oks_notes_csr(l_sourceobjectcode,
l_sourceobjectid,
l_user_id) loop
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
,P_ACC_TABLE_NAME => g_notes_acc_table_name
,P_PK1_NAME => g_notes_pk1_name
,P_PK1_NUM_VALUE => l_oks_notes_rec.jtf_note_id
,P_USER_ID => l_oks_notes_rec.user_id
);
'CSM_CONTRACT_EVENT_PKG.DELETE_OKS_NOTES_ACC',FND_LOG.LEVEL_EXCEPTION);
END DELETE_OKS_NOTES_ACC;
PROCEDURE INSERT_OKS_NOTES_ACC(p_contract_service_id IN VARCHAR2,
p_user_id IN number)
IS
l_markdirty BOOLEAN;
SELECT jtf_note_id
FROM jtf_notes_b
WHERE source_object_code = p_sourceobjectcode
AND source_object_id = p_sourceobjectid;
--delete for the user
for l_oks_notes_rec in l_oks_notes_csr(l_sourceobjectcode,
l_sourceobjectid) loop
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
,P_ACC_TABLE_NAME => g_notes_acc_table_name
,P_SEQ_NAME => g_notes_seq_name
,P_PK1_NAME => g_notes_pk1_name
,P_PK1_NUM_VALUE => l_oks_notes_rec.jtf_note_id
,P_USER_ID => l_user_id
);
'CSM_CONTRACT_EVENT_PKG.INSERT_OKS_NOTES_ACC',FND_LOG.LEVEL_EXCEPTION);
END INSERT_OKS_NOTES_ACC;
INSERT_CONTRACT_HEADER( p_incident_id => p_incident_id
, p_user_id => p_user_id);
DELETE_SR_CONTRACT_ACC
( p_incident_id => p_incident_id
, p_user_id => p_user_id
);
/* The contract service id is updated for the SR **
** the ACC records has to be deleted for this SR-contract records */
DELETE_SR_CONTRACT_ACC
( p_incident_id => p_incident_id
, p_user_id => p_user_id
);
DELETE_OKS_NOTES_ACC(p_contract_service_id => p_old_contract_service_id,
p_user_id => p_user_id);
/* The contract service id is updated for the SR **
** the ACC records has to be created for this SR-contract records */
INSERT_CONTRACT_HEADER( p_incident_id => p_incident_id
, p_user_id => p_user_id);
INSERT_OKS_NOTES_ACC(p_contract_service_id => p_contract_service_id,
p_user_id => p_user_id);