[Home] [Help]
PACKAGE BODY: APPS.CSL_CONTRACT_HANDLING_PKG
Source
1 PACKAGE BODY CSL_CONTRACT_HANDLING_PKG AS
2 /* $Header: cslctrhb.pls 120.0 2005/05/25 11:06:41 appldev noship $ */
3
4 /*** Globals ***/
5
6 -- CSL_SR_CONTRACT_HEADERS
7 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSL_SR_CONTRACT_HEADERS_ACC';
8 g_publication_item_name1 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
9 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_SR_CONTRACT_HEADERS');
10 g_table_name1 CONSTANT VARCHAR2(30) := 'CSL_SR_CONTRACT_HEADERS';
11 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
12
13 -- CSL_CONTR_BUSS_PROCESSES
14 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSL_CONTR_BUSS_PROCESSES_ACC';
15 g_publication_item_name2 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
16 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CONTR_BUSS_PROCESSES');
17 g_table_name2 CONSTANT VARCHAR2(30) := 'CSL_CONTR_BUSS_PROCESSES';
18 g_pk1_name2 CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
19 g_pk2_name2 CONSTANT VARCHAR2(30) := 'BUSINESS_PROCESS_ID';
20
21 -- CSL_CONTR_BUSS_TXN_TYPES
22 g_acc_table_name3 CONSTANT VARCHAR2(30) := 'CSL_CONTR_BUSS_TXN_TYPES_ACC';
23 g_publication_item_name3 CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
24 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_CONTR_BUSS_TXN_TYPES');
25 g_table_name3 CONSTANT VARCHAR2(30) := 'CSL_CONTR_BUSS_TXN_TYPES';
26 g_pk1_name3 CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
27 g_pk2_name3 CONSTANT VARCHAR2(30) := 'TXN_BILLING_TYPE_ID';
28 g_pk3_name3 CONSTANT VARCHAR2(30) := 'BUSINESS_PROCESS_ID';
29
30
31 g_debug_level NUMBER; -- debug level
32
33 FUNCTION INSERT_TXN_TYPES_RECORD
34 ( p_incident_id IN NUMBER
35 , p_txn_billing_type_id IN NUMBER
36 , p_cov_txn_grp_line_id IN NUMBER
37 , p_bp_id IN NUMBER
38 , p_up_to_amount IN VARCHAR2
39 , p_percent_covered IN VARCHAR2
40 )
41 RETURN BOOLEAN
42 IS
43 BEGIN
44
45 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
46 jtm_message_log_pkg.Log_Msg
47 ( p_incident_id
48 , g_table_name1
49 , 'Entering INSERT_TXN_TYPES_RECORD'
50 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
51 , 'csl_contract_handling_pkg');
52 END IF;
53
54 INSERT INTO CSL_CONTR_BUSS_TXN_TYPES
55 ( INCIDENT_ID
56 , TXN_BILLING_TYPE_ID
57 , BUSINESS_PROCESS_ID
58 , CONTRACT_SERVICE_ID
59 , UP_TO_AMOUNT
60 , PERCENT_COVERED
61 , LAST_UPDATE_DATE
62 , LAST_UPDATED_BY
63 , CREATION_DATE
64 , CREATED_BY
65 )
66 VALUES
67 ( p_incident_id
68 , p_txn_billing_type_id
69 , p_bp_id
70 , p_cov_txn_grp_line_id
71 , p_up_to_amount
72 , p_percent_covered
73 , sysdate
74 , 1
75 , sysdate
76 , 1
77 );
78
79 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
80 jtm_message_log_pkg.Log_Msg
81 ( p_incident_id
82 , g_table_name1
83 , 'Leaving INSERT_TXN_TYPES_RECORD'
84 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
85 , 'csl_contract_handling_pkg');
86 END IF;
87
88 RETURN TRUE;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
93 jtm_message_log_pkg.Log_Msg
94 ( p_incident_id
95 , g_table_name1
96 , 'Exception occured in INSERT_TXN_TYPES_RECORD'
97 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
98 , 'csl_contract_handling_pkg');
99 END IF;
100 RETURN FALSE;
101 END INSERT_TXN_TYPES_RECORD;
102
103
104 /* Function will get the Txn types and the related settings of it **
105 ** - Up to amount **
106 ** - Percent covered **
107 ** and will insert these into the CSL-Contract txn types table */
108 FUNCTION INSERT_CONTRACT_TXN_TYPES
109 ( p_incident_id IN NUMBER
110 , p_cov_txn_grp_line_id IN NUMBER
111 , p_business_process_id IN NUMBER
112 , p_resource_id IN NUMBER
113 )
114 RETURN NUMBER
115 IS
116
117 --Fix for Bug #3478401
118 CURSOR c_csl_contr_txn_types ( b_incident_id NUMBER
119 , b_txn_bill_type_id NUMBER
120 , b_bp_id NUMBER
121 , b_cov_txn_grp_line_id NUMBER) IS
122 SELECT null
123 FROM CSL_CONTR_BUSS_TXN_TYPES
124 WHERE INCIDENT_ID = b_incident_id
125 AND TXN_BILLING_TYPE_ID = b_txn_bill_type_id
126 AND BUSINESS_PROCESS_ID = b_bp_id
127 AND CONTRACT_SERVICE_ID = b_cov_txn_grp_line_id;
128
129 r_csl_contr_txn_types c_csl_contr_txn_types%ROWTYPE;
130
131 l_oks_out_tbl_bt OKS_ENTITLEMENTS_PUB.OUTPUT_TBL_BT;
132 l_oks_out_tbl_br OKS_ENTITLEMENTS_PUB.OUTPUT_TBL_BR;
133
134 l_effected_records NUMBER := 0;
135 l_success BOOLEAN;
136
137 p_init_msg_list VARCHAR2(4000);
138 x_return_status VARCHAR2(1);
139 x_msg_count NUMBER;
140 x_msg_data VARCHAR2(4000);
141 BEGIN
142
143 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
144 jtm_message_log_pkg.Log_Msg
145 ( p_incident_id
146 , g_table_name1
147 , 'Entering INSERT_CONTRACT_TXN_TYPES'
148 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
149 , 'csl_contract_handling_pkg');
150 END IF;
151
152 /* This call to the Contracts API will return the Txn billing types in table
153 ** variable: l_oks_out_tbl_bt
154 ** This table should contain the following information:
155 ** Txn_BT_line_id (NUMBER): Txn billing type id
156 ** txn_billing_type_id (NUMBER): Business process if
157 ** Covered_upto_amount (NUMBER): Upto amount covered
158 ** percent_covered (NUMBER): percent_covered
159 */
160 OKS_ENTITLEMENTS_PUB.Get_txn_billing_types
161 ( p_api_version => 1.0
162 , p_init_msg_list => p_init_msg_list
163 , p_cov_txngrp_line_id=> p_cov_txn_grp_line_id
164 , p_return_bill_rates_YN => 'N' -- Not interested on the billing rates
165 , x_return_status => x_return_status
166 , x_msg_count => x_msg_count
167 , x_msg_data => x_msg_data
168 , x_txn_bill_types => l_oks_out_tbl_bt
169 , x_txn_bill_rates => l_oks_out_tbl_br -- Should return null
170 );
171 IF l_oks_out_tbl_bt.COUNT > 0 THEN
172 /* Looping over the table if table contain any record(s) */
173 FOR i IN l_oks_out_tbl_bt.FIRST .. l_oks_out_tbl_bt.LAST LOOP
174
175 --Modified to fix Bug #3478401
176 OPEN c_csl_contr_txn_types
177 ( p_incident_id
178 , l_oks_out_tbl_bt(i).txn_bill_type_id
179 , p_business_process_id
180 , p_cov_txn_grp_line_id
181 );
182
183 FETCH c_csl_contr_txn_types INTO r_csl_contr_txn_types;
184 IF c_csl_contr_txn_types%NOTFOUND THEN
185 /* Insert the new Txn bill type record into the CSL contract table */
186 l_success := INSERT_TXN_TYPES_RECORD
187 ( p_incident_id => p_incident_id
188 , p_txn_billing_type_id => l_oks_out_tbl_bt(i).txn_bill_type_id
189 , p_cov_txn_grp_line_id => p_cov_txn_grp_line_id
190 , p_bp_id => p_business_process_id
191 , p_up_to_amount => l_oks_out_tbl_bt(i).Covered_upto_amount
192 , p_percent_covered => l_oks_out_tbl_bt(i).percent_covered
193 );
194 ELSE
195 l_success := TRUE;
196 END IF;
197 CLOSE c_csl_contr_txn_types;
198
199 IF l_success THEN
200 -- Push record to the Resource (insert in ACC)
201 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
202 jtm_message_log_pkg.Log_Msg
203 ( l_oks_out_tbl_bt(i).txn_bt_line_id
204 , g_table_name3
205 , 'Inserting ACC record for resource_id = ' || p_resource_id
206 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
207 , 'csl_contract_handling_pkg');
208 END IF;
209
210 JTM_HOOK_UTIL_PKG.Insert_Acc
211 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
212 ,P_ACC_TABLE_NAME => g_acc_table_name3
213 ,P_RESOURCE_ID => p_resource_id
214 ,P_PK1_NAME => g_pk1_name3
215 ,P_PK1_NUM_VALUE => p_incident_id
216 ,P_PK2_NAME => g_pk2_name3
217 ,P_PK2_NUM_VALUE => l_oks_out_tbl_bt(i).txn_bill_type_id
218 ,P_PK3_NAME => g_pk3_name3
219 ,P_PK3_NUM_VALUE => p_business_process_id
220 );
221
222 /* increase effected records if a new record has been created */
223 l_effected_records := l_effected_records + 1;
224 END IF;
225
226 END LOOP;
227 END IF;
228
229 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
230 jtm_message_log_pkg.Log_Msg
231 ( p_incident_id
232 , g_table_name1
233 , 'Leaving INSERT_CONTRACT_TXN_TYPES'
234 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
235 , 'csl_contract_handling_pkg');
236 END IF;
237
238 RETURN l_effected_records;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
243 jtm_message_log_pkg.Log_Msg
244 ( p_incident_id
245 , g_table_name1
246 , 'Exception occured in INSERT_CONTRACT_TXN_TYPES'
247 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
248 , 'csl_contract_handling_pkg');
249 END IF;
250 RETURN l_effected_records;
251 END INSERT_CONTRACT_TXN_TYPES;
252
253 FUNCTION INSERT_BUSS_PROCESSES_RECORD
254 ( p_incident_id IN NUMBER
255 , p_cov_txn_grp_line_id IN NUMBER
256 , p_bp_id IN NUMBER
257 , p_start_date IN DATE
258 , p_end_date IN DATE
259 )
260 RETURN BOOLEAN
261 IS
262 BEGIN
263
264 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
265 jtm_message_log_pkg.Log_Msg
266 ( p_incident_id
267 , g_table_name1
268 , 'Entering INSERT_BUSS_PROCESSES_RECORD'
269 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
270 , 'csl_contract_handling_pkg');
271 END IF;
272
273 INSERT INTO CSL_CONTR_BUSS_PROCESSES
274 ( INCIDENT_ID
275 , BUSINESS_PROCESS_ID
276 , CONTRACT_SERVICE_ID
277 , START_DATE
278 , END_DATE
279 , LAST_UPDATE_DATE
280 , LAST_UPDATED_BY
281 , CREATION_DATE
282 , CREATED_BY
283 )
284 VALUES
285 ( p_incident_id
286 , p_bp_id
287 , p_cov_txn_grp_line_id
288 , p_start_date
289 , p_end_date
290 , sysdate
291 , 1
292 , sysdate
293 , 1
294 );
295
296 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
297 jtm_message_log_pkg.Log_Msg
298 ( p_incident_id
299 , g_table_name1
300 , 'Leaving INSERT_BUSS_PROCESSES_RECORD'
301 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
302 , 'csl_contract_handling_pkg');
303 END IF;
304
305 RETURN TRUE;
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
310 jtm_message_log_pkg.Log_Msg
311 ( p_incident_id
312 , g_table_name1
313 , 'Exception occured in INSERT_BUSS_PROCESSES_RECORD'
314 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
315 , 'csl_contract_handling_pkg');
316 END IF;
317 RETURN FALSE;
318 END INSERT_BUSS_PROCESSES_RECORD;
319
320 /* Function will get the field service related buss_processes of **
321 ** the Contract Line and will insert the details of these buss_processes **
322 ** into the CSL-Contract buss process table */
323 FUNCTION INSERT_CONTRACT_BUSS_PROCESSES
324 ( p_incident_id IN NUMBER
325 , p_contract_service_id IN NUMBER
326 , p_resource_id IN NUMBER
327 )
328 RETURN NUMBER
329 IS
330 CURSOR c_csl_contr_bus_proc (b_incident_id NUMBER, b_cov_txn_grp_line_id NUMBER, b_bp_id NUMBER) IS
331 SELECT null
332 FROM CSL_CONTR_BUSS_PROCESSES
333 WHERE INCIDENT_ID = b_incident_id
334 AND BUSINESS_PROCESS_ID = b_bp_id
335 AND CONTRACT_SERVICE_ID = b_cov_txn_grp_line_id;
336
337 r_csl_contr_bus_proc c_csl_contr_bus_proc%ROWTYPE;
338
339 l_oks_in_rec_bp OKS_ENTITLEMENTS_PUB.INP_REC_BP;
340 l_oks_out_tbl_bp OKS_ENTITLEMENTS_PUB.OUTPUT_TBL_BP;
341
342 l_effected_records NUMBER := 0;
343 l_rec NUMBER := 0;
344 l_success BOOLEAN;
345
346 p_init_msg_list VARCHAR2(4000);
347 x_return_status VARCHAR2(1);
348 x_msg_count NUMBER;
349 x_msg_data VARCHAR2(4000);
350 BEGIN
351
352 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
353 jtm_message_log_pkg.Log_Msg
354 ( p_incident_id
355 , g_table_name1
356 , 'Entering INSERT_CONTRACT_BUSS_PROCESSES'
357 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
358 , 'csl_contract_handling_pkg');
359 END IF;
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 */
365 l_oks_in_rec_bp.contract_line_id := p_contract_service_id;
366 /* Y: API will check the business process definition */
367 l_oks_in_rec_bp.check_bp_def := 'Y';
368 /* Y: Validate with Service Request enabled flag of the bus-proc */
369 l_oks_in_rec_bp.sr_enabled := 'Y';
370 /* N: Don't validate with Depot Repair enabled flag of the bus-proc */
371 /* BUG 2641172: Pass NULL i.o. 'N' */
372 /* l_oks_in_rec_bp.dr_enabled := 'N'; */
373 /* Y: Validate with Field Service enabled flag of the bus-proc */
374 l_oks_in_rec_bp.fs_enabled := 'Y';
375
376
377 /* This call to the Contracts API will return the business processes in table
378 ** variable: l_oks_out_tbl_bp
379 ** This table should contain the following information:
380 ** cov_txn_grp_line_id (NUMBER): Contract line id for business process
381 ** bp_id (NUMBER): Business Process ID
382 ** start_date (DATE) : Start Date for business process
383 ** end_date (DATE) : End Date for business process */
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 IF l_oks_out_tbl_bp.COUNT > 0 THEN
394 /* Looping over the table if table contain any record(s) */
395 FOR i IN l_oks_out_tbl_bp.FIRST .. l_oks_out_tbl_bp.LAST LOOP
396
397 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
398 jtm_message_log_pkg.Log_Msg
399 ( p_incident_id
400 , g_table_name1
401 , 'Processing business_process_id ' || l_oks_out_tbl_bp(i).bp_id
402 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
403 , 'csl_contract_handling_pkg');
404 END IF;
405
406 OPEN c_csl_contr_bus_proc
407 ( p_incident_id
408 , l_oks_out_tbl_bp(i).cov_txn_grp_line_id
409 , l_oks_out_tbl_bp(i).bp_id
410 );
411 FETCH c_csl_contr_bus_proc INTO r_csl_contr_bus_proc;
412 IF c_csl_contr_bus_proc%NOTFOUND THEN
413 /* Insert the new bus proc record into the CSL contract table */
414 l_success := INSERT_BUSS_PROCESSES_RECORD
415 ( p_incident_id => p_incident_id
416 , p_cov_txn_grp_line_id => l_oks_out_tbl_bp(i).cov_txn_grp_line_id
417 , p_bp_id => l_oks_out_tbl_bp(i).bp_id
418 , p_start_date => l_oks_out_tbl_bp(i).start_date
419 , p_end_date => l_oks_out_tbl_bp(i).end_date
420 );
421 ELSE
422 l_success := TRUE;
423 END IF;
424 CLOSE c_csl_contr_bus_proc;
425
426 IF l_success THEN
427
428 /* Insert all the Txn Billing Types for the Bus-proc */
429 l_rec := INSERT_CONTRACT_TXN_TYPES
430 ( p_incident_id => p_incident_id
431 , p_cov_txn_grp_line_id => l_oks_out_tbl_bp(i).cov_txn_grp_line_id
432 , p_business_process_id => l_oks_out_tbl_bp(i).bp_id
433 , p_resource_id => p_resource_id
434 );
435
436 -- Push record to the Resource (insert in ACC)
437 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
438 jtm_message_log_pkg.Log_Msg
439 ( l_oks_out_tbl_bp(i).bp_id
440 , g_table_name2
441 , 'Inserting ACC record for resource_id = ' || p_resource_id
442 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
443 , 'csl_contract_handling_pkg');
444 END IF;
445
446 JTM_HOOK_UTIL_PKG.Insert_Acc
447 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
448 ,P_ACC_TABLE_NAME => g_acc_table_name2
449 ,P_RESOURCE_ID => p_resource_id
450 ,P_PK1_NAME => g_pk1_name2
451 ,P_PK1_NUM_VALUE => p_incident_id
452 ,P_PK2_NAME => g_pk2_name2
453 ,P_PK2_NUM_VALUE => l_oks_out_tbl_bp(i).bp_id
454 );
455
456 /* increase effected records if a new record has been created */
457 l_effected_records := l_effected_records + 1;
458 END IF;
459
460 END LOOP;
461 END IF;
462
463 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
464 jtm_message_log_pkg.Log_Msg
465 ( p_incident_id
466 , g_table_name1
467 , 'Leaving INSERT_CONTRACT_BUSS_PROCESSES'
468 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
469 , 'csl_contract_handling_pkg');
470 END IF;
471
472 RETURN l_effected_records;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
477 jtm_message_log_pkg.Log_Msg
478 ( p_incident_id
479 , g_table_name1
480 , 'Exception occured in INSERT_CONTRACT_BUSS_PROCESSES'
481 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
482 , 'csl_contract_handling_pkg');
483 END IF;
484 RETURN l_effected_records;
485 END INSERT_CONTRACT_BUSS_PROCESSES;
486
487 FUNCTION INSERT_CONTRACT_HEADER_RECORD
488 ( p_incident_id IN NUMBER
489 , p_cov_txn_grp_line_id IN NUMBER
490 , p_contract_number IN VARCHAR2
491 , p_service_name IN VARCHAR2
492 , p_service_description IN VARCHAR2
493 , p_amount_uom_code IN VARCHAR2
494 )
495 RETURN BOOLEAN
496 IS
497 BEGIN
498
499 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
500 jtm_message_log_pkg.Log_Msg
501 ( p_incident_id
502 , g_table_name1
503 , 'Entering INSERT_CONTRACT_HEADER_RECORD'
504 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
505 , 'csl_contract_handling_pkg');
506 END IF;
507
508 INSERT INTO CSL_SR_CONTRACT_HEADERS
509 ( INCIDENT_ID
510 , CONTRACT_SERVICE_ID
511 , CONTRACT_NUMBER
512 , SERVICE_NAME
513 , SERVICE_DESCRIPTION
514 , AMOUNT_UOM_CODE
515 , LAST_UPDATE_DATE
516 , LAST_UPDATED_BY
517 , CREATION_DATE
518 , CREATED_BY
519 )
520 VALUES
521 ( p_incident_id
522 , p_cov_txn_grp_line_id
523 , p_contract_number
524 , p_service_name
525 , p_service_description
526 , p_amount_uom_code
527 , sysdate
528 , 1
529 , sysdate
530 , 1
531 );
532
533 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
534 jtm_message_log_pkg.Log_Msg
535 ( p_incident_id
536 , g_table_name1
537 , 'Leaving INSERT_CONTRACT_HEADER_RECORD'
538 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
539 , 'csl_contract_handling_pkg');
540 END IF;
541
542 RETURN TRUE;
543
544 EXCEPTION
545 WHEN OTHERS THEN
546 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
547 jtm_message_log_pkg.Log_Msg
548 ( p_incident_id
549 , g_table_name1
550 , 'Exception occured in INSERT_CONTRACT_HEADER_RECORD'
551 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
552 , 'csl_contract_handling_pkg');
553 END IF;
554 RETURN FALSE;
555 END INSERT_CONTRACT_HEADER_RECORD;
556
557 /* Function will get the details of the Contract Line **
558 ** and will insert the details into the CSL-Contract header table */
559 FUNCTION INSERT_CONTRACT_HEADER
560 ( p_incident_id IN NUMBER
561 , p_resource_id IN NUMBER
562 )
563 RETURN BOOLEAN
564 IS
565
566 --Added sts_code = 'ACTIVE' to fix bug #3484383
567 CURSOR c_csl_contract_line_details (b_incident_id NUMBER ) IS
568 select l.id
569 , h.contract_number
570 , s.name
571 , s.description
572 , l.currency_code
573 from okc_k_headers_b h
574 , okc_k_lines_b l
575 , Okx_System_Items_V s
576 , Okc_K_Items IT
577 , CS_INCIDENTS_ALL_B cs
578 where cs.INCIDENT_ID = b_incident_id
579 and cs.CONTRACT_SERVICE_ID = l.id
580 and h.id = l.dnz_chr_id
581 and l.id = it.CLE_ID
582 AND s.Id1 = to_number(IT.Object1_Id1)
583 AND s.Id2 = to_number(IT.Object1_Id2)
584 AND h.sts_code = 'ACTIVE';
585
586 CURSOR c_csl_contr_record (b_incident_id NUMBER) IS
587 SELECT NULL
588 FROM CSL_SR_CONTRACT_HEADERS
589 WHERE INCIDENT_ID = b_incident_id;
590
591 r_csl_contr_record c_csl_contr_record%ROWTYPE;
592
593 l_success BOOLEAN := FALSE;
594 l NUMBER;
595
596 l_contract_service_id NUMBER;
597 l_contract_nuber VARCHAR2(120);
598 l_service_name VARCHAR2(120);
599 l_service_description VARCHAR2(2000);
600 l_amount_uom_code VARCHAR2(30);
601 l_return BOOLEAN;
602
603 --Added by UTEKUMAL on 04-Mar-2004 to fix Bug #3478401
604 CURSOR c_csl_contract_service_id (b_incident_id NUMBER) IS
605 SELECT CONTRACT_SERVICE_ID
606 FROM CS_INCIDENTS_ALL_B CS
607 WHERE CS.incident_id = b_incident_id;
608
609 BEGIN
610
611 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
612 jtm_message_log_pkg.Log_Msg
613 ( p_incident_id
614 , g_table_name1
615 , 'Entering INSERT_CONTRACT_HEADER'
616 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
617 , 'csl_contract_handling_pkg');
618 END IF;
619
620 OPEN c_csl_contr_record(p_incident_id);
621 FETCH c_csl_contr_record INTO r_csl_contr_record;
622 IF c_csl_contr_record%NOTFOUND THEN
623
624 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
625 jtm_message_log_pkg.Log_Msg
626 ( p_incident_id
627 , g_table_name1
628 , 'Contract Line for INCIDENT_ID: ' || p_incident_id || ' does not exist in CSL_SR_CONTRACT_HEADERS'
629 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
630 , 'csl_contract_handling_pkg');
631
632 jtm_message_log_pkg.Log_Msg
633 ( p_incident_id
634 , g_table_name1
635 , 'Getting the details of the Contract Line for INCIDENT_ID: ' || p_incident_id
636 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
637 , 'csl_contract_handling_pkg');
638 END IF;
639
640 OPEN c_csl_contract_line_details(p_incident_id);
641 FETCH c_csl_contract_line_details INTO l_contract_service_id
642 , l_contract_nuber
643 , l_service_name
644 , l_service_description
645 , l_amount_uom_code;
646 IF c_csl_contract_line_details%NOTFOUND THEN
647 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
648 jtm_message_log_pkg.Log_Msg
649 ( p_incident_id
650 , g_table_name1
651 , 'No data found in CURSOR Contract Line for INCIDENT_ID: ' || p_incident_id
652 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
653 , 'csl_contract_handling_pkg');
654 END IF;
655 ELSE
656
657 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
658 jtm_message_log_pkg.Log_Msg
659 ( p_incident_id
660 , g_table_name1
661 , 'Details of Contract Line for INCIDENT_ID: ' || p_incident_id
662 || ', CONTRACT_SERVICE_ID: ' || l_contract_service_id
663 || ', CONTRACT_SERVICE_ID: ' || l_contract_nuber
664 || ', CONTRACT_SERVICE_ID: ' || l_service_name
665 || ', CONTRACT_SERVICE_ID: ' || l_service_description
666 || ', CONTRACT_SERVICE_ID: ' || l_amount_uom_code
667 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
668 , 'csl_contract_handling_pkg');
669 END IF;
670
671 /* Insert the new contract line record into the CSL contract table */
672 l_success := INSERT_CONTRACT_HEADER_RECORD
673 ( p_incident_id => p_incident_id
674 , p_cov_txn_grp_line_id => l_contract_service_id
675 , p_contract_number => l_contract_nuber
676 , p_service_name => l_service_name
677 , p_service_description => l_service_description
678 , p_amount_uom_code => l_amount_uom_code
679 );
680 END IF;
681 CLOSE c_csl_contract_line_details;
682 ELSE
683 l_success := TRUE;
684 END IF;
685 CLOSE c_csl_contr_record;
686
687 IF l_success THEN
688 --Fix for Bug #3478401
689 IF l_contract_service_id IS NULL THEN
690 OPEN c_csl_contract_service_id(p_incident_id);
691 FETCH c_csl_contract_service_id INTO l_contract_service_id;
692 CLOSE c_csl_contract_service_id;
693 END IF;
694
695 l := INSERT_CONTRACT_BUSS_PROCESSES
696 ( p_incident_id => p_incident_id
697 , p_contract_service_id => l_contract_service_id
698 , p_resource_id => p_resource_id
699 );
700
701 -- Push record to the Resource (insert in ACC)
702 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
703 jtm_message_log_pkg.Log_Msg
704 ( p_incident_id
705 , g_table_name1
706 , 'Inserting ACC record for resource_id = ' || p_resource_id
707 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
708 , 'csl_contract_handling_pkg');
709 END IF;
710
711 -- Bug 3107687 fix:
712 -- check if bus process is not empty insert header info to acc table.
713 IF l > 0 THEN
714
715 JTM_HOOK_UTIL_PKG.Insert_Acc
716 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
717 ,P_ACC_TABLE_NAME => g_acc_table_name1
718 ,P_RESOURCE_ID => p_resource_id
719 ,P_PK1_NAME => g_pk1_name1
720 ,P_PK1_NUM_VALUE => p_incident_id
721 );
722
723 -- ER 3168529 - Support for contract notes
724 l_return := CSL_JTF_NOTES_ACC_PKG.PRE_INSERT_CHILDREN
725 ( P_SOURCE_OBJ_ID => l_contract_service_id
726 , P_SOURCE_OBJ_CODE => 'OKS_COV_NOTE'
727 , P_RESOURCE_ID => p_resource_id
728 );
729
730 IF NOT(l_return) THEN
731 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
732 jtm_message_log_pkg.Log_Msg
733 ( l_contract_service_id
734 , g_table_name1
735 , 'Inserting Contract Notes Failed for resource_id = '
736 || p_resource_id
737 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
738 , 'csl_contract_handling_pkg');
739 END IF;
740 END IF;
741
742 END IF;
743 END IF;
744
745 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
746 jtm_message_log_pkg.Log_Msg
747 ( p_incident_id
748 , g_table_name1
749 , 'Leaving INSERT_CONTRACT_HEADER'
750 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
751 , 'csl_contract_handling_pkg');
752 END IF;
753
754 RETURN TRUE;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
759 jtm_message_log_pkg.Log_Msg
760 ( p_incident_id
761 , g_table_name1
762 , 'Exception occured in INSERT_CONTRACT_HEADER'
763 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
764 , 'csl_contract_handling_pkg');
765 END IF;
766 RETURN FALSE;
767 END INSERT_CONTRACT_HEADER;
768
769
770 /* Will delete all the sr-contract acc records for all mobile resources */
771 -- ER 3168529 - Support for contract notes. Added a new parameter
772 -- p_contract_service_id
773 PROCEDURE DELETE_SR_CONTRACT_ACC
774 ( p_incident_id IN NUMBER
775 , p_resource_id IN NUMBER
776 , p_contract_service_id IN NUMBER
777 )
778 IS
779 BEGIN
780
781 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
782 jtm_message_log_pkg.Log_Msg
783 ( p_incident_id
784 , g_table_name1
785 , 'Entering DELETE_SR_CONTRACT_ACC'
786 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
787 , 'csl_contract_handling_pkg');
788 END IF;
789
790 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
791 jtm_message_log_pkg.Log_Msg
792 ( p_incident_id
793 , g_table_name1
794 , 'Deleting Header ACC record for resource_id = ' || p_resource_id || ', incident_id = ' || p_incident_id
795 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
796 , 'csl_contract_handling_pkg'
797 );
798 END IF;
799
800 JTM_HOOK_UTIL_PKG.Delete_Acc
801 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
802 ,P_ACC_TABLE_NAME => g_acc_table_name1
803 ,P_PK1_NAME => g_pk1_name1
804 ,P_PK1_NUM_VALUE => p_incident_id
805 ,P_RESOURCE_ID => p_resource_id
806 );
807
808 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
809 jtm_message_log_pkg.Log_Msg
810 ( p_incident_id
811 , g_table_name2
812 , 'Deleting Buss proc ACC record for resource_id = ' || p_resource_id || ', incident_id = ' || p_incident_id
813 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
814 , 'csl_contract_handling_pkg');
815 END IF;
816
817 JTM_HOOK_UTIL_PKG.Delete_Acc
818 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
819 ,P_ACC_TABLE_NAME => g_acc_table_name2
820 ,P_PK1_NAME => g_pk1_name2
821 ,P_PK1_NUM_VALUE => p_incident_id
822 ,P_RESOURCE_ID => p_resource_id
823 );
824
825 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
826 jtm_message_log_pkg.Log_Msg
827 ( p_incident_id
828 , g_table_name3
829 , 'Deleting Buss Txn type ACC record for resource_id = ' || p_resource_id || ', incident_id = ' || p_incident_id
830 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
831 , 'csl_contract_handling_pkg');
832 END IF;
833
834 JTM_HOOK_UTIL_PKG.Delete_Acc
835 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name3
836 ,P_ACC_TABLE_NAME => g_acc_table_name3
837 ,P_PK1_NAME => g_pk1_name3
838 ,P_PK1_NUM_VALUE => p_incident_id
839 ,P_RESOURCE_ID => p_resource_id
840 );
841
842 -- ER 3168529 - Support for contract notes
843 CSL_JTF_NOTES_ACC_PKG.POST_DELETE_CHILDREN
844 (
845 P_SOURCE_OBJ_ID => p_contract_service_id
846 , P_SOURCE_OBJ_CODE => 'OKS_COV_NOTE'
847 , P_RESOURCE_ID => p_resource_id
848 );
849
850 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
851 jtm_message_log_pkg.Log_Msg
852 ( p_incident_id
853 , g_table_name1
854 , 'Leaving DELETE_SR_CONTRACT_ACC'
855 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
856 , 'csl_contract_handling_pkg');
857 END IF;
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
862 jtm_message_log_pkg.Log_Msg
863 ( p_incident_id
864 , g_table_name1
865 , 'Exception occured in DELETE_SR_CONTRACT_ACC'
866 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR
867 , 'csl_contract_handling_pkg');
868 END IF;
869 END DELETE_SR_CONTRACT_ACC;
870
871
872 /* Called after SR-ACC Insert */
873 PROCEDURE POST_INSERT_SR_CONTRACT_ACC (
874 p_incident_id IN NUMBER
875 , p_resource_id IN NUMBER
876 , x_return_status OUT NOCOPY VARCHAR2)
877 IS
878 l_success BOOLEAN := FALSE;
879 BEGIN
880 /*** get debug level ***/
881 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
882
883 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
884 jtm_message_log_pkg.Log_Msg
885 ( p_incident_id
886 , g_table_name1
887 , 'Entering POST_INSERT_SR_CONTRACT_ACC'
888 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
889 , 'csl_contract_handling_pkg');
890 END IF;
891
892 l_success := INSERT_CONTRACT_HEADER
893 ( p_incident_id => p_incident_id
894 , p_resource_id => p_resource_id
895 );
896
897 IF l_success THEN
898 x_return_status := FND_API.G_RET_STS_SUCCESS;
899 ELSE
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 END IF;
902
903 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
904 jtm_message_log_pkg.Log_Msg
905 ( p_incident_id
906 , g_table_name1
907 , 'Leaving POST_INSERT_SR_CONTRACT_ACC'
908 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
909 , 'csl_contract_handling_pkg');
910 END IF;
911
912 END POST_INSERT_SR_CONTRACT_ACC;
913
914
915 /* Called after SR-ACC Update */
916 PROCEDURE POST_UPDATE_SR_CONTRACT_ACC (
917 p_incident_id IN NUMBER
918 , p_old_contract_service_id IN NUMBER
919 , p_new_contract_service_id IN NUMBER
920 , p_resource_id IN NUMBER
921 , x_return_status OUT NOCOPY VARCHAR2)
922 IS
923 CURSOR c_csl_contr_record (b_incident_id NUMBER) IS
924 SELECT CONTRACT_SERVICE_ID
925 FROM CSL_SR_CONTRACT_HEADERS
926 WHERE INCIDENT_ID = b_incident_id;
927 r_csl_contr_record c_csl_contr_record%ROWTYPE;
928
929 BEGIN
930 /*** get debug level ***/
931 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
932
933 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
934 jtm_message_log_pkg.Log_Msg
935 ( p_incident_id
936 , g_table_name1
937 , 'Entering POST_UPDATE_SR_CONTRACT_ACC'
938 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
939 , 'csl_contract_handling_pkg');
940 END IF;
941
942 /*** is new contract different from contract stored in csl_sr_contract_headers? ***/
943 OPEN c_csl_contr_record( p_incident_id );
944 FETCH c_csl_contr_record INTO r_csl_contr_record;
945 IF c_csl_contr_record%FOUND THEN
946 IF r_csl_contr_record.contract_service_id <> NVL( p_new_contract_service_id, FND_API.G_MISS_NUM ) THEN
947 /*** yes -> delete old contract from csl contract tables ***/
948 DELETE CSL_CONTR_BUSS_TXN_TYPES WHERE incident_id = p_incident_id;
949 DELETE CSL_CONTR_BUSS_PROCESSES WHERE incident_id = p_incident_id;
950 DELETE CSL_SR_CONTRACT_HEADERS WHERE incident_id = p_incident_id;
951 END IF;
952 END IF;
953 CLOSE c_csl_contr_record;
954
955 IF p_old_contract_service_id IS NOT NULL THEN
956 /* The contract service id is updated for the SR **
957 ** the ACC records has to be deleted for this SR-contract records */
958 -- ER 3168529
959 DELETE_SR_CONTRACT_ACC
960 ( p_incident_id => p_incident_id
961 , p_resource_id => p_resource_id
962 , p_contract_service_id => p_old_contract_service_id
963 );
964 END IF;
965
966 IF p_new_contract_service_id IS NOT NULL THEN
967 /* The contract service id is updated for the SR **
968 ** the ACC records has to be created for this SR-contract records */
969 POST_INSERT_SR_CONTRACT_ACC
970 ( p_incident_id => p_incident_id
971 , p_resource_id => p_resource_id
972 , x_return_status => x_return_status
973 );
974 END IF;
975
976 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
977 jtm_message_log_pkg.Log_Msg
978 ( p_incident_id
979 , g_table_name1
980 , 'Leaving POST_UPDATE_SR_CONTRACT_ACC'
981 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
982 , 'csl_contract_handling_pkg');
983 END IF;
984
985 END POST_UPDATE_SR_CONTRACT_ACC;
986
987
988 /* Called before SR-ACC delete */
989 PROCEDURE PRE_DELETE_SR_CONTRACT_ACC (
990 p_incident_id IN NUMBER
991 , p_resource_id IN NUMBER
992 , x_return_status OUT NOCOPY VARCHAR2) IS
993
994 CURSOR c_csl_contr_record (b_incident_id NUMBER) IS
995 SELECT CONTRACT_SERVICE_ID
996 FROM CSL_SR_CONTRACT_HEADERS
997 WHERE INCIDENT_ID = b_incident_id;
998 r_csl_contr_record c_csl_contr_record%ROWTYPE;
999
1000 BEGIN
1001 /*** get debug level ***/
1002 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
1003
1004 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1005 jtm_message_log_pkg.Log_Msg
1006 ( p_incident_id
1007 , g_table_name1
1008 , 'Entering PRE_DELETE_SR_CONTRACT_ACC'
1009 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1010 , 'csl_contract_handling_pkg');
1011 END IF;
1012
1013 OPEN c_csl_contr_record (p_incident_id);
1014 FETCH c_csl_contr_record INTO r_csl_contr_record;
1015 CLOSE c_csl_contr_record;
1016
1017 -- ER 3168529
1018 DELETE_SR_CONTRACT_ACC
1019 ( p_incident_id => p_incident_id
1020 , p_resource_id => p_resource_id
1021 , p_contract_service_id => r_csl_contr_record.contract_service_id
1022 );
1023
1024 x_return_status := FND_API.G_RET_STS_SUCCESS;
1025
1026 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1027 jtm_message_log_pkg.Log_Msg
1028 ( p_incident_id
1029 , g_table_name1
1030 , 'Entering PRE_DELETE_SR_CONTRACT_ACC'
1031 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
1032 , 'csl_contract_handling_pkg');
1033 END IF;
1034
1035 END PRE_DELETE_SR_CONTRACT_ACC;
1036
1037 END CSL_CONTRACT_HANDLING_PKG;