[Home] [Help]
PACKAGE BODY: APPS.CSM_CONTRACT_EVENT_PKG
Source
1 PACKAGE BODY CSM_CONTRACT_EVENT_PKG AS
2 /* $Header: csmectrb.pls 120.2.12010000.2 2009/09/03 05:03:28 trajasek ship $*/
3
4 /*** Globals ***/
5 -- CSM_CONTR_HEADERS
6 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_CONTR_HEADERS_ACC';
7 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
8 CSM_ACC_PKG.t_publication_item_list('CSM_CONTR_HEADERS');
9 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
10 g_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_CONTR_HEADERS_ACC_S';
11
12 -- CSM_CONTR_BUSS_PROCESSES
13 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_CONTR_BUSS_PROCESSES_ACC';
14 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
15 CSM_ACC_PKG.t_publication_item_list('CSM_CONTR_BUSS_PROCESSES');
16 g_pk1_name2 CONSTANT VARCHAR2(30) := 'CONTRACT_SERVICE_ID';
17 g_pk2_name2 CONSTANT VARCHAR2(30) := 'BUSINESS_PROCESS_ID';
18 g_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_CONTR_BUSS_PROCESSES_ACC_S';
19
20 -- CSM_CONTR_BUSS_TXN_TYPES
21 g_acc_table_name3 CONSTANT VARCHAR2(30) := 'CSM_CONTR_BUSS_TXN_TYPES_ACC';
22 g_publication_item_name3 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
23 CSM_ACC_PKG.t_publication_item_list('CSM_CONTR_BUSS_TXN_TYPES');
24 g_pk1_name3 CONSTANT VARCHAR2(30) := 'CONTRACT_SERVICE_ID';
25 g_pk2_name3 CONSTANT VARCHAR2(30) := 'BUSINESS_PROCESS_ID';
26 g_pk3_name3 CONSTANT VARCHAR2(30) := 'TXN_BILLING_TYPE_ID';
27 g_sequence_name3 CONSTANT VARCHAR2(30) := 'CSM_CONTR_BUSS_TXN_TYPES_ACC_S';
28
29 --CSM_COV_ACTION_TIMES
30 g_acc_table_name4 CONSTANT VARCHAR2(30) := 'CSM_COV_ACTION_TIMES_ACC';
31 g_publication_item_name4 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
32 CSM_ACC_PKG.t_publication_item_list('CSM_COV_ACTION_TIMES');
33 g_pk1_name4 CONSTANT VARCHAR2(30) := 'ID';
34 g_pk2_name4 CONSTANT VARCHAR2(30) := 'INCIDENT_SEVERITY_ID1';
35 g_pk3_name4 CONSTANT VARCHAR2(30) := 'ACTION_TYPE_CODE';
36 g_sequence_name4 CONSTANT VARCHAR2(30) := 'CSM_COV_ACTION_TIMES_ACC_S';
37
38 -- CSF_M_NOTES
39 g_notes_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_ACC';
40 g_notes_table_name CONSTANT VARCHAR2(30) := 'JTF_NOTES_B';
41 g_notes_seq_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_ACC_S';
42 g_notes_pk1_name CONSTANT VARCHAR2(30) := 'JTF_NOTE_ID';
43 g_notes_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
44 CSM_ACC_PKG.t_publication_item_list('CSF_M_NOTES');
45
46 /* Function will get the field service related buss_processes of **
47 ** the Contract Line and the Action and the Reaction Times **
48 ** Associated with it */
49 PROCEDURE INSERT_COV_ACTION_TIMES
50 ( p_user_id IN NUMBER,
51 p_contract_service_id IN VARCHAR2,
52 p_business_proc_id IN NUMBER
53 )
54 IS
55
56 CURSOR c_get_cov_id(c_contract_service_id IN NUMBER, c_business_process_id in number)
57 IS
58 SELECT BPL.Id
59 FROM OKS_K_Lines_B KSL,
60 OKC_K_LINES_B BPL
61 WHERE KSL.Cle_ID = c_contract_service_id
62 AND BPL.Cle_ID = KSL.COVERAGE_ID
63 AND BPL.Lse_Id IN (3,16,21)
64 AND EXISTS (SELECT 'x'
65 FROM OKC_K_Items BIT
66 WHERE BIT.Cle_id = BPL.Id
67 AND Object1_Id1 = c_business_process_id
68 AND Jtot_Object1_Code = 'OKX_BUSIPROC');
69
70 CURSOR c_get_cov_times(c_bus_proc_cle_id in number)
71 IS
72 select ID,INCIDENT_SEVERITY_ID1,ACTION_TYPE_CODE
73 FROM OKS_COV_ACTION_TIMES_V
74 WHERE BUS_PROCESS_CLE_ID = c_bus_proc_cle_id;
75
76 l_sqlerrno VARCHAR2(20);
77 l_sqlerrmsg VARCHAR2(2000);
78 l_bus_proc_cle_id NUMBER;
79
80 BEGIN
81 CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES'
82 , 'CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES', FND_LOG.LEVEL_PROCEDURE);
83
84 --get the Business process Cle id
85 OPEN c_get_cov_id(p_contract_service_id, p_business_proc_id);
86 FETCH c_get_cov_id INTO l_bus_proc_cle_id;
87 CLOSE c_get_cov_id;
88
89 IF l_bus_proc_cle_id IS NOT NULL THEN
90
91 FOR r_get_cov_times IN c_get_cov_times(l_bus_proc_cle_id) LOOP
92
93 -- Push record to the Resource (insert in ACC)
94 CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id and contract serviceid = '
95 || p_user_id || ' - ' || p_contract_service_id
96 , 'CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES', FND_LOG.LEVEL_STATEMENT);
97
98 CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
99 ,P_ACC_TABLE_NAME => g_acc_table_name4
100 ,P_SEQ_NAME => g_sequence_name4
101 ,P_PK1_NAME => g_pk1_name4
102 ,P_PK1_NUM_VALUE => r_get_cov_times.ID
103 ,P_PK2_NAME => g_pk2_name4
104 ,P_PK2_NUM_VALUE => r_get_cov_times.INCIDENT_SEVERITY_ID1
105 ,P_PK3_NAME => g_pk3_name4
106 ,P_PK3_CHAR_VALUE => r_get_cov_times.ACTION_TYPE_CODE
107 ,P_USER_ID => p_user_id
108 );
109
110 UPDATE CSM_COV_ACTION_TIMES_ACC
111 SET JTOT_OBJECT1_CODE = 'OKX_BUSIPROC',
112 OBJECT1_ID1 = p_business_proc_id,
113 CONTRACT_SERVICE_ID = p_contract_service_id
114 WHERE ID = r_get_cov_times.ID
115 AND INCIDENT_SEVERITY_ID1 = r_get_cov_times.INCIDENT_SEVERITY_ID1
116 AND ACTION_TYPE_CODE = r_get_cov_times.ACTION_TYPE_CODE
117 AND USER_ID = p_user_id;
118
119 END LOOP;
120 END IF;
121
122 CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES'
123 , 'CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES', FND_LOG.LEVEL_PROCEDURE);
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 l_sqlerrno := to_char(SQLCODE);
128 l_sqlerrmsg := substr(SQLERRM, 1,2000);
129 CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES:' || l_sqlerrno || ':' || l_sqlerrmsg
130 , 'CSM_CONTRACT_EVENT_PKG.INSERT_COV_ACTION_TIMES', FND_LOG.LEVEL_ERROR);
131 RAISE;
132
133 END INSERT_COV_ACTION_TIMES;
134
135 PROCEDURE DELETE_COV_ACTION_TIMES
136 ( p_user_id IN NUMBER,
137 p_contract_service_id IN VARCHAR2,
138 p_business_proc_id IN NUMBER
139 )
140 IS
141 CURSOR c_delete_cov_times(c_contract_service_id IN VARCHAR2, c_business_process_id in NUMBER, c_user_id NUMBER)
142 IS
143 SELECT ID,INCIDENT_SEVERITY_ID1,ACTION_TYPE_CODE
144 FROM CSM_COV_ACTION_TIMES_ACC
145 WHERE USER_ID = c_user_id
146 AND CONTRACT_SERVICE_ID = c_contract_service_id
147 AND OBJECT1_ID1 = c_business_process_id;
148
149 l_sqlerrno VARCHAR2(20);
150 l_sqlerrmsg VARCHAR2(2000);
151 l_bus_proc_cle_id NUMBER;
152
153 BEGIN
154 CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES'
155 , 'CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES', FND_LOG.LEVEL_PROCEDURE);
156
157 FOR r_get_cov_times IN c_delete_cov_times(p_contract_service_id, p_business_proc_id, p_user_id) LOOP
158
159 -- Push record to the Resource (insert in ACC)
160 CSM_UTIL_PKG.LOG( 'Deleting ACC record for user_id and contract serviceid = '
161 || p_user_id || ' - ' || p_contract_service_id
162 , 'CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES', FND_LOG.LEVEL_STATEMENT);
163
164 CSM_ACC_PKG.Delete_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name4
165 ,P_ACC_TABLE_NAME => g_acc_table_name4
166 ,P_PK1_NAME => g_pk1_name4
167 ,P_PK1_NUM_VALUE => r_get_cov_times.ID
168 ,P_PK2_NAME => g_pk2_name4
169 ,P_PK2_NUM_VALUE => r_get_cov_times.INCIDENT_SEVERITY_ID1
170 ,P_PK3_NAME => g_pk3_name4
171 ,P_PK3_CHAR_VALUE => r_get_cov_times.ACTION_TYPE_CODE
172 ,P_USER_ID => p_user_id
173 );
174
175 END LOOP;
176
177 CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES'
178 , 'CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES', FND_LOG.LEVEL_PROCEDURE);
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 l_sqlerrno := to_char(SQLCODE);
183 l_sqlerrmsg := substr(SQLERRM, 1,2000);
184 CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES:' || l_sqlerrno || ':' || l_sqlerrmsg
185 , 'CSM_CONTRACT_EVENT_PKG.DELETE_COV_ACTION_TIMES', FND_LOG.LEVEL_ERROR);
186 RAISE;
187
188 END DELETE_COV_ACTION_TIMES;
189
190 /* Function will get the Txn types and the related settings of it **
191 ** - Up to amount **
192 ** - Percent covered **
193 ** and will insert these into the CSM-Contract txn types table */
194 PROCEDURE INSERT_CONTRACT_TXN_TYPES
195 ( p_cov_txn_grp_line_id IN NUMBER
196 , p_business_process_id IN NUMBER
197 , p_contract_service_id IN VARCHAR2
198 , p_user_id IN NUMBER
199 )
200 IS
201 CURSOR l_access_id_csr(p_contract_service_id IN VARCHAR2, p_business_process_id in number,
202 p_txn_billing_type_id in number, p_user_id IN number)
203 IS
204 SELECT acc.access_id, acc.counter
205 FROM csm_contr_buss_txn_types_acc acc
206 WHERE user_id = p_user_id
207 AND contract_service_id = p_contract_service_id
208 AND business_process_id = p_business_process_id
209 AND txn_billing_type_id = p_txn_billing_type_id;
210
211 l_oks_out_tbl_bt OKS_ENTITLEMENTS_PUB.OUTPUT_TBL_BT;
212 l_oks_out_tbl_br OKS_ENTITLEMENTS_PUB.OUTPUT_TBL_BR;
213
214 l_effected_records NUMBER := 0;
215 l_success BOOLEAN;
216
217 p_init_msg_list VARCHAR2(4000);
218 x_return_status VARCHAR2(1);
219 x_msg_count NUMBER;
220 x_msg_data VARCHAR2(4000);
221 l_sql VARCHAR2(2000);
222 l_sqlerrno VARCHAR2(20);
223 l_sqlerrmsg VARCHAR2(2000);
224 l_access_id number;
225 l_counter number;
226
227 BEGIN
228 CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES'
229 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_PROCEDURE);
230
231 /* This call to the Contracts API will return the Txn billing types in table **
232 ** variable: l_oks_out_tbl_bt **
233 ** This table should contain the following information: **
234 ** Txn_BT_line_id (NUMBER): Txn billing type id **
235 ** txn_billing_type_id (NUMBER): Business process if **
236 ** Covered_upto_amount (NUMBER): Upto amount covered **
237 ** percent_covered (NUMBER): percent_covered */
238
239 OKS_ENTITLEMENTS_PUB.Get_txn_billing_types
240 ( p_api_version => 1.0
241 , p_init_msg_list => p_init_msg_list
242 , p_cov_txngrp_line_id=> p_cov_txn_grp_line_id
243 , p_return_bill_rates_YN => 'N' -- We're not interested on the billing rates
244 , x_return_status => x_return_status
245 , x_msg_count => x_msg_count
246 , x_msg_data => x_msg_data
247 , x_txn_bill_types => l_oks_out_tbl_bt
248 , x_txn_bill_rates => l_oks_out_tbl_br -- Should return null
249 );
250
251 IF l_oks_out_tbl_bt.COUNT > 0 THEN
252 /* Looping over the table if table contain any record(s) */
253 FOR i IN l_oks_out_tbl_bt.FIRST .. l_oks_out_tbl_bt.LAST LOOP
254
255 -- Push record to the Resource (insert in ACC)
256 CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id = ' || p_user_id
257 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_STATEMENT);
258
259 CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
260 ,P_ACC_TABLE_NAME => g_acc_table_name3
261 ,P_SEQ_NAME => g_sequence_name3
262 ,P_USER_ID => p_user_id
263 ,P_PK1_NAME => g_pk1_name3
264 -- ,P_PK1_CHAR_VALUE => p_cov_txn_grp_line_id
265 ,P_PK1_CHAR_VALUE => p_contract_service_id
266 ,P_PK2_NAME => g_pk2_name3
267 ,P_PK2_NUM_VALUE => p_business_process_id
268 ,P_PK3_NAME => g_pk3_name3
269 ,P_PK3_NUM_VALUE => l_oks_out_tbl_bt(i).txn_bill_type_id
270 );
271
272 /* l_sql := ' UPDATE '|| g_acc_table_name3 ||
273 ' SET percent_covered = ' || '''' || l_oks_out_tbl_bt(i).percent_covered || '''' ||
274 ' ,up_to_amount = ' || '''' || l_oks_out_tbl_bt(i).Covered_upto_amount || '''' ||
275 ' WHERE contract_service_id = ' || '''' || p_contract_service_id || '''' ||
276 ' AND business_process_id = ' || p_business_process_id ||
277 ' AND txn_billing_type_id = ' || l_oks_out_tbl_bt(i).txn_bill_type_id ;
278
279 EXECUTE IMMEDIATE l_sql;
280 */
281
282 l_sql := ' UPDATE '|| g_acc_table_name3 ||
283 ' SET percent_covered = :1 ' ||
284 ' ,up_to_amount = :2 ' ||
285 ' WHERE contract_service_id = :3' ||
286 ' AND business_process_id = :4' ||
287 ' AND txn_billing_type_id = :5';
288
289 EXECUTE IMMEDIATE l_sql USING l_oks_out_tbl_bt(i).percent_covered, l_oks_out_tbl_bt(i).Covered_upto_amount,
290 p_contract_service_id, p_business_process_id, l_oks_out_tbl_bt(i).txn_bill_type_id;
291
292 OPEN l_access_id_csr(p_contract_service_id, p_business_process_id,
293 l_oks_out_tbl_bt(i).txn_bill_type_id, p_user_id);
294 FETCH l_access_id_csr INTO l_access_id, l_counter;
295 CLOSE l_access_id_csr;
296
297 -- if counter > 1 then pass update to the Pub item as we are updating acc table
298 IF l_counter > 1 THEN
299 CSM_ACC_PKG.UPDATE_ACC (p_publication_item_names => g_publication_item_name3
300 ,p_acc_table_name => g_acc_table_name3
301 ,p_user_id => p_user_id
302 ,p_access_id => l_access_id
303 );
304 END IF;
305
306 /* increase effected records if a new record has been created */
307 l_effected_records := l_effected_records + 1;
308
309 END LOOP;
310 END IF;
311
312 CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES'
313 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_PROCEDURE);
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 l_sqlerrno := to_char(SQLCODE);
318 l_sqlerrmsg := substr(SQLERRM, 1,2000);
319 CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES:' || l_sqlerrno || ':' || l_sqlerrmsg
320 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_TXN_TYPES', FND_LOG.LEVEL_ERROR);
321 RAISE;
322 END INSERT_CONTRACT_TXN_TYPES;
323
324
325 /* Function will get the field service related buss_processes of **
326 ** the Contract Line and will insert the details of these buss_processes **
327 ** into the CSM-Contract buss process table */
328 PROCEDURE INSERT_CONTRACT_BUSS_PROCESSES
329 ( p_contract_service_id IN VARCHAR2
330 , p_user_id IN NUMBER
331 )
332 IS
333 CURSOR l_access_id_csr(p_contract_service_id IN VARCHAR2, p_business_process_id in number, p_user_id IN number)
334 IS
335 SELECT acc.access_id, acc.counter
336 FROM csm_contr_buss_processes_acc acc
337 WHERE user_id = p_user_id
338 AND contract_service_id = p_contract_service_id
339 AND business_process_id = p_business_process_id;
340
341 l_oks_in_rec_bp OKS_ENTITLEMENTS_PUB.INP_REC_BP;
342 l_oks_out_tbl_bp OKS_ENTITLEMENTS_PUB.OUTPUT_TBL_BP;
343 l_effected_records NUMBER := 0;
344 l_rec NUMBER := 0;
345 l_success BOOLEAN;
346
347 p_init_msg_list VARCHAR2(4000);
348 x_return_status VARCHAR2(1);
349 x_msg_count NUMBER;
350 x_msg_data VARCHAR2(4000);
351 l_sql VARCHAR2(2000);
352 l_sqlerrno VARCHAR2(20);
353 l_sqlerrmsg VARCHAR2(2000);
354 l_access_id number;
355 l_counter number;
356
357 BEGIN
358 CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES'
359 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_PROCEDURE);
360
361 /* The l_oks_rec_bp should contain the information about the contract line **
362 ** which the information should get from and the flags will make the criteria **
363 ** of the result set which you're interest in */
364 l_oks_in_rec_bp.contract_line_id := p_contract_service_id;
365 /* Y: API will check the business process definition */
366 l_oks_in_rec_bp.check_bp_def := 'Y';
367 /* Y: Validate with Service Request enabled flag of the bus-proc */
368 l_oks_in_rec_bp.sr_enabled := 'Y';
369 /* N: Don't validate with Depot Repair enabled flag of the bus-proc */
370 --BUG 2613672: Pass NULL instead of 'N '
371 --l_oks_in_rec_bp.dr_enabled := 'N';
372 /* Y: Validate with Field Service enabled flag of the bus-proc */
373 l_oks_in_rec_bp.fs_enabled := 'Y';
374
375
376 /* This call to the Contracts API will return the business processes in table **
377 ** variable: l_oks_out_tbl_bp **
378 ** This table should contain the following information: **
379 ** cov_txn_grp_line_id (NUMBER): Contract line id for business process **
380 ** bp_id (NUMBER): Business Process ID **
381 ** start_date (DATE) : Start Date for business process **
382 ** end_date (DATE) : End Date for business process */
383
384 OKS_ENTITLEMENTS_PUB.Get_cov_txn_groups
385 ( p_api_version => 1.0
386 , p_init_msg_list => p_init_msg_list
387 , p_inp_rec_bp => l_oks_in_rec_bp
388 , x_return_status => x_return_status
389 , x_msg_count => x_msg_count
390 , x_msg_data => x_msg_data
391 , x_cov_txn_grp_lines => l_oks_out_tbl_bp
392 );
393
394 IF l_oks_out_tbl_bp.COUNT > 0 THEN
395 /* Looping over the table if table contain any record(s) */
396 FOR i IN l_oks_out_tbl_bp.FIRST .. l_oks_out_tbl_bp.LAST LOOP
397
398 /* Insert all the Txn Billing Types for the Bus-proc */
399 INSERT_CONTRACT_TXN_TYPES
400 ( p_cov_txn_grp_line_id => l_oks_out_tbl_bp(i).cov_txn_grp_line_id
401 , p_business_process_id => l_oks_out_tbl_bp(i).bp_id
402 , p_contract_service_id => p_contract_service_id
403 , p_user_id => p_user_id
404 );
405
406 /* Insert all the Coverage Action and Reactiton times for the Bus-proc */
407 INSERT_COV_ACTION_TIMES
408 ( p_user_id => p_user_id,
409 p_contract_service_id => p_contract_service_id,
410 p_business_proc_id => l_oks_out_tbl_bp(i).bp_id
411 );
412
413 -- Push record to the Resource (insert in ACC)
414 CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id = ' || p_user_id
415 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_STATEMENT);
416
417 CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
418 ,P_ACC_TABLE_NAME => g_acc_table_name2
419 ,P_SEQ_NAME => g_sequence_name2
420 ,P_PK1_NAME => g_pk1_name2
421 ,P_PK1_CHAR_VALUE => p_contract_service_id
422 ,P_PK2_NAME => g_pk2_name2
423 ,P_PK2_NUM_VALUE => l_oks_out_tbl_bp(i).bp_id
424 ,P_USER_ID => p_user_id
425 );
426
427 /* l_sql := ' UPDATE '|| g_acc_table_name2 ||
428 ' SET start_date = ' || '''' || l_oks_out_tbl_bp(i).start_date || '''' ||
429 ' ,end_date = '|| '''' || l_oks_out_tbl_bp(i).end_date || '''' ||
430 ' WHERE contract_service_id = ' || '''' || p_contract_service_id || '''' ||
431 ' AND business_process_id = ' || l_oks_out_tbl_bp(i).bp_id;
432
433 EXECUTE IMMEDIATE l_sql;
434 */
435 l_sql := ' UPDATE '|| g_acc_table_name2 ||
436 ' SET start_date = :1 ' ||
437 ' ,end_date = :2'||
438 ' WHERE contract_service_id = :3' ||
439 ' AND business_process_id = :4';
440
441 EXECUTE IMMEDIATE l_sql USING l_oks_out_tbl_bp(i).start_date,l_oks_out_tbl_bp(i).end_date,
442 p_contract_service_id,l_oks_out_tbl_bp(i).bp_id;
443
444 OPEN l_access_id_csr(p_contract_service_id, l_oks_out_tbl_bp(i).bp_id, p_user_id);
445 FETCH l_access_id_csr INTO l_access_id, l_counter;
446 CLOSE l_access_id_csr;
447
448 -- if counter > 1 then pass update to the Pub item as we are updating acc table
449 IF l_counter > 1 THEN
450 CSM_ACC_PKG.UPDATE_ACC (p_publication_item_names => g_publication_item_name2
451 ,p_acc_table_name => g_acc_table_name2
452 ,p_user_id => p_user_id
453 ,p_access_id => l_access_id
454 );
455 END IF;
456
457 /* increase effected records if a new record has been created */
458 l_effected_records := l_effected_records + 1;
459
460 END LOOP;
461 END IF;
462
463 CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES'
464 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_PROCEDURE);
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 l_sqlerrno := to_char(SQLCODE);
469 l_sqlerrmsg := substr(SQLERRM, 1,2000);
470 CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES:' || l_sqlerrno || ':' || l_sqlerrmsg
471 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_BUSS_PROCESSES', FND_LOG.LEVEL_ERROR);
472 RAISE;
473
474 END INSERT_CONTRACT_BUSS_PROCESSES;
475
476 /* Function will get the details of the Contract Line **
477 ** and will insert the details into the CSM-Contract header table */
478 PROCEDURE INSERT_CONTRACT_HEADER
479 ( p_incident_id IN NUMBER
480 , p_user_id IN NUMBER
481 )
482 IS
483 CURSOR l_csm_contract_line_details (b_incident_id NUMBER )
484 IS
485 SELECT cs.CONTRACT_SERVICE_ID
486 , h.contract_number
487 , s.name
488 , s.description
489 , l.currency_code
490 FROM okc_k_headers_all_b h
491 , okc_k_lines_b l
492 , Okx_System_Items_V s
493 , Okc_K_Items IT
494 , CS_INCIDENTS_ALL_B cs
495 WHERE cs.INCIDENT_ID = b_incident_id
496 AND cs.CONTRACT_SERVICE_ID = l.id
497 AND h.id = l.dnz_chr_id
498 AND l.id = it.CLE_ID
499 AND s.Id1 = to_number(IT.Object1_Id1)
500 AND s.Id2 = to_number(IT.Object1_Id2);
501
502 CURSOR l_access_id_csr(p_incident_id IN number, p_user_id IN number)
503 IS
504 SELECT acc.access_id, acc.counter
505 FROM csm_contr_headers_acc acc
506 WHERE incident_id = p_incident_id
507 AND user_id = p_user_id;
508
509 r_csm_contract_line_details l_csm_contract_line_details%ROWTYPE;
510 l_sql VARCHAR2(2000);
511 l_sqlerrno varchar2(20);
512 l_sqlerrmsg varchar2(2000);
513 l_access_id number;
514 l_counter number;
515
516 BEGIN
517 CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER'
518 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_PROCEDURE);
519
520 OPEN l_csm_contract_line_details(p_incident_id);
521 FETCH l_csm_contract_line_details INTO r_csm_contract_line_details;
522 IF l_csm_contract_line_details%NOTFOUND THEN
523 CSM_UTIL_PKG.LOG( 'No data found in CURSOR Contract Line for INCIDENT_ID: ' || p_incident_id
524 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_EXCEPTION);
525 ELSE
526 CSM_UTIL_PKG.LOG( 'Details of Contract Line for INCIDENT_ID: ' || p_incident_id
527 || ', CONTRACT_SERVICE_ID: ' || r_csm_contract_line_details.contract_service_id
528 || ', CONTRACT_NUMBER: ' || r_csm_contract_line_details.contract_number
529 || ', SERVICE_NAME: ' || r_csm_contract_line_details.name
530 || ', SERVICE_DESCRIPTION: ' || r_csm_contract_line_details.description
531 || ', AMOUNT_UOM_CODE: ' || r_csm_contract_line_details.currency_code
532 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER'
533 , FND_LOG.LEVEL_STATEMENT);
534
535 INSERT_CONTRACT_BUSS_PROCESSES
536 ( p_contract_service_id => r_csm_contract_line_details.contract_service_id
537 , p_user_id => p_user_id
538 );
539
540 CSM_UTIL_PKG.LOG( 'Inserting ACC record for user_id = ' || p_user_id
541 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_STATEMENT);
542
543 CSM_ACC_PKG.Insert_Acc ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
544 ,P_ACC_TABLE_NAME => g_acc_table_name1
545 ,P_SEQ_NAME => g_sequence_name1
546 ,P_PK1_NAME => g_pk1_name1
547 ,P_PK1_NUM_VALUE => p_incident_id
548 ,P_USER_ID => p_user_id
549 );
550
551
552 /* l_sql := ' UPDATE '|| g_acc_table_name1 ||
553 ' SET contract_service_id = ' || '''' || r_csm_contract_line_details.contract_service_id || '''' ||
554 ', contract_number = '|| '''' || r_csm_contract_line_details.contract_number || '''' ||
555 ', service_name = '|| '''' ||r_csm_contract_line_details.name ||'''' ||
556 ', service_description = '||'''' || r_csm_contract_line_details.description ||'''' ||
557 ', amount_uom_code = '|| '''' ||r_csm_contract_line_details.currency_code ||'''' ||
558 ' WHERE incident_id = '|| p_incident_id;
559
560 EXECUTE IMMEDIATE l_sql;
561 */
562 l_sql := ' UPDATE '|| g_acc_table_name1 ||
563 ' SET contract_service_id = :1' ||
564 ', contract_number = :2 '||
565 ', service_name = :3'||
566 ', service_description = :4'||
567 ', amount_uom_code = :5'||
568 ' WHERE incident_id = :6 ';
569
570 EXECUTE IMMEDIATE l_sql USING r_csm_contract_line_details.contract_service_id, r_csm_contract_line_details.contract_number,
571 r_csm_contract_line_details.name, r_csm_contract_line_details.description,
572 r_csm_contract_line_details.currency_code, p_incident_id;
573
574 OPEN l_access_id_csr(p_incident_id, p_user_id);
575 FETCH l_access_id_csr INTO l_access_id, l_counter;
576 CLOSE l_access_id_csr;
577
578 -- if counter > 1 then pass update to the Pub item as we are updating acc table
579 IF l_counter > 1 THEN
580 CSM_ACC_PKG.UPDATE_ACC (p_publication_item_names => g_publication_item_name1
581 ,p_acc_table_name => g_acc_table_name1
582 ,p_user_id => p_user_id
583 ,p_access_id => l_access_id
584 );
585 END IF;
586
587 END IF;
588 CLOSE l_csm_contract_line_details;
589
590 CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER'
591 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER' , FND_LOG.LEVEL_PROCEDURE);
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 IF l_csm_contract_line_details%ISOPEN THEN
596 CLOSE l_csm_contract_line_details;
597 END IF;
598 l_sqlerrno := to_char(SQLCODE);
599 l_sqlerrmsg := substr(SQLERRM, 1,2000);
600 CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER:' || l_sqlerrno || ':' || l_sqlerrmsg
601 , 'CSM_CONTRACT_EVENT_PKG.INSERT_CONTRACT_HEADER', FND_LOG.LEVEL_ERROR);
602 RAISE;
603 END INSERT_CONTRACT_HEADER;
604
605
606 /* Will delete all the sr-contract acc records for all mobile resources */
607 PROCEDURE DELETE_SR_CONTRACT_ACC
608 ( p_incident_id IN NUMBER
609 , p_user_id IN NUMBER
610 )
611 IS
612 l_sqlerrno varchar2(20);
613 l_sqlerrmsg varchar2(2000);
614
615 CURSOR l_contr_headers_csr(p_incidentid IN number,
616 p_userid IN number)
617 IS
618 SELECT incident_id, contract_service_id
619 FROM CSM_CONTR_HEADERS_ACC
620 WHERE user_id = p_user_id
621 AND incident_id = p_incidentid;
622
623 CURSOR l_contr_buss_processes_csr(p_contractserviceid IN VARCHAR2,
624 p_userid IN number)
625 IS
626 SELECT contract_service_id, business_process_id
627 FROM CSM_CONTR_BUSS_PROCESSES_ACC
628 WHERE user_id = p_userid
629 AND contract_service_id = p_contractserviceid;
630
631 CURSOR l_contr_buss_txn_types_csr(p_contractserviceid IN varchar2,
632 p_businessprocessid IN number,
633 p_userid IN number)
634 IS
635 SELECT contract_service_id, business_process_id, txn_billing_type_id
636 FROM csm_contr_buss_txn_types_acc
637 WHERE user_id = p_userid
638 AND contract_service_id = p_contractserviceid
639 AND business_process_id = p_businessprocessid;
640
641 BEGIN
642 CSM_UTIL_PKG.LOG( 'Entering CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC'
643 , 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_PROCEDURE);
644
645 FOR r_contr_headers_rec IN l_contr_headers_csr(p_incident_id, p_user_id) LOOP
646
647 FOR r_contr_buss_processes_rec IN l_contr_buss_processes_csr(r_contr_headers_rec.contract_service_id, p_user_id) LOOP
648
649 FOR r_contr_buss_txn_types_rec IN l_contr_buss_txn_types_csr(r_contr_buss_processes_rec.contract_service_id,
650 r_contr_buss_processes_rec.business_process_id,
651 p_user_id) LOOP
652 CSM_UTIL_PKG.LOG( 'Deleting Buss Txn type ACC record for user_id = ' || p_user_id ||
653 ', contract_service_id = ' || r_contr_buss_txn_types_rec.contract_service_id ||
654 ', business_process_id = ' || r_contr_buss_txn_types_rec.business_process_id ||
655 ', txn_billing_type_id = ' || r_contr_buss_txn_types_rec.txn_billing_type_id
656 , 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_STATEMENT);
657
658 CSM_ACC_PKG.Delete_Acc
659 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
660 ,P_ACC_TABLE_NAME => g_acc_table_name3
661 ,P_PK1_NAME => g_pk1_name3
662 ,P_PK1_CHAR_VALUE => r_contr_buss_txn_types_rec.contract_service_id
663 ,P_PK2_NAME => g_pk2_name3
664 ,P_PK2_NUM_VALUE => r_contr_buss_txn_types_rec.business_process_id
665 ,P_PK3_NAME => g_pk3_name3
666 ,P_PK3_NUM_VALUE => r_contr_buss_txn_types_rec.txn_billing_type_id
667 ,P_USER_ID => p_user_id
668 );
669 END LOOP;
670
671 CSM_UTIL_PKG.LOG( 'Deleting Buss proc ACC record for user_id = ' || p_user_id ||
672 ', contract_service_id = ' || r_contr_buss_processes_rec.contract_service_id ||
673 ', business_process_id = ' || r_contr_buss_processes_rec.business_process_id
674 , 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_STATEMENT);
675
676 CSM_ACC_PKG.Delete_Acc
677 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
678 ,P_ACC_TABLE_NAME => g_acc_table_name2
679 ,P_PK1_NAME => g_pk1_name2
680 ,P_PK1_CHAR_VALUE => r_contr_buss_processes_rec.contract_service_id
681 ,P_PK2_NAME => g_pk2_name2
682 ,P_PK2_NUM_VALUE => r_contr_buss_processes_rec.business_process_id
683 ,P_USER_ID => p_user_id
684 );
685
686 DELETE_COV_ACTION_TIMES
687 ( p_user_id => p_user_id,
688 p_contract_service_id => r_contr_buss_processes_rec.contract_service_id,
689 p_business_proc_id => r_contr_buss_processes_rec.business_process_id
690 );
691 END LOOP;
692
693 CSM_UTIL_PKG.LOG( 'Deleting Contract Header ACC record for user_id = ' || p_user_id ||
694 ', incident_id = ' || p_incident_id
695 , 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_STATEMENT);
696
697 CSM_ACC_PKG.Delete_Acc
698 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
699 ,P_ACC_TABLE_NAME => g_acc_table_name1
700 ,P_PK1_NAME => g_pk1_name1
701 ,P_PK1_NUM_VALUE => r_contr_headers_rec.incident_id
702 ,P_USER_ID => p_user_id
703 );
704
705 END LOOP;
706
707 CSM_UTIL_PKG.LOG( 'Leaving CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC'
708 , 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_PROCEDURE);
709
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 l_sqlerrno := to_char(SQLCODE);
714 l_sqlerrmsg := substr(SQLERRM, 1,2000);
715 CSM_UTIL_PKG.LOG( 'Exception occured in CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC:' || l_sqlerrno || ':' || l_sqlerrmsg
716 , 'CSM_CONTRACT_EVENT_PKG.DELETE_SR_CONTRACT_ACC', FND_LOG.LEVEL_ERROR);
717 RAISE;
718
719 END DELETE_SR_CONTRACT_ACC;
720
721 PROCEDURE DELETE_OKS_NOTES_ACC(p_contract_service_id IN varchar2,
722 p_user_id IN number)
723 IS
724 l_markdirty BOOLEAN;
725 l_dmllist asg_download.dml_list;
726 l_dml varchar2(1);
727 l_timestamp DATE;
728 l_accesslist asg_download.access_list;
729 l_null_accesslist asg_download.access_list;
730 l_resourcelist asg_download.user_list;
731 l_null_resourcelist asg_download.user_list;
732 l_user_id NUMBER;
733 l_sourceobjectid NUMBER;
734 l_sourceobjectcode VARCHAR2(240);
735 l_publicationitemname VARCHAR2(30);
736 l_access_count NUMBER;
737 l_pkvalueslist asg_download.pk_list;
738 l_notes_found boolean;
739
740 CURSOR l_oks_notes_csr (p_sourceobjectcode VARCHAR2,
741 p_sourceobjectid NUMBER,
742 p_user_id NUMBER) IS
743 SELECT acc.jtf_note_id, acc.user_id
744 FROM jtf_notes_b notes, csm_notes_acc acc
745 WHERE notes.source_object_code = p_sourceobjectcode
746 AND notes.source_object_id = p_sourceobjectid
747 AND notes.jtf_note_id = acc.jtf_note_id
748 AND acc.user_id = p_user_id;
749
750 BEGIN
751 l_sourceobjectcode := 'OKS_COV_NOTE';
752 l_sourceobjectid := p_contract_service_id;
753 l_user_id := p_user_id;
754
755 --delete for the user
756 for l_oks_notes_rec in l_oks_notes_csr(l_sourceobjectcode,
757 l_sourceobjectid,
758 l_user_id) loop
759 CSM_ACC_PKG.Delete_Acc
760 ( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
761 ,P_ACC_TABLE_NAME => g_notes_acc_table_name
762 ,P_PK1_NAME => g_notes_pk1_name
763 ,P_PK1_NUM_VALUE => l_oks_notes_rec.jtf_note_id
764 ,P_USER_ID => l_oks_notes_rec.user_id
765 );
766
767 end loop;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 CSM_UTIL_PKG.LOG( sqlerrm|| ' for Contract_Service_Id ' || to_char(l_sourceobjectid),
772 'CSM_CONTRACT_EVENT_PKG.DELETE_OKS_NOTES_ACC',FND_LOG.LEVEL_EXCEPTION);
773 RAISE;
774
775 END DELETE_OKS_NOTES_ACC;
776
777 PROCEDURE INSERT_OKS_NOTES_ACC(p_contract_service_id IN VARCHAR2,
778 p_user_id IN number)
779 IS
780 l_markdirty BOOLEAN;
781 l_dmllist asg_download.dml_list;
782 l_dml varchar2(1);
783 l_timestamp DATE;
784 l_accesslist asg_download.access_list;
785 l_resourcelist asg_download.user_list;
786 l_user_id NUMBER;
787 l_sourceobjectid NUMBER;
788 l_sourceobjectcode VARCHAR2(240);
789 l_publicationitemname VARCHAR2(30);
790 l_access_count NUMBER;
791 l_notes_found boolean;
792
793 CURSOR l_oks_notes_csr (p_sourceobjectcode VARCHAR2,
794 p_sourceobjectid NUMBER) IS
795 SELECT jtf_note_id
796 FROM jtf_notes_b
797 WHERE source_object_code = p_sourceobjectcode
798 AND source_object_id = p_sourceobjectid;
799
800 BEGIN
801 l_sourceobjectcode := 'OKS_COV_NOTE';
802 l_sourceobjectid := p_contract_service_id;
803 l_user_id := p_user_id;
804
805 --delete for the user
806 for l_oks_notes_rec in l_oks_notes_csr(l_sourceobjectcode,
807 l_sourceobjectid) loop
808
809 CSM_ACC_PKG.Insert_Acc
810 ( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
811 ,P_ACC_TABLE_NAME => g_notes_acc_table_name
812 ,P_SEQ_NAME => g_notes_seq_name
813 ,P_PK1_NAME => g_notes_pk1_name
814 ,P_PK1_NUM_VALUE => l_oks_notes_rec.jtf_note_id
815 ,P_USER_ID => l_user_id
816 );
817
818 end loop;
819
820 EXCEPTION WHEN OTHERS THEN
821 CSM_UTIL_PKG.LOG( sqlerrm|| ' for Contract_Service_Id ' || to_char(l_sourceobjectid),
822 'CSM_CONTRACT_EVENT_PKG.INSERT_OKS_NOTES_ACC',FND_LOG.LEVEL_EXCEPTION);
823 RAISE;
824
825 END INSERT_OKS_NOTES_ACC;
826
827 PROCEDURE SR_CONTRACT_ACC_I (p_incident_id IN NUMBER, p_user_id IN NUMBER)
828 IS
829 l_sqlerrno VARCHAR2(20);
830 l_sqlerrmsg VARCHAR2(4000);
831 l_error_msg VARCHAR2(4000);
832 l_return_status VARCHAR2(2000);
833
834 BEGIN
835 CSM_UTIL_PKG.LOG('Entering SR_CONTRACT_ACC_I for incident_id: ' || p_incident_id,
836 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_I',FND_LOG.LEVEL_PROCEDURE);
837
838 INSERT_CONTRACT_HEADER( p_incident_id => p_incident_id
839 , p_user_id => p_user_id);
840
841 CSM_UTIL_PKG.LOG('Leaving SR_CONTRACT_ACC_I for incident_id: ' || p_incident_id,
842 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_I',FND_LOG.LEVEL_PROCEDURE);
843 EXCEPTION
844 WHEN OTHERS THEN
845 l_sqlerrno := to_char(SQLCODE);
846 l_sqlerrmsg := substr(SQLERRM, 1,2000);
847 l_error_msg := ' Exception in SR_CONTRACT_ACC_I for incident_id:'
848 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
849 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_I',FND_LOG.LEVEL_EXCEPTION);
850 RAISE;
851 END SR_CONTRACT_ACC_I;
852
853 PROCEDURE SR_CONTRACT_ACC_D (p_incident_id IN NUMBER, p_user_id IN NUMBER)
854 IS
855 l_sqlerrno VARCHAR2(20);
856 l_sqlerrmsg VARCHAR2(4000);
857 l_error_msg VARCHAR2(4000);
858 l_return_status VARCHAR2(2000);
859
860 BEGIN
861 CSM_UTIL_PKG.LOG('Entering SR_CONTRACT_ACC_D for incident_id: ' || p_incident_id,
862 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_D',FND_LOG.LEVEL_PROCEDURE);
863
864 DELETE_SR_CONTRACT_ACC
865 ( p_incident_id => p_incident_id
866 , p_user_id => p_user_id
867 );
868
869 CSM_UTIL_PKG.LOG('Leaving SR_CONTRACT_ACC_D for incident_id: ' || p_incident_id,
870 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_D',FND_LOG.LEVEL_PROCEDURE);
871 EXCEPTION
872 WHEN OTHERS THEN
873 l_sqlerrno := to_char(SQLCODE);
874 l_sqlerrmsg := substr(SQLERRM, 1,2000);
875 l_error_msg := ' Exception in SR_CONTRACT_ACC_D for incident_id:'
876 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
877 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_D',FND_LOG.LEVEL_EXCEPTION);
878 RAISE;
879 END SR_CONTRACT_ACC_D;
880
881 PROCEDURE SR_CONTRACT_ACC_U (p_incident_id IN NUMBER, p_old_contract_service_id IN NUMBER,
882 p_contract_service_id IN NUMBER, p_user_id IN NUMBER)
883 IS
884 l_sqlerrno VARCHAR2(20);
885 l_sqlerrmsg VARCHAR2(4000);
886 l_error_msg VARCHAR2(4000);
887 l_return_status VARCHAR2(2000);
888
889 BEGIN
890 CSM_UTIL_PKG.LOG('Entering SR_CONTRACT_ACC_U for incident_id: ' || p_incident_id,
891 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_U',FND_LOG.LEVEL_PROCEDURE);
892
893 IF p_old_contract_service_id IS NOT NULL THEN
894 /* The contract service id is updated for the SR **
895 ** the ACC records has to be deleted for this SR-contract records */
896 DELETE_SR_CONTRACT_ACC
897 ( p_incident_id => p_incident_id
898 , p_user_id => p_user_id
899 );
900
901 -- delete contract notes
902 DELETE_OKS_NOTES_ACC(p_contract_service_id => p_old_contract_service_id,
903 p_user_id => p_user_id);
904 END IF;
905
906
907 IF p_contract_service_id IS NOT NULL THEN
908 /* The contract service id is updated for the SR **
909 ** the ACC records has to be created for this SR-contract records */
910 INSERT_CONTRACT_HEADER( p_incident_id => p_incident_id
911 , p_user_id => p_user_id);
912
913 -- insert contract notes
914 INSERT_OKS_NOTES_ACC(p_contract_service_id => p_contract_service_id,
915 p_user_id => p_user_id);
916
917 END IF;
918
919 CSM_UTIL_PKG.LOG('Leaving SR_CONTRACT_ACC_U for incident_id: ' || p_incident_id,
920 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_U',FND_LOG.LEVEL_PROCEDURE);
921 EXCEPTION
922 WHEN OTHERS THEN
923 l_sqlerrno := to_char(SQLCODE);
924 l_sqlerrmsg := substr(SQLERRM, 1,2000);
925 l_error_msg := ' Exception in SR_CONTRACT_ACC_U for incident_id:'
926 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
927 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_CONTRACT_EVENT_PKG.SR_CONTRACT_ACC_U',FND_LOG.LEVEL_EXCEPTION);
928 RAISE;
929 END SR_CONTRACT_ACC_U;
930
931 END CSM_CONTRACT_EVENT_PKG;