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