[Home] [Help]
PACKAGE BODY: APPS.OKL_UBB_INTEGRATION_PVT
Source
1 PACKAGE BODY OKL_UBB_INTEGRATION_PVT AS
2 /* $Header: OKLRUBIB.pls 120.8 2007/11/08 21:27:16 avsingh noship $*/
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- Global Variables
9 G_INIT_NUMBER NUMBER := -9999;
10
11 SUBTYPE rulv_rec_type IS OKL_RULE_PUB.rulv_rec_type;
12 SUBTYPE oks_header_rec_type IS OKS_CONTRACTS_PUB.Header_rec_type;
13 SUBTYPE oks_contact_tbl_type IS OKS_CONTRACTS_PUB.contact_tbl;
14 SUBTYPE oks_salescredit_tbl_type IS OKS_CONTRACTS_PUB.salescredit_tbl;
15 SUBTYPE oks_obj_articles_tbl_type IS OKS_CONTRACTS_PUB.obj_articles_tbl;
16 SUBTYPE oks_line_rec_type IS OKS_CONTRACTS_PUB.line_rec_type;
17 SUBTYPE oks_supp_line_rec_type IS OKS_CONTRACTS_PUB.line_rec_type;
18 SUBTYPE oks_covered_level_rec_type IS OKS_CONTRACTS_PUB.covered_level_rec_type;
19 SUBTYPE oks_pricing_attrb_rec_type IS OKS_CONTRACTS_PUB.pricing_attributes_type;
20 --SUBTYPE oks_StreamHdr_rec_type IS OKS_BILL_SCH.StreamHdr_type;
21 SUBTYPE oks_StreamLvl_tbl_type IS OKS_BILL_SCH.StreamLvl_tbl;
22
23 SUBTYPE crjv_rec_type IS OKC_K_REL_OBJS_PUB.crjv_rec_type;
24 SUBTYPE cimv_rec_type IS OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
25
26
27 TYPE header_rec_type IS RECORD (
28 id okl_k_headers_full_v.id%TYPE,
29 inv_organization_id okl_k_headers_full_v.inv_organization_id%TYPE,
30 sts_code okl_k_headers_full_v.sts_code%TYPE,
31 qcl_id okl_k_headers_full_v.qcl_id%TYPE,
32 scs_code okl_k_headers_full_v.scs_code%TYPE,
33 contract_number okl_k_headers_full_v.contract_number%TYPE,
34 currency_code okl_k_headers_full_v.currency_code%TYPE,
35 cust_po_number okl_k_headers_full_v.cust_po_number%TYPE,
36 short_description okl_k_headers_full_v.short_description%TYPE,
37 start_date okl_k_headers_full_v.start_date%TYPE,
38 end_date okl_k_headers_full_v.end_date%TYPE,
39 term_duration okl_k_headers_full_v.term_duration%TYPE,
40 authoring_org_id okl_k_headers_full_v.authoring_org_id%TYPE
41 );
42
43 -- Cursors
44 --Fixed Bug # 5484903
45 CURSOR usage_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
46 select *
47 from okl_k_lines_full_v line
48 where line.dnz_chr_id = p_chr_id and
49 --Bug# 6374869: subquesry was fetching multiple records as 'USAGE' line
50 --exists for OKS as well as OKL
51 line.lse_id = 56
52 --line.lse_id = (
53 -- select id
54 -- from okc_line_styles_b
55 -- where lty_code = 'USAGE'
56 -- )
57 -- added to handle abandon line
58 and not exists (
59 select 'Y'
60 from okc_statuses_b okcsts
61 where okcsts.code = line.sts_code
62 and okcsts.ste_code in ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
63
64
65 g_usage_rec usage_csr%ROWTYPE;
66
67 CURSOR link_asset_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
68 p_usage_line_id OKC_K_LINES_V.ID%TYPE) IS
69 SELECT id,
70 name ASSET_NUMBER,
71 line_number
72 FROM OKL_K_LINES_FULL_V line
73 WHERE line.dnz_chr_id = p_chr_id
74 AND line.cle_id = p_usage_line_id
75 -- added to handle abandon line
76 AND NOT EXISTS (
77 SELECT 'Y'
78 FROM okc_statuses_v okcsts
79 WHERE okcsts.code = line.sts_code
80 AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
81
82 CURSOR ib_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
83 p_top_line_id OKC_K_LINES_V.ID%TYPE) IS
84 SELECT ib_line.id,
85 ib_line.line_number
86 FROM okl_k_lines_full_v ib_line,
87 okl_k_lines_full_v inst_line,
88 okl_k_lines_full_v top_line
89 WHERE ib_line.cle_id = inst_line.id
90 AND inst_line.cle_id = top_line.id
91 AND ib_line.lse_id = (select id from okc_line_styles_v where lty_code = 'INST_ITEM')
92 AND inst_line.lse_id = (select id from okc_line_styles_v where lty_code = 'FREE_FORM2')
93 AND top_line.lse_id = (select id from okc_line_styles_v where lty_code = 'FREE_FORM1')
94 AND top_line.id = p_top_line_id
95 AND top_line.dnz_chr_id = p_chr_id;
96
97 CURSOR counter_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
98 p_usage_item_id NUMBER,
99 p_ib_line_id OKC_K_LINES_V.ID%TYPE) IS
100 SELECT cc.counter_id,
101 cc.uom_code
102 FROM cs_counter_groups csg,
103 cs_counters cc,
104 okc_k_items cim,
105 okc_k_lines_b cle,
106 okc_line_styles_b lse
107 WHERE TO_CHAR(csg.source_object_id) = cim.object1_id1
108 AND cim.cle_id = cle.id
109 AND cle.lse_id = lse.id
110 AND csg.counter_group_id = cc.counter_group_id
111 AND lse.lty_code = 'INST_ITEM'
112 AND cc.usage_item_id = p_usage_item_id
113 AND cle.dnz_chr_id = p_chr_id
114 AND cle.id = p_ib_line_id;
115
116
117 ------------------------------------------------------------------------------
118 -- PROCEDURE Report_Error
119 -- It is a generalized routine to display error on Concurrent Manager Log file
120 -- Calls:
121 -- Called by:
122 ------------------------------------------------------------------------------
123
124 PROCEDURE Report_Error(
125 x_msg_count OUT NOCOPY NUMBER,
126 x_msg_data OUT NOCOPY VARCHAR2
127 ) IS
128
129 x_msg_index_out NUMBER;
130 x_msg_out VARCHAR2(2000);
131
132 BEGIN
133
134 okl_api.end_activity(
135 X_msg_count => x_msg_count,
136 X_msg_data => x_msg_data
137 );
138
139 FOR i in 1..x_msg_count
140 LOOP
141 FND_MSG_PUB.GET(
142 p_msg_index => i,
143 p_encoded => FND_API.G_FALSE,
144 p_data => x_msg_data,
145 p_msg_index_out => x_msg_index_out
146 );
147
148 -- DBMS_OUTPUT to be replaced by FND_FILE.PUT_LINE(FND_FILE.LOG, "message to be printed")
149 --dbms_output.put_line('Error '||to_char(i)||': '||x_msg_data);
150 END LOOP;
151 return;
152 EXCEPTION
153 WHEN OTHERS THEN
154 NULL;
155 END Report_Error;
156
157 ------------------------------------------------------------------------------
158 -- PROCEDURE get_rule_information
159 --
160 -- This procedure returns Rule information attached to Contract Header or Line
161 --
162 -- Calls:
163 -- Called By:
164 -- create_ubb_contract
165 ------------------------------------------------------------------------------
166 PROCEDURE get_rule_information(
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2,
170 p_rule_information_category IN OKC_RULES_V.RULE_INFORMATION_CATEGORY%TYPE,
171 p_rgd_code IN OKC_RULE_GROUPS_V.RGD_CODE%TYPE,
172 p_jtot_object1_code IN OKC_RULES_V.JTOT_OBJECT1_CODE%TYPE,
173 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
174 p_cle_id IN OKC_K_LINES_V.ID%TYPE,
175 x_rulv_rec OUT NOCOPY rulv_rec_type
176 ) IS
177
178
179 CURSOR rulv_csr (p_rule_info_catg OKC_RULES_V.RULE_INFORMATION_CATEGORY%TYPE,
180 p_rgd_code OKC_RULE_GROUPS_V.RGD_CODE%TYPE,
181 p_jtot_code OKC_RULES_V.JTOT_OBJECT1_CODE%TYPE,
182 p_chr_id OKC_K_HEADERS_V.ID%TYPE,
183 p_cle_id OKC_K_LINES_V.ID%TYPE) IS
184 SELECT
185 rule.ID,
186 rule.OBJECT_VERSION_NUMBER,
187 rule.SFWT_FLAG,
188 rule.OBJECT1_ID1,
189 rule.OBJECT2_ID1,
190 rule.OBJECT3_ID1,
191 rule.OBJECT1_ID2,
192 rule.OBJECT2_ID2,
193 rule.OBJECT3_ID2,
194 rule.JTOT_OBJECT1_CODE,
195 rule.JTOT_OBJECT2_CODE,
196 rule.JTOT_OBJECT3_CODE,
197 rule.DNZ_CHR_ID,
198 rule.RGP_ID,
199 rule.PRIORITY,
200 rule.STD_TEMPLATE_YN,
201 rule.COMMENTS,
202 rule.WARN_YN,
203 rule.ATTRIBUTE_CATEGORY,
204 rule.ATTRIBUTE1,
205 rule.ATTRIBUTE2,
206 rule.ATTRIBUTE3,
207 rule.ATTRIBUTE4,
208 rule.ATTRIBUTE5,
209 rule.ATTRIBUTE6,
210 rule.ATTRIBUTE7,
211 rule.ATTRIBUTE8,
212 rule.ATTRIBUTE9,
213 rule.ATTRIBUTE10,
214 rule.ATTRIBUTE11,
215 rule.ATTRIBUTE12,
216 rule.ATTRIBUTE13,
217 rule.ATTRIBUTE14,
218 rule.ATTRIBUTE15,
219 rule.CREATED_BY,
220 rule.CREATION_DATE,
221 rule.LAST_UPDATED_BY,
222 rule.LAST_UPDATE_DATE,
223 rule.LAST_UPDATE_LOGIN,
224 rule.RULE_INFORMATION_CATEGORY,
225 rule.RULE_INFORMATION1,
226 rule.RULE_INFORMATION2,
227 rule.RULE_INFORMATION3,
228 rule.RULE_INFORMATION4,
229 rule.RULE_INFORMATION5,
230 rule.RULE_INFORMATION6,
231 rule.RULE_INFORMATION7,
232 rule.RULE_INFORMATION8,
233 rule.RULE_INFORMATION9,
234 rule.RULE_INFORMATION10,
235 rule.RULE_INFORMATION11,
236 rule.RULE_INFORMATION12,
237 rule.RULE_INFORMATION13,
238 rule.RULE_INFORMATION14,
239 rule.RULE_INFORMATION15,
240 template_yn,
241 ans_set_jtot_object_code,
242 ans_set_jtot_object_id1,
243 ans_set_jtot_object_id2,
244 display_sequence
245 FROM okc_rules_v rule,
246 okc_rule_groups_v grp
247 WHERE rule_information_category = p_rule_info_catg --'CAN'
248 AND jtot_object1_code = p_jtot_code --'OKX_CUSTACCT'
249 AND grp.rgd_code = p_rgd_code --'LACAN'
250 AND (grp.dnz_chr_id = NVL(p_chr_id, G_INIT_NUMBER)
251 OR
252 grp.cle_id = NVL(p_cle_id, G_INIT_NUMBER)
253 )
254 AND rule.rgp_id = grp.id;
255
256 l_proc_name VARCHAR2(35) := 'GET_RULE_INFORMATION';
257 l_rulv_rec rulv_csr%ROWTYPE;
258 rule_failed EXCEPTION;
259
260 BEGIN
261 IF (G_DEBUG_ENABLED = 'Y') THEN
262 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
263 END IF;
264
265 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
266 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
267 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,p_chr_id);
268 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'jtot: '||p_jtot_object1_code);
269 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'rgd: '||p_rgd_code);
270 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'info: '||p_rule_information_category);
271 END IF;
272
273 x_return_status := OKC_API.G_RET_STS_SUCCESS;
274
275 OPEN rulv_csr (p_rule_information_category,
276 p_rgd_code,
277 p_jtot_object1_code,
278 p_chr_id,
279 p_cle_id);
280
281 FETCH rulv_csr INTO
282 l_rulv_rec.ID,
283 l_rulv_rec.OBJECT_VERSION_NUMBER,
284 l_rulv_rec.SFWT_FLAG,
285 l_rulv_rec.OBJECT1_ID1,
286 l_rulv_rec.OBJECT2_ID1,
287 l_rulv_rec.OBJECT3_ID1,
288 l_rulv_rec.OBJECT1_ID2,
289 l_rulv_rec.OBJECT2_ID2,
290 l_rulv_rec.OBJECT3_ID2,
291 l_rulv_rec.JTOT_OBJECT1_CODE,
292 l_rulv_rec.JTOT_OBJECT2_CODE,
293 l_rulv_rec.JTOT_OBJECT3_CODE,
294 l_rulv_rec.DNZ_CHR_ID,
295 l_rulv_rec.RGP_ID,
296 l_rulv_rec.PRIORITY,
297 l_rulv_rec.STD_TEMPLATE_YN,
298 l_rulv_rec.COMMENTS,
299 l_rulv_rec.WARN_YN,
300 l_rulv_rec.ATTRIBUTE_CATEGORY,
301 l_rulv_rec.ATTRIBUTE1,
302 l_rulv_rec.ATTRIBUTE2,
303 l_rulv_rec.ATTRIBUTE3,
304 l_rulv_rec.ATTRIBUTE4,
305 l_rulv_rec.ATTRIBUTE5,
306 l_rulv_rec.ATTRIBUTE6,
307 l_rulv_rec.ATTRIBUTE7,
308 l_rulv_rec.ATTRIBUTE8,
309 l_rulv_rec.ATTRIBUTE9,
310 l_rulv_rec.ATTRIBUTE10,
311 l_rulv_rec.ATTRIBUTE11,
312 l_rulv_rec.ATTRIBUTE12,
313 l_rulv_rec.ATTRIBUTE13,
314 l_rulv_rec.ATTRIBUTE14,
315 l_rulv_rec.ATTRIBUTE15,
316 l_rulv_rec.CREATED_BY,
317 l_rulv_rec.CREATION_DATE,
318 l_rulv_rec.LAST_UPDATED_BY,
319 l_rulv_rec.LAST_UPDATE_DATE,
320 l_rulv_rec.LAST_UPDATE_LOGIN,
321 l_rulv_rec.RULE_INFORMATION_CATEGORY,
322 l_rulv_rec.RULE_INFORMATION1,
323 l_rulv_rec.RULE_INFORMATION2,
324 l_rulv_rec.RULE_INFORMATION3,
325 l_rulv_rec.RULE_INFORMATION4,
326 l_rulv_rec.RULE_INFORMATION5,
327 l_rulv_rec.RULE_INFORMATION6,
328 l_rulv_rec.RULE_INFORMATION7,
329 l_rulv_rec.RULE_INFORMATION8,
330 l_rulv_rec.RULE_INFORMATION9,
331 l_rulv_rec.RULE_INFORMATION10,
332 l_rulv_rec.RULE_INFORMATION11,
333 l_rulv_rec.RULE_INFORMATION12,
334 l_rulv_rec.RULE_INFORMATION13,
335 l_rulv_rec.RULE_INFORMATION14,
336 l_rulv_rec.RULE_INFORMATION15,
337 l_rulv_rec.TEMPLATE_YN,
338 l_rulv_rec.ANS_SET_JTOT_OBJECT_CODE,
339 l_rulv_rec.ANS_SET_JTOT_OBJECT_ID1,
340 l_rulv_rec.ANS_SET_JTOT_OBJECT_ID2,
341 l_rulv_rec.DISPLAY_SEQUENCE;
342
343 IF rulv_csr%NOTFOUND THEN
344 RAISE rule_failed;
345 END IF;
346
347 CLOSE rulv_csr;
348
349 x_rulv_rec := l_rulv_rec;
350
351 EXCEPTION
352 WHEN rule_failed THEN
353 IF rulv_csr%ISOPEN THEN
354 CLOSE rulv_csr;
355 END IF;
356 x_return_status := OKC_API.G_RET_STS_ERROR;
357 okl_api.set_message(
358 G_APP_NAME,
359 G_OKL_RULE_ERROR
360 );
361
362 WHEN OTHERS THEN
363 x_return_status := OKC_API.G_RET_STS_ERROR;
364 okl_api.set_message(
365 G_APP_NAME,
366 G_UNEXPECTED_ERROR,
367 'OKL_SQLCODE',
368 SQLCODE,
369 'OKL_SQLERRM',
370 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
371 );
372
373 END get_rule_information;
374
375 ------------------------------------------------------------------------------
376 -- PROCEDURE get_party_id
377 --
378 -- This procedure gets party id for a contract header/line
379 --
380 -- Calls:
381 -- Called By:
382 -- populate_header_rec
383 ------------------------------------------------------------------------------
384
385 PROCEDURE get_party_id(
386 x_return_status OUT NOCOPY VARCHAR2,
387 x_msg_count OUT NOCOPY NUMBER,
388 x_msg_data OUT NOCOPY VARCHAR2,
389 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
390 p_rle_code IN OKC_K_PARTY_ROLES_V.RLE_CODE%TYPE,
391 p_jtot_object1_code IN OKC_K_PARTY_ROLES_V.JTOT_OBJECT1_CODE%TYPE,
392 x_party_id OUT NOCOPY OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE
393 ) IS
394 --Fixed Bug # 5484903
395 CURSOR party_csr(p_chr_id OKC_K_HEADERS_V.ID%TYPE,
396 p_rle_code OKC_K_PARTY_ROLES_V.RLE_CODE%TYPE,
397 p_jtot_object1_code OKC_K_PARTY_ROLES_V.JTOT_OBJECT1_CODE%TYPE) IS
398 SELECT object1_id1
399 FROM okc_k_party_roles_b
400 WHERE rle_code = p_rle_code
401 AND dnz_chr_id = p_chr_id
402 AND dnz_chr_id = chr_id
403 AND jtot_object1_code = p_jtot_object1_code;
404
405 l_proc_name VARCHAR2(35) := 'GET_PARTY_ID';
406 party_failed EXCEPTION;
407 l_object1_id1 OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE;
408
409 BEGIN
410 IF (G_DEBUG_ENABLED = 'Y') THEN
411 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
412 END IF;
413 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
414 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
415 END IF;
416 x_return_status := OKC_API.G_RET_STS_SUCCESS;
417
418 OPEN party_csr (p_chr_id,
419 p_rle_code,
420 p_jtot_object1_code);
421 FETCH party_csr INTO l_object1_id1;
422
423 IF party_csr%NOTFOUND THEN
424 raise party_failed;
425 END IF;
426
427 x_party_id := l_object1_id1;
428
429 return;
430 EXCEPTION
431 WHEN party_failed THEN
432 x_return_status := OKC_API.G_RET_STS_SUCCESS;
433 IF party_csr%ISOPEN THEN
434 CLOSE party_csr;
435 END IF;
436
437 okl_api.set_message(
438 G_APP_NAME,
439 G_OKL_PARTY_ROLE_ERROR
440 );
441
442 WHEN OTHERS THEN
443 x_return_status := OKC_API.G_RET_STS_ERROR;
444 okl_api.set_message(
445 G_APP_NAME,
446 G_UNEXPECTED_ERROR,
447 'OKL_SQLCODE',
448 SQLCODE,
449 'OKL_SQLERRM',
450 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
451 );
452 END get_party_id;
453
454 ------------------------------------------------------------------------------
455 -- PROCEDURE get_contract_header
456 --
457 -- This procedure returns contract header information
458 --
459 -- Calls:
460 -- Called By:
461 -- create_ubb_contract
462 ------------------------------------------------------------------------------
463 PROCEDURE get_contract_header(
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 p_chr_id IN okl_k_headers_full_v.id%TYPE,
468 x_header_rec OUT NOCOPY header_rec_type
469 ) IS
470 CURSOR header_csr (p_chr_id okl_k_headers_full_v.id%TYPE) IS
471 SELECT id,
472 inv_organization_id,
473 sts_code,
474 qcl_id,
475 scs_code,
476 contract_number,
477 currency_code,
478 cust_po_number,
479 short_description,
480 start_date,
481 end_date,
482 term_duration,
483 authoring_org_id
484 FROM okl_k_headers_full_v
485 WHERE id = p_chr_id;
486
487 header_failed EXCEPTION;
488 l_proc_name VARCHAR2(35) := 'GET_CONTRACT_HEADER';
489
490 BEGIN
491 IF (G_DEBUG_ENABLED = 'Y') THEN
492 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
493 END IF;
494 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
495 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
496 END IF;
497 x_return_status := OKC_API.G_RET_STS_SUCCESS;
498
499 OPEN header_csr(p_chr_id);
500 FETCH header_csr INTO x_header_rec;
501 IF header_csr%NOTFOUND THEN
502 RAISE header_failed;
503 END IF;
504 CLOSE header_csr;
505
506 EXCEPTION
507 WHEN header_failed THEN
508 IF header_csr%ISOPEN THEN
509 CLOSE header_csr;
510 END IF;
511 x_return_status := OKC_API.G_RET_STS_ERROR;
512 okl_api.set_message(
513 G_APP_NAME,
514 G_OKL_NO_CONTRACT_HEADER
515 );
516 WHEN OTHERS THEN
517 x_return_status := OKC_API.G_RET_STS_ERROR;
518 okl_api.set_message(
519 G_APP_NAME,
520 G_UNEXPECTED_ERROR,
521 'OKL_SQLCODE',
522 SQLCODE,
523 'OKL_SQLERRM',
524 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
525 );
526 END get_contract_header;
527
528 ------------------------------------------------------------------------------
529 -- PROCEDURE get_item_uom_code
530 --
531 -- This procedure gets UOM code from Item setup, primary_uom_code
532 --
533 -- Calls:
534 -- Called By:
535 -- create_ubb_contract
536 ------------------------------------------------------------------------------
537 PROCEDURE get_item_uom_code(
538 x_return_status OUT NOCOPY VARCHAR2,
539 x_msg_count OUT NOCOPY NUMBER,
540 x_msg_data OUT NOCOPY VARCHAR2,
541 p_item_id IN mtl_system_items.inventory_item_id%TYPE,
542 p_org_id IN mtl_system_items.organization_id%TYPE,
543 x_uom_code OUT NOCOPY mtl_system_items.primary_uom_code%TYPE
544 ) IS
545
546 l_proc_name VARCHAR2(35) := 'GET_ITEM_UOM_CODE';
547
548 CURSOR uom_csr (p_item_id mtl_system_items.inventory_item_id%TYPE,
549 p_inv_org_id mtl_system_items.organization_id%TYPE) IS
550 SELECT primary_uom_code
551 FROM mtl_system_items
552 WHERE inventory_item_id = p_item_id
553 AND organization_id = p_inv_org_id;
554
555 l_uom_code mtl_system_items.primary_uom_code%TYPE;
556
557 BEGIN
558 IF (G_DEBUG_ENABLED = 'Y') THEN
559 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
560 END IF;
561
562 x_return_status := OKL_API.G_RET_STS_SUCCESS;
563 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
564 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
565 END IF;
566
567 l_uom_code := NULL;
568 OPEN uom_csr (p_item_id,
569 p_org_id);
570 FETCH uom_csr INTO l_uom_code;
571 CLOSE uom_csr;
572
573 x_uom_code := l_uom_code;
574
575 RETURN;
576
577 EXCEPTION
578 WHEN OTHERS THEN
579 x_return_status := OKC_API.G_RET_STS_ERROR;
580 okl_api.set_message(
581 G_APP_NAME,
582 G_UNEXPECTED_ERROR,
583 'OKL_SQLCODE',
584 SQLCODE,
585 'OKL_SQLERRM',
586 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
587 );
588 x_uom_code := NULL;
589
590 END get_item_uom_code;
591
592 ------------------------------------------------------------------------------
593 -- PROCEDURE get_cust_account
594 --
595 -- This procedure returns bill to id from contract header
596 --
597 -- Calls:
598 -- Called By:
599 ------------------------------------------------------------------------------
600 PROCEDURE get_cust_account(
601 x_return_status OUT NOCOPY VARCHAR2,
602 x_msg_count OUT NOCOPY NUMBER,
603 x_msg_data OUT NOCOPY VARCHAR2,
604 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
605 x_cust_acc_id OUT NOCOPY OKC_K_HEADERS_B.CUST_ACCT_ID%TYPE
606 ) IS
607
608 l_proc_name VARCHAR2(35) := 'GET_CUST_ACCOUNT';
609
610 CURSOR cust_acc_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
611 SELECT cust_acct_id
612 FROM okc_k_headers_b
613 WHERE id = p_chr_id;
614
615 cust_acc_failed EXCEPTION;
616
617 BEGIN
618 IF (G_DEBUG_ENABLED = 'Y') THEN
619 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
620 END IF;
621 x_return_status := OKL_API.G_RET_STS_SUCCESS;
622 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
623 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
624 END IF;
625
626 x_cust_acc_id := NULL;
627 OPEN cust_acc_csr (p_chr_id);
628 FETCH cust_acc_csr INTO x_cust_acc_id;
629 IF cust_acc_csr%NOTFOUND THEN
630 RAISE cust_acc_failed;
631 END IF;
632 CLOSE cust_acc_csr;
633
634 IF (x_cust_acc_id IS NULL) THEN
635 RAISE cust_acc_failed;
636 END IF;
637
638 RETURN;
639
640 EXCEPTION
641
642 WHEN cust_acc_failed THEN
643 IF cust_acc_csr%ISOPEN THEN
644 CLOSE cust_acc_csr;
645 END IF;
646
647 x_return_status := OKL_API.G_RET_STS_ERROR;
648
649 okl_api.set_message(
650 G_APP_NAME,
651 G_OKL_RULE_ERROR
652 );
653
654 WHEN OTHERS THEN
655 x_return_status := OKC_API.G_RET_STS_ERROR;
656 okl_api.set_message(
657 G_APP_NAME,
658 G_UNEXPECTED_ERROR,
659 'OKL_SQLCODE',
660 SQLCODE,
661 'OKL_SQLERRM',
662 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
663 );
664
665 END get_cust_account;
666
667 ------------------------------------------------------------------------------
668 -- PROCEDURE get_bill_to
669 --
670 -- This procedure returns bill to id from contract header
671 --
672 -- Calls:
673 -- Called By:
674 ------------------------------------------------------------------------------
675 PROCEDURE get_bill_to(
676 x_return_status OUT NOCOPY VARCHAR2,
677 x_msg_count OUT NOCOPY NUMBER,
678 x_msg_data OUT NOCOPY VARCHAR2,
679 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
680 x_bill_to_id OUT NOCOPY OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE
681 ) IS
682
683 l_proc_name VARCHAR2(35) := 'GET_BILL_TO';
684
685 CURSOR bill_to_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
686 SELECT bill_to_site_use_id
687 FROM okc_k_headers_b
688 WHERE id = p_chr_id;
689
690 bill_to_failed EXCEPTION;
691
692 BEGIN
693 IF (G_DEBUG_ENABLED = 'Y') THEN
694 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
695 END IF;
696 x_return_status := OKL_API.G_RET_STS_SUCCESS;
697 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
698 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
699 END IF;
700
701 x_bill_to_id := NULL;
702 OPEN bill_to_csr (p_chr_id);
703 FETCH bill_to_csr INTO x_bill_to_id;
704 IF bill_to_csr%NOTFOUND THEN
705 RAISE bill_to_failed;
706 END IF;
707 CLOSE bill_to_csr;
708
709 IF (x_bill_to_id IS NULL) THEN
710 RAISE bill_to_failed;
711 END IF;
712
713 RETURN;
714
715 EXCEPTION
716
717 WHEN bill_to_failed THEN
718 IF bill_to_csr%ISOPEN THEN
719 CLOSE bill_to_csr;
720 END IF;
721
722 x_return_status := OKL_API.G_RET_STS_ERROR;
723
724 okl_api.set_message(
725 G_APP_NAME,
726 G_OKL_RULE_ERROR
727 );
728
729 WHEN OTHERS THEN
730 x_return_status := OKC_API.G_RET_STS_ERROR;
731 okl_api.set_message(
732 G_APP_NAME,
733 G_UNEXPECTED_ERROR,
734 'OKL_SQLCODE',
735 SQLCODE,
736 'OKL_SQLERRM',
737 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
738 );
739 END get_bill_to;
740
741 ------------------------------------------------------------------------------
742 -- PROCEDURE populate_header_rec
743 --
744 -- This procedure popuates OKS header Record with OKL header Rec values
745 --
746 -- Calls:
747 -- Called By:
748 -- create_ubb_contract
749 ------------------------------------------------------------------------------
750 PROCEDURE populate_header_rec(
751 x_return_status OUT NOCOPY VARCHAR2,
752 x_msg_count OUT NOCOPY NUMBER,
753 x_msg_data OUT NOCOPY VARCHAR2,
754 p_header_rec IN header_rec_type,
755 x_oks_header_rec OUT NOCOPY oks_header_rec_type
756 ) IS
757
758 l_proc_name VARCHAR2(35) := 'POPULATE_HEADER_REC';
759 x_party_id NUMBER;
760 header_failed EXCEPTION;
761 x_rulv_rec rulv_rec_type;
762 l_rule_id ra_rules.rule_id%TYPE;
763 l_qcl_id okc_qa_check_lists_v.id%TYPE;
764 l_pdf_id okc_process_defs_v.id%TYPE;
765 --l_bill_to_id NUMBER;
766
767 /*
768 CURSOR ra_rule_csr (p_name ra_rules.name%TYPE) IS
769 SELECT rule_id
770 FROM ra_rules
771 WHERE name = p_name;
772 */
773
774 CURSOR qcl_csr (p_name okc_qa_check_lists_v.name%TYPE) IS
775 SELECT id
776 FROM okc_qa_check_lists_v
777 WHERE name = p_name;
778 --Fixed Bug # 5484903
779 CURSOR wf_csr (p_name okc_process_defs_v.name%TYPE) IS
780 SELECT id
781 FROM okc_process_defs_b
782 WHERE wf_name = p_name;
783
784 l_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
785
786 BEGIN
787 IF (G_DEBUG_ENABLED = 'Y') THEN
788 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
789 END IF;
790 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
791 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
792 END IF;
793 x_return_status := OKC_API.G_RET_STS_SUCCESS;
794
795 x_oks_header_rec.contract_number := p_header_rec.contract_number||'-OKL';
796 x_oks_header_rec.start_date := p_header_rec.start_date;
797 x_oks_header_rec.end_date := p_header_rec.end_date; -- + 1; -- To match with OKS bill Schedule dates
798 x_oks_header_rec.sts_code := 'ACTIVE'; --'SIGNED'; --'ENTERED'; --p_header_rec.sts_code;
799 x_oks_header_rec.scs_code := 'SERVICE'; --p_header_rec.scs_code;
800 x_oks_header_rec.authoring_org_id := p_header_rec.authoring_org_id;
801 x_oks_header_rec.short_description := p_header_rec.short_description;
802 x_oks_header_rec.currency := p_header_rec.currency_code;
803 x_oks_header_rec.cust_po_number := p_header_rec.cust_po_number;
804 x_oks_header_rec.organization_id := p_header_rec.inv_organization_id;
805
806 /* donot use name, pass qcl_id directly, Bug# 3672188
807
808 OPEN qcl_csr ('DEFAULT QA CHECK LIST');
809 FETCH qcl_csr INTO l_qcl_id;
810 IF qcl_csr%NOTFOUND THEN
811 okl_api.set_message(
812 G_APP_NAME,
813 G_INVALID_VALUE,
814 'COL_NAME',
815 'QA CCHECK NAME'
816 );
817 RAISE header_failed;
818 END IF;
819 CLOSE qcl_csr;
820 */
821
822 --x_oks_header_rec.qcl_id := l_qcl_id; --1; --p_header_rec.qcl_id;
823 x_oks_header_rec.qcl_id := 1; -- Bug 3672188
824
825 OPEN wf_csr('OKCAUKAP');
826 FETCH wf_csr INTO l_pdf_id;
827 IF wf_csr%NOTFOUND THEN
828 okl_api.set_message(
829 G_APP_NAME,
830 G_INVALID_VALUE,
831 'COL_NAME',
832 'WORKFLOW NAME'
833 );
834 RAISE header_failed;
835 END IF;
836 CLOSE wf_csr;
837
838 x_oks_header_rec.pdf_id := l_pdf_id; --3; -- OKC Approval Workflow default value
839
840 /* use rule_id instead
841 OPEN ra_rule_csr('IMMEDIATE');
842 FETCH ra_rule_csr INTO l_rule_id;
843 IF ra_rule_csr%NOTFOUND THEN
844 okl_api.set_message(
845 G_APP_NAME,
846 G_INVALID_VALUE,
847 'COL_NAME',
848 'ACCOUNTING RULE TYPE'
849 );
850 RAISE header_failed;
851 END IF;
852 CLOSE ra_rule_csr;
853 */
854 x_oks_header_rec.accounting_rule_type := 1; -- IMMEDIATE, ra_rules table
855
856 /* use rule_id instead
857 OPEN ra_rule_csr('ARREARS INVOICE');
858 FETCH ra_rule_csr INTO l_rule_id;
859 IF ra_rule_csr%NOTFOUND THEN
860 okl_api.set_message(
861 G_APP_NAME,
862 G_INVALID_VALUE,
863 'COL_NAME',
864 'INVOICE RULE TYPE'
865 );
866 RAISE header_failed;
867 END IF;
868 CLOSE ra_rule_csr;
869 */
870
871 x_oks_header_rec.invoice_rule_type := -3; -- ARREARS INVOICE, ra_rules table
872
873 get_party_id(
874 x_return_status => x_return_status,
875 x_msg_count => x_msg_count,
876 x_msg_data => x_msg_data,
877 p_chr_id => p_header_rec.id,
878 p_rle_code => 'LESSEE',
879 p_jtot_object1_code => 'OKX_PARTY',
880 x_party_id => x_party_id
881 );
882
883 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
884 RAISE header_failed;
885 END IF;
886
887 x_oks_header_rec.party_id := x_party_id;
888
889 -- Get BILL_TO information
890 /* Rule migration
891 get_rule_information(
892 x_return_status => x_return_status,
893 x_msg_count => x_msg_count,
894 x_msg_data => x_msg_data,
895 p_rule_information_category => 'BTO',
896 p_rgd_code => 'LABILL',
897 p_jtot_object1_code => 'OKX_BILLTO',
898 p_chr_id => p_header_rec.id,
899 p_cle_id => NULL,
900 x_rulv_rec => x_rulv_rec
901 );
902 */
903 get_bill_to(
904 x_return_status => x_return_status,
905 x_msg_count => x_msg_count,
906 x_msg_data => x_msg_data,
907 p_chr_id => p_header_rec.id,
908 x_bill_to_id => l_bill_to_id
909 );
910
911 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
912 RAISE header_failed;
913 END IF;
914
915 --debug_message('Bill to object1_id1 : '||x_rulv_rec.object1_id1);
916 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
917 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Bill to object1_id1 : '||l_bill_to_id);
918 END IF;
919
920 --x_rulv_rec.object1_id1 := 1329; -- ???
921 --x_oks_header_rec.bill_to_id := x_rulv_rec.object1_id1;
922 x_oks_header_rec.bill_to_id := l_bill_to_id;
923 --x_oks_header_rec.ship_to_id := x_rulv_rec.object1_id1; -- 1329; -- ???
924 --debug_message(x_rulv_rec.object1_id1);
925
926 -- Get Priceing information
927
928 get_rule_information(
929 x_return_status => x_return_status,
930 x_msg_count => x_msg_count,
931 x_msg_data => x_msg_data,
932 p_rule_information_category => 'LAUSBB',
933 p_rgd_code => 'LAUSBB',
934 p_jtot_object1_code => 'OKX_USAGE',
935 p_chr_id => p_header_rec.id,
936 p_cle_id => NULL,
937 x_rulv_rec => x_rulv_rec
938 );
939 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
940 RAISE header_failed;
941 END IF;
942
943 x_oks_header_rec.price_list_id := x_rulv_rec.object2_id1;
944
945 EXCEPTION
946 WHEN header_failed THEN
947 x_return_status := OKC_API.G_RET_STS_ERROR;
948
949 WHEN OTHERS THEN
950 x_return_status := OKC_API.G_RET_STS_ERROR;
951 okl_api.set_message(
952 G_APP_NAME,
953 G_UNEXPECTED_ERROR,
954 'OKL_SQLCODE',
955 SQLCODE,
956 'OKL_SQLERRM',
957 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
958 );
959
960 END populate_header_rec;
961
962
963 ------------------------------------------------------------------------------
964 -- PROCEDURE populate_line_rec
965 --
966 -- This procedure popuates OKS Line Record with OKL line Rec values
967 --
968 -- Calls:
969 -- Called By:
970 -- create_ubb_contract
971 ------------------------------------------------------------------------------
972 PROCEDURE populate_line_rec(
973 x_return_status OUT NOCOPY VARCHAR2,
974 x_msg_count OUT NOCOPY NUMBER,
975 x_msg_data OUT NOCOPY VARCHAR2,
976 p_header_id IN NUMBER,
977 p_line_rec IN usage_csr%ROWTYPE,
978 p_line_number IN NUMBER,
979 p_customer_product_id IN NUMBER,
980 p_uom_code IN VARCHAR2,
981 p_oks_header_id IN NUMBER,
982 x_usage_item_id OUT NOCOPY NUMBER,
983 x_oks_line_rec OUT NOCOPY oks_line_rec_type
984 ) IS
985
986 l_proc_name VARCHAR2(35) := 'POPULATE_LINE_REC';
987 line_failed EXCEPTION;
988
989 l_rule_id ra_rules.rule_id%TYPE;
990
991 x_oks_header_rec header_rec_type;
992 x_party_id NUMBER;
993 x_rulv_rec rulv_rec_type;
994
995 l_oks_start_date DATE;
996 l_oks_end_date DATE;
997 l_authoring_org_id NUMBER;
998
999 l_uom_code mtl_system_items.primary_uom_code%TYPE;
1000
1001 CURSOR oks_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1002 SELECT start_date,
1003 end_date,
1004 authoring_org_id
1005 FROM okc_k_headers_v
1006 WHERE id = p_chr_id;
1007
1008 /*
1009 CURSOR ra_rule_csr (p_name ra_rules.name%TYPE) IS
1010 SELECT rule_id
1011 FROM ra_rules
1012 WHERE name = p_name;
1013 */
1014
1015 l_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
1016 l_cust_acc_id OKC_K_HEADERS_B.CUST_ACCT_ID%TYPE;
1017
1018 BEGIN
1019 IF (G_DEBUG_ENABLED = 'Y') THEN
1020 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1021 END IF;
1022
1023 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1024 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1025 END IF;
1026 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1027
1028 get_contract_header(
1029 x_return_status => x_return_status,
1030 x_msg_count => x_msg_count,
1031 x_msg_data => x_msg_data,
1032 p_chr_id => p_header_id,
1033 x_header_rec => x_oks_header_rec
1034 );
1035
1036 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1037 RAISE line_failed;
1038 END IF;
1039
1040 IF (p_oks_header_id IS NOT NULL) THEN
1041 x_oks_line_rec.k_hdr_id := p_oks_header_id; -- for second line onward
1042 END IF;
1043
1044 x_oks_line_rec.k_line_number := p_line_number;
1045 x_oks_line_rec.line_sts_code := 'ACTIVE'; -- 'SIGNED'; --'ENTERED'; --p_line_rec.sts_code;
1046 x_oks_line_rec.org_id := x_oks_header_rec.authoring_org_id; --204; --??? same as header
1047 x_oks_line_rec.organization_id := x_oks_header_rec.authoring_org_id; --204; --??? same as header
1048 x_oks_line_rec.line_type := 'U';
1049 x_oks_line_rec.currency := p_line_rec.currency_code;
1050 --x_oks_line_rec.usage_type := 'FRT'; -- Get usage_type from LAUSBB later
1051 --x_oks_line_rec.usage_period := 'MTH'; -- Populated below from LAUSBB
1052 x_oks_line_rec.customer_product_id := p_customer_product_id;
1053 --x_oks_line_rec.uom_code := p_uom_code; -- Get usage_type from LAUSBB later
1054
1055 /* use rule_id instead
1056 OPEN ra_rule_csr('IMMEDIATE');
1057 FETCH ra_rule_csr INTO l_rule_id;
1058 IF ra_rule_csr%NOTFOUND THEN
1059 okl_api.set_message(
1060 G_APP_NAME,
1061 G_INVALID_VALUE,
1062 'COL_NAME',
1063 'ACCOUNTING RULE TYPE'
1064 );
1065 RAISE line_failed;
1066 END IF;
1067 CLOSE ra_rule_csr;
1068 */
1069
1070 x_oks_line_rec.accounting_rule_type := 1; -- IMMEDIATE, ra_rules table
1071
1072 /* use rule_id instead
1073 OPEN ra_rule_csr('ARREARS INVOICE');
1074 FETCH ra_rule_csr INTO l_rule_id;
1075 IF ra_rule_csr%NOTFOUND THEN
1076 okl_api.set_message(
1077 G_APP_NAME,
1078 G_INVALID_VALUE,
1079 'COL_NAME',
1080 'INVOICE RULE TYPE'
1081 );
1082 RAISE line_failed;
1083 END IF;
1084 CLOSE ra_rule_csr;
1085 */
1086 x_oks_line_rec.invoicing_rule_type := -3; -- ARREARS INVOICE, ra_rules table
1087
1088 -- Get LAUSBB information
1089 get_rule_information(
1090 x_return_status => x_return_status,
1091 x_msg_count => x_msg_count,
1092 x_msg_data => x_msg_data,
1093 p_rule_information_category => 'LAUSBB',
1094 p_rgd_code => 'LAUSBB',
1095 p_jtot_object1_code => 'OKX_USAGE',
1096 p_chr_id => NULL,
1097 p_cle_id => p_line_rec.id,
1098 x_rulv_rec => x_rulv_rec
1099 );
1100 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1101 RAISE line_failed;
1102 END IF;
1103
1104 x_usage_item_id := x_rulv_rec.object1_id1; -- Required to get Counter later
1105 x_oks_line_rec.srv_id := x_rulv_rec.object1_id1;
1106 x_oks_line_rec.usage_type := x_rulv_rec.rule_information6;
1107 x_oks_line_rec.uom_code := x_rulv_rec.object3_id1;
1108
1109 x_oks_line_rec.usage_period := x_rulv_rec.rule_information8;
1110
1111 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1112 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Service Contract Header :'||p_oks_header_id);
1113 END IF;
1114
1115 IF (p_line_rec.start_date IS NULL
1116 AND
1117 p_line_rec.end_date IS NULL) THEN
1118
1119 x_oks_line_rec.srv_sdt := x_oks_header_rec.start_date;
1120 x_oks_line_rec.srv_edt := x_oks_header_rec.end_date;
1121
1122 ELSIF (p_line_rec.start_date IS NULL
1123 AND
1124 p_line_rec.end_date IS NOT NULL) THEN
1125
1126 x_oks_line_rec.srv_sdt := x_oks_header_rec.start_date;
1127 x_oks_line_rec.srv_edt := p_line_rec.end_date; -- + 1; -- To match OKS need
1128
1129 ELSIF (p_line_rec.start_date IS NOT NULL
1130 AND
1131 p_line_rec.end_date IS NULL) THEN
1132
1133 x_oks_line_rec.srv_edt := x_oks_header_rec.end_date;
1134 x_oks_line_rec.srv_sdt := p_line_rec.start_date; -- + 1; -- To match OKS need
1135
1136 ELSE
1137 x_oks_line_rec.srv_sdt := p_line_rec.start_date;
1138 x_oks_line_rec.srv_edt := p_line_rec.end_date; -- + 1; -- To match OKS need
1139 END IF;
1140
1141 get_party_id(
1142 x_return_status => x_return_status,
1143 x_msg_count => x_msg_count,
1144 x_msg_data => x_msg_data,
1145 p_chr_id => p_header_id,
1146 p_rle_code => 'LESSEE',
1147 p_jtot_object1_code => 'OKX_PARTY',
1148 x_party_id => x_party_id
1149 );
1150
1151 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1152 RAISE line_failed;
1153 END IF;
1154
1155 x_oks_line_rec.customer_id := x_party_id;
1156
1157 -- Get CUSTOMER ACCOUNT information
1158 /* Rule migration
1159 get_rule_information(
1160 x_return_status => x_return_status,
1161 x_msg_count => x_msg_count,
1162 x_msg_data => x_msg_data,
1163 p_rule_information_category => 'CAN',
1164 p_rgd_code => 'LACAN',
1165 p_jtot_object1_code => 'OKX_CUSTACCT',
1166 p_chr_id => p_header_id,
1167 p_cle_id => NULL,
1168 x_rulv_rec => x_rulv_rec
1169 );
1170 */
1171
1172 get_cust_account(
1173 x_return_status => x_return_status,
1174 x_msg_count => x_msg_count,
1175 x_msg_data => x_msg_data,
1176 p_chr_id => p_header_id,
1177 x_cust_acc_id => l_cust_acc_id
1178 );
1179
1180 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1181 RAISE line_failed;
1182 END IF;
1183
1184 --x_oks_line_rec.cust_account := x_rulv_rec.object1_id1;
1185 x_oks_line_rec.cust_account := l_cust_acc_id;
1186
1187 -- Get BILL_TO information
1188 /* Rule migration
1189 get_rule_information(
1190 x_return_status => x_return_status,
1191 x_msg_count => x_msg_count,
1192 x_msg_data => x_msg_data,
1193 p_rule_information_category => 'BTO',
1194 p_rgd_code => 'LABILL',
1195 p_jtot_object1_code => 'OKX_BILLTO',
1196 p_chr_id => p_header_id,
1197 p_cle_id => NULL,
1198 x_rulv_rec => x_rulv_rec
1199 );
1200 */
1201
1202 get_bill_to(
1203 x_return_status => x_return_status,
1204 x_msg_count => x_msg_count,
1205 x_msg_data => x_msg_data,
1206 p_chr_id => p_header_id,
1207 x_bill_to_id => l_bill_to_id
1208 );
1209
1210 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1211 RAISE line_failed;
1212 END IF;
1213
1214 --x_oks_line_rec.bill_to_id := x_rulv_rec.object1_id1;
1215 x_oks_line_rec.bill_to_id := l_bill_to_id;
1216
1217 RETURN;
1218
1219 EXCEPTION
1220
1221 WHEN line_failed THEN
1222 IF oks_csr%ISOPEN THEN
1223 CLOSE oks_csr;
1224 END IF;
1225 /*
1226 IF ra_rule_csr%ISOPEN THEN
1227 CLOSE ra_rule_csr;
1228 END IF;
1229 */
1230 x_return_status := OKC_API.G_RET_STS_ERROR;
1231
1232 WHEN OTHERS THEN
1233 x_return_status := OKC_API.G_RET_STS_ERROR;
1234 okl_api.set_message(
1235 G_APP_NAME,
1236 G_UNEXPECTED_ERROR,
1237 'OKL_SQLCODE',
1238 SQLCODE,
1239 'OKL_SQLERRM',
1240 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
1241 );
1242
1243 END populate_line_rec;
1244
1245 ------------------------------------------------------------------------------
1246 -- PROCEDURE populate_covered_rec
1247 --
1248 -- This procedure popuates OKS covered Line Record with OKL Rules
1249 --
1250 -- Calls:
1251 -- Called By:
1252 -- create_ubb_contract
1253 ------------------------------------------------------------------------------
1254 PROCEDURE populate_covered_rec(
1255 x_return_status OUT NOCOPY VARCHAR2,
1256 x_msg_count OUT NOCOPY NUMBER,
1257 x_msg_data OUT NOCOPY VARCHAR2,
1258 p_oks_header_id IN NUMBER,
1259 p_counter_id IN NUMBER,
1260 p_usage_line_id IN NUMBER,
1261 p_usage_start_date IN DATE,
1262 p_oks_usage_line_id IN NUMBER,
1263 p_header_rec IN header_rec_type,
1264 x_oks_covered_rec OUT NOCOPY oks_covered_level_rec_type
1265 ) IS
1266 l_proc_name VARCHAR2(35) := 'POPULATE_COVERED_REC';
1267 x_rulv_rec rulv_rec_type;
1268 covered_failed EXCEPTION;
1269
1270 l_uom_code mtl_system_items.primary_uom_code%TYPE;
1271
1272 BEGIN
1273 IF (G_DEBUG_ENABLED = 'Y') THEN
1274 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1275 END IF;
1276 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1277 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1278 END IF;
1279 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1280
1281 IF (p_oks_header_id IS NOT NULL) THEN
1282 x_oks_covered_rec.k_id := p_oks_header_id;
1283 END IF;
1284
1285 IF (p_oks_usage_line_id IS NOT NULL) THEN
1286 x_oks_covered_rec.attach_2_line_id := p_oks_usage_line_id;
1287 END IF;
1288
1289 -- Get LAUSBB information
1290 get_rule_information(
1291 x_return_status => x_return_status,
1292 x_msg_count => x_msg_count,
1293 x_msg_data => x_msg_data,
1294 p_rule_information_category => 'LAUSBB',
1295 p_rgd_code => 'LAUSBB',
1296 p_jtot_object1_code => 'OKX_USAGE',
1297 p_chr_id => NULL,
1298 p_cle_id => p_usage_line_id,
1299 x_rulv_rec => x_rulv_rec
1300 );
1301 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1302 RAISE covered_failed;
1303 END IF;
1304
1305 x_oks_covered_rec.customer_product_id := p_counter_id;
1306 x_oks_covered_rec.product_start_date := p_usage_start_date; --SYSDATE;
1307 --x_oks_covered_rec.product_end_date := SYSDATE + 365; -- ??? check without end date, or end date of contract
1308
1309 --x_oks_covered_rec.uom_code := x_rulv_rec.rule_information7; dedey, 10/21/2002
1310 x_oks_covered_rec.uom_code := x_rulv_rec.object3_id1; -- dedey, 10/21/2002 Bug# 2569732
1311
1312 x_oks_covered_rec.currency_code := p_header_rec.currency_code; --'USD'; --??? from header
1313 x_oks_covered_rec.period := x_rulv_rec.rule_information8; --'MTH';
1314 x_oks_covered_rec.amcv_flag := x_rulv_rec.rule_information3;
1315 x_oks_covered_rec.fixed_qty := x_rulv_rec.rule_information7;
1316 x_oks_covered_rec.level_yn := x_rulv_rec.rule_information4;
1317 x_oks_covered_rec.base_reading := x_rulv_rec.rule_information5;
1318 x_oks_covered_rec.minimum_qty := x_rulv_rec.rule_information1;
1319 x_oks_covered_rec.default_qty := x_rulv_rec.rule_information2;
1320
1321 RETURN;
1322
1323 EXCEPTION
1324
1325 WHEN covered_failed THEN
1326 x_return_status := OKC_API.G_RET_STS_ERROR;
1327
1328 WHEN OTHERS THEN
1329 x_return_status := OKC_API.G_RET_STS_ERROR;
1330 okl_api.set_message(
1331 G_APP_NAME,
1332 G_UNEXPECTED_ERROR,
1333 'OKL_SQLCODE',
1334 SQLCODE,
1335 'OKL_SQLERRM',
1336 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
1337 );
1338 END populate_covered_rec;
1339
1340 ------------------------------------------------------------------------------
1341 -- PROCEDURE get_top_line_id
1342 --
1343 -- This procedure returns TOP line for a contract with USAGE line
1344 -- from Usage Sub Line ID. Getting the link thru OKC_K_ITEMS.OBJECT1_ID1
1345 --
1346 -- Calls:
1347 -- Called By:
1348 -- create_ubb_contract
1349 ------------------------------------------------------------------------------
1350 PROCEDURE get_top_line_id (
1351 x_return_status OUT NOCOPY VARCHAR2,
1352 x_msg_count OUT NOCOPY NUMBER,
1353 x_msg_data OUT NOCOPY VARCHAR2,
1354 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1355 p_link_asset_line_id IN OKL_K_LINES_FULL_V.ID%TYPE,
1356 p_link_asset_line_no IN OKL_K_LINES_FULL_V.LINE_NUMBER%TYPE,
1357 x_top_line_id OUT NOCOPY OKC_K_LINES_V.ID%TYPE
1358 ) IS
1359
1360 l_id OKC_K_LINES_V.ID%TYPE;
1361 top_failed EXCEPTION;
1362 l_proc_name VARCHAR2(35) := 'GET_TOP_LINE_ID';
1363
1364 CURSOR top_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
1365 p_link_asset_line_id OKL_K_LINES_FULL_V.ID%TYPE) IS
1366 SELECT oki.object1_id1
1367 FROM okl_k_lines_full_v oklf,
1368 okc_k_items oki
1369 WHERE oklf.id = p_link_asset_line_id
1370 AND oklf.dnz_chr_id = p_chr_id
1371 AND oklf.id = oki.cle_id;
1372
1373 BEGIN
1374 IF (G_DEBUG_ENABLED = 'Y') THEN
1375 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1376 END IF;
1377 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1378 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1379 END IF;
1380 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1381
1382 OPEN top_csr(p_chr_id,
1383 p_link_asset_line_id);
1384 FETCH top_csr INTO l_id;
1385 IF top_csr%NOTFOUND THEN
1386 RAISE top_failed;
1387 END IF;
1388
1389 x_top_line_id := l_id;
1390
1391 CLOSE top_csr;
1392 EXCEPTION
1393 WHEN top_failed THEN
1394 IF top_csr%ISOPEN THEN
1395 CLOSE top_csr;
1396 END IF;
1397 x_return_status := OKC_API.G_RET_STS_ERROR;
1398 okl_api.set_message(
1399 G_APP_NAME,
1400 G_OKL_NO_TOP_LINE,
1401 'LINE_NUM',
1402 p_link_asset_line_no
1403 );
1404 WHEN OTHERS THEN
1405 x_return_status := OKC_API.G_RET_STS_ERROR;
1406 okl_api.set_message(
1407 G_APP_NAME,
1408 G_UNEXPECTED_ERROR,
1409 'OKL_SQLCODE',
1410 SQLCODE,
1411 'OKL_SQLERRM',
1412 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
1413 );
1414 END get_top_line_id;
1415
1416 ------------------------------------------------------------------------------
1417 --Start of comments
1418 --Bug#2498796
1419 --API Name : update_counter_instance
1420 --Purpose : Local API called from Process_IB_Line2
1421 -- Will update any CS counter created during
1422 -- IB activation.
1423 --Modification History :
1424 --13-Aug-2002 avsingh Created
1425 --End of Comments
1426 ------------------------------------------------------------------------------
1427 procedure update_counter_instance (
1428 x_return_status OUT NOCOPY VARCHAR2,
1429 x_msg_count OUT NOCOPY NUMBER,
1430 x_msg_data OUT NOCOPY VARCHAR2,
1431 p_okl_usage_line_id IN OKC_K_LINES_V.ID%TYPE,
1432 p_oks_usage_line_id IN OKC_K_LINES_V.ID%TYPE) is
1433
1434 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1435 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER_INSTANCE';
1436 l_api_version CONSTANT NUMBER := 1.0;
1437
1438
1439 --cursor to get any counter instances created as part of activation for this line
1440 Cursor ctr_cur (p_oks_top_line_id IN NUMBER) IS
1441 select ct.*
1442 --Bug# 6374869
1443 from --CS_COUNTERS ct,
1444 CSI_COUNTERS_B ct,
1445 OKC_K_ITEMS ct_item,
1446 OKC_K_LINES_B ct_line
1447 where ct.counter_id = to_number(ct_item.object1_id1)
1448 and ct_item.cle_id = ct_line.id
1449 and ct_item.dnz_chr_id = ct_line.dnz_chr_id
1450 and ct_line.cle_id = p_oks_top_line_id;
1451
1452
1453
1454 l_ctr_rec ctr_cur%ROWTYPE;
1455 l_oks_top_line_id NUMBER;
1456
1457 --cursor to get LAUSBB rule values
1458 Cursor lausbb_cur(p_okl_top_line_id IN NUMBER) is
1459 Select to_number(rul.object1_id1) usage_item_id
1460 ,to_number(rul.object1_id2) usage_item_inv_org_id
1461 ,to_number(rul.object2_id1) price_list_id
1462 ,rul.rule_information1 Miminum_Quantity
1463 ,rul.rule_information2 Default_Quantity
1464 ,rul.rule_information3 Avg_monthly_Counter_Value
1465 ,rul.rule_information4 ct_Level
1466 ,rul.rule_information5 Base_Reading
1467 ,rul.object3_id1 base_reading_uom
1468 From OKC_RULES_B rul,
1469 OKC_RULE_GROUPS_B rgp,
1470 OKC_K_LINES_B usage_cle
1471 Where rul.rgp_id = rgp.id
1472 and rul.rule_information_category = 'LAUSBB'
1473 and rul.dnz_chr_id = rgp.dnz_chr_id
1474 and rgp.dnz_chr_id = usage_cle.dnz_chr_id
1475 and rgp.cle_id = usage_cle.id
1476 and usage_cle.id = p_okl_top_line_id;
1477
1478 l_lausbb_rec lausbb_cur%ROWTYPE;
1479 l_okl_top_line_id NUMBER;
1480
1481 --Bug# 6374869
1482 --l_csi_ctr_rec CS_COUNTERS_PUB.ctr_rec_type;
1483 l_counter_instance_rec CSI_CTR_DATASTRUCTURES_PUB.Counter_instance_rec;
1484 l_ctr_properties_tbl CSI_CTR_DATASTRUCTURES_PUB.Ctr_properties_tbl;
1485 l_counter_relationships_tbl CSI_CTR_DATASTRUCTURES_PUB.counter_relationships_tbl;
1486 l_ctr_derived_filters_tbl CSI_CTR_DATASTRUCTURES_PUB.ctr_derived_filters_tbl;
1487 l_counter_associations_tbl CSI_CTR_DATASTRUCTURES_PUB.counter_associations_tbl;
1488 --Bug# 6374869 End
1489
1490 l_object_version_number NUMBER;
1491
1492 ctr_exception EXCEPTION;
1493
1494 Begin
1495
1496 l_oks_top_line_id := p_oks_usage_line_id;
1497 l_okl_top_line_id := p_okl_usage_line_id;
1498
1499 OPEN lausbb_cur(p_okl_top_line_id => l_okl_top_line_id);
1500 FETCH lausbb_cur into l_lausbb_rec;
1501 IF lausbb_cur%NOTFOUND Then
1502 Null;
1503 Else
1504 OPEN ctr_cur (p_oks_top_line_id => l_oks_top_line_id);
1505 Loop
1506 FETCH ctr_cur into l_ctr_rec;
1507 Exit When ctr_cur%NOTFOUND;
1508 --Bug# 6374869
1509 l_counter_instance_rec.counter_id := l_ctr_rec.counter_id;
1510 IF l_lausbb_rec.base_reading is not null then
1511 --Bug# 6374869
1512 --l_csi_ctr_rec.initial_reading := l_lausbb_rec.base_reading;
1513 l_counter_instance_rec.initial_reading := l_lausbb_rec.base_reading;
1514 End If;
1515
1516 IF l_lausbb_rec.base_reading_uom is not null then
1517 --Bug# 6374869
1518 --l_csi_ctr_rec.uom_code := l_lausbb_rec.base_reading_uom;
1519 l_counter_instance_rec.uom_code := l_lausbb_rec.base_reading_uom;
1520 End If;
1521
1522 If l_lausbb_rec.usage_item_id is not null then
1523 --Bug# 6374869
1524 --l_csi_ctr_rec.usage_item_id := l_lausbb_rec.usage_item_id;
1525 l_counter_instance_rec.usage_item_id := l_lausbb_rec.usage_item_id;
1526 End If;
1527
1528 ---------------------------------------------------------------------
1529 --Bug# 6374869: R12 IB uptake replacing the call from CS_COUNTERS_PUB
1530 -- to CSI_COUNTER_PUB
1531 ---------------------------------------------------------------------
1532 --call the csi api to update counter
1533 /*--CS_COUNTERS_PUB.UPDATE_COUNTER
1534 --(p_api_version => 1.0,
1535 --p_init_msg_list => OKL_API.G_FALSE,
1536 --p_commit => OKL_API.G_FALSE,
1537 --x_return_status => x_return_status,
1538 --x_msg_count => x_msg_count,
1539 --x_msg_data => x_msg_data,
1540 --p_ctr_id => l_ctr_rec.counter_id,
1541 --p_object_version_number => l_ctr_rec.object_version_number,
1542 --p_ctr_rec => l_csi_ctr_rec,
1543 --p_cascade_upd_to_instances => OKL_API.G_FALSE,
1544 --x_object_version_number => l_object_version_number);
1545 */
1546 CSI_COUNTER_PUB.update_counter(
1547 p_api_version => 1.0
1548 ,p_init_msg_list => OKL_API.G_FALSE
1549 ,p_commit => OKL_API.G_FALSE
1550 ,p_validation_level => fnd_api.g_valid_level_full
1551 ,p_counter_instance_rec => l_counter_instance_rec
1552 ,P_ctr_properties_tbl => l_ctr_properties_tbl
1553 ,P_counter_relationships_tbl => l_counter_relationships_tbl
1554 ,P_ctr_derived_filters_tbl => l_ctr_derived_filters_tbl
1555 ,P_counter_associations_tbl => l_counter_associations_tbl
1556 ,x_return_status => x_return_status
1557 ,x_msg_count => x_msg_count
1558 ,x_msg_data => x_msg_data
1559 );
1560 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1561 RAISE ctr_exception;
1562 END IF;
1563
1564 End Loop;
1565 CLOSE ctr_cur;
1566 END IF;
1567 CLOSE lausbb_cur;
1568
1569 EXCEPTION
1570 WHEN ctr_exception THEN
1571 If ctr_cur%ISOPEN Then
1572 CLOSE ctr_cur;
1573 End If;
1574
1575 WHEN OTHERS THEN
1576 If lausbb_cur%ISOPEN Then
1577 CLOSE lausbb_cur;
1578 End If;
1579
1580 If ctr_cur%ISOPEN Then
1581 CLOSE ctr_cur;
1582 End If;
1583
1584 End Update_Counter_Instance;
1585
1586 ------------------------------------------------------------------------------
1587 -- PROCEDURE create_ubb_contract
1588 --
1589 -- This procedure creats Service Contract corresponding to Usage Base Line
1590 -- for a given contract. It also registers error, if any and it is calling
1591 -- modules responsibility to print error message from error stack
1592 --
1593 -- Calls:
1594 -- get_top_line_id
1595 -- Called By:
1596 -- start process
1597 ------------------------------------------------------------------------------
1598 PROCEDURE create_ubb_contract (
1599 p_api_version IN NUMBER,
1600 p_init_msg_list IN VARCHAR2,
1601 x_return_status OUT NOCOPY VARCHAR2,
1602 x_msg_count OUT NOCOPY NUMBER,
1603 x_msg_data OUT NOCOPY VARCHAR2,
1604 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1605 x_chr_id OUT NOCOPY OKC_K_HEADERS_V.ID%TYPE
1606 ) IS
1607
1608 l_proc_name VARCHAR2(35) := 'CREATE_UBB_CONTRACT';
1609 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_UBB_CONTRACT';
1610 l_api_version CONSTANT NUMBER := 1;
1611 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1612
1613 x_top_line_id OKC_K_LINES_V.ID%TYPE;
1614 x_usage_item_id CS_COUNTERS.USAGE_ITEM_ID%TYPE;
1615
1616 l_usage_count NUMBER := 0;
1617 l_link_asset_count NUMBER := 0;
1618 l_ib_count NUMBER := 0;
1619 l_counter_count NUMBER := 0;
1620 l_customer_product_id NUMBER;
1621 l_usage_id_prev NUMBER := G_INIT_NUMBER;
1622 l_usage_item_id NUMBER;
1623
1624 l_counter_id CS_COUNTERS.COUNTER_ID%TYPE;
1625 l_counter_uom_code CS_COUNTERS.UOM_CODE%TYPE;
1626 x_header_rec header_rec_type;
1627
1628 --OKS record definition
1629 l_oks_header_rec oks_Header_rec_type;
1630 l_oks_hdr_contact_tbl oks_contact_tbl_type;
1631 l_oks_line_contact_tbl oks_contact_tbl_type;
1632 l_oks_supp_contact_tbl oks_contact_tbl_type;
1633 l_oks_hdr_salescredit_tbl oks_salescredit_tbl_type;
1634 l_oks_line_salescredit_tbl oks_salescredit_tbl_type;
1635 l_oks_supp_salescredit_tbl oks_salescredit_tbl_type;
1636 l_oks_obj_articles_tbl oks_obj_articles_tbl_type;
1637 l_oks_line_rec oks_line_rec_type;
1638 l_oks_supp_line_rec oks_line_rec_type;
1639 l_oks_covered_level_rec oks_covered_level_rec_type;
1640 l_oks_price_attribs_rec oks_pricing_attrb_rec_type;
1641 --l_oks_strm_hdr oks_streamhdr_rec_type;
1642 l_oks_strm_lvl oks_streamlvl_tbl_type;
1643 l_oks_contact_tbl oks_contact_tbl_type;
1644
1645
1646 l_streamhdr_rec oks_bill_sch.streamhdr_type;
1647 l_streamlvl_tbl oks_bill_sch.streamlvl_tbl;
1648
1649 x_oks_usage_line_id NUMBER;
1650 x_oks_cp_line_id NUMBER;
1651 x_oks_chr_id NUMBER;
1652
1653 x_rulv_rec rulv_rec_type;
1654
1655 -- Check for 11.5.9 or 11.5.10 OKS version
1656 CURSOR check_oks_ver IS
1657 SELECT 1
1658 FROM okc_class_operations
1659 WHERE cls_code = 'SERVICE'
1660 AND opn_code = 'CHECK_RULE';
1661
1662 l_dummy NUMBER;
1663 l_oks_ver VARCHAR2(3);
1664
1665 BEGIN -- main process starts here
1666 IF (G_DEBUG_ENABLED = 'Y') THEN
1667 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1668 END IF;
1669
1670 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1671 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1672 END IF;
1673 -- call START_ACTIVITY to create savepoint, check compatibility
1674 -- and initialize message list
1675 x_return_status := OKC_API.START_ACTIVITY(
1676 p_api_name => l_api_name,
1677 p_pkg_name => G_PKG_NAME,
1678 p_init_msg_list => p_init_msg_list,
1679 l_api_version => l_api_version,
1680 p_api_version => p_api_version,
1681 p_api_type => G_API_TYPE,
1682 x_return_status => x_return_status);
1683
1684 -- check if activity started successfully
1685 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1686 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1687 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1688 raise OKC_API.G_EXCEPTION_ERROR;
1689 END IF;
1690
1691 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1692
1693 l_usage_count := 0;
1694 l_usage_id_prev := G_INIT_NUMBER;
1695
1696 -- get contract header information
1697 get_contract_header(
1698 x_return_status => x_return_status,
1699 x_msg_count => x_msg_count,
1700 x_msg_data => x_msg_data,
1701 p_chr_id => p_chr_id,
1702 x_header_rec => x_header_rec
1703 );
1704
1705 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1706 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1707 END IF;
1708 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1709 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'STS Code : '||x_header_rec.sts_code);
1710 END IF;
1711
1712 -- Create Service Contract Header only
1713 populate_header_rec(
1714 x_return_status => x_return_status,
1715 x_msg_count => x_msg_count,
1716 x_msg_data => x_msg_data,
1717 p_header_rec => x_header_rec,
1718 x_oks_header_rec => l_oks_header_rec
1719 );
1720
1721 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1722 RAISE OKC_API.G_EXCEPTION_ERROR;
1723 END IF;
1724 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1725 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'STS Code : '||l_oks_header_rec.sts_code);
1726 END IF;
1727
1728 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1729 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'contract: '||l_oks_header_rec.contract_number);
1730 END IF;
1731
1732 OKS_CONTRACTS_PUB.create_contract_header(
1733 p_k_header_rec => l_oks_header_rec,
1734 p_header_contacts_tbl => l_oks_hdr_contact_tbl,
1735 p_header_sales_crd_tbl => l_oks_hdr_salescredit_tbl,
1736 p_header_articles_tbl => l_oks_obj_articles_tbl,
1737 x_chrid => x_oks_chr_id,
1738 x_return_status => x_return_status,
1739 x_msg_count => x_msg_count,
1740 x_msg_data => x_msg_data
1741 );
1742
1743 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1744 RAISE OKC_API.G_EXCEPTION_ERROR;
1745 END IF;
1746
1747 x_chr_id := x_oks_chr_id;
1748 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1749 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract Header Id: '||x_oks_chr_id);
1750 END IF;
1751
1752 FOR usage_rec IN usage_csr (p_chr_id)
1753 LOOP
1754 -- Get all Rules associated with this line x_usage_item_id
1755
1756 l_usage_count := l_usage_count + 1;
1757
1758 g_usage_rec := usage_rec;
1759 -- create OKS Service line under the header created above
1760
1761 populate_line_rec(
1762 x_return_status => x_return_status,
1763 x_msg_count => x_msg_count,
1764 x_msg_data => x_msg_data,
1765 p_header_id => x_header_rec.id,
1766 p_line_rec => g_usage_rec,
1767 p_line_number => l_usage_count,
1768 p_customer_product_id => l_customer_product_id,
1769 p_uom_code => 'Ea', -- Not being used now
1770 p_oks_header_id => x_oks_chr_id,
1771 x_usage_item_id => l_usage_item_id,
1772 x_oks_line_rec => l_oks_line_rec
1773 );
1774
1775 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1776 RAISE OKC_API.G_EXCEPTION_ERROR;
1777 END IF;
1778
1779 --
1780 -- Defaults from Contract Header
1781 --
1782 l_oks_line_rec.org_id := x_header_rec.authoring_org_id;
1783 --Fix Bug# 3008830 :
1784 --l_oks_line_rec.organization_id := x_header_rec.authoring_org_id;
1785 l_oks_line_rec.organization_id := x_header_rec.inv_organization_id;
1786
1787 OKS_CONTRACTS_PUB.create_service_line(
1788 p_k_line_rec => l_oks_line_rec,
1789 p_contact_tbl => l_oks_line_contact_tbl,
1790 p_line_sales_crd_tbl => l_oks_line_salescredit_tbl,
1791 x_service_line_id => x_oks_usage_line_id,
1792 x_return_status => x_return_status,
1793 x_msg_count => x_msg_count,
1794 x_msg_data => x_msg_data
1795 );
1796
1797 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1798 RAISE OKC_API.G_EXCEPTION_ERROR;
1799 END IF;
1800
1801 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1802 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKS Usage Line ID: '||x_oks_usage_line_id);
1803 END IF;
1804
1805 l_link_asset_count := 0;
1806 FOR link_asset_rec IN link_asset_csr(p_chr_id,
1807 usage_rec.id)
1808 LOOP
1809 l_link_asset_count := l_link_asset_count + 1;
1810
1811 get_top_line_id (
1812 x_return_status => x_return_status,
1813 x_msg_count => x_msg_count,
1814 x_msg_data => x_msg_data,
1815 p_chr_id => p_chr_id,
1816 p_link_asset_line_id => link_asset_rec.id,
1817 p_link_asset_line_no => link_asset_rec.line_number,
1818 x_top_line_id => x_top_line_id
1819 );
1820 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1821 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1822 END IF;
1823
1824 l_ib_count := 0;
1825 FOR ib_rec IN ib_csr(p_chr_id,
1826 x_top_line_id)
1827 LOOP
1828 l_ib_count := l_ib_count + 1;
1829
1830 l_counter_count := 0;
1831 FOR counter_rec IN counter_csr(p_chr_id,
1832 l_usage_item_id,
1833 ib_rec.id)
1834 LOOP
1835
1836 l_counter_count := l_counter_count + 1;
1837
1838 l_counter_id := counter_rec.counter_id;
1839 l_counter_uom_code := counter_rec.uom_code;
1840
1841 populate_covered_rec(
1842 x_return_status => x_return_status,
1843 x_msg_count => x_msg_count,
1844 x_msg_data => x_msg_data,
1845 p_oks_header_id => x_oks_chr_id,
1846 p_counter_id => l_counter_id,
1847 p_usage_line_id => g_usage_rec.id,
1848 p_usage_start_date => g_usage_rec.start_date,
1849 p_oks_usage_line_id => x_oks_usage_line_id,
1850 p_header_rec => x_header_rec,
1851 x_oks_covered_rec => l_oks_covered_level_rec
1852 );
1853
1854 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1855 RAISE OKC_API.G_EXCEPTION_ERROR;
1856 END IF;
1857
1858 l_oks_covered_level_rec.currency_code := x_header_rec.currency_code;
1859 l_oks_covered_level_rec.product_end_date := ADD_MONTHS(x_header_rec.start_date, x_header_rec.term_duration) - 1;
1860
1861 IF (l_oks_covered_level_rec.uom_code IS NULL) THEN
1862 l_oks_covered_level_rec.uom_code := l_counter_uom_code;
1863 END IF;
1864
1865 OKS_CONTRACTS_PUB.create_covered_line(
1866 p_k_covd_rec => l_oks_covered_level_rec,
1867 p_price_attribs => l_oks_price_attribs_rec,
1868 x_cp_line_id => x_oks_cp_line_id,
1869 x_return_status => x_return_status,
1870 x_msg_count => x_msg_count,
1871 x_msg_data => x_msg_data
1872 );
1873 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1874 RAISE OKC_API.G_EXCEPTION_ERROR;
1875 END IF;
1876 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1877 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After covered line');
1878 END IF;
1879
1880 END LOOP; -- counter_csr
1881
1882 END LOOP; -- ib_csr
1883
1884 END LOOP; --link_asset_csr
1885
1886
1887 /* Changed after OKS rule migration, see below
1888 -- Attach Billing Schedule
1889 l_streamhdr_rec.cle_id := x_oks_usage_line_id;
1890 l_streamhdr_rec.rule_information_category := 'SLH';
1891 l_streamhdr_rec.rule_information1 := 'T';
1892
1893 l_streamlvl_tbl(1).rule_information1 := 10;
1894 l_streamlvl_tbl(1).rule_information2 := x_header_rec.start_date; -- start date of contract
1895 l_streamlvl_tbl(1).rule_information3 := x_header_rec.term_duration;
1896 l_streamlvl_tbl(1).rule_information4 := 1; --30.47222222; -- Average days in a month
1897 l_streamlvl_tbl(1).object1_id1 := x_rulv_rec.rule_information8; --'MTH'; --'DAY'; --'MTH';
1898 l_streamlvl_tbl(1).rule_information_category := 'SLL';
1899
1900
1901 oks_contracts_pub.create_bill_schedule(
1902 p_Strm_hdr_rec => l_streamhdr_rec,
1903 p_strm_level_tbl => l_streamlvl_tbl,
1904 p_invoice_rule_id => NULL,
1905 x_return_status => x_return_status
1906 );
1907
1908 -- Attach Billing Schedule
1909
1910 l_streamlvl_tbl(1).cle_id := x_oks_usage_line_id;
1911 l_streamlvl_tbl(1).dnz_chr_id := x_oks_chr_id;
1912 l_streamlvl_tbl(1).sequence_no := 10;
1913 l_streamlvl_tbl(1).uom_code := 'MTH'; --'DAY'; --'MTH';
1914 l_streamlvl_tbl(1).start_date := x_header_rec.start_date; -- start date of contract
1915 l_streamlvl_tbl(1).level_periods := x_header_rec.term_duration;
1916 l_streamlvl_tbl(1).uom_per_period := 1; --30.47222222; -- Average days in a month
1917 */
1918 -- Get LAUSBB information
1919 get_rule_information(
1920 x_return_status => x_return_status,
1921 x_msg_count => x_msg_count,
1922 x_msg_data => x_msg_data,
1923 p_rule_information_category => 'LAUSBB',
1924 p_rgd_code => 'LAUSBB',
1925 p_jtot_object1_code => 'OKX_USAGE',
1926 p_chr_id => NULL,
1927 p_cle_id => g_usage_rec.id,
1928 x_rulv_rec => x_rulv_rec
1929 );
1930
1931 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1932 RAISE OKC_API.G_EXCEPTION_ERROR;
1933 END IF;
1934
1935 l_oks_ver := '?';
1936 OPEN check_oks_ver;
1937 FETCH check_oks_ver INTO l_dummy;
1938 IF check_oks_ver%NOTFOUND THEN
1939 l_oks_ver := '9';
1940 ELSE
1941 l_oks_ver := '10';
1942 END IF;
1943 CLOSE check_oks_ver;
1944
1945 IF (l_oks_ver = '10') THEN
1946
1947 -- Fixed paramter assignment for Bug 4113684
1948 l_streamlvl_tbl(1).cle_id := x_oks_usage_line_id;
1949 l_streamlvl_tbl(1).rule_information_category := 'SLH';
1950 l_streamlvl_tbl(1).rule_information1 := 'T';
1951
1952 l_streamlvl_tbl(1).Sequence_no := 10;
1953 l_streamlvl_tbl(1).start_date := x_header_rec.start_date; -- start date of contract
1954 l_streamlvl_tbl(1).level_periods := x_rulv_rec.rule_information9; --x_header_rec.term_duration;
1955 l_streamlvl_tbl(1).uom_per_period := 1; --30.47222222; -- Average days in a month
1956 l_streamlvl_tbl(1).uom_code := x_rulv_rec.rule_information8; --'MTH'; --'DAY'; --'MTH';
1957 --l_streamlvl_tbl(1).rule_information_category := 'SLL';
1958
1959 oks_contracts_pub.create_bill_schedule(
1960 p_billing_sch => 'T',
1961 p_strm_level_tbl => l_streamlvl_tbl,
1962 p_invoice_rule_id => NULL,
1963 x_return_status => x_return_status
1964 );
1965 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1966 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Bill Status : '||x_return_status);
1967 END IF;
1968
1969 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1970 RAISE OKC_API.G_EXCEPTION_ERROR;
1971 END IF;
1972
1973 ELSE -- oks_ver = 9
1974
1975 -- Attach Billing Schedule
1976 l_streamhdr_rec.cle_id := x_oks_usage_line_id;
1977 l_streamhdr_rec.rule_information_category := 'SLH';
1978 l_streamhdr_rec.rule_information1 := 'T';
1979
1980 l_streamlvl_tbl(1).rule_information1 := 10;
1981 l_streamlvl_tbl(1).rule_information2 := x_header_rec.start_date; -- start date of contract
1982 l_streamlvl_tbl(1).rule_information3 := x_rulv_rec.rule_information9; --x_header_rec.term_duration;
1983 l_streamlvl_tbl(1).rule_information4 := 1; --30.47222222; -- Average days in a month
1984 l_streamlvl_tbl(1).object1_id1 := x_rulv_rec.rule_information8; --'MTH'; --'DAY'; --'MTH';
1985 l_streamlvl_tbl(1).rule_information_category := 'SLL';
1986
1987
1988 oks_contracts_pub.create_bill_schedule(
1989 p_Strm_hdr_rec => l_streamhdr_rec,
1990 p_strm_level_tbl => l_streamlvl_tbl,
1991 p_invoice_rule_id => NULL,
1992 x_return_status => x_return_status
1993 );
1994 END IF;
1995
1996 -- check the presence of All lines in proper structure
1997 IF (l_link_asset_count = 0) THEN
1998 okl_api.set_message(
1999 G_APP_NAME,
2000 G_OKL_NO_LINK_ASSET_LINE
2001 );
2002 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2003 END IF;
2004
2005 IF (l_ib_count = 0) THEN
2006 okl_api.set_message(
2007 G_APP_NAME,
2008 G_OKL_NO_IB_LINE
2009 );
2010 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2011 END IF;
2012
2013 IF (l_counter_count = 0) THEN
2014 okl_api.set_message(
2015 G_APP_NAME,
2016 G_OKL_NO_COUNTER_INSTANCE
2017 );
2018 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2019 END IF;
2020
2021 -- Link OKS Usage line with that of OKL
2022 link_oks_line(
2023 x_return_status => x_return_status,
2024 x_msg_count => x_msg_count,
2025 x_msg_data => x_msg_data,
2026 p_okl_header_id => p_chr_id,
2027 p_okl_usage_line_id => usage_rec.id, -- OKL Usage Line ID
2028 p_oks_usage_line_id => x_oks_usage_line_id -- OKS Usage Line ID
2029 );
2030
2031 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2032 RAISE OKC_API.G_EXCEPTION_ERROR;
2033 END IF;
2034
2035 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2036 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Link Line Done');
2037 END IF;
2038
2039 --
2040 -- Update Counter Instance at OKS Usage Line
2041 -- Fix Bug# 2498796
2042 --
2043 update_counter_instance(
2044 x_return_status => x_return_status,
2045 x_msg_count => x_msg_count,
2046 x_msg_data => x_msg_data,
2047 p_okl_usage_line_id => usage_rec.id,
2048 p_oks_usage_line_id => x_oks_usage_line_id);
2049
2050 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2051 RAISE OKC_API.G_EXCEPTION_ERROR;
2052 END IF;
2053
2054 END LOOP; -- usage_csr
2055
2056 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2057
2058 IF (l_usage_count = 0) THEN
2059 okl_api.set_message(
2060 G_APP_NAME,
2061 G_OKL_NO_USAGE_LINE
2062 );
2063 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2064 END IF;
2065
2066
2067 -- Link OKS Header, create above, with that of OKL
2068 link_oks_header(
2069 x_return_status => x_return_status,
2070 x_msg_count => x_msg_count,
2071 x_msg_data => x_msg_data,
2072 p_okl_header_id => p_chr_id,
2073 p_oks_header_id => x_chr_id
2074 );
2075
2076 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2077 RAISE OKC_API.G_EXCEPTION_ERROR;
2078 END IF;
2079 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2080 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Link Header Done');
2081 END IF;
2082
2083
2084 -- End activity
2085
2086 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
2087 x_msg_data => x_msg_data);
2088
2089 Exception
2090 when OKC_API.G_EXCEPTION_ERROR then
2091
2092 IF counter_csr%ISOPEN THEN
2093 CLOSE counter_csr;
2094 END IF;
2095
2096 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2097 p_api_name => l_api_name,
2098 p_pkg_name => G_PKG_NAME,
2099 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2100 x_msg_count => x_msg_count,
2101 x_msg_data => x_msg_data,
2102 p_api_type => G_API_TYPE);
2103
2104
2105 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2106
2107 IF counter_csr%ISOPEN THEN
2108 CLOSE counter_csr;
2109 END IF;
2110
2111 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2112 p_api_name => l_api_name,
2113 p_pkg_name => G_PKG_NAME,
2114 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2115 x_msg_count => x_msg_count,
2116 x_msg_data => x_msg_data,
2117 p_api_type => G_API_TYPE);
2118
2119 when OTHERS then
2120
2121 IF counter_csr%ISOPEN THEN
2122 CLOSE counter_csr;
2123 END IF;
2124
2125 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2126 p_api_name => l_api_name,
2127 p_pkg_name => G_PKG_NAME,
2128 p_exc_name => 'OTHERS',
2129 x_msg_count => x_msg_count,
2130 x_msg_data => x_msg_data,
2131 p_api_type => G_API_TYPE);
2132
2133 END create_ubb_contract;
2134
2135 ------------------------------------------------------------------------------
2136 -- PROCEDURE link_oks_header
2137 --
2138 -- This procedure Links OKS Contract Header with that of OKL. It is called
2139 -- after successful creation of one OKS contract from OKl Usage line
2140 --
2141 -- Calls:
2142 -- Called By:
2143 ------------------------------------------------------------------------------
2144 PROCEDURE link_oks_header(
2145 x_return_status OUT NOCOPY VARCHAR2,
2146 x_msg_count OUT NOCOPY NUMBER,
2147 x_msg_data OUT NOCOPY VARCHAR2,
2148 p_okl_header_id IN OKC_K_HEADERS_V.ID%TYPE,
2149 p_oks_header_id IN OKC_K_HEADERS_V.ID%TYPE
2150 ) IS
2151 l_proc_name VARCHAR2(35) := 'LINK_OKS_HEADER';
2152 l_crjv_rec crjv_rec_type;
2153 x_crjv_rec crjv_rec_type;
2154 oks_header_failed EXCEPTION;
2155
2156 BEGIN
2157 IF (G_DEBUG_ENABLED = 'Y') THEN
2158 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2159 END IF;
2160 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2161 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2162 END IF;
2163 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2164
2165 l_crjv_rec.chr_id := p_okl_header_id;
2166 l_crjv_rec.rty_code := 'OKLUBB';
2167 l_crjv_rec.object1_id1 := p_oks_header_id;
2168 l_crjv_rec.object1_id2 := '#';
2169 l_crjv_rec.jtot_object1_code := 'OKL_SERVICE';
2170
2171 OKC_K_REL_OBJS_PUB.create_row (
2172 p_api_version => 1.0,
2173 p_init_msg_list => OKC_API.G_FALSE,
2174 x_return_status => x_return_status,
2175 x_msg_count => x_msg_count,
2176 x_msg_data => x_msg_data,
2177 p_crjv_rec => l_crjv_rec,
2178 x_crjv_rec => x_crjv_rec
2179 );
2180
2181 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2182 RAISE oks_header_failed;
2183 END IF;
2184
2185 EXCEPTION
2186 WHEN oks_header_failed THEN
2187 x_return_status := OKC_API.G_RET_STS_ERROR;
2188
2189 WHEN OTHERS THEN
2190 x_return_status := OKC_API.G_RET_STS_ERROR;
2191 okl_api.set_message(
2192 G_APP_NAME,
2193 G_UNEXPECTED_ERROR,
2194 'OKL_SQLCODE',
2195 SQLCODE,
2196 'OKL_SQLERRM',
2197 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
2198 );
2199 END link_oks_header;
2200
2201 ------------------------------------------------------------------------------
2202 -- PROCEDURE create_ubb_contract
2203 --
2204 -- This procedure Links OKS Contract service line with that of OKL (Usage Line)
2205 -- after successful creation of one OKS service line
2206 --
2207 -- Calls:
2208 -- Called By:
2209 ------------------------------------------------------------------------------
2210 PROCEDURE link_oks_line(
2211 x_return_status OUT NOCOPY VARCHAR2,
2212 x_msg_count OUT NOCOPY NUMBER,
2213 x_msg_data OUT NOCOPY VARCHAR2,
2214 p_okl_header_id IN OKC_K_HEADERS_V.ID%TYPE,
2215 p_okl_usage_line_id IN OKC_K_LINES_V.ID%TYPE,
2216 p_oks_usage_line_id IN OKC_K_LINES_V.ID%TYPE
2217 ) IS
2218 CURSOR item_csr (p_cle_id NUMBER) IS
2219 SELECT id
2220 FROM okc_k_items_v
2221 WHERE cle_id = p_cle_id
2222 AND dnz_chr_id = p_okl_header_id;
2223 --AND jtot_object1_code = 'OKL_USAGE';
2224
2225 l_proc_name VARCHAR2(35) := 'LINK_OKS_LINE';
2226 oks_line_failed EXCEPTION;
2227 l_cimv_rec cimv_rec_type;
2228 x_cimv_rec cimv_rec_type;
2229 l_item_line_id NUMBER;
2230
2231 BEGIN
2232 IF (G_DEBUG_ENABLED = 'Y') THEN
2233 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2234 END IF;
2235
2236 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2237 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2238 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2239 END IF;
2240
2241 OPEN item_csr(p_okl_usage_line_id);
2242 FETCH item_csr INTO l_item_line_id;
2243
2244 IF item_csr%NOTFOUND THEN
2245 okl_api.set_message(
2246 G_APP_NAME,
2247 G_OKL_NO_ITEM_LINK
2248 );
2249 RAISE oks_line_failed;
2250 END IF;
2251 CLOSE item_csr;
2252
2253 l_cimv_rec.id := l_item_line_id;
2254 l_cimv_rec.object1_id1 := p_oks_usage_line_id;
2255 l_cimv_rec.object1_id2 := '#';
2256
2257 l_cimv_rec.jtot_object1_code := 'OKL_USAGE'; --Rviriyal Added for bug 6270667
2258 ----- Changes by Kanti
2259 ----- Validate the JTOT Object code, ID1 and ID2
2260
2261 okl_la_validation_util_pvt.VALIDATE_STYLE_JTOT (p_api_version => 1.0,
2262 p_init_msg_list => OKC_API.G_FALSE,
2263 x_return_status => x_return_status,
2264 x_msg_count => x_msg_count,
2265 x_msg_data => x_msg_data,
2266 p_object_name => l_cimv_rec.jtot_object1_code,
2267 p_id1 => l_cimv_rec.object1_id1,
2268 p_id2 => l_cimv_rec.object1_id2);
2269
2270 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2271 RAISE oks_line_failed;
2272 END IF;
2273
2274 ---- Changes End
2275
2276 OKC_CONTRACT_ITEM_PUB.update_contract_item(
2277 p_api_version => 1.0,
2278 p_init_msg_list => OKC_API.G_FALSE,
2279 x_return_status => x_return_status,
2280 x_msg_count => x_msg_count,
2281 x_msg_data => x_msg_data,
2282 p_cimv_rec => l_cimv_rec,
2283 x_cimv_rec => x_cimv_rec
2284 );
2285
2286 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2287 RAISE oks_line_failed;
2288 END IF;
2289
2290 EXCEPTION
2291
2292 WHEN oks_line_failed THEN
2293
2294 IF item_csr%ISOPEN THEN
2295 CLOSE item_csr;
2296 END IF;
2297
2298 x_return_status := OKC_API.G_RET_STS_ERROR;
2299
2300 WHEN OTHERS THEN
2301 x_return_status := OKC_API.G_RET_STS_ERROR;
2302 okl_api.set_message(
2303 G_APP_NAME,
2304 G_UNEXPECTED_ERROR,
2305 'OKL_SQLCODE',
2306 SQLCODE,
2307 'OKL_SQLERRM',
2308 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
2309 );
2310 END link_oks_line;
2311
2312 END OKL_UBB_INTEGRATION_PVT;