[Home] [Help]
PACKAGE BODY: APPS.OKS_SETUP_UTIL_PUB
Source
1 PACKAGE BODY OKS_SETUP_UTIL_PUB AS
2 /* $Header: OKSSETUB.pls 120.24.12020000.2 2013/04/05 08:25:14 vgujarat ship $ */
3
4
5 l_conc_program VARCHAR2(200) := 'Y';
6
7 -------------------------------------------------------------------------------
8 -- Procedure: Get_Error_Stack
9 -- Purpose: This procedure will copy the message stack to the debug
10 -- log file.
11 -----------------------------------------------------------------------------
12 PROCEDURE Get_Error_Stack IS
13 l_msg_index NUMBER;
14 l_msg_data VARCHAR2(32000);
15 Begin
16 l_msg_index := 1;
17 For i in 1..fnd_msg_pub.count_msg
18 loop
19 fnd_msg_pub.get
20 (
21 p_msg_index => i,
22 p_encoded => 'F',
23 p_data => l_msg_data,
24 p_msg_index_out => l_msg_index
25 );
26 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
27 p_perf_msg =>l_msg_data);
28
29 End loop;
30
31 End Get_Error_Stack;
32
33 FUNCTION chk_party_Exists(p_chr_id IN NUMBER) return BOOLEAN IS
34
35 CURSOR l_party_csr(p_chr_id NUMBER) IS
36 SELECT count(*)
37 FROM okc_k_party_roles_b
38 WHERE dnz_chr_id =p_chr_id
39 AND rle_code in ('CUSTOMER','SUBSCRIBER')
40 AND cle_id is null;
41 --AND chr_id is null;
42
43 l_party_count NUMBER:=0;
44
45 BEGIN
46
47 OPEN l_party_csr(p_chr_id);
48 FETCH l_party_csr INTO l_party_count;
49 close l_party_csr;
50 IF l_party_count>0 then
51 return True;
52 ELSE
53 return False;
54 END IF;
55 END ;
56
57
58
59
60 -------------------------------------------------------------------------------
61 -- Procedure: check_ccr_rule
62 -- Purpose: this procedure updates the bank account id and auth
63 -- code
64 -- In Parameters: p_chr_id the contract id
65 -- Out Parameters: x_return_status standard return status
66 -----------------------------------------------------------------------------
67 Procedure check_CCR_rule
68 (p_chr_id IN Number,
69 x_return_status OUT NOCOPY Varchar2)
70 IS
71 l_return_status Varchar2(1);
72 l_msg_count Number;
73 l_msg_data Varchar2(2000);
74 l_api_version Number := 1;
75 l_init_msg_list Varchar2(1) := 'F';
76
77 Cursor get_ccr(p_chr_id IN NUMBER) is
78 select
79 id
80 ,chr_id
81 ,cc_no
82 ,cc_expiry_date
83 ,cc_bank_acct_id
84 ,cc_auth_code
85 ,object_version_number
86 from oks_k_headers_b
87 where chr_id=p_chr_id;
88
89 get_ccr_rec get_ccr%rowtype;
90 l_khrv_tbl OKS_KHR_PVT.khrv_tbl_type;
91 x_khrv_tbl OKS_KHR_PVT.khrv_tbl_type;
92 l_error_tbl OKC_API.ERROR_TBL_TYPE;
93
94 Begin
95 l_return_status := OKC_API.G_RET_STS_SUCCESS;
96
97 For get_ccr_rec In get_ccr(p_chr_id)
98 Loop
99
100 IF get_ccr_rec.cc_bank_acct_id is not null then
101
102 l_khrv_tbl.delete;
103
104 l_khrv_tbl(1).ID :=get_ccr_rec.id;
105 l_khrv_tbl(1).chr_id :=get_ccr_rec.chr_id;
106 l_khrv_tbl(1).CC_BANK_ACCT_ID :=NULL;
107 l_khrv_tbl(1).CC_AUTH_CODE :=NULL;
108 l_khrv_tbl(1).OBJECT_VERSION_NUMBER :=get_ccr_rec.OBJECT_VERSION_NUMBER;
109 l_khrv_tbl(1).CREATED_BY :=OKC_API.G_MISS_NUM;
110 l_khrv_tbl(1).CREATION_DATE :=OKC_API.G_MISS_DATE;
111 l_khrv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
112 l_khrv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
113 l_khrv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
114
115 OKS_CONTRACT_HDR_PUB.update_header (
116 p_api_version => l_api_version,
117 p_init_msg_list => OKC_API.G_FALSE,
118 x_return_status => l_return_status,
119 x_msg_count => l_msg_count,
120 x_msg_data => l_msg_data,
121 p_khrv_tbl => l_khrv_tbl,
122 x_khrv_tbl => x_khrv_tbl,
123 p_validate_yn => 'N');
124
125 END IF;
126
127 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
128 RAISE G_EXCEPTION_HALT_VALIDATION;
129 END IF;
130
131 End Loop; --For get_line_id_rec In get_line_id_csr
132
133 x_return_status := l_return_status;
134
135 EXCEPTION
136 WHEN G_EXCEPTION_HALT_VALIDATION THEN
137 x_return_status := l_return_status;
138 WHEN Others THEN
139 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
140 OKC_API.set_message
141 (
142 G_APP_NAME,
143 G_UNEXPECTED_ERROR,
144 G_SQLCODE_TOKEN,
145 SQLCODE,
146 G_SQLERRM_TOKEN,
147 SQLERRM
148 );
149
150
151 End check_CCR_rule;
152
153 -------------------------------------------------------------------------------
154 -- Procedure: check_ccr_rule_line
155 -- Purpose: this procedure updates the bank account id and auth
156 -- code
157 -- In Parameters: p_chr_id the contract id
158 -- In Parameters: p_cle_id the lineid
159 -- Out Parameters: x_return_status standard return status
160 -----------------------------------------------------------------------------
161 Procedure check_CCR_rule_line
162 (p_chr_id IN Number,
163 p_cle_id IN Number,
164 x_return_status OUT NOCOPY Varchar2)
165 IS
166 l_return_status Varchar2(1);
167 l_msg_count Number;
168 l_msg_data Varchar2(2000);
169 l_api_version Number := 1;
170 l_init_msg_list Varchar2(1) := 'F';
171
172 Cursor get_ccr(p_chr_id IN NUMBER,p_cle_id IN NUMBER) is
173 select
174 id
175 ,cle_id
176 ,dnz_chr_id
177 ,cc_no
178 ,cc_expiry_date
179 ,cc_bank_acct_id
180 ,cc_auth_code
181 ,object_version_number
182 from oks_k_lines_b
183 where dnz_chr_id=p_chr_id
184 and cle_id =p_cle_id;
185
186 get_ccr_rec get_ccr%rowtype;
187 l_klnv_tbl oks_kln_pvt.klnv_tbl_type;
188 x_klnv_tbl oks_kln_pvt.klnv_tbl_type;
189 l_error_tbl OKC_API.ERROR_TBL_TYPE;
190
191 Begin
192 l_return_status := OKC_API.G_RET_STS_SUCCESS;
193
194 For get_ccr_rec In get_ccr(p_chr_id,p_cle_id)
195 Loop
196 IF get_ccr_rec.cc_bank_acct_id is not null then
197 l_klnv_tbl(1).id := get_ccr_rec.id;
198 l_klnv_tbl(1).dnz_chr_id := get_ccr_rec.dnz_chr_id;
199 l_klnv_tbl(1).CC_BANK_ACCT_ID :=NULL;
200 l_klnv_tbl(1).CC_AUTH_CODE :=NULL;
201 l_klnv_tbl(1).object_version_number := get_ccr_rec.object_version_number;
202 l_klnv_tbl(1).CREATED_BY :=OKC_API.G_MISS_NUM;
203 l_klnv_tbl(1).CREATION_DATE :=OKC_API.G_MISS_DATE;
204 l_klnv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
205 l_klnv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
206 l_klnv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
207
208 OKS_CONTRACT_LINE_PUB.update_line (
209 p_api_version => l_api_version,
210 p_init_msg_list => OKC_API.G_FALSE,
211 x_return_status => l_return_status,
212 x_msg_count => l_msg_count,
213 x_msg_data => l_msg_data,
214 p_klnv_tbl => l_klnv_tbl,
215 x_klnv_tbl => x_klnv_tbl,
216 p_validate_yn => 'N');
217 END IF;
218
219 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
220 RAISE G_EXCEPTION_HALT_VALIDATION;
221 END IF;
222
223 End Loop; --For get_line_id_rec In get_line_id_csr
224
225 x_return_status := l_return_status;
226
227 EXCEPTION
228 WHEN G_EXCEPTION_HALT_VALIDATION THEN
229 x_return_status := l_return_status;
230 WHEN Others THEN
231 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
232 OKC_API.set_message
233 (
234 G_APP_NAME,
235 G_UNEXPECTED_ERROR,
236 G_SQLCODE_TOKEN,
237 SQLCODE,
238 G_SQLERRM_TOKEN,
239 SQLERRM
240 );
241
242 End check_CCR_rule_line;
243
244 FUNCTION chk_coverage_Exists(p_cle_id IN NUMBER) return BOOLEAN IS
245
246 CURSOR l_coverage_csr(p_cle_id NUMBER) IS
247 SELECT id from okc_k_lines_b
248 WHERE cle_id = p_cle_id
249 and lse_id in (2,20);
250
251 CURSOR check_oks_csr(p_line_id number) is
252 select count(*) from oks_k_lines_b
253 where cle_id=p_line_id;
254
255 l_coverage_count NUMBER;
256 l_line_id NUMBER;
257
258 BEGIN
259 OPEN l_coverage_csr(p_cle_id);
260 FETCH l_coverage_csr INTO l_line_id;
261 close l_coverage_csr;
262 OPEN check_oks_csr(l_line_id);
263 FETCH check_oks_csr into l_coverage_count;
264 IF check_oks_csr%NOTFOUND THEN
265 CLOSE check_oks_csr;
266
267 ELSE
268 CLOSE check_oks_csr;
269
270
271 END IF;
272 IF l_coverage_count>0 then
273 return True;
274 else
275 return False;
276 END IF;
277 END ;
278 -------------------------------------------------------------------------------
279 -- Procedure: copy_hdr_attr
280 -- Purpose: This procedure copies header attributes from the old cont-- ract and creates a row in the OKS_K_HEADERS_B table
281 -- In Parameters: p_chr_id the contract id
282 -- p_new_chr_id new contract id
283 -- Out Parameters: x_return_status standard return status
284 -----------------------------------------------------------------------------
285
286 Procedure copy_hdr_attr
287 (p_chr_id IN NUMBER,
288 p_new_chr_id IN NUMBER,
289 p_duration_match IN VARCHAR2,
290 p_renew_ref_YN IN VARCHAR2 DEFAULT 'N',
291 x_return_status OUT NOCOPY VARCHAR2)
292 IS
293 ctr NUMBER :=1;
294 l_return_status Varchar2(1);
295 l_msg_count Number;
296 l_msg_data Varchar2(2000);
297 l_api_version Number := 1;
298 l_init_msg_list Varchar2(1) := 'F';
299 l_khrv_tbl OKS_KHR_PVT.khrv_tbl_type;
300 x_khrv_tbl OKS_KHR_PVT.khrv_tbl_type;
301
302 CURSOR get_hdr_attr_csr (p_chr_id NUMBER) IS
303 SELECT *
304 FROM oks_k_headers_b
305 WHERE chr_id = p_chr_id;
306
307 l_api_name CONSTANT VARCHAR2(30) := 'copy_hdr_attr';
308 l_module_name VARCHAR2(256) := G_APP_NAME ||'.plsql.' || G_PKG_NAME || '.' || l_api_name;
309
310 -- 8/5/2005 hkamdar R12 Partial Period Project
311 -- Added new cursor to fetch the org id for the contract
312 CURSOR get_org_id_csr (p_new_chr_id NUMBER) IS
313 SELECT org_id
314 FROM okc_k_headers_b
315 WHERE id = p_new_chr_id;
316
317 l_period_type varchar2(30):=null;
318 l_period_start varchar2(30):=null;
319 l_price_uom varchar2(30):=null;
320 l_org_id number;
321 -- End hkamdar R12
322
323
324 BEGIN
325 l_return_status := OKC_API.G_RET_STS_SUCCESS;
326 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
327 FND_LOG.string(FND_LOG.level_procedure
328 ,'OKS.plsql.OKS_COPY_CONTRACT_PVT.'||l_module_name||'.Begin'
329 ,'Entered OKS_SETUP_UTIL_PUB.copy_hdr_attr'
330 ||', p_chr_id ='||p_chr_id
331 ||', p_new_chr_id='||p_new_chr_id
332 ||', p_renew_ref_YN='||p_renew_ref_YN
333 );
334 END IF;
335
336 l_khrv_tbl.DELETE;
337 x_khrv_tbl.DELETE;
338
339 -- 8/5/2005 hkamdar R12 Partial Period Project
340 -- get GCD values
341 open get_org_id_csr(p_new_chr_id);
342 fetch get_org_id_csr into l_org_id;
343 close get_org_id_csr;
344
345 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
346 FND_LOG.string(FND_LOG.level_statement
347 ,'OKS.plsql.OKS_COPY_CONTRACT_PVT.'||l_module_name
348 ,'Before call to OKS_RENEW_UTIL_PUB.get_period_defaults'
349 ||', l_org_id ='||l_org_id
350 );
351 END IF;
352
353 OKS_RENEW_UTIL_PUB.get_period_defaults
354 (p_hdr_id => NULL,-- passing NULL to fetch values from GCD
355 p_org_id => l_org_id,
356 x_period_type => l_period_type,
357 x_period_start => l_period_start,
358 x_price_uom => l_price_uom,
359 x_return_status => l_return_status);
360
361 --Updating Global Variables with GCD Defaults
362 G_GCD_PERIOD_START := l_period_start;
363 G_GCD_PERIOD_TYPE := l_period_type;
364 G_GCD_PRICE_UOM := l_price_uom;
365
366 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
367 FND_LOG.string(FND_LOG.level_statement
368 ,'OKS.plsql.OKS_COPY_CONTRACT_PVT.'||l_module_name
369 ,'after call to OKS_RENEW_UTIL_PUB.get_period_defaults'
370 ||', l_period_type ='||l_period_type
371 ||', l_period_start ='||l_period_start
372 ||', l_price_uom ='||l_price_uom
373 );
374 END IF;
375
376 -- End hkamdar R12 Partial Period Project
377
378 --get header record and copy
379 FOR get_hdr_attr_rec IN get_hdr_attr_csr (p_chr_id)
380 LOOP
381 l_khrv_tbl(ctr).ID :=OKC_API.G_MISS_NUM;
382 l_khrv_tbl(ctr).CHR_ID :=p_new_chr_id;
383 l_khrv_tbl(ctr).ACCT_RULE_ID :=get_hdr_attr_rec.ACCT_RULE_ID;
384 l_khrv_tbl(ctr).PAYMENT_TYPE :=get_hdr_attr_rec.PAYMENT_TYPE;
385 l_khrv_tbl(ctr).CC_NO :=get_hdr_attr_rec.CC_NO;
386 l_khrv_tbl(ctr).CC_EXPIRY_DATE :=get_hdr_attr_rec.CC_EXPIRY_DATE;
387 l_khrv_tbl(ctr).CC_BANK_ACCT_ID :=get_hdr_attr_rec.CC_BANK_ACCT_ID;
388 l_khrv_tbl(ctr).CC_AUTH_CODE :=get_hdr_attr_rec.CC_AUTH_CODE ;
389 l_khrv_tbl(ctr).COMMITMENT_ID :=get_hdr_attr_rec.COMMITMENT_ID;
390 l_khrv_tbl(ctr).GRACE_DURATION :=get_hdr_attr_rec.GRACE_DURATION;
391 l_khrv_tbl(ctr).GRACE_PERIOD :=get_hdr_attr_rec.GRACE_PERIOD;
392 l_khrv_tbl(ctr).EST_REV_PERCENT :=get_hdr_attr_rec.EST_REV_PERCENT;
393 l_khrv_tbl(ctr).EST_REV_DATE :=get_hdr_attr_rec.EST_REV_DATE;
394 l_khrv_tbl(ctr).TAX_AMOUNT :=get_hdr_attr_rec.TAX_AMOUNT;
395 l_khrv_tbl(ctr).TAX_STATUS :=get_hdr_attr_rec.TAX_STATUS;
396 l_khrv_tbl(ctr).TAX_CODE :=get_hdr_attr_rec.TAX_CODE ;
397 l_khrv_tbl(ctr).TAX_EXEMPTION_ID :=get_hdr_attr_rec.TAX_EXEMPTION_ID;
398 l_khrv_tbl(ctr).BILLING_PROFILE_ID :=get_hdr_attr_rec.BILLING_PROFILE_ID;
399 -------- This field should only be updated by workflow. --------------------
400 --- This interim renewal status will be used to defer the workflow. Finally it will be stamped as 'DRAFT'
401 IF p_renew_ref_YN = 'Y' THEN
402 l_khrv_tbl(ctr).RENEWAL_STATUS := 'PREDRAFT'; --- get_hdr_attr_rec.RENEWAL_STATUS;
403 ELSE
404 l_khrv_tbl(ctr).RENEWAL_STATUS := 'DRAFT'; --- get_hdr_attr_rec.RENEWAL_STATUS;
405 END IF;
406 ----------------------------------------------------------------------------
407 l_khrv_tbl(ctr).ELECTRONIC_RENEWAL_FLAG :=get_hdr_attr_rec.ELECTRONIC_RENEWAL_FLAG;
408
409 l_khrv_tbl(ctr).QUOTE_TO_CONTACT_ID :=get_hdr_attr_rec.QUOTE_TO_CONTACT_ID;
410 l_khrv_tbl(ctr).QUOTE_TO_SITE_ID :=get_hdr_attr_rec.QUOTE_TO_SITE_ID;
411 l_khrv_tbl(ctr).QUOTE_TO_EMAIL_ID :=get_hdr_attr_rec.QUOTE_TO_EMAIL_ID;
412 l_khrv_tbl(ctr).QUOTE_TO_PHONE_ID :=get_hdr_attr_rec.QUOTE_TO_PHONE_ID;
413 l_khrv_tbl(ctr).QUOTE_TO_FAX_ID :=get_hdr_attr_rec.QUOTE_TO_FAX_ID;
414
415 l_khrv_tbl(ctr).RENEWAL_PO_REQUIRED :=get_hdr_attr_rec.RENEWAL_PO_REQUIRED;
416 l_khrv_tbl(ctr).RENEWAL_PO_NUMBER :=get_hdr_attr_rec.RENEWAL_PO_NUMBER;
417 l_khrv_tbl(ctr).RENEWAL_PRICE_LIST :=get_hdr_attr_rec.RENEWAL_PRICE_LIST;
418 l_khrv_tbl(ctr).RENEWAL_PRICING_TYPE :=get_hdr_attr_rec.RENEWAL_PRICING_TYPE;
419 l_khrv_tbl(ctr).RENEWAL_MARKUP_PERCENT :=get_hdr_attr_rec.RENEWAL_MARKUP_PERCENT;
420 l_khrv_tbl(ctr).RENEWAL_GRACE_DURATION :=get_hdr_attr_rec.RENEWAL_GRACE_DURATION ;
421 l_khrv_tbl(ctr).RENEWAL_GRACE_PERIOD :=get_hdr_attr_rec.RENEWAL_GRACE_PERIOD;
422 l_khrv_tbl(ctr).RENEWAL_EST_REV_PERCENT :=get_hdr_attr_rec.RENEWAL_EST_REV_PERCENT;
423 l_khrv_tbl(ctr).RENEWAL_EST_REV_DURATION :=get_hdr_attr_rec.RENEWAL_EST_REV_DURATION;
424 l_khrv_tbl(ctr).RENEWAL_EST_REV_PERIOD :=get_hdr_attr_rec.RENEWAL_EST_REV_PERIOD;
425
426
427 -- Renewal Rules used details will be copied only in the case of Renewal
428 -- it won't be copied in copy contract
429 -- This code is commented out for bug 3566235 as now renewal api is
430 -- now updating renew rules used
431 /*
432 IF p_duration_match='F' THen
433 l_khrv_tbl(ctr).RENEWAL_PRICE_LIST_USED :=get_hdr_attr_rec.RENEWAL_PRICE_LIST_USED;
434 l_khrv_tbl(ctr).RENEWAL_TYPE_USED :=get_hdr_attr_rec.RENEWAL_TYPE_USED;
435 l_khrv_tbl(ctr).RENEWAL_NOTIFICATION_TO :=get_hdr_attr_rec.RENEWAL_NOTIFICATION_TO;
436 l_khrv_tbl(ctr).RENEWAL_PO_USED :=get_hdr_attr_rec.RENEWAL_PO_USED ;
437 l_khrv_tbl(ctr).RENEWAL_PRICING_TYPE_USED :=get_hdr_attr_rec.RENEWAL_PRICING_TYPE_USED;
438 l_khrv_tbl(ctr).RENEWAL_MARKUP_PERCENT_USED :=get_hdr_attr_rec.RENEWAL_MARKUP_PERCENT_USED;
439 l_khrv_tbl(ctr).REV_EST_PERCENT_USED :=get_hdr_attr_rec.REV_EST_PERCENT_USED;
440 l_khrv_tbl(ctr).REV_EST_DURATION_USED :=get_hdr_attr_rec.REV_EST_DURATION_USED;
441 l_khrv_tbl(ctr).REV_EST_PERIOD_USED :=get_hdr_attr_rec.REV_EST_PERIOD_USED;
442 l_khrv_tbl(ctr).BILLING_PROFILE_USED :=get_hdr_attr_rec.BILLING_PROFILE_USED;
443 l_khrv_tbl(ctr).ERN_FLAG_USED_YN :=get_hdr_attr_rec.ERN_FLAG_USED_YN;
444 l_khrv_tbl(ctr).EVN_THRESHOLD_AMT :=get_hdr_attr_rec.EVN_THRESHOLD_AMT;
445 l_khrv_tbl(ctr).EVN_THRESHOLD_CUR :=get_hdr_attr_rec.EVN_THRESHOLD_CUR;
446 l_khrv_tbl(ctr).ERN_THRESHOLD_AMT :=get_hdr_attr_rec.ERN_THRESHOLD_AMT ;
447 l_khrv_tbl(ctr).ERN_THRESHOLD_CUR :=get_hdr_attr_rec.ERN_THRESHOLD_CUR;
448 l_khrv_tbl(ctr).RENEWAL_GRACE_DURATION_USED :=get_hdr_attr_rec.RENEWAL_GRACE_DURATION_USED;
449 l_khrv_tbl(ctr).RENEWAL_GRACE_PERIOD_USED :=get_hdr_attr_rec.RENEWAL_GRACE_PERIOD_USED;
450 ELSE*/
451 l_khrv_tbl(ctr).RENEWAL_PRICE_LIST_USED :=null;
452 l_khrv_tbl(ctr).RENEWAL_TYPE_USED :=null;
453 l_khrv_tbl(ctr).RENEWAL_NOTIFICATION_TO :=null;
454 l_khrv_tbl(ctr).RENEWAL_PO_USED :=null;
455 l_khrv_tbl(ctr).RENEWAL_PRICING_TYPE_USED :=null;
456 l_khrv_tbl(ctr).RENEWAL_MARKUP_PERCENT_USED :=null;
457 l_khrv_tbl(ctr).REV_EST_PERCENT_USED :=null;
458 l_khrv_tbl(ctr).REV_EST_DURATION_USED :=null;
459 l_khrv_tbl(ctr).REV_EST_PERIOD_USED :=null;
460 l_khrv_tbl(ctr).BILLING_PROFILE_USED :=null;
461 l_khrv_tbl(ctr).ERN_FLAG_USED_YN :=null;
462 l_khrv_tbl(ctr).EVN_THRESHOLD_AMT :=null;
463 l_khrv_tbl(ctr).EVN_THRESHOLD_CUR :=null;
464 l_khrv_tbl(ctr).ERN_THRESHOLD_AMT :=null;
465 l_khrv_tbl(ctr).ERN_THRESHOLD_CUR :=null;
466 l_khrv_tbl(ctr).RENEWAL_GRACE_DURATION_USED :=null;
467 l_khrv_tbl(ctr).RENEWAL_GRACE_PERIOD_USED :=null;
468 --END IF;
469 l_khrv_tbl(ctr).INV_TRX_TYPE :=get_hdr_attr_rec.INV_TRX_TYPE;
470 l_khrv_tbl(ctr).INV_PRINT_PROFILE :=get_hdr_attr_rec.INV_PRINT_PROFILE;
471
472 --R12 Renewal Requirement--
473 IF (p_renew_ref_YN = 'Y') THEN --RENEW case
474 If (get_hdr_attr_rec.AR_INTERFACE_YN = 'R') then
475 l_khrv_tbl(ctr).AR_INTERFACE_YN := 'Y';
476 Else
477 l_khrv_tbl(ctr).AR_INTERFACE_YN := get_hdr_attr_rec.AR_INTERFACE_YN;
478 End If;
479 ELSIF (p_renew_ref_YN = 'N') THEN --COPY case
480 l_khrv_tbl(ctr).AR_INTERFACE_YN := get_hdr_attr_rec.AR_INTERFACE_YN;
481 END IF;
482
483 l_khrv_tbl(ctr).HOLD_BILLING :=get_hdr_attr_rec.HOLD_BILLING;
484 l_khrv_tbl(ctr).SUMMARY_TRX_YN :=get_hdr_attr_rec.SUMMARY_TRX_YN;
485 l_khrv_tbl(ctr).SERVICE_PO_NUMBER :=get_hdr_attr_rec.SERVICE_PO_NUMBER;
486 l_khrv_tbl(ctr).SERVICE_PO_REQUIRED :=get_hdr_attr_rec.SERVICE_PO_REQUIRED;
487 l_khrv_tbl(ctr).BILLING_SCHEDULE_TYPE :=get_hdr_attr_rec.BILLING_SCHEDULE_TYPE;
488 l_khrv_tbl(ctr).OBJECT_VERSION_NUMBER :=OKC_API.G_MISS_NUM;
489 l_khrv_tbl(ctr).SECURITY_GROUP_ID :=get_hdr_attr_rec.SECURITY_GROUP_ID;
490 l_khrv_tbl(ctr).REQUEST_ID :=get_hdr_attr_rec.REQUEST_ID;
491 l_khrv_tbl(ctr).CREATED_BY :=OKC_API.G_MISS_NUM;
492 l_khrv_tbl(ctr).CREATION_DATE :=OKC_API.G_MISS_DATE;
493 l_khrv_tbl(ctr).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
494 l_khrv_tbl(ctr).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
495 l_khrv_tbl(ctr).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
496
497 ----New columns added in R12-------
498 l_khrv_tbl(ctr).CC_NO:= get_hdr_attr_rec.CC_NO;
499 l_khrv_tbl(ctr).CC_EXPIRY_DATE:= get_hdr_attr_rec.CC_EXPIRY_DATE;
500 l_khrv_tbl(ctr).CC_BANK_ACCT_ID:= get_hdr_attr_rec.CC_BANK_ACCT_ID;
501 l_khrv_tbl(ctr).CC_AUTH_CODE:= get_hdr_attr_rec.CC_AUTH_CODE;
502 l_khrv_tbl(ctr).ELECTRONIC_RENEWAL_FLAG:= get_hdr_attr_rec.ELECTRONIC_RENEWAL_FLAG;
503
504 --Changed the logic for GCD defaults to only use the defaulting mechanism if the corresponding values
505 --in the source contract are null
506
507 If (get_hdr_attr_rec.period_type IS NOT NULL) then
508 l_khrv_tbl(ctr).period_type := get_hdr_attr_rec.period_type;
509 Else
510 l_khrv_tbl(ctr).period_type := l_period_type;
511 End If;
512
513 If (get_hdr_attr_rec.period_start IS NOT NULL) then
514 l_khrv_tbl(ctr).period_start := get_hdr_attr_rec.period_start;
515 Else
516 l_khrv_tbl(ctr).period_start := l_period_start;
517 End If;
518
519 If (get_hdr_attr_rec.price_uom IS NOT NULL) then
520 l_khrv_tbl(ctr).price_uom := get_hdr_attr_rec.price_uom;
521 Else
522 l_khrv_tbl(ctr).price_uom := l_price_uom;
523 End If;
524
525 /*
526 -- 8/5/2005 hkamdar R12 Partial Period Project
527 -- Storing GCD defaults in l_khrv_tbl table to be used to create the Header.
528 l_khrv_tbl(ctr).Period_Type := l_period_type;
529 l_khrv_tbl(ctr).Period_Start := l_period_start;
530 l_khrv_tbl(ctr).Price_uom := l_price_uom;
531 -- End hkamdar R12 Partial Period Project
532 */
533
534 l_khrv_tbl(ctr).trxn_extension_id := get_hdr_attr_rec.trxn_extension_id;
535 l_khrv_tbl(ctr).person_party_id := get_hdr_attr_rec.person_party_id;
536 l_khrv_tbl(ctr).tax_classification_code := get_hdr_attr_rec.tax_classification_code;
537 l_khrv_tbl(ctr).exempt_certificate_number := get_hdr_attr_rec.exempt_certificate_number;
538 l_khrv_tbl(ctr).exempt_reason_code := get_hdr_attr_rec.exempt_reason_code;
539
540 IF (p_renew_ref_YN = 'Y') THEN --RENEW case
541 l_khrv_tbl(ctr).renewal_comment := OKC_API.G_MISS_CHAR;
542 ELSE
543 l_khrv_tbl(ctr).renewal_comment := get_hdr_attr_rec.renewal_comment;
544 END IF;
545
546 ctr := ctr+1;
547 END LOOP;
548 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
549 FND_LOG.string(FND_LOG.level_statement
550 ,l_module_name
551 ,'After get_hdr_attr_csr LOOP'
552 ||'l_khrv_tbl.COUNT = '||l_khrv_tbl.COUNT
553 );
554 END IF;
555
556 IF l_khrv_tbl.COUNT >0 THEN
557
558 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
559 FND_LOG.string(FND_LOG.level_statement
560 ,l_module_name||'.OKS_CONTRACT_HDR_PUB.create_header'
561 ,'Before call to OKS_CONTRACT_HDR_PUB.create_header'
562 );
563 END IF;
564
565 OKS_CONTRACT_HDR_PUB.create_header (
566 p_api_version => l_api_version,
567 p_init_msg_list => OKC_API.G_FALSE,
568 x_return_status => l_return_status,
569 x_msg_count => l_msg_count,
570 x_msg_data => l_msg_data,
571 p_khrv_tbl => l_khrv_tbl,
572 x_khrv_tbl => x_khrv_tbl,
573 p_validate_yn => 'N');
574
575 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
576 FND_LOG.string(FND_LOG.level_statement
577 ,l_module_name||'.OKS_CONTRACT_HDR_PUB.create_header'
578 ,'After call to OKS_CONTRACT_HDR_PUB.create_header'
579 ||', x_return_status='||l_return_status
580 );
581 END IF;
582
583 END IF;
584
585 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
586 RAISE G_EXCEPTION_HALT_VALIDATION;
587 END IF;
588
589 x_return_status := l_return_status;
590
591 EXCEPTION
592 WHEN G_EXCEPTION_HALT_VALIDATION THEN
593 x_return_status := l_return_status;
594 WHEN Others THEN
595 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
596 OKC_API.set_message
597 (
598 G_APP_NAME,
599 G_UNEXPECTED_ERROR,
600 G_SQLCODE_TOKEN,
601 SQLCODE,
602 G_SQLERRM_TOKEN,
603 SQLERRM
604 );
605
606
607 END copy_hdr_attr;
608
609 -------------------------------------------------------------------------------
610 -- Procedure: copy_lines_attr
611 -- Purpose: This procedure copies old line attributes and creates
612 -- respective new rows in the OKS_K_LINES_B table.This proc
613 -- also checks for the date_terminated. If this is not null
614 -- then termination related fields are not copied.
615 -- In Parameters: p_new_chr_id the contract id
616 -- p_new_cle_id new line id passed
617 -- p_cle_id old line id passed
618 -- Out Parameters: x_return_status standard return status
619 -----------------------------------------------------------------------------
620
621 Procedure copy_lines_attr
622 (p_cle_id IN NUMBER,
623 p_new_cle_id IN NUMBER,
624 p_new_chr_id IN NUMBER,
625 p_do_copy IN Boolean,
626 x_return_status OUT NOCOPY VARCHAR2)
627 IS
628 ctr NUMBER :=1;
629 l_return_status Varchar2(1);
630 l_msg_count Number;
631 l_msg_data Varchar2(2000);
632 l_api_version Number := 1;
633 l_init_msg_list Varchar2(1) := 'F';
634
635 l_klnv_tbl oks_kln_pvt.klnv_tbl_type;
636 x_klnv_tbl oks_kln_pvt.klnv_tbl_type;
637
638 CURSOR get_lines_attr_csr (p_cle_id NUMBER) IS
639 SELECT *
640 FROM oks_k_lines_v
641 WHERE cle_id = p_cle_id;
642
643 CURSOR get_lines_details_csr (p_cle_id NUMBER) IS
644 SELECT date_terminated, price_list_id
645 FROM okc_k_lines_b
646 WHERE id = p_cle_id;
647 -- Added for price lock
648 cursor get_contract_number(l_chr_id number) is
649 select contract_number
650 from okc_k_headers_b
651 where id = l_chr_id;
652
653 l_contract_number VARCHAR2(120);
654 l_old_price_list_id number;
655 l_new_price_list_id number;
656 l_locked_price_list_id number;
657 l_locked_price_list_line_id number;
658 --- End added for price lock
659 l_date_terminated Date;
660
661 BEGIN
662 l_return_status := OKC_API.G_RET_STS_SUCCESS;
663 l_klnv_tbl.DELETE;
664 x_klnv_tbl.DELETE;
665
666 FOR get_lines_attr_rec IN get_lines_attr_csr (p_cle_id)
667 LOOP
668 OPEN get_lines_details_csr(P_CLE_ID);
669 FETCH get_lines_details_csr INTO l_date_terminated, l_old_price_list_id;
670 CLOSE get_lines_details_csr;
671
672 l_klnv_tbl(ctr).ID :=OKC_API.G_MISS_NUM;
673 l_klnv_tbl(ctr).CLE_ID :=p_new_cle_id;
674 l_klnv_tbl(ctr).DNZ_CHR_ID :=p_new_chr_id;
675 l_klnv_tbl(ctr).DISCOUNT_LIST :=get_lines_attr_rec.DISCOUNT_LIST ;
676 l_klnv_tbl(ctr).ACCT_RULE_ID :=get_lines_attr_rec.ACCT_RULE_ID;
677 l_klnv_tbl(ctr).PAYMENT_TYPE :=get_lines_attr_rec.PAYMENT_TYPE;
678 l_klnv_tbl(ctr).CC_NO :=get_lines_attr_rec.CC_NO;
679 l_klnv_tbl(ctr).CC_EXPIRY_DATE :=get_lines_attr_rec.CC_EXPIRY_DATE;
680 l_klnv_tbl(ctr).CC_BANK_ACCT_ID :=get_lines_attr_rec.CC_BANK_ACCT_ID;
681 l_klnv_tbl(ctr).CC_AUTH_CODE :=get_lines_attr_rec.CC_AUTH_CODE;
682 l_klnv_tbl(ctr).COMMITMENT_ID :=get_lines_attr_rec.COMMITMENT_ID;
683 -- Nulled for bug # 3845954
684 l_klnv_tbl(ctr).LOCKED_PRICE_LIST_ID:= null; --get_lines_attr_rec.LOCKED_PRICE_LIST_ID;
685 -- Added for bug # 3625365
686 l_klnv_tbl(ctr).LOCKED_PRICE_LIST_LINE_ID := null; --get_lines_attr_rec.LOCKED_PRICE_LIST_LINE_ID;
687 l_klnv_tbl(ctr).prorate := get_lines_attr_rec.prorate; -- prorate needs to be there bug # 3880955
688 l_klnv_tbl(ctr).break_uom := null;
689 ----------------------------------
690 -- Copies the lock in case of copy
691 If p_do_copy and get_lines_attr_rec.LOCKED_PRICE_LIST_ID is not null Then
692 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
693 p_perf_msg =>'do copy is true');
694 Open get_contract_number(p_new_chr_id);
695 Fetch get_contract_number into l_contract_number;
696 Close get_contract_number;
697 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
698 p_perf_msg =>'get_lines_attr_rec.LOCKED_PRICE_LIST_LINE_ID: ' || get_lines_attr_rec.LOCKED_PRICE_LIST_LINE_ID);
699
700 -- MKS Commented out.. Need to put back in once QP patch is ready.
701 /*
702 QP_LOCK_PRICELIST_GRP.Lock_Price(p_source_list_line_id => get_lines_attr_rec.LOCKED_PRICE_LIST_LINE_ID,
703 p_list_source_code => 'OKS',
704 p_orig_system_header_ref => l_contract_number,
705 x_locked_price_list_id => l_locked_price_list_id,
706 x_locked_list_line_id => l_locked_price_list_line_id,
707 x_return_status => l_return_status,
708 x_msg_count => l_msg_count,
709 x_msg_data => l_msg_data);
710
711 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
712 RAISE G_EXCEPTION_HALT_VALIDATION;
713 END IF;
714 */
715 l_klnv_tbl(ctr).LOCKED_PRICE_LIST_ID:= l_locked_price_list_id;
716 l_klnv_tbl(ctr).LOCKED_PRICE_LIST_LINE_ID := l_locked_price_list_line_id;
717 l_klnv_tbl(ctr).break_uom := get_lines_attr_rec.break_uom;
718
719 End If;
720 ------------------------
721 l_klnv_tbl(ctr).USAGE_EST_YN :=get_lines_attr_rec.USAGE_EST_YN;
722 l_klnv_tbl(ctr).USAGE_EST_METHOD :=get_lines_attr_rec.USAGE_EST_METHOD;
723 l_klnv_tbl(ctr).USAGE_EST_START_DATE:=get_lines_attr_rec.USAGE_EST_START_DATE;
724 l_klnv_tbl(ctr).TERMN_METHOD :=get_lines_attr_rec.TERMN_METHOD;
725 /*
726 l_klnv_tbl(ctr).UBT_AMOUNT :=get_lines_attr_rec.UBT_AMOUNT;
727 l_klnv_tbl(ctr).CREDIT_AMOUNT :=get_lines_attr_rec.CREDIT_AMOUNT;
728 l_klnv_tbl(ctr).SUPPRESSED_CREDIT :=get_lines_attr_rec.SUPPRESSED_CREDIT;
729 l_klnv_tbl(ctr).OVERRIDE_AMOUNT :=get_lines_attr_rec.OVERRIDE_AMOUNT;
730 */
731 l_klnv_tbl(ctr).CUST_PO_NUMBER_REQ_YN :=get_lines_attr_rec.CUST_PO_NUMBER_REQ_YN;
732 l_klnv_tbl(ctr).CUST_PO_NUMBER :=get_lines_attr_rec.CUST_PO_NUMBER;
733 l_klnv_tbl(ctr).GRACE_DURATION :=get_lines_attr_rec.GRACE_DURATION;
734 l_klnv_tbl(ctr).GRACE_PERIOD :=get_lines_attr_rec.GRACE_PERIOD;
735 l_klnv_tbl(ctr).INV_PRINT_FLAG :=get_lines_attr_rec.INV_PRINT_FLAG;
736 l_klnv_tbl(ctr).PRICE_UOM :=get_lines_attr_rec.PRICE_UOM;
737 l_klnv_tbl(ctr).TAX_AMOUNT :=get_lines_attr_rec.TAX_AMOUNT;
738 l_klnv_tbl(ctr).TAX_INCLUSIVE_YN :=get_lines_attr_rec.TAX_INCLUSIVE_YN;
739 l_klnv_tbl(ctr).TAX_STATUS :=get_lines_attr_rec.TAX_STATUS;
740 l_klnv_tbl(ctr).TAX_CODE :=get_lines_attr_rec.TAX_CODE;
741 l_klnv_tbl(ctr).TAX_EXEMPTION_ID :=get_lines_attr_rec.TAX_EXEMPTION_ID;
742 l_klnv_tbl(ctr).IB_TRANS_TYPE :=get_lines_attr_rec.IB_TRANS_TYPE;
743 l_klnv_tbl(ctr).IB_TRANS_DATE :=get_lines_attr_rec.IB_TRANS_DATE;
744 l_klnv_tbl(ctr).PROD_PRICE :=get_lines_attr_rec.PROD_PRICE;
745 l_klnv_tbl(ctr).SERVICE_PRICE :=get_lines_attr_rec.SERVICE_PRICE;
746 l_klnv_tbl(ctr).CLVL_LIST_PRICE :=get_lines_attr_rec.CLVL_LIST_PRICE;
747 l_klnv_tbl(ctr).CLVL_QUANTITY :=get_lines_attr_rec.CLVL_QUANTITY;
748 l_klnv_tbl(ctr).CLVL_EXTENDED_AMT :=get_lines_attr_rec.CLVL_EXTENDED_AMT;
749 l_klnv_tbl(ctr).CLVL_UOM_CODE :=get_lines_attr_rec.CLVL_UOM_CODE;
750 l_klnv_tbl(ctr).TOPLVL_OPERAND_CODE :=get_lines_attr_rec.TOPLVL_OPERAND_CODE;
751 l_klnv_tbl(ctr).TOPLVL_OPERAND_VAL :=get_lines_attr_rec.TOPLVL_OPERAND_VAL;
752 l_klnv_tbl(ctr).TOPLVL_QUANTITY :=get_lines_attr_rec.TOPLVL_QUANTITY;
753 l_klnv_tbl(ctr).TOPLVL_UOM_CODE :=get_lines_attr_rec.TOPLVL_UOM_CODE;
754 l_klnv_tbl(ctr).TOPLVL_ADJ_PRICE :=get_lines_attr_rec.TOPLVL_ADJ_PRICE;
755 l_klnv_tbl(ctr).TOPLVL_PRICE_QTY :=get_lines_attr_rec.TOPLVL_PRICE_QTY;
756 l_klnv_tbl(ctr).AVERAGING_INTERVAL :=get_lines_attr_rec.AVERAGING_INTERVAL ;
757 l_klnv_tbl(ctr).SETTLEMENT_INTERVAL :=get_lines_attr_rec.SETTLEMENT_INTERVAL;
758 l_klnv_tbl(ctr).MINIMUM_QUANTITY :=get_lines_attr_rec.MINIMUM_QUANTITY ;
759 l_klnv_tbl(ctr).DEFAULT_QUANTITY :=get_lines_attr_rec.DEFAULT_QUANTITY;
760 l_klnv_tbl(ctr).AMCV_FLAG :=get_lines_attr_rec.AMCV_FLAG;
761 l_klnv_tbl(ctr).FIXED_QUANTITY :=get_lines_attr_rec.FIXED_QUANTITY;
762 l_klnv_tbl(ctr).USAGE_DURATION :=get_lines_attr_rec.USAGE_DURATION;
763 l_klnv_tbl(ctr).USAGE_PERIOD :=get_lines_attr_rec.USAGE_PERIOD;
764 l_klnv_tbl(ctr).LEVEL_YN :=get_lines_attr_rec.LEVEL_YN;
765 l_klnv_tbl(ctr).USAGE_TYPE :=get_lines_attr_rec.USAGE_TYPE;
766 l_klnv_tbl(ctr).UOM_QUANTIFIED :=get_lines_attr_rec.UOM_QUANTIFIED;
767 l_klnv_tbl(ctr).BASE_READING :=get_lines_attr_rec.BASE_READING;
768 l_klnv_tbl(ctr).COUNTER_VALUE_ID :=get_lines_attr_rec.COUNTER_VALUE_ID;
769 l_klnv_tbl(ctr).BILLING_SCHEDULE_TYPE :=get_lines_attr_rec.BILLING_SCHEDULE_TYPE;
770 l_klnv_tbl(ctr).COVERAGE_TYPE :=get_lines_attr_rec.COVERAGE_TYPE;
771 l_klnv_tbl(ctr).EXCEPTION_COV_ID :=get_lines_attr_rec.EXCEPTION_COV_ID;
772 l_klnv_tbl(ctr).LIMIT_UOM_QUANTIFIED:=get_lines_attr_rec.LIMIT_UOM_QUANTIFIED;
773 l_klnv_tbl(ctr).DISCOUNT_AMOUNT :=get_lines_attr_rec.DISCOUNT_AMOUNT;
774 l_klnv_tbl(ctr).DISCOUNT_PERCENT :=get_lines_attr_rec.DISCOUNT_PERCENT;
775 l_klnv_tbl(ctr).OFFSET_DURATION :=get_lines_attr_rec.OFFSET_DURATION;
776 l_klnv_tbl(ctr).OFFSET_PERIOD :=get_lines_attr_rec.OFFSET_PERIOD;
777 l_klnv_tbl(ctr).INCIDENT_SEVERITY_ID:=get_lines_attr_rec.INCIDENT_SEVERITY_ID;
778 l_klnv_tbl(ctr).PDF_ID :=get_lines_attr_rec.PDF_ID;
779 l_klnv_tbl(ctr).WORK_THRU_YN :=get_lines_attr_rec.WORK_THRU_YN;
780 l_klnv_tbl(ctr).REACT_ACTIVE_YN :=get_lines_attr_rec.REACT_ACTIVE_YN;
781 l_klnv_tbl(ctr).TRANSFER_OPTION :=get_lines_attr_rec.TRANSFER_OPTION;
782 l_klnv_tbl(ctr).PROD_UPGRADE_YN :=get_lines_attr_rec.PROD_UPGRADE_YN;
783 l_klnv_tbl(ctr).INHERITANCE_TYPE :=get_lines_attr_rec.INHERITANCE_TYPE;
784 l_klnv_tbl(ctr).PM_PROGRAM_ID :=get_lines_attr_rec.PM_PROGRAM_ID;
785 l_klnv_tbl(ctr).PM_CONF_REQ_YN :=get_lines_attr_rec.PM_CONF_REQ_YN;
786 l_klnv_tbl(ctr).PM_SCH_EXISTS_YN :=get_lines_attr_rec.PM_SCH_EXISTS_YN;
787 l_klnv_tbl(ctr).ALLOW_BT_DISCOUNT :=get_lines_attr_rec.ALLOW_BT_DISCOUNT;
788 l_klnv_tbl(ctr).APPLY_DEFAULT_TIMEZONE:=get_lines_attr_rec.APPLY_DEFAULT_TIMEZONE;
789 l_klnv_tbl(ctr).sync_date_install :=get_lines_attr_rec.sync_date_install ;
790 l_klnv_tbl(ctr).sfwt_flag :=get_lines_attr_rec.sfwt_flag ;
791 l_klnv_tbl(ctr).invoice_text :=get_lines_attr_rec.invoice_text;
792 l_klnv_tbl(ctr).ib_trx_details :=get_lines_attr_rec.ib_trx_details ;
793 l_klnv_tbl(ctr).status_text :=get_lines_attr_rec.status_text ;
794 l_klnv_tbl(ctr).react_time_name :=get_lines_attr_rec.react_time_name;
795 l_klnv_tbl(ctr).OBJECT_VERSION_NUMBER:=get_lines_attr_rec.OBJECT_VERSION_NUMBER;
796 l_klnv_tbl(ctr).SECURITY_GROUP_ID :=get_lines_attr_rec.SECURITY_GROUP_ID;
797 l_klnv_tbl(ctr).REQUEST_ID :=get_lines_attr_rec.REQUEST_ID;
798 l_klnv_tbl(ctr).CREATED_BY :=get_lines_attr_rec.CREATED_BY;
799 l_klnv_tbl(ctr).CREATION_DATE :=get_lines_attr_rec.CREATION_DATE;
800 l_klnv_tbl(ctr).LAST_UPDATED_BY :=get_lines_attr_rec.LAST_UPDATED_BY;
801 l_klnv_tbl(ctr).LAST_UPDATE_DATE :=get_lines_attr_rec.LAST_UPDATE_DATE;
802 l_klnv_tbl(ctr).LAST_UPDATE_LOGIN :=get_lines_attr_rec.LAST_UPDATE_LOGIN;
803
804 --Bug 4722452: R12 columns added.
805 l_klnv_tbl(ctr).TRXN_EXTENSION_ID := get_lines_attr_rec.TRXN_EXTENSION_ID;
806 l_klnv_tbl(ctr).TAX_CLASSIFICATION_CODE := get_lines_attr_rec.TAX_CLASSIFICATION_CODE;
807 l_klnv_tbl(ctr).EXEMPT_CERTIFICATE_NUMBER := get_lines_attr_rec.EXEMPT_CERTIFICATE_NUMBER;
808 l_klnv_tbl(ctr).EXEMPT_REASON_CODE := get_lines_attr_rec.EXEMPT_REASON_CODE;
809 --End of Fix for bug 4722452
810
811 l_klnv_tbl(ctr).UBT_AMOUNT := null; --get_lines_attr_rec.UBT_AMOUNT;
812 l_klnv_tbl(ctr).CREDIT_AMOUNT := null; -- get_lines_attr_rec.CREDIT_AMOUNT;
813 l_klnv_tbl(ctr).SUPPRESSED_CREDIT := null; -- get_lines_attr_rec.SUPPRESSED_CREDIT;
814
815 IF l_Date_terminated is NULL Then
816 l_klnv_tbl(ctr).OVERRIDE_AMOUNT :=get_lines_attr_rec.OVERRIDE_AMOUNT;
817 l_klnv_tbl(ctr).FULL_CREDIT :=get_lines_attr_rec.FULL_CREDIT;
818 ELSE
819 l_klnv_tbl(ctr).OVERRIDE_AMOUNT :=NULL;
820 l_klnv_tbl(ctr).FULL_CREDIT :=NULL;
821 END IF;
822
823 ctr := ctr+1;
824 END LOOP;
825 IF l_klnv_tbl.COUNT >0 THEN
826 oks_contract_line_pub.create_line
827 (
828 p_api_version => l_api_version,
829 p_init_msg_list => l_init_msg_list,
830 x_return_status => l_return_status,
831 x_msg_count => l_msg_count,
832 x_msg_data => l_msg_data,
833 p_klnv_tbl => l_klnv_tbl,
834 x_klnv_tbl => x_klnv_tbl,
835 p_validate_yn => 'N' );
836
837 END IF; --IF l_rev_tbl.COUNT >0
838
839 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
840 RAISE G_EXCEPTION_HALT_VALIDATION;
841 END IF;
842
843 x_return_status := l_return_status;
844
845 EXCEPTION
846 WHEN G_EXCEPTION_HALT_VALIDATION THEN
847 x_return_status := l_return_status;
848 WHEN Others THEN
849 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
850 OKC_API.set_message
851 (
852 G_APP_NAME,
853 G_UNEXPECTED_ERROR,
854 G_SQLCODE_TOKEN,
855 SQLCODE,
856 G_SQLERRM_TOKEN,
857 SQLERRM
858 );
859
860
861 END copy_lines_attr;
862
863 -------------------------------------------------------------------------------
864 -- Procedure: Update_hdr_amount
865 -- Purpose: This procedure updates the header amount
866 -- used to pass to OC APIs
867 -- In Parameters: p_chr_id the contract id
868 -- Out Parameters: x_return_status standard return status
869 -----------------------------------------------------------------------------
870
871 Procedure Update_Hdr_Amount
872 (
873 p_api_version IN Number,
874 p_init_msg_list IN Varchar2,
875 p_chr_id IN Number,
876 x_return_status OUT NOCOPY Varchar2,
877 x_msg_count OUT NOCOPY Number,
878 x_msg_data OUT NOCOPY Varchar2
879 )
880 IS
881
882 l_return_status Varchar2(1) := OKC_API.G_RET_STS_SUCCESS;
883 l_api_name CONSTANT VARCHAR2(30) := 'Update_Hdr_Amount';
884 l_api_version Number := 1.0;
885
886 --Contract Header
887 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
888 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
889
890
891 Cursor l_line_csr Is Select Sum(Nvl(PRICE_NEGOTIATED,0))
892 From OKC_K_LINES_B
893 Where dnz_chr_id = p_chr_id And
894 lse_id in (7,8,9,10,11,35,25);
895
896 l_hdr_amount Number;
897
898 BEGIN
899
900 l_return_status := OKC_API.START_ACTIVITY(l_api_name
901 ,p_init_msg_list
902 ,'_PUB'
903 ,x_return_status
904 );
905 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
906 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
907 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
908 RAISE OKC_API.G_EXCEPTION_ERROR;
909 END IF;
910
911 Open l_line_csr;
912 Fetch l_line_csr into l_hdr_amount;
913 Close l_line_csr;
914
915
916 l_chrv_tbl_in(1).id := p_chr_id;
917 l_chrv_tbl_in(1).estimated_amount := l_hdr_amount;
918
919 okc_contract_pub.update_contract_header
920 (
921 p_api_version => l_api_version,
922 p_init_msg_list => p_init_msg_list,
923 x_return_status => x_return_status,
924 x_msg_count => x_msg_count,
925 x_msg_data => x_msg_data,
926 p_chrv_tbl => l_chrv_tbl_in,
927 x_chrv_tbl => l_chrv_tbl_out
928 );
929
930 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
931 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
932 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
933 RAISE OKC_API.G_EXCEPTION_ERROR;
934 END IF;
935
936 OKC_API.END_ACTIVITY(x_msg_count,x_msg_data);
937
938 x_return_status := l_return_status;
939
940 EXCEPTION
941 WHEN OKC_API.G_EXCEPTION_ERROR THEN
942 x_return_status := OKC_API.HANDLE_EXCEPTIONS
943 (l_api_name,
944 G_PKG_NAME,
945 'OKC_API.G_RET_STS_ERROR',
946 x_msg_count,
947 x_msg_data,
948 '_PUB');
949 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
950 x_return_status := OKC_API.HANDLE_EXCEPTIONS
951 (l_api_name,
952 G_PKG_NAME,
953 'OKC_API.G_RET_STS_UNEXP_ERROR',
954 x_msg_count,
955 x_msg_data,
956 '_PUB');
957 WHEN OTHERS THEN
958 x_return_status := OKC_API.HANDLE_EXCEPTIONS
959 (l_api_name,
960 G_PKG_NAME,
961 'OTHERS',
962 x_msg_count,
963 x_msg_data,
964 '_PUB');
965
966 END Update_Hdr_Amount;
967
968 -------------------------------------------------------------------------------
969 -- Procedure: Copy_Hdr_Bill_Sch
970 -- Purpose: This procedure copies the header billing schedule from
971 -- old contract to new contract
972 -- In Parameters: p_chr_id the contract id
973 -- p_new_chr_id new contract id
974 -- Out Parameters: x_return_status standard return status
975 -----------------------------------------------------------------------------
976
977 Procedure Copy_Hdr_Bill_Sch( p_chr_id IN Number,
978 p_new_chr_id IN NUMBER,
979 x_return_status OUT NOCOPY Varchar2)
980 IS
981
982 l_return_status Varchar2(1);
983 l_msg_count Number;
984 l_msg_data Varchar2(2000);
985 l_api_version Number := 1;
986 l_init_msg_list Varchar2(1) := 'F';
987 l_tbl_ctr Number;
988 l_rgp_id Number;
989 l_start_date Date;
990 l_time_value Number;
991 l_adv_arr VARCHAR2(40);
992 l_invoice_rule_id NUMBER;
993 l_rule_slh_id NUMBER;
994 l_sll_exist boolean := false;
995 l_slh_exist boolean := false;
996 l_strlvl_tbl OKS_SLL_PVT.sllv_tbl_type;
997 x_strlvl_tbl OKS_SLL_PVT.sllv_tbl_type;
998 l_error_tbl OKC_API.ERROR_TBL_TYPE;
999
1000 CURSOR strlvl_csr (p_chr_id NUMBER) IS
1001 SELECT ID
1002 ,CHR_ID
1003 ,CLE_ID
1004 ,DNZ_CHR_ID
1005 ,SEQUENCE_NO
1006 ,UOM_CODE
1007 ,START_DATE
1008 ,LEVEL_PERIODS
1009 ,UOM_PER_PERIOD
1010 ,ADVANCE_PERIODS
1011 ,LEVEL_AMOUNT
1012 ,INVOICE_OFFSET_DAYS
1013 ,INTERFACE_OFFSET_DAYS
1014 ,COMMENTS
1015 ,DUE_ARR_YN
1016 ,AMOUNT
1017 ,LINES_DETAILED_YN
1018 FROM oks_stream_levels_b
1019 WHERE chr_id=p_chr_id
1020 ORDER BY SEQUENCE_NO;/*BUG 7450286 */
1021
1022
1023 strlvl_rec strlvl_csr%ROWTYPE;
1024
1025 cursor hdr_csr (p_chr_id Number) Is
1026 SELECT start_date
1027 FROM okc_k_headers_b
1028 WHERE id = p_chr_id;
1029
1030
1031 Begin
1032 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1033 l_tbl_ctr := 0;
1034 Open hdr_csr(p_chr_id);
1035 Fetch hdr_csr into l_start_date;
1036 Close hdr_csr;
1037
1038
1039 l_strlvl_tbl.delete;
1040
1041 For strlvl_rec in strlvl_csr (p_chr_id)
1042 Loop
1043 l_tbl_ctr := l_tbl_ctr + 1;
1044 l_strlvl_tbl(l_tbl_ctr).ID :=OKC_API.G_MISS_NUM;
1045 l_strlvl_tbl(l_tbl_ctr).CHR_ID :=p_new_chr_id;
1046 l_strlvl_tbl(l_tbl_ctr).CLE_ID :=NULL;
1047 l_strlvl_tbl(l_tbl_ctr).DNZ_CHR_ID :=p_new_chr_id;
1048 l_strlvl_tbl(l_tbl_ctr).SEQUENCE_NO :=strlvl_rec.SEQUENCE_NO;
1049 l_strlvl_tbl(l_tbl_ctr).UOM_CODE :=strlvl_rec.UOM_CODE;
1050 l_strlvl_tbl(l_tbl_ctr).START_DATE :=strlvl_rec.START_DATE;
1051 l_strlvl_tbl(l_tbl_ctr).LEVEL_PERIODS:=strlvl_rec.LEVEL_PERIODS;
1052 l_strlvl_tbl(l_tbl_ctr).UOM_PER_PERIOD:=strlvl_rec.UOM_PER_PERIOD;
1053 l_strlvl_tbl(l_tbl_ctr).ADVANCE_PERIODS:=strlvl_rec.ADVANCE_PERIODS;
1054 l_strlvl_tbl(l_tbl_ctr).LEVEL_AMOUNT :=strlvl_rec.LEVEL_AMOUNT;
1055 l_strlvl_tbl(l_tbl_ctr).INVOICE_OFFSET_DAYS:=strlvl_rec.INVOICE_OFFSET_DAYS;
1056 l_strlvl_tbl(l_tbl_ctr).INTERFACE_OFFSET_DAYS:=strlvl_rec.INTERFACE_OFFSET_DAYS;
1057 l_strlvl_tbl(l_tbl_ctr).COMMENTS :=strlvl_rec.COMMENTS;
1058 l_strlvl_tbl(l_tbl_ctr).DUE_ARR_YN :=strlvl_rec.DUE_ARR_YN;
1059 l_strlvl_tbl(l_tbl_ctr).AMOUNT :=strlvl_rec.AMOUNT;
1060 l_strlvl_tbl(l_tbl_ctr).LINES_DETAILED_YN:=strlvl_rec.LINES_DETAILED_YN;
1061 l_sll_exist := true;
1062 End Loop;
1063
1064 If l_strlvl_tbl.count > 0 Then
1065
1066 oks_contract_sll_pub.create_sll (
1067 p_api_version => l_api_version,
1068 p_init_msg_list => OKC_API.G_FALSE,
1069 x_return_status => l_return_status,
1070 x_msg_count => l_msg_count,
1071 x_msg_data => l_msg_data,
1072 p_sllv_tbl => l_strlvl_tbl,
1073 x_sllv_tbl => x_strlvl_tbl,
1074 p_validate_yn => 'N');
1075
1076 x_return_status := l_return_status;
1077
1078 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1079 RAISE G_EXCEPTION_HALT_VALIDATION;
1080 END IF;
1081
1082
1083 OKS_BILL_SCH.Create_hdr_schedule(
1084 p_contract_id => p_new_chr_id,
1085 x_return_status => l_return_status,
1086 x_msg_count => l_msg_count,
1087 x_msg_data => l_msg_data);
1088
1089 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1090 RAISE G_EXCEPTION_HALT_VALIDATION;
1091 END IF;
1092
1093 x_return_status := l_return_status;
1094
1095
1096 End if;
1097
1098
1099 Exception
1100 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1101 x_return_status := l_return_status;
1102 Null;
1103
1104 WHEN OTHERS THEN
1105 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1106 OKC_API.set_message
1107 (G_APP_NAME,
1108 G_UNEXPECTED_ERROR,
1109 G_SQLCODE_TOKEN,
1110 SQLCODE,
1111 G_SQLERRM_TOKEN,
1112 SQLERRM);
1113
1114 End Copy_Hdr_Bill_Sch;
1115
1116
1117 -------------------------------------------------------------------------------
1118 -- Procedure: copy_revenue_distb
1119 -- Purpose: This procedure copies revenue distribution details from
1120 -- old contract to new contract
1121 -- In Parameters: p_cle_id the line id
1122 -- p_new_cle_id new line id
1123 -- p_new_chr_id new contract id
1124 -- Out Parameters: x_return_status standard return status
1125 -----------------------------------------------------------------------------
1126
1127 Procedure copy_revenue_distb
1128 (p_cle_id IN NUMBER,
1129 p_new_cle_id IN NUMBER,
1130 p_new_chr_id IN NUMBER,
1131 x_return_status OUT NOCOPY VARCHAR2)
1132 IS
1133
1134 ctr NUMBER :=1;
1135 l_rev_tbl OKS_REV_DISTR_PUB.rdsv_tbl_type;
1136 x_rev_tbl OKS_REV_DISTR_PUB.rdsv_tbl_type;
1137 l_return_status VARCHAR2(1);
1138 l_msg_count NUMBER;
1139 l_msg_data VARCHAR2(2000);
1140 l_api_version NUMBER := 1;
1141 l_init_msg_list VARCHAR2(1) := 'F';
1142 l_id NUMBER;
1143
1144 CURSOR get_revenue_dist_rule_csr (p_cle_id NUMBER)
1145 IS
1146 SELECT account_class,code_combination_id,percent,object_version_number
1147 security_group_id
1148 FROM oks_rev_distributions
1149 WHERE cle_id = p_cle_id;
1150
1151 CURSOR rev_exist(cleId number, chrId number) is
1152 SELECT id
1153 FROM oks_rev_distributions
1154 WHERE cle_id = cleId and chr_id = chrId;
1155
1156 BEGIN
1157 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1158 l_rev_tbl.DELETE;
1159 --get revenue distribution record and copy
1160 FOR get_revenue_dist_rule_rec IN get_revenue_dist_rule_csr (p_cle_id)
1161 LOOP
1162 l_rev_tbl(ctr).chr_id := p_new_chr_id;
1163 l_rev_tbl(ctr).cle_id := p_new_cle_id;
1164 l_rev_tbl(ctr).account_class := get_revenue_dist_rule_rec.account_class;
1165 l_rev_tbl(ctr).code_combination_id := get_revenue_dist_rule_rec.code_combination_id;
1166 l_rev_tbl(ctr).percent := get_revenue_dist_rule_rec.percent;
1167 l_rev_tbl(ctr).object_version_number := OKC_API.G_MISS_NUM;
1168 l_rev_tbl(ctr).created_by := OKC_API.G_MISS_NUM;
1169 l_rev_tbl(ctr).creation_date := OKC_API.G_MISS_DATE;
1170 l_rev_tbl(ctr).last_updated_by := OKC_API.G_MISS_NUM;
1171 l_rev_tbl(ctr).last_update_date := OKC_API.G_MISS_DATE;
1172 l_rev_tbl(ctr).last_update_login := OKC_API.G_MISS_NUM;
1173
1174 ctr := ctr+1;
1175 END LOOP;
1176
1177 IF l_rev_tbl.COUNT >0
1178 THEN
1179 ctr := 1;
1180 for rev_exist_rec in rev_exist(p_new_cle_id, p_new_chr_id)
1181 Loop
1182 l_rev_tbl(ctr).id := rev_exist_rec.id;
1183 ctr:= ctr + 1;
1184 End Loop;
1185 /*
1186 If ctr > 1 then
1187 -- Delete the revenue accounting code if it already exists
1188 OKS_REV_DISTR_PUB.delete_Revenue_Distr(
1189 p_api_version => l_api_version,
1190 x_return_status => l_return_status,
1191 x_msg_count => l_msg_count,
1192 x_msg_data => l_msg_data,
1193 p_rdsv_tbl => l_rev_tbl);
1194 End If;
1195 */
1196 -- if ctr = 1 then this line didn't have a revenue distribution code.
1197 -- if ctr > 1
1198 If ctr = 1 then
1199 OKS_REV_DISTR_PUB.insert_Revenue_Distr(
1200 p_api_version => l_api_version,
1201 x_return_status => l_return_status,
1202 x_msg_count => l_msg_count,
1203 x_msg_data => l_msg_data,
1204 p_rdsv_tbl => l_rev_tbl,
1205 x_rdsv_tbl => x_rev_tbl);
1206 End If;
1207 END IF; --IF l_rev_tbl.COUNT >0
1208
1209 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1210 RAISE G_EXCEPTION_HALT_VALIDATION;
1211 END IF;
1212
1213 x_return_status := l_return_status;
1214
1215 EXCEPTION
1216 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1217 x_return_status := l_return_status;
1218 WHEN Others THEN
1219 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1220 OKC_API.set_message
1221 (
1222 G_APP_NAME,
1223 G_UNEXPECTED_ERROR,
1224 G_SQLCODE_TOKEN,
1225 SQLCODE,
1226 G_SQLERRM_TOKEN,
1227 SQLERRM
1228 );
1229
1230 END copy_revenue_distb;
1231
1232 -------------------------------------------------------------------------------
1233 -- Procedure: copy_hdr_sales_credits
1234 -- Purpose: This procedure copies the header salescredit details
1235 -- from old contract to new contract
1236 -- In Parameters: p_chr_id the contract id
1237 -- p_new_chr_id new contract id
1238 -- Out Parameters: x_return_status standard return status
1239 -----------------------------------------------------------------------------
1240
1241 Procedure copy_hdr_sales_credits
1242 (p_chr_id IN NUMBER,
1243 p_new_chr_id IN NUMBER,
1244 x_return_status OUT NOCOPY VARCHAR2)
1245 IS
1246 ctr NUMBER :=1;
1247 l_scrv_tbl OKS_SALES_CREDIT_PUB.scrv_tbl_type;
1248 x_scrv_tbl OKS_SALES_CREDIT_PUB.scrv_tbl_type;
1249 l_return_status Varchar2(1);
1250 l_msg_count Number;
1251 l_msg_data Varchar2(2000);
1252 l_api_version Number := 1;
1253 l_init_msg_list Varchar2(1) := 'F';
1254
1255 CURSOR get_sales_credits_csr (p_chr_id NUMBER) IS
1256 SELECT
1257 percent,
1258 chr_id,
1259 ctc_id,
1260 sales_group_id,
1261 sales_credit_type_id1,
1262 sales_credit_type_id2
1263 FROM oks_k_sales_credits
1264 WHERE chr_id = p_chr_id
1265 AND cle_id IS NULL;
1266
1267 BEGIN
1268 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1269 l_scrv_tbl.DELETE;
1270 x_scrv_tbl.DELETE;
1271
1272 --get sale credits record and copy
1273 FOR get_sales_credits_rec IN get_sales_credits_csr (p_chr_id)
1274 LOOP
1275 l_scrv_tbl(ctr).id := OKC_API.G_MISS_NUM;
1276 l_scrv_tbl(ctr).chr_id := p_new_chr_id;
1277 l_scrv_tbl(ctr).cle_id := OKC_API.G_MISS_NUM;
1278 l_scrv_tbl(ctr).percent := get_sales_credits_rec.percent;
1279 l_scrv_tbl(ctr).ctc_id := get_sales_credits_rec.ctc_id;
1280 l_scrv_tbl(ctr).sales_group_id := get_sales_credits_rec.sales_group_id;
1281 l_scrv_tbl(ctr).sales_credit_type_id1 := get_sales_credits_rec.sales_credit_type_id1;
1282 l_scrv_tbl(ctr).sales_credit_type_id2 := get_sales_credits_rec.sales_credit_type_id2;
1283 l_scrv_tbl(ctr).object_version_number := OKC_API.G_MISS_NUM;
1284 l_scrv_tbl(ctr).created_by := OKC_API.G_MISS_NUM;
1285 l_scrv_tbl(ctr).creation_date := OKC_API.G_MISS_DATE;
1286 l_scrv_tbl(ctr).last_updated_by := OKC_API.G_MISS_NUM;
1287 l_scrv_tbl(ctr).last_update_date := OKC_API.G_MISS_DATE;
1288 ctr := ctr+1;
1289 END LOOP;
1290
1291 IF l_scrv_tbl.COUNT >0
1292 THEN
1293 OKS_SALES_CREDIT_PUB.insert_Sales_credit(
1294 p_api_version => l_api_version,
1295 x_return_status => l_return_status,
1296 x_msg_count => l_msg_count,
1297 x_msg_data => l_msg_data,
1298 p_scrv_tbl => l_scrv_tbl,
1299 x_scrv_tbl => x_scrv_tbl);
1300
1301 END IF; --IF l_rev_tbl.COUNT >0
1302
1303 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1304 RAISE G_EXCEPTION_HALT_VALIDATION;
1305 END IF;
1306
1307 x_return_status := l_return_status;
1308
1309 EXCEPTION
1310 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1311 x_return_status := l_return_status;
1312 WHEN Others THEN
1313 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1314 OKC_API.set_message
1315 (
1316 G_APP_NAME,
1317 G_UNEXPECTED_ERROR,
1318 G_SQLCODE_TOKEN,
1319 SQLCODE,
1320 G_SQLERRM_TOKEN,
1321 SQLERRM
1322 );
1323
1324
1325 END copy_hdr_sales_credits;
1326
1327 -------------------------------------------------------------------------------
1328 -- Procedure: copy_line_sales_credits
1329 -- Purpose: This procedure copies lines sales credit from old
1330 -- contract to new contract
1331 --
1332 -- In Parameters: p_cle_id the line id
1333 -- p_new_cle_id new line id
1334 -- p_new_chr_id new contract id
1335 -- Out Parameters: x_return_status standard return status
1336 -----------------------------------------------------------------------------
1337
1338 Procedure copy_line_sales_credits
1339 (p_cle_id IN NUMBER,
1340 p_new_cle_id IN NUMBER,
1341 p_new_chr_id IN NUMBER,
1342 x_return_status OUT NOCOPY VARCHAR2)
1343 IS
1344 ctr NUMBER :=1;
1345 l_scrv_tbl OKS_SALES_CREDIT_PUB.scrv_tbl_type;
1346 x_scrv_tbl OKS_SALES_CREDIT_PUB.scrv_tbl_type;
1347 l_return_status Varchar2(1);
1348 l_msg_count Number;
1349 l_msg_data Varchar2(2000);
1350 l_api_version Number := 1;
1351 l_init_msg_list Varchar2(1) := 'F';
1352
1353 CURSOR get_sales_credits_csr (p_cle_id NUMBER) IS
1354 SELECT
1355 percent,
1356 chr_id,
1357 ctc_id,
1358 sales_group_id,
1359 sales_credit_type_id1,
1360 sales_credit_type_id2
1361 FROM oks_k_sales_credits
1362 WHERE cle_id = p_cle_id;
1363
1364 -- Added for dup sales credit bug
1365 l_exist number;
1366
1367 CURSOR sales_credit_exist_csr (p_new_cle_id NUMBER) IS
1368 SELECT percent
1369 FROM oks_k_sales_credits
1370 WHERE cle_id = p_new_cle_id;
1371 -------
1372
1373 BEGIN
1374 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1375 l_scrv_tbl.DELETE;
1376 x_scrv_tbl.DELETE;
1377
1378 --get sales credits record and copy
1379 FOR get_sales_credits_rec IN get_sales_credits_csr (p_cle_id)
1380 LOOP
1381 -- Don't add sales credit to a line that already has one.
1382 Open sales_credit_exist_csr(p_new_cle_id);
1383 Fetch sales_credit_exist_csr into l_exist;
1384 If sales_credit_exist_csr%NOTFOUND Then
1385 l_scrv_tbl(ctr).id := OKC_API.G_MISS_NUM;
1386 l_scrv_tbl(ctr).chr_id := p_new_chr_id;
1387 l_scrv_tbl(ctr).cle_id := p_new_cle_id;
1388 l_scrv_tbl(ctr).percent := get_sales_credits_rec.percent;
1389 l_scrv_tbl(ctr).ctc_id := get_sales_credits_rec.ctc_id;
1390 l_scrv_tbl(ctr).sales_group_id := get_sales_credits_rec.sales_group_id;
1391 l_scrv_tbl(ctr).sales_credit_type_id1 := get_sales_credits_rec.sales_credit_type_id1;
1392 l_scrv_tbl(ctr).sales_credit_type_id2 := get_sales_credits_rec.sales_credit_type_id2;
1393 l_scrv_tbl(ctr).object_version_number := OKC_API.G_MISS_NUM;
1394 l_scrv_tbl(ctr).created_by := OKC_API.G_MISS_NUM;
1395 l_scrv_tbl(ctr).creation_date := OKC_API.G_MISS_DATE;
1396 l_scrv_tbl(ctr).last_updated_by := OKC_API.G_MISS_NUM;
1397 l_scrv_tbl(ctr).last_update_date := OKC_API.G_MISS_DATE;
1398 ctr := ctr+1;
1399 End If;
1400 Close sales_credit_exist_csr;
1401
1402 END LOOP;
1403
1404
1405 IF l_scrv_tbl.COUNT >0 THEN
1406
1407 OKS_SALES_CREDIT_PUB.insert_Sales_credit(
1408 p_api_version => l_api_version,
1409 x_return_status => l_return_status,
1410 x_msg_count => l_msg_count,
1411 x_msg_data => l_msg_data,
1412 p_scrv_tbl => l_scrv_tbl,
1413 x_scrv_tbl => x_scrv_tbl);
1414
1415 END IF; --IF l_rev_tbl.COUNT >0
1416
1417 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1418 RAISE G_EXCEPTION_HALT_VALIDATION;
1419 END IF;
1420
1421 x_return_status := l_return_status;
1422
1423 EXCEPTION
1424 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1425 x_return_status := l_return_status;
1426 WHEN Others THEN
1427 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1428 OKC_API.set_message
1429 (
1430 G_APP_NAME,
1431 G_UNEXPECTED_ERROR,
1432 G_SQLCODE_TOKEN,
1433 SQLCODE,
1434 G_SQLERRM_TOKEN,
1435 SQLERRM
1436 );
1437
1438
1439 END copy_line_sales_credits;
1440
1441 /*
1442 * This procedure will copy the old item instance and it will instantiate a new
1443 * subscription template for each subscription line.
1444 *
1445 */
1446 Procedure copy_subscr_inst(p_new_chr_id IN NUMBER,
1447 p_cle_id IN NUMBER,
1448 p_intent IN VARCHAR2 DEFAULT NULL,
1449 x_return_status OUT NOCOPY VARCHAR2) IS
1450
1451 l_api_version NUMBER := 1.0;
1452 l_init_msg_list VARCHAR2(3) :=OKC_API.G_FALSE;
1453 l_return_status VARCHAR2(1) := 'S';
1454 l_msg_count NUMBER;
1455 l_msg_data VARCHAR2(2000);
1456 l_intent VARCHAR2(90);
1457 l_temp number;
1458 l_old_chr_id number;
1459 l_new_inst_id number;
1460 l_old_inst_id number;
1461 l_cimv_rec OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1462 x_cimv_rec OKC_CONTRACT_ITEM_PUB.cimv_rec_type;
1463
1464 Cursor get_subscr_toplines(p_new_chr_id IN NUMBER,
1465 p_cle_id IN NUMBER) IS
1466 SELECT id, NVL(orig_system_id1, cle_id_renewed) old_line_id
1467 FROM okc_k_lines_b
1468 WHERE dnz_chr_id = p_new_chr_id and lse_id = 46
1469 AND id=p_cle_id;
1470
1471 Cursor get_subscr_toplines_C(p_new_chr_id IN NUMBER
1472 ) IS
1473 SELECT id, NVL(orig_system_id1, cle_id_renewed) old_line_id
1474 FROM okc_k_lines_b
1475 WHERE dnz_chr_id = p_new_chr_id and lse_id = 46;
1476
1477
1478
1479 -- get original chr_id
1480 Cursor get_old_chr_id IS
1481 SELECT orig_system_id1
1482 FROM okc_k_headers_b
1483 WHERE id = p_new_chr_id;
1484
1485 -- If the new_chrId is from a renewed contract this cursor will return a record.
1486 -- It will not return any records if new_chrId is from a copied contract.
1487 Cursor got_renewed(new_chrId number) IS
1488 SELECT subject_chr_id new_chr_id
1489 FROM okc_operation_lines
1490 WHERE subject_chr_id =new_chrId;
1491
1492 -- gets old and new item instance
1493 Cursor get_item_instance(chrId number, cleId number) IS
1494 SELECT b.instance_id
1495 FROM oks_subscr_header_b b
1496 WHERE b.dnz_chr_id = chrId and b.cle_id = cleId;
1497
1498 -- Gets the new covered lines that are covering the old item instance.
1499 Cursor get_new_cps(oldItemInst number) IS
1500 SELECT b.id
1501 FROM okc_k_lines_b a, okc_k_items b
1502 WHERE b.cle_id = a.id and a.lse_id = 9 and b.object1_id1 = oldItemInst
1503 AND a.dnz_chr_id = p_new_chr_id;
1504
1505 Begin
1506 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1507 l_intent := p_intent; --'RENEW' or NULL
1508 -- Renewed contracts have a record in okc_operation_lines
1509 If p_intent is null then
1510 open got_renewed(p_new_chr_id);
1511 Fetch got_renewed into l_temp;
1512 If got_renewed%NOTFOUND Then
1513 l_intent := 'COPY';
1514 End If;
1515 Close got_renewed;
1516 End If;
1517 -- First get all the new subscription top lines
1518 If l_intent is not null then
1519 IF P_cle_id is null then
1520 For new_topline_rec in get_subscr_toplines_c(p_new_chr_id) Loop
1521 -- The item instance will get copied
1522 -- a new subscription template will get instanciated.
1523 OKS_SUBSCRIPTION_PUB.copy_subscription(
1524 l_api_version,
1525 l_init_msg_list,
1526 l_return_status,
1527 l_msg_count,
1528 l_msg_data,
1529 new_topline_rec.old_line_id,
1530 new_topline_rec.id,
1531 l_intent
1532 );
1533 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1534 RAISE G_EXCEPTION_HALT_VALIDATION;
1535 END IF;
1536 If l_intent = 'COPY' then
1537 Open get_old_chr_id;
1538 Fetch get_old_chr_id into l_old_chr_id;
1539 Close get_old_chr_id;
1540 Open get_item_instance(l_old_chr_id, new_topline_rec.old_line_id);
1541 Fetch get_item_instance into l_old_inst_id;
1542 Close get_item_instance;
1543 Open get_item_instance(p_new_chr_id, new_topline_rec.id);
1544 Fetch get_item_instance into l_new_inst_id;
1545 Close get_item_instance;
1546 If l_old_inst_id <> l_new_inst_id Then
1547 For new_cp_rec in get_new_cps(l_old_inst_id) Loop
1548 -- update the item instance in object1_id1 to l_new_item_ins
1549 l_cimv_rec.id := new_cp_rec.id;
1550 l_cimv_rec.object1_id1 := l_new_inst_id;
1551 OKC_CONTRACT_ITEM_PUB.update_contract_item(l_api_version,
1552 l_init_msg_list,
1553 l_return_status,
1554 l_msg_count,
1555 l_msg_data,
1556 l_cimv_rec,
1557 x_cimv_rec);
1558 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1559 RAISE G_EXCEPTION_HALT_VALIDATION;
1560 end if;
1561 End Loop;
1562
1563 End If; -- l_old_inst_id <> l_new_inst_id
1564
1565 End if; -- l_intent = 'COPY'
1566 END Loop;
1567
1568 ELSE
1569 For new_topline_rec in get_subscr_toplines(p_new_chr_id,p_cle_id) Loop
1570 -- The item instance will get copied
1571 -- a new subscription template will get instanciated.
1572 OKS_SUBSCRIPTION_PUB.copy_subscription(
1573 l_api_version,
1574 l_init_msg_list,
1575 l_return_status,
1576 l_msg_count,
1577 l_msg_data,
1578 new_topline_rec.old_line_id,
1579 new_topline_rec.id,
1580 l_intent
1581 );
1582 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1583 RAISE G_EXCEPTION_HALT_VALIDATION;
1584 END IF;
1585 If l_intent = 'COPY' then
1586 Open get_old_chr_id;
1587 Fetch get_old_chr_id into l_old_chr_id;
1588 Close get_old_chr_id;
1589 Open get_item_instance(l_old_chr_id, new_topline_rec.old_line_id);
1590 Fetch get_item_instance into l_old_inst_id;
1591 Close get_item_instance;
1592 Open get_item_instance(p_new_chr_id, new_topline_rec.id);
1593 Fetch get_item_instance into l_new_inst_id;
1594 Close get_item_instance;
1595 If l_old_inst_id <> l_new_inst_id Then
1596 For new_cp_rec in get_new_cps(l_old_inst_id) Loop
1597 -- update the item instance in object1_id1 to l_new_item_ins
1598 l_cimv_rec.id := new_cp_rec.id;
1599 l_cimv_rec.object1_id1 := l_new_inst_id;
1600 OKC_CONTRACT_ITEM_PUB.update_contract_item(l_api_version,
1601 l_init_msg_list,
1602 l_return_status,
1603 l_msg_count,
1604 l_msg_data,
1605 l_cimv_rec,
1606 x_cimv_rec);
1607 if (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1608 RAISE G_EXCEPTION_HALT_VALIDATION;
1609 end if;
1610 End Loop;
1611
1612 End If; -- l_old_inst_id <> l_new_inst_id
1613
1614 End if; -- l_intent = 'COPY'
1615 END Loop;
1616 END IF;
1617 End If;
1618 x_return_status := l_return_status;
1619
1620 EXCEPTION
1621 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1622 x_return_status := l_return_status;
1623 WHEN Others THEN
1624 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1625 OKC_API.set_message
1626 (
1627 G_APP_NAME,
1628 G_UNEXPECTED_ERROR,
1629 G_SQLCODE_TOKEN,
1630 SQLCODE,
1631 G_SQLERRM_TOKEN,
1632 SQLERRM
1633 );
1634
1635 End;
1636
1637 -------------------------------------------------------------------------------
1638 -- Procedure: get_billing_attr
1639 -- Purpose: This procedure gets the billing schedule type
1640 -- In Parameters: p_chr_id the contract id
1641 -- p_cle_id line_id
1642 --Out Parameters x_billing_schedule_type billing_sch_type
1643 -- x_inv_rule_id Invoice Rule id
1644 -- Out Parameters: x_return_status standard return status
1645 -----------------------------------------------------------------------------
1646
1647 PROCEDURE get_billing_attr
1648 (p_chr_id IN NUMBER,
1649 p_cle_id IN NUMBER,
1650 x_billing_schedule_type OUT NOCOPY VARCHAR2,
1651 x_inv_rule_id OUT NOCOPY VARCHAR2,
1652 x_return_status OUT NOCOPY VARCHAR2
1653 )
1654 IS
1655 Cursor get_billing_info is
1656 SELECT okc.inv_rule_id,
1657 oks.billing_schedule_type
1658 FROM okc_k_lines_b okc,
1659 oks_k_lines_b oks
1660 WHERE okc.id=p_cle_id
1661 AND okc.id=oks.cle_id;
1662
1663 l_tbl_ctr NUMBER;
1664 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
1665 l_msg_count Number;
1666 l_msg_data Varchar2(2000);
1667 l_api_version Number := 1;
1668 l_init_msg_list Varchar2(1) := 'F';
1669 l_billing_schedule_type VARCHAR2(30);
1670 l_inv_rule_id VARCHAR2(30);
1671 Begin
1672
1673 OPEN get_billing_info;
1674 FETCH get_billing_info INTO l_inv_rule_id,l_billing_schedule_type;
1675 CLOSE get_billing_info;
1676
1677 x_billing_schedule_type:=l_billing_schedule_type;
1678 x_inv_rule_id:=l_inv_rule_id;
1679 x_return_status:='S';
1680
1681 END get_billing_attr;
1682
1683 FUNCTION set_flag(p_old_cle_id IN NUMBER,
1684 p_new_cle_id IN NUMBER
1685 ) RETURN VARCHAR2
1686 IS
1687
1688 l_flag varchar2(30);
1689
1690 CURSOR get_orig_line_amount(l_orig_line_id number) is
1691 SELECT TRUNC(date_terminated) line_term_dt,
1692 (nvl(line.price_negotiated,0) +nvl(dtl.ubt_amount,0) +
1693 nvl(dtl.credit_amount,0) + nvl(dtl.suppressed_credit,0) ) line_amt
1694 FROM okc_k_lines_b line, oks_k_lines_b dtl
1695 WHERE line.id = dtl.cle_id AND line.Id = l_orig_line_id;
1696
1697 l_orig_line_rec get_orig_line_amount%ROWTYPE;
1698
1699 CURSOR get_price(l_new_line_id number) is
1700 select price_negotiated
1701 from okc_k_lines_b
1702 where id = l_new_line_id;
1703
1704 l_new_price number;
1705
1706
1707 Begin
1708 Open get_orig_line_amount(p_old_cle_id);
1709 Fetch get_orig_line_amount into l_orig_line_rec;
1710 Close get_orig_line_amount;
1711
1712
1713 Open get_price(p_new_cle_id);
1714 Fetch get_price into l_new_price;
1715 Close get_price;
1716 If NVL(l_new_price, 0) <> NVL(l_orig_line_rec.line_amt, 0) Then
1717 l_flag := '99';
1718 Else
1719 l_flag := null;
1720 End If;
1721
1722
1723 return l_flag;
1724
1725 End set_flag;
1726 -------------------------------------------------------------------------------
1727 -- Procedure: get_strlvls
1728 -- Purpose: Build several records/tables that hold information to be
1729 -- used to pass to OC APIs
1730 -- In Parameters: p_chr_id the contract id
1731 -- p_cle_id line id
1732 -- x_strlvl_tbl stream levels
1733 -- Out Parameters: x_return_status standard return status
1734 -----------------------------------------------------------------------------
1735
1736 PROCEDURE get_strlvls
1737 (p_chr_id IN NUMBER,
1738 p_cle_id IN NUMBER,
1739 p_billsch_type IN VARCHAR2,
1740 x_strlvl_tbl OUT NOCOPY OKS_BILL_SCH.StreamLvl_tbl,
1741 x_return_status OUT NOCOPY VARCHAR2
1742 )
1743 IS
1744
1745
1746 l_tbl_ctr NUMBER;
1747 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
1748 l_msg_count Number;
1749 l_msg_data Varchar2(2000);
1750 l_api_version Number := 1;
1751 l_init_msg_list Varchar2(1) := 'F';
1752
1753
1754 l_bil_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
1755 l_strlvl_tbl OKS_BILL_SCH.StreamLvl_tbl;
1756 l_klnv_tbl oks_kln_pvt.klnv_tbl_type;
1757 x_klnv_tbl oks_kln_pvt.klnv_tbl_type;
1758
1759 CURSOR old_cle_csr(p_cle_id NUMBER) IS
1760 SELECT orig_system_id1
1761 FROM okc_k_lines_b
1762 WHERE id=p_cle_id;
1763
1764 CURSOR was_line_terminated(p_old_cle_id number) is
1765 select date_terminated
1766 from okc_k_lines_b
1767 where id = p_old_cle_id ;
1768
1769 CURSOR strlvl_csr (p_cle_id NUMBER) IS
1770 SELECT ID
1771 ,CHR_ID
1772 ,CLE_ID
1773 ,DNZ_CHR_ID
1774 ,SEQUENCE_NO
1775 ,UOM_CODE
1776 ,START_DATE
1777 ,END_DATE
1778 ,LEVEL_PERIODS
1779 ,UOM_PER_PERIOD
1780 ,ADVANCE_PERIODS
1781 ,LEVEL_AMOUNT
1782 ,INVOICE_OFFSET_DAYS
1783 ,INTERFACE_OFFSET_DAYS
1784 ,COMMENTS
1785 ,DUE_ARR_YN
1786 ,AMOUNT
1787 ,LINES_DETAILED_YN
1788 FROM oks_stream_levels_b
1789 WHERE cle_id=p_cle_id
1790 ORDER BY SEQUENCE_NO;/*BUG 7450286 */
1791 strlvl_rec strlvl_csr%ROWTYPE;
1792
1793 Cursor get_ccr(p_cle_id IN NUMBER) is
1794 SELECT
1795 id
1796 ,dnz_chr_id
1797 ,cc_no
1798 ,cc_expiry_date
1799 ,cc_bank_acct_id
1800 ,cc_auth_code
1801 ,object_version_number
1802 FROM oks_k_lines_b
1803 WHERE cle_id=p_cle_id;
1804
1805 l_old_cle_id number;
1806 l_date_terminated date;
1807
1808 BEGIN
1809
1810 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
1811 p_perf_msg =>'Inside get_strlvls procedure ');
1812
1813 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1814
1815 l_tbl_ctr := 0;
1816 l_strlvl_tbl.delete;
1817 l_bil_sch_out_tbl.delete;
1818
1819 strlvl_rec.ID :=NULL;
1820 strlvl_rec.CHR_ID :=NULL;
1821 strlvl_rec.CLE_ID :=NULL;
1822 strlvl_rec.DNZ_CHR_ID :=NULL;
1823 strlvl_rec.SEQUENCE_NO :=NULL;
1824 strlvl_rec.UOM_CODE :=NULL;
1825 strlvl_rec.START_DATE :=NULL;
1826 strlvl_rec.END_DATE :=NULL;
1827 strlvl_rec.LEVEL_PERIODS :=NULL;
1828 strlvl_rec.UOM_PER_PERIOD :=NULL;
1829 strlvl_rec.ADVANCE_PERIODS :=NULL;
1830 strlvl_rec.LEVEL_AMOUNT :=NULL;
1831 strlvl_rec.INVOICE_OFFSET_DAYS :=NULL;
1832 strlvl_rec.INTERFACE_OFFSET_DAYS :=NULL;
1833 strlvl_rec.COMMENTS :=NULL;
1834 strlvl_rec.DUE_ARR_YN :=NULL;
1835 strlvl_rec.AMOUNT :=NULL;
1836 strlvl_rec.LINES_DETAILED_YN :=NULL;
1837
1838 OPEN old_cle_csr(p_cle_id);
1839 FETCH old_cle_csr INTO l_old_cle_id;
1840 CLOSE old_cle_csr;
1841
1842 FOR strlvl_rec IN strlvl_csr (l_old_cle_id)
1843 LOOP
1844 l_tbl_ctr := l_tbl_ctr + 1;
1845
1846 l_strlvl_tbl(l_tbl_ctr).id :=OKC_API.G_MISS_NUM;
1847 l_strlvl_tbl(l_tbl_ctr).CHR_ID :=OKC_API.G_MISS_NUM;
1848 l_strlvl_tbl(l_tbl_ctr).CLE_ID :=p_cle_id;
1849 l_strlvl_tbl(l_tbl_ctr).DNZ_CHR_ID :=P_CHR_ID;
1850 l_strlvl_tbl(l_tbl_ctr).SEQUENCE_NO :=strlvl_rec.SEQUENCE_NO;
1851 l_strlvl_tbl(l_tbl_ctr).UOM_CODE :=strlvl_rec.UOM_CODE;
1852 l_strlvl_tbl(l_tbl_ctr).START_DATE :=strlvl_rec.START_DATE;
1853 l_strlvl_tbl(l_tbl_ctr).END_DATE :=strlvl_rec.END_DATE;
1854 l_strlvl_tbl(l_tbl_ctr).LEVEL_PERIODS :=strlvl_rec.LEVEL_PERIODS;
1855 l_strlvl_tbl(l_tbl_ctr).UOM_PER_PERIOD :=strlvl_rec.UOM_PER_PERIOD;
1856 l_strlvl_tbl(l_tbl_ctr).ADVANCE_PERIODS :=strlvl_rec.ADVANCE_PERIODS;
1857 l_strlvl_tbl(l_tbl_ctr).LEVEL_AMOUNT :=strlvl_rec.LEVEL_AMOUNT;
1858 l_strlvl_tbl(l_tbl_ctr).INVOICE_OFFSET_DAYS :=strlvl_rec.INVOICE_OFFSET_DAYS;
1859 l_strlvl_tbl(l_tbl_ctr).INTERFACE_OFFSET_DAYS :=strlvl_rec.INTERFACE_OFFSET_DAYS;
1860 l_strlvl_tbl(l_tbl_ctr).COMMENTS :=strlvl_rec.COMMENTS;
1861 l_strlvl_tbl(l_tbl_ctr).DUE_ARR_YN :=strlvl_rec.DUE_ARR_YN;
1862 l_strlvl_tbl(l_tbl_ctr).AMOUNT :=strlvl_rec.AMOUNT;
1863 l_strlvl_tbl(l_tbl_ctr).LINES_DETAILED_YN :=strlvl_rec.LINES_DETAILED_YN;
1864 -------- Added for bug
1865 If p_billsch_type in ('E', 'P') Then
1866 l_strlvl_tbl(l_tbl_ctr).comments := set_flag(l_old_cle_id, p_cle_id);
1867 End If;
1868 -------------
1869
1870 END LOOP;
1871
1872 x_strlvl_tbl := l_strlvl_tbl;
1873
1874 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
1875 p_perf_msg =>'Building Stream Levels Successful ');
1876
1877 For get_ccr_rec In get_ccr(p_cle_id)
1878 Loop
1879 IF get_ccr_rec.cc_bank_acct_id is not null then
1880 l_klnv_tbl(1).id := get_ccr_rec.id;
1881 l_klnv_tbl(1).dnz_chr_id := get_ccr_rec.dnz_chr_id;
1882 l_klnv_tbl(1).CC_BANK_ACCT_ID :=NULL;
1883 l_klnv_tbl(1).CC_AUTH_CODE :=NULL;
1884 l_klnv_tbl(1).object_version_number := get_ccr_rec.object_version_number;
1885 l_klnv_tbl(1).CREATED_BY :=OKC_API.G_MISS_NUM;
1886 l_klnv_tbl(1).CREATION_DATE :=OKC_API.G_MISS_DATE;
1887 l_klnv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
1888 l_klnv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
1889 l_klnv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
1890
1891 OKS_CONTRACT_LINE_PUB.update_line (
1892 p_api_version => l_api_version,
1893 p_init_msg_list => OKC_API.G_FALSE,
1894 x_return_status => l_return_status,
1895 x_msg_count => l_msg_count,
1896 x_msg_data => l_msg_data,
1897 p_klnv_tbl => l_klnv_tbl,
1898 x_klnv_tbl => x_klnv_tbl,
1899 p_validate_yn => 'N');
1900 END IF;
1901 End Loop; --For get_line_id_rec In get_line_id_csr
1902
1903 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
1904 p_perf_msg =>'After Update CCR Rule ');
1905
1906 x_return_status := l_return_status;
1907
1908 END get_strlvls;
1909
1910 -------------------------------------------------------------------------------
1911 -- Procedure: sub_copy
1912 -- Purpose: This procedure creates the sublines for the toplines
1913 -- copied,copies the subline revenue distribution
1914 -- creates the billing schedule for the top lines which
1915 -- in turn creates the billing schedule for the sublines
1916 -- also
1917 -- In Parameters: p_chr_id the contract id
1918 -- In Parameters: p_cle_id line id
1919 -- In Parameters: p_start_date start Date
1920 -- In Parameters: p_upd_line_flag flag to check where the call is m
1921 -- made from renew or copy
1922 -- In Parameters: p_billing_schedule_type line billing schedule type
1923 -- In Parameters: p_duration_match flag to check copied/renewed
1924 -- contract duration
1925 -- Out Parameters: x_return_status standard return status
1926 -----------------------------------------------------------------------------
1927
1928 PROCEDURE sub_copy
1929 (
1930 p_chr_id IN NUMBER,
1931 p_cle_id IN NUMBER,
1932 p_start_date IN DATE,
1933 p_upd_line_flag IN Varchar2,
1934 p_billing_schedule_type IN VARCHAR2,
1935 p_duration_match IN Varchar2,
1936 p_bill_profile_flag IN Varchar2,
1937 p_do_copy IN boolean,
1938 x_return_status OUT NOCOPY VARCHAR2
1939 )
1940 IS
1941
1942 l_msg_count Number;
1943 l_msg_data Varchar2(2000);
1944 l_api_version Number := 1;
1945 l_init_msg_list Varchar2(1) := 'F';
1946 l_return_status VARCHAR2(1);
1947 l_adv_arr VARCHAR2(40);
1948 l_invoice_rule_id NUMBER;
1949 l_tbl_ctr NUMBER;
1950 l_time_value NUMBER;
1951 l_billing_type VARCHAR2(3);
1952
1953 --tbl type
1954
1955 l_SLL_tbl_type OKS_BILL_SCH.StreamLvl_tbl;
1956 l_bil_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
1957 l_strlvl_tbl OKS_BILL_SCH.StreamLvl_tbl;
1958
1959 --Sub Lines information
1960
1961 CURSOR sub_line_grp_csr (p_chr_id NUMBER,p_cle_id NUMBER) IS
1962 SELECT
1963 lines.id,
1964 lines.start_date,lines.dnz_chr_id,lines.orig_system_id1
1965 FROM okc_k_lines_b lines
1966 WHERE lines.dnz_chr_id = p_chr_id
1967 AND lines.cle_id = p_cle_id
1968 AND lines.lse_id in (7,8,9,10,11,13,18,25,35);
1969
1970 CURSOR get_adv_arr_csr (p_cle_id NUMBER) IS
1971 SELECT inv_rule_id,cle_id
1972 FROM okc_k_lines_b
1973 WHERE id=p_cle_id;
1974
1975 CURSOR Subline_Billsch_type(p_cle_id NUMBER) IS
1976 SELECT Billing_schedule_type
1977 FROM oks_k_lines_b
1978 WHERE cle_id =p_cle_id;
1979
1980 l_subline_billsch_type Varchar2(30);
1981 l_price_negotiated NUMBER;
1982 BEGIN
1983
1984 l_invoice_rule_id := NULL;
1985 l_time_value :=NULL;
1986
1987 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1988
1989 FOR get_adv_arr_rec IN get_adv_arr_csr (p_cle_id)
1990 LOOP
1991 l_invoice_rule_id := get_adv_arr_rec.inv_rule_id;
1992 -- l_adv_arr:=-2;
1993 --commented out for the reason when billing_sch is not there
1994 -- inv_rule_is is null in okc_k_lines table
1995 END LOOP;
1996
1997
1998 FOR sub_line_grp_rec IN sub_line_grp_csr (p_chr_id,p_cle_id)
1999 LOOP
2000 --IF (p_upd_line_flag IS NULL) THEN
2001
2002 copy_lines_attr
2003 (p_cle_id => sub_line_grp_rec.orig_system_id1,
2004 p_new_cle_id => sub_line_grp_rec.id,
2005 p_new_chr_id => sub_line_grp_rec.dnz_chr_id,
2006 p_do_copy => p_do_copy,
2007 x_return_status => l_return_status);
2008
2009 OKS_RENEW_PVT.Debug_Log
2010 (p_program_name => 'OKSCOPY'
2011 ,p_perf_msg =>'SubLine copy_lines_attr Status '||l_return_status);
2012
2013 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
2014 copy_revenue_distb
2015 (p_cle_id => sub_line_grp_rec.orig_system_id1,
2016 p_new_cle_id => sub_line_grp_rec.id,
2017 p_new_chr_id => sub_line_grp_rec.dnz_chr_id,
2018 x_return_status => l_return_status);
2019
2020 END IF;
2021
2022 OKS_RENEW_PVT.Debug_Log
2023 (p_program_name => 'OKSCOPY'
2024 ,p_perf_msg =>'SubLine copy_revenue_distb Status'||l_return_status);
2025 x_return_status := l_return_status;
2026
2027
2028 OPEN Subline_Billsch_type(sub_line_grp_rec.id);
2029 FETCH Subline_Billsch_type INTO l_subline_billsch_type;
2030 CLOSE Subline_Billsch_type;
2031
2032 IF l_subline_billsch_type ='P' THEN
2033 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2034 If p_duration_match = 'T' and p_bill_profile_flag is null Then
2035
2036 get_strlvls
2037 (p_chr_id => p_chr_id,
2038 p_cle_id => sub_line_grp_rec.id,
2039 p_billsch_type => l_subline_billsch_type,
2040 x_strlvl_tbl => l_strlvl_tbl,
2041 x_return_status => l_return_status
2042 );
2043
2044 IF l_return_status = 'S' and l_strlvl_tbl.count > 0 THEN
2045
2046 -- Call bill API
2047 oks_bill_sch.create_bill_sch_rules
2048 (
2049 p_billing_type => l_subline_billsch_type,
2050 p_sll_tbl => l_strlvl_tbl,
2051 p_invoice_rule_id => l_invoice_rule_id,
2052 x_bil_sch_out_tbl => l_bil_sch_out_tbl,
2053 x_return_status =>l_return_status);
2054
2055
2056
2057 OKS_RENEW_PVT.Debug_Log
2058 (p_program_name => 'OKSCOPY'
2059 ,p_perf_msg =>'P SubLine Billing Schedule Creation Status '||l_return_status);
2060
2061 END IF; --l_return_status = 'S'
2062 END IF;
2063 END IF; -- IF sub_line_grp_rec.billing_schedule_type = 'P'
2064
2065
2066
2067 END LOOP; --FOR sub_line_grp_rec IN sub_line_grp_csr (p_chr_id,p_cle_id)
2068
2069
2070 IF p_upd_line_flag IS NULL and p_chr_id is not null THEN
2071 UPDATE okc_k_lines_b set
2072 price_negotiated = (SELECT sum(price_negotiated) FROM okc_k_lines_b
2073 WHERE dnz_chr_id = p_chr_id AND chr_id is null
2074 AND cle_id = p_cle_id)
2075 WHERE lse_id in (1, 19, 12) -- added 12 in the IN clause for bug # 3534513
2076 AND chr_id = p_chr_id and id = p_cle_id;
2077 END IF;
2078
2079 If p_duration_match = 'T' and p_bill_profile_flag is null Then
2080 -- fix for bug # 3387603
2081 If l_subline_billsch_type is null Then
2082 OPEN Subline_Billsch_type(p_cle_id);
2083 FETCH Subline_Billsch_type INTO l_subline_billsch_type;
2084 CLOSE Subline_Billsch_type;
2085 End If;
2086 --------------
2087 get_strlvls
2088 (p_chr_id => p_chr_id,
2089 p_cle_id => p_cle_id,
2090 p_billsch_type => l_subline_billsch_type,
2091 x_strlvl_tbl => l_strlvl_tbl,
2092 x_return_status => l_return_status
2093 );
2094
2095
2096 --Calls API for level element creation
2097 --This take care covered level of 'E' and 'T as well
2098
2099 OKS_RENEW_PVT.Debug_Log
2100 (p_program_name => 'OKSCOPY',
2101 p_perf_msg =>'Before Creating Billing Schedule');
2102
2103
2104 --- IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
2105
2106 IF l_strlvl_tbl.COUNT > 0 THEN
2107
2108 -- Call bill API
2109 oks_bill_sch.create_bill_sch_rules
2110 (
2111 p_billing_type => p_billing_schedule_type,
2112 p_sll_tbl => l_strlvl_tbl,
2113 p_invoice_rule_id => l_invoice_rule_id,
2114 x_bil_sch_out_tbl => l_bil_sch_out_tbl,
2115 x_return_status => l_return_status);
2116 END IF;--l_SLL_tbl_type.COUNT > 0
2117
2118 -- END IF;
2119
2120 OKS_RENEW_PVT.Debug_Log
2121 (p_program_name => 'OKSCOPY'
2122 ,p_perf_msg =>'Billing Schedule Creation Status '||l_return_status);
2123
2124 END IF;
2125
2126 END sub_copy;
2127
2128 -------------------------------------------------------------------------------
2129 -- Procedure: chk_Sll_Exists
2130 -- Purpose: This function call checks if Stream Levels exist for
2131 -- the line id passed or not
2132 -- In Parameters: p_cle_id the line id
2133 -- Out Parameters: x_return_status standard return status
2134 -----------------------------------------------------------------------------
2135
2136 FUNCTION chk_Sll_Exists(p_cle_id IN NUMBER) return number IS
2137
2138 CURSOR l_sll_csr(p_cle_id NUMBER) IS
2139 SELECT count(*)
2140 FROM oks_stream_levels_b
2141 WHERE cle_ID = p_cle_id;
2142
2143 l_sll_rec l_sll_csr%ROWTYPE;
2144 l_sll_count NUMBER;
2145
2146 BEGIN
2147
2148 OPEN l_sll_csr(p_cle_id);
2149 FETCH l_sll_csr INTO l_sll_count;
2150
2151 IF l_sll_csr%NOTFOUND THEN
2152 CLOSE l_sll_csr;
2153 l_sll_count:=0;
2154 return(l_sll_count);
2155 ELSE
2156 CLOSE l_sll_csr;
2157 return(l_sll_count);
2158 END IF;
2159 END ;
2160
2161 -------------------------------------------------------------------------------
2162 -- Function : chk_topline_Exists
2163 -- Purpose: This function checks if the topline already exist in
2164 -- oks table to avoid the duplicacy
2165 -- In Parameters: p_cle_id the line id
2166 -- Out Parameters: x_return_status standard return status
2167 -----------------------------------------------------------------------------
2168
2169 FUNCTION chk_topline_Exists(p_cle_id IN NUMBER) return number IS
2170
2171 CURSOR l_topline_csr(p_cle_id NUMBER) IS
2172 SELECT count(*) from oks_k_lines_b
2173 WHERE cle_id = p_cle_id;
2174
2175 l_topline_count NUMBER;
2176
2177 BEGIN
2178
2179 OPEN l_topline_csr(p_cle_id);
2180 FETCH l_topline_csr INTO l_topline_count;
2181
2182 IF l_topline_csr%NOTFOUND THEN
2183 CLOSE l_topline_csr;
2184 return(null);
2185 ELSE
2186 CLOSE l_topline_csr;
2187 return(l_topline_count);
2188 END IF;
2189 END ;
2190
2191 -------------------------------------------------------------------------------
2192 -- Procedure: chk_hdr_effectivity
2193 -- Purpose: This procedure checks the effectivity dates of old
2194 -- contract and the new copied contract
2195 -- In Parameters: p_new_chr_id new contract id
2196 -- Out Parameters: x_return_status standard return status
2197 -- x_flag yes no flag
2198 -----------------------------------------------------------------------------
2199
2200 PROCEDURE chk_hdr_effectivity
2201 (
2202 p_new_chr_id IN NUMBER,
2203 x_flag OUT NOCOPY VARCHAR2,
2204 x_return_status OUT NOCOPY VARCHAR2
2205 )
2206 IS
2207
2208 CURSOR l_chr_renew_csr (l_new_chr_id NUMBER) IS
2209 SELECT start_date,end_date,orig_system_id1
2210 FROM okc_k_headers_b
2211 WHERE id = l_new_chr_id;
2212
2213 l_chr_renew_rec l_chr_renew_csr%ROWTYPE;
2214
2215 CURSOR l_chr_old_csr (l_old_chr_id NUMBER) IS
2216 SELECT start_date,end_date
2217 FROM okc_k_headers_b
2218 WHERE id = l_old_chr_id;
2219
2220
2221 l_chr_old_rec l_chr_old_csr%ROWTYPE;
2222 l_old_chr_id NUMBER;
2223 l_old_duration NUMBER := 0;
2224 l_old_time VARCHAR2(450) ;
2225 l_renew_duration NUMBER := 0;
2226 l_renew_time VARCHAR2(450) ;
2227 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2228
2229 BEGIN
2230 x_flag := 'T';
2231 OPEN l_chr_renew_csr (p_new_chr_id);
2232 FETCH l_chr_renew_csr INTO l_chr_renew_rec;
2233 CLOSE l_chr_renew_csr;
2234
2235 l_old_chr_id := l_chr_renew_rec.orig_system_id1;
2236 -- Added for bug # 4066428
2237 If l_old_chr_id is null Then
2238 x_return_status := l_return_status;
2239 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',
2240 p_perf_msg => 'Exiting chk_hdr_effectivity because orig_system_id1 was null');
2241 return;
2242 End If;
2243 -- End of add for bug # 4066428
2244 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',
2245 p_perf_msg => 'l_old_chr_id ' || l_old_chr_id);
2246
2247
2248 OPEN l_chr_old_csr(l_old_chr_id);
2249 FETCH l_chr_old_csr INTO l_chr_old_rec;
2250 CLOSE l_chr_old_csr;
2251
2252 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'old start date ' || l_chr_old_rec.start_date);
2253 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'old end date ' || l_chr_old_rec.end_date);
2254 OKC_TIME_UTIL_PUB.get_duration(
2255 l_chr_old_rec.start_date
2256 ,l_chr_old_rec.end_date
2257 ,l_old_duration
2258 ,l_old_time
2259 ,l_return_status
2260 );
2261 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'l_old_duration ' || l_old_duration);
2262 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'l_old_time ' || l_old_time);
2263 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'l_return_status ' || l_return_status);
2264 IF l_return_status = 'S' Then
2265 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'new start date ' || l_chr_renew_rec.start_date);
2266 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'new end date ' || l_chr_renew_rec.end_date);
2267
2268 OKC_TIME_UTIL_PUB.get_duration(
2269 l_chr_renew_rec.start_date
2270 ,l_chr_renew_rec.end_date
2271 ,l_renew_duration
2272 ,l_renew_time
2273 ,l_return_status
2274 );
2275 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'l_new_duration ' || l_renew_duration);
2276 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'l_renew_time ' || l_renew_time);
2277 oks_renew_pvt.Debug_Log(p_program_name => 'OKSCOPY',p_perf_msg => 'l_return_status ' || l_return_status);
2278
2279 IF l_return_status = 'S'
2280 THEN
2281 IF ((l_old_duration <> l_renew_duration) OR
2282 (l_old_time <> l_renew_time))
2283 THEN
2284 x_flag := 'F';
2285 ELSE
2286 x_flag := 'T';
2287 END IF; -- IF ((l_old_duration <> l_renew_duration) OR
2288 END IF;
2289 END IF;
2290 x_return_status := l_return_status;
2291 Exception
2292 WHEN Others THEN
2293 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2294 OKC_API.set_message(
2295 G_APP_NAME,
2296 G_UNEXPECTED_ERROR,
2297 G_SQLCODE_TOKEN,
2298 SQLCODE,
2299 G_SQLERRM_TOKEN,
2300 SQLERRM
2301 );
2302
2303 END chk_hdr_effectivity;
2304
2305
2306
2307 Procedure Party_cleanup
2308 (p_chr_id IN Number,
2309 x_return_status OUT NOCOPY Varchar2)
2310 IS
2311 l_return_status Varchar2(1);
2312 l_msg_count Number;
2313 l_msg_data Varchar2(2000);
2314 l_api_version Number := 1;
2315 l_init_msg_list Varchar2(1) := 'F';
2316
2317 Cursor get_qto(p_chr_id IN NUMBER) is
2318 select
2319 id
2320 ,chr_id
2321 ,quote_to_contact_id
2322 ,quote_to_site_id
2323 ,quote_to_email_id
2324 ,quote_to_phone_id
2325 ,quote_to_fax_id
2326 ,object_version_number
2327 from oks_k_headers_b
2328 where chr_id=p_chr_id;
2329
2330 CURSOR get_toplines(p_chr_id number) IS
2331 SELECT id,object_version_number
2332 FROM OKC_K_LINES_B
2333 WHERE chr_id = p_chr_id
2334 AND lse_id IN (1,12,19,46);
2335
2336 CURSOR get_okstoplines(p_cle_id NUMBER) IS
2337 SELECT id,object_version_number
2338 FROM OKS_k_LINES_B
2339 WHERE cle_id = p_cle_id;
2340
2341 CURSOR get_cpl(p_chr_id number) IS
2342 SELECT id
2343 FROM okc_k_party_roles_b
2344 WHERE dnz_chr_id = p_chr_id
2345 AND cle_id is not null
2346 AND rle_code in ('CUSTOMER','SUBSCRIBER');
2347
2348 Cursor get_contacts(p_cpl_id number)IS
2349 SELECT id
2350 FROM Okc_contacts
2351 WHERE cpl_id = p_cpl_id;
2352
2353 get_topline_rec get_toplines%Rowtype;
2354 --get_qto_rec get_qto%rowtype;
2355 get_cpl_rec get_cpl%rowtype;
2356 l_khrv_tbl oks_khr_pvt.khrv_tbl_type;
2357 x_khrv_tbl oks_khr_pvt.khrv_tbl_type;
2358 l_error_tbl okc_api.error_tbl_type;
2359 l_chrv_tbl_in okc_contract_pub.chrv_tbl_type;
2360 l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
2361 l_clev_rec_in okc_contract_pub.clev_rec_type;
2362 l_clev_rec_out okc_contract_pub.clev_rec_type;
2363 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
2364 l_ctcv_tbl_out okc_contract_party_pub.ctcv_tbl_type;
2365 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
2366 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
2367 l_cpl_id NUMBER;
2368 l_contact_id NUMBER;
2369
2370 Begin
2371 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2372
2373 l_chrv_tbl_in(1).id := p_chr_id;
2374 l_chrv_tbl_in(1).cust_acct_id := NULL;
2375 l_chrv_tbl_in(1).Bill_to_site_use_id := NULL;
2376 l_chrv_tbl_in(1).ship_to_site_use_id := NULL;
2377 l_chrv_tbl_in(1).cust_po_number := NULL;
2378 l_chrv_tbl_in(1).cust_po_number_req_yn := NULL;
2379
2380 okc_contract_pub.update_contract_header
2381 (
2382 p_api_version => l_api_version,
2383 p_init_msg_list => l_init_msg_list,
2384 x_return_status => l_return_status,
2385 x_msg_count => l_msg_count,
2386 x_msg_data => l_msg_data,
2387 p_chrv_tbl => l_chrv_tbl_in,
2388 x_chrv_tbl => l_chrv_tbl_out
2389 );
2390 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2392 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2393 RAISE OKC_API.G_EXCEPTION_ERROR;
2394 END IF;
2395
2396
2397 For get_qto_rec In get_qto(p_chr_id)
2398 Loop
2399
2400
2401 l_khrv_tbl.delete;
2402
2403 l_khrv_tbl(1).id :=get_qto_rec.id;
2404 l_khrv_tbl(1).chr_id :=get_qto_rec.chr_id;
2405 l_khrv_tbl(1).quote_to_contact_id :=NULL;
2406 l_khrv_tbl(1).quote_to_site_id :=NULL;
2407 l_khrv_tbl(1).quote_to_email_id :=NULL;
2408 l_khrv_tbl(1).quote_to_phone_id :=NULL;
2409 l_khrv_tbl(1).quote_to_fax_id :=NULL;
2410 l_khrv_tbl(1).commitment_id :=NULL;
2411 l_khrv_tbl(1).payment_type :=NULL;
2412 l_khrv_tbl(1).service_po_number :=NULL;
2413 l_khrv_tbl(1).service_po_required :=NULL;
2414 l_khrv_tbl(1).renewal_po_number :=NULL;
2415 l_khrv_tbl(1).renewal_po_required :=NULL;
2416 l_khrv_tbl(1).renewal_po_used :=NULL;
2417 l_khrv_tbl(1).OBJECT_VERSION_NUMBER :=get_qto_rec.OBJECT_VERSION_NUMBER;
2418 l_khrv_tbl(1).CREATED_BY :=OKC_API.G_MISS_NUM;
2419 l_khrv_tbl(1).CREATION_DATE :=OKC_API.G_MISS_DATE;
2420 l_khrv_tbl(1).LAST_UPDATED_BY :=OKC_API.G_MISS_NUM;
2421 l_khrv_tbl(1).LAST_UPDATE_DATE :=OKC_API.G_MISS_DATE;
2422 l_khrv_tbl(1).LAST_UPDATE_LOGIN :=OKC_API.G_MISS_NUM;
2423
2424 OKS_CONTRACT_HDR_PUB.update_header (
2425 p_api_version => l_api_version,
2426 p_init_msg_list => OKC_API.G_FALSE,
2427 x_return_status => l_return_status,
2428 x_msg_count => l_msg_count,
2429 x_msg_data => l_msg_data,
2430 p_khrv_tbl => l_khrv_tbl,
2431 x_khrv_tbl => x_khrv_tbl,
2432 p_validate_yn => 'N');
2433
2434 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
2435 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2436 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
2437 RAISE OKC_API.G_EXCEPTION_ERROR;
2438 END IF;
2439
2440 End Loop; --For get_qto
2441
2442 x_return_status := l_return_status;
2443
2444 For get_TOPLINEs_rec In get_toplines(p_chr_id)
2445 LOOP
2446 l_clev_rec_in.id := get_toplines_rec.id;
2447 l_clev_rec_in.object_version_number:= get_toplines_rec.object_version_number;
2448 l_clev_rec_in.cust_acct_id := NULL;
2449 l_clev_rec_in.Bill_to_site_use_id := NULL;
2450 l_clev_rec_in.ship_to_site_use_id := NULL;
2451
2452 OKC_CONTRACT_PUB.UPDATE_CONTRACT_LINE (
2453 p_api_version => l_api_version,
2454 p_init_msg_list => l_init_msg_list,
2455 x_return_status => l_return_status,
2456 x_msg_count => l_msg_count,
2457 x_msg_data => l_msg_data,
2458 p_clev_rec => l_clev_rec_in,
2459 x_clev_rec => l_clev_rec_out
2460 );
2461 For get_okstoplines_rec In get_okstoplines(get_toplines_rec.id)
2462
2463 Loop
2464 -- errorout('oks_line id ='|| get_okstoplines_rec.id);
2465 l_kln_rec_in.id := get_okstoplines_rec.id;
2466 l_kln_rec_in.object_version_number:= get_okstoplines_rec.object_version_number;
2467 l_kln_rec_in.commitment_id := NULL;
2468 l_kln_rec_in.cust_po_number := NULL;
2469 l_kln_rec_in.cust_po_number_req_yn:= NULL;
2470 l_kln_rec_in.payment_type := NULL;
2471
2472 OKS_CONTRACT_LINE_PUB.UPDATE_LINE(
2473 p_api_version => l_api_version,
2474 p_init_msg_list => l_init_msg_list,
2475 x_return_status => x_return_status,
2476 x_msg_count => l_msg_count,
2477 x_msg_data => l_msg_data,
2478 p_klnv_rec => l_kln_rec_in,
2479 x_klnv_rec => l_kln_rec_out,
2480 p_validate_yn => 'N'
2481 );
2482 -- errorout('COPY update line status='||x_return_status);
2483 END LOOP;
2484
2485 END LOOP;
2486
2487 OPEN get_cpl(p_chr_id);
2488 FETCH get_cpl INTO l_cpl_id;
2489 CLOSE get_cpl;
2490 OPEN get_contacts(l_cpl_id);
2491 FETCH get_contacts into l_contact_id;
2492
2493 IF get_contacts%found Then
2494 ClOSE get_contacts;
2495
2496 l_ctcv_tbl_in(1).id := l_contact_id;
2497
2498 Okc_contract_party_pub.delete_contact
2499 (
2500 p_api_version => 1,
2501 p_init_msg_list => 'F',
2502 x_return_status => l_return_status,
2503 x_msg_count => l_msg_count,
2504 x_msg_data => l_msg_data,
2505 p_ctcv_tbl => l_ctcv_tbl_in
2506 );
2507 END IF;
2508
2509 EXCEPTION
2510 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2511 x_return_status := l_return_status;
2512 WHEN Others THEN
2513 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2514 OKC_API.set_message
2515 (
2516 G_APP_NAME,
2517 G_UNEXPECTED_ERROR,
2518 G_SQLCODE_TOKEN,
2519 SQLCODE,
2520 G_SQLERRM_TOKEN,
2521 SQLERRM
2522 );
2523
2524
2525 End Party_cleanup;
2526 -------------------------------------------------------------------------------
2527 -- Procedure: okscopy
2528 -- Purpose: copy procedure to be called from renew and copy forms
2529 -- and this copies header, topline and subline details
2530 -- It also updates the line numbers for the sublines copied
2531 -- It instantiates the coverage and subcription also
2532 -- In Parameters: p_chr_id contract id
2533 -- p_cle_id ine id
2534 -- p_upd_line_flag upd_line_flag
2535 -- p_bill_profile_flag the line id
2536 -- Out Parameters: x_return_status standard return status
2537 -----------------------------------------------------------------------------
2538
2539
2540 Procedure Okscopy
2541 ( p_chr_id NUMBER,
2542 p_cle_id NUMBER,
2543 x_return_status OUT NOCOPY VARCHAR2,
2544 p_upd_line_flag VARCHAR2,
2545 p_bill_profile_flag IN VARCHAR2 DEFAULT NULL
2546 ) IS
2547
2548 l_return_status VARCHAR2(1) :=OKC_API.G_RET_STS_SUCCESS;
2549 l_api_version CONSTANT NUMBER := 1.0;
2550 l_init_msg_list CONSTANT VARCHAR2(1):= 'F';
2551 l_msg_count NUMBER;
2552 l_msg_data VARCHAR2(2000):=null;
2553 l_old_cle_id NUMBER;
2554 l_count NUMBER;
2555 l_SLL_tbl_type OKS_BILL_SCH.StreamLvl_tbl;
2556 l_bil_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
2557 l_strlvl_tbl OKS_BILL_SCH.StreamLvl_tbl;
2558 l_ste_code VARCHAR2(30);
2559 l_old_chr_id number;
2560
2561
2562 CURSOR csr_top_lines(p_chr_id number) IS
2563 SELECT ID FROM OKC_K_LINES_B
2564 WHERE CHR_ID = P_CHR_ID AND LSE_ID IN (1,12,19,46);
2565
2566 CURSOR hdr_top_line_grp_csr (p_chr_id NUMBER) IS
2567 SELECT
2568 lines.id,
2569 lines.start_date,
2570 lines.orig_system_id1,
2571 lines.dnz_chr_id,
2572 lines.lse_id
2573 FROM okc_k_lines_b lines
2574 WHERE lines.dnz_chr_id = p_chr_id
2575 AND lines.cle_id IS NULL
2576 AND lines.lse_id IN (1,12,19, 46)
2577 ORDER BY lines.id;
2578
2579 CURSOR check_top_line_exist_csr (p_chr_id NUMBER) IS
2580 SELECT
2581 lines.id,
2582 lines.start_date,
2583 lines.orig_system_id1,
2584 lines.dnz_chr_id,
2585 lines.lse_id,
2586 price_list_id
2587 FROM okc_k_lines_b lines
2588 WHERE lines.dnz_chr_id = p_chr_id
2589 AND lines.cle_id IS NULL
2590 AND lines.lse_id IN (1,12,19, 46)
2591 ORDER BY lines.id;
2592
2593 CURSOR cle_grp_csr (p_chr_id NUMBER,p_cle_id NUMBER) IS
2594 SELECT
2595 lines.id,
2596 lines.start_date,
2597 lines.orig_system_id1,
2598 lines.dnz_chr_id,
2599 lines.lse_id
2600 FROM okc_k_lines_b lines
2601 WHERE lines.dnz_chr_id = p_chr_id
2602 AND lines.id = p_cle_id
2603 AND lines.lse_id IN (1,12,19, 46,7,8,9,10,11,35,13,18,25)
2604 ORDER BY lines.id;
2605 /*
2606 CURSOR Hdr_Billsch_type(p_chr_id NUMBER) IS
2607 SELECT billing_schedule_type
2608 FROM oks_k_headers_b
2609 where chr_id =p_chr_id;
2610 */
2611 -- Added for price lock project.
2612 CURSOR get_lines_attr_csr (p_cle_id NUMBER) IS
2613 SELECT *
2614 FROM oks_k_lines_b
2615 WHERE cle_id = p_cle_id
2616 and (LOCKED_PRICE_LIST_ID is not null or
2617 LOCKED_PRICE_LIST_LINE_ID is not null);
2618
2619 cursor get_price_list(l_cle_id number) is
2620 select PRICE_LIST_ID
2621 from okc_k_lines_b
2622 where id = l_cle_id;
2623
2624 CURSOR get_all_lines_csr (p_chr_id NUMBER) IS
2625 SELECT
2626 lines.id,
2627 lines.start_date,
2628 lines.orig_system_id1,
2629 lines.dnz_chr_id,
2630 lines.lse_id,
2631 lines.price_list_id
2632 FROM okc_k_lines_b lines
2633 WHERE lines.dnz_chr_id = p_chr_id
2634 AND lines.lse_id IN (12,13);
2635
2636 cursor get_contract_number(l_chr_id number) is
2637 select contract_number
2638 from okc_k_headers_b
2639 where id = l_chr_id;
2640
2641 -- End added for price lock project
2642
2643 CURSOR line_Billsch_type(p_cle_id NUMBER) IS
2644 SELECT Billing_schedule_type
2645 FROM oks_k_lines_b
2646 WHERE cle_id =p_cle_id;
2647
2648
2649 CURSOR get_item_csr (p_cle_id NUMBER) IS
2650 SELECT COUNT(id) cnt
2651 FROM okc_k_items
2652 WHERE cle_id = p_cle_id;
2653 get_item_rec get_item_csr%ROWTYPE;
2654
2655 /*
2656 CURSOR get_old_chr_id_csr (p_chr_id NUMBER) IS
2657 SELECT orig_system_id1
2658 FROM okc_k_headers_b
2659 WHERE id = p_chr_id;
2660 */
2661 CURSOR get_status_csr (l_chr_id NUMBER) IS
2662 SELECT b.ste_code, orig_system_id1
2663 FROM okc_k_headers_b a, okc_statuses_b b
2664 WHERE a.id = l_chr_id
2665 and a.sts_code = b.code;
2666
2667
2668 -- Get all top lines
2669 /*
2670 CURSOR get_top_lines(P_CHR_ID IN NUMBER) IS
2671 SELECT id
2672 FROM okc_k_lines_b
2673 WHERE chr_id = p_chr_id
2674 AND cle_id is null;
2675 */
2676
2677 --get_old_chr_id_rec get_old_chr_id_csr%ROWTYPE;
2678
2679 Cursor got_renewed(l_new_chr_id number) IS
2680 SELECT object_chr_id old_chr_id
2681 FROM okc_operation_lines
2682 WHERE subject_chr_id =l_new_chr_id and object_chr_id is not null;
2683
2684 Cursor is_terminated(l_old_chr_id number) IS
2685 select line_number
2686 from okc_k_lines_b
2687 where dnz_chr_id = l_old_chr_id
2688 and date_terminated is not null;
2689
2690 cursor cur_pradj (p_chr_id NUMBEr) is
2691 select chr_id,cle_id
2692 from okc_price_adjustments
2693 where chr_id =p_chr_id
2694 and cle_id is not null;
2695
2696 cursor is_line_copy(l_new_chr_id number, l_old_chr_id number) is
2697 select id from okc_k_lines_b
2698 where dnz_chr_id = l_new_chr_id and
2699 orig_system_id1 not in (select id from okc_k_lines_b where dnz_chr_id = l_old_chr_id);
2700
2701 l_oldchr_id NUMBER;
2702 l_duration_match VARCHAR2(1) := 'F';
2703 l_billing_schedule_type VARCHAR2(30);
2704 l_inv_rule_id NUMBER;
2705 l_hdr_billsch_type VARCHAR2(30);
2706 l_line_billsch_type VARCHAR2(30);
2707 l_sll_count NUMBER;
2708 l_topline_count NUMBER;
2709
2710 l_do_copy boolean;
2711 l_second_call boolean := false;
2712 l_line_num number;
2713 l_update_top_line boolean;
2714 l_okc_hdr_tbl OKC_CONTRACT_PUB.chrv_tbl_type;
2715 x_okc_hdr_tbl OKC_CONTRACT_PUB.chrv_tbl_type;
2716 l_line_id number;
2717 -- Added for price lock project
2718 l_contract_number VARCHAR2(120);
2719 l_old_price_list_id number;
2720 l_locked_price_list_id number;
2721 l_locked_price_list_line_id number;
2722 l_line_attr_rec get_lines_attr_csr%ROWTYPE;
2723 l_oks_line_id number;
2724 -- End price lock project
2725 --This proc call for chr copy
2726
2727
2728 -- 05-Aug-2005 hkamdar Added for R12 Partial Period Project
2729 -- This cursor fetches the Contract Id belonging to Original contract
2730 CURSOR get_old_id_csr (p_new_chr_id NUMBER) IS
2731 SELECT orig_system_id1
2732 FROM okc_k_headers_b
2733 WHERE id = p_new_chr_id;
2734
2735 -- New Variables
2736 l_new_period_type OKS_K_HEADERS_B.period_type%TYPE;
2737 l_new_period_start OKS_K_HEADERS_B.period_start%TYPE;
2738 l_old_period_type OKS_K_HEADERS_B.period_type%TYPE;
2739 l_old_period_start OKS_K_HEADERS_B.period_start%TYPE;
2740 l_price_uom OKS_K_HEADERS_B.price_uom%TYPE;
2741 l_period_start_equal VARCHAR2(1) := 'Y';
2742
2743 -- End for partial periods --
2744
2745 --begin Okscopy procedure
2746 BEGIN
2747
2748 OKS_RENEW_PVT.Debug_Log
2749 (p_program_name => 'OKSCOPY'
2750 ,p_perf_msg =>'OKSCOPY: start time= '||to_char(sysdate,'HH:MI:SS'));
2751 -- initialize return status
2752 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2753
2754 -- mk start
2755 -- The reason for this logic is that okscopy gets called twice for renew
2756 -- the first time, the new start date/end date of the contract header is
2757 -- not updated; therefore, we can't check to see if the contract duration is
2758 -- the same as original.
2759 l_do_copy := true;
2760 IF p_chr_id IS NOT NULL Then
2761 If p_upd_line_flag is null then
2762 -- If old chr id found it means the contract is getting renewed.
2763 Open got_renewed(p_chr_id);
2764 Fetch got_renewed into l_oldchr_id;
2765 If got_renewed%NOTFOUND Then
2766 l_duration_match := 'T'; -- The contract is being copied therefore the duration matches.
2767 l_do_copy := true;
2768 OKS_RENEW_PVT.Debug_Log
2769 (p_program_name => 'OKSCOPY'
2770 ,p_perf_msg =>'copy is true= '||to_char(sysdate,'HH:MI:SS'));
2771 Else
2772 -- Check if any line belongs to another contract then it's
2773 -- a line copy function on a renewed contract
2774 Open is_line_copy(p_chr_id, l_oldchr_id);
2775 Fetch is_line_copy into l_line_id;
2776 If is_line_copy%FOUND Then
2777 l_duration_match := 'T'; -- The contract is being copied therefore the duration matches.
2778 l_do_copy := true;
2779 OKS_RENEW_PVT.Debug_Log
2780 (p_program_name => 'OKSCOPY'
2781 ,p_perf_msg =>'copy is true2= '||to_char(sysdate,'HH:MI:SS'));
2782 Else
2783 l_duration_match := 'F';
2784 l_do_copy := false;
2785 OKS_RENEW_PVT.Debug_Log
2786 (p_program_name => 'OKSCOPY'
2787 ,p_perf_msg =>'copy is false 1= '||to_char(sysdate,'HH:MI:SS'));
2788 End If;
2789 Close is_line_copy;
2790 End If;
2791 Close got_renewed;
2792 Else
2793 l_do_copy := false;
2794 l_second_call := true;
2795 OKS_RENEW_PVT.Debug_Log
2796 (p_program_name => 'OKSCOPY'
2797 ,p_perf_msg =>'copy is false2 = '||to_char(sysdate,'HH:MI:SS'));
2798 chk_hdr_effectivity(
2799 p_chr_id,
2800 l_duration_match,
2801 l_return_status
2802 );
2803
2804 IF l_return_status <> 'S' Then
2805 OKC_API.set_message
2806 (G_APP_NAME,
2807 G_UNEXPECTED_ERROR,
2808 G_SQLCODE_TOKEN,
2809 SQLCODE,
2810 G_SQLERRM_TOKEN,
2811 'Check Header Effectivity ERROR');
2812
2813 RAISE G_EXCEPTION_HALT_VALIDATION;
2814 End If;
2815 End If;
2816
2817 -- 05-Aug-2005 hkamdar new logic for R12 Partial Periods --
2818 IF l_duration_match = 'T' THEN
2819
2820 OKS_RENEW_UTIL_PUB.get_period_defaults
2821 (p_hdr_id => p_chr_id,
2822 p_org_id => NULL,
2823 x_period_type => l_new_period_type,
2824 x_period_start => l_new_period_start,
2825 x_price_uom => l_price_uom,
2826 x_return_status => l_return_status);
2827
2828 If l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2829
2830 RAISE G_EXCEPTION_HALT_VALIDATION;
2831
2832 End If;
2833
2834 Open get_old_id_csr(p_chr_id);
2835 Fetch get_old_id_csr into l_oldchr_id;
2836 close get_old_id_csr;
2837
2838 OKS_RENEW_UTIL_PUB.get_period_defaults
2839 (p_hdr_id => l_oldchr_id,
2840 p_org_id => NULL,
2841 x_period_type => l_old_period_start,
2842 x_period_start => l_old_period_type,
2843 x_price_uom => l_price_uom,
2844 x_return_status => l_return_status);
2845
2846 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2847
2848 RAISE G_EXCEPTION_HALT_VALIDATION;
2849
2850 End If;
2851
2852 --If period starts of original and renewed contract is
2853 --same or both are null then set the l_period_start_equal
2854 -- to 'Y'. At the time of creation of billing schedule
2855 -- verify whether l_period_start_equal flag is 'Y' or not.
2856
2857 -- Added this portion as per Partial Periods Change Request 001.
2858 IF NVL(l_new_period_start,'SERVICE')= NVl(l_old_period_start,'SERVICE')
2859 THEN
2860 l_Period_start_equal := 'Y';
2861 ELSE
2862 l_Period_start_equal := 'X';
2863 END IF; -- End for l_new_period_start is Not Null
2864 END IF; -- l_duration
2865 -- End new logic for R12 Partial Periods --
2866
2867 -- Added so that Terminated and Active contracts go into QA hold during copy.
2868 Open get_status_csr(p_chr_id);
2869 Fetch get_status_csr into l_ste_code, l_old_chr_id;
2870 Close get_status_csr;
2871
2872 If l_ste_code in ('ACTIVE', 'TERMINATED') Then
2873 okc_version_pub.save_version
2874 (p_chr_id => p_chr_id,
2875 p_api_version => 1.0,
2876 p_init_msg_list => 'T',
2877 x_return_status => l_return_status,
2878 x_msg_count => l_msg_count,
2879 x_msg_data => l_msg_data,
2880 p_commit => 'F'
2881 );
2882 IF l_return_status <> 'S' Then
2883 OKC_API.set_message
2884 (G_APP_NAME,
2885 G_UNEXPECTED_ERROR,
2886 G_SQLCODE_TOKEN,
2887 SQLCODE,
2888 G_SQLERRM_TOKEN,
2889 'Error in save version');
2890 RAISE G_EXCEPTION_HALT_VALIDATION;
2891 End If;
2892 oks_contract_hdr_pub.save_version
2893 ( p_api_version => 1.0,
2894 p_init_msg_list => 'T',
2895 x_return_status => l_return_status,
2896 x_msg_count => l_msg_count,
2897 x_msg_data => l_msg_data,
2898 p_chr_id => p_chr_id);
2899 IF l_return_status <> 'S' Then
2900 OKC_API.set_message
2901 (G_APP_NAME,
2902 G_UNEXPECTED_ERROR,
2903 G_SQLCODE_TOKEN,
2904 SQLCODE,
2905 G_SQLERRM_TOKEN,
2906 'Error in OKS save version');
2907 RAISE G_EXCEPTION_HALT_VALIDATION;
2908 End If;
2909 -- Go into QA hold.
2910 l_okc_hdr_tbl(1).id := p_chr_id;
2911 l_okc_hdr_tbl(1).sts_code := 'QA_HOLD';
2912 OKC_CONTRACT_PUB.update_contract_header(
2913 p_api_version => 1.0,
2914 p_init_msg_list => OKC_API.G_FALSE,
2915 x_return_status => l_return_status,
2916 x_msg_count => l_msg_count,
2917 x_msg_data => l_msg_data,
2918 p_restricted_update => 'N',
2919 p_chrv_tbl => l_okc_hdr_tbl,
2920 x_chrv_tbl => x_okc_hdr_tbl
2921 );
2922 IF l_return_status <> 'S' Then
2923 OKC_API.set_message
2924 (G_APP_NAME,
2925 G_UNEXPECTED_ERROR,
2926 G_SQLCODE_TOKEN,
2927 SQLCODE,
2928 G_SQLERRM_TOKEN,
2929 'Error in update_contract_header');
2930 RAISE G_EXCEPTION_HALT_VALIDATION;
2931 End If;
2932 OKS_RENEW_PVT.Debug_Log
2933 (p_program_name => 'OKSCOPY'
2934 ,p_perf_msg =>'Went into QA hold: start time= '||to_char(sysdate,'HH:MI:SS'));
2935 End If;
2936
2937
2938 End If; -- If p_chr_id IS NOT NULL
2939
2940
2941 --1. copy on header level
2942
2943 IF (p_chr_id IS NOT NULL AND (p_cle_id IS NULL
2944 OR p_cle_id = OKC_API.G_MISS_NUM)) THEN
2945
2946 For cur_pradj_rec in cur_pradj(p_chr_id)
2947 LOOP
2948 update okc_price_adjustments
2949 set chr_id = null
2950 where chr_id =cur_pradj_rec.chr_id
2951 and cle_id =cur_pradj_rec.cle_id;
2952 END LOOP;
2953 --Copy header attributes
2954
2955 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
2956 copy_hdr_attr
2957 (p_chr_id => l_old_chr_id,
2958 p_new_chr_id => p_chr_id,
2959 p_duration_match => l_duration_match,
2960 x_return_status => l_return_status);
2961 END IF;
2962
2963 --Added as part of fix for 5002535
2964 IF (l_return_status = 'E') then
2965 RAISE G_EXCEPTION_HALT_VALIDATION;
2966 END IF;
2967
2968
2969 OKS_RENEW_PVT.Debug_Log
2970 (p_program_name => 'OKSCOPY'
2971 ,p_perf_msg =>'Copy Header Attribute status '||l_return_status);
2972
2973 x_return_status := l_return_status;
2974
2975 --- Enhancement done on 12/04/2003 asked by Siti
2976 -- Header notes should be created during copy and renew.
2977 If (l_return_status = 'S' AND p_upd_line_flag IS NULL) Then
2978
2979 OKS_COVERAGES_PVT.COPY_K_HDR_NOTES(
2980 p_api_version => l_api_version,
2981 p_init_msg_list => l_init_msg_list,
2982 p_chr_id => p_chr_id,
2983 x_return_status => l_return_status,
2984 x_msg_count => l_msg_count,
2985 x_msg_data => l_msg_data);
2986
2987 OKS_RENEW_PVT.Debug_Log
2988 (p_program_name => 'OKSCOPY'
2989 ,p_perf_msg =>'COPY_K_HDR_NOTES '||l_return_status);
2990
2991 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2992 -- Empties the message stack and puts the error in the log file.
2993 Get_Error_Stack;
2994 -- We don't want an error in copy notes to stop everything.
2995 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2996 End If;
2997 End If;
2998
2999
3000
3001 --Calls procedure copy_hdr_sales_credits
3002
3003 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3004 copy_hdr_sales_credits
3005 (p_chr_id => l_old_chr_id,
3006 p_new_chr_id => p_chr_id,
3007 x_return_status => l_return_status);
3008
3009 x_return_status := l_return_status;
3010
3011 OKS_RENEW_PVT.Debug_Log
3012 (p_program_name => 'OKSCOPY'
3013 ,p_perf_msg =>'copy Hedader salescredit Status '||l_return_status);
3014
3015 END IF; --l_return_status = 'S'
3016
3017 --Copy Header billing schedules
3018
3019 IF l_duration_match = 'T' and p_bill_profile_flag is null Then
3020 IF (l_return_status = 'S' ) THEN
3021
3022 --AND p_upd_line_flag IS NULL) THEN commented out because not copying
3023 --header billing schedule in the case of renewal and okscopy is called --from renewal to copy billing schedule
3024
3025 Copy_Hdr_Bill_Sch( p_chr_id => l_old_chr_id,
3026 p_new_chr_id => p_chr_id,
3027 x_return_status => l_return_status);
3028
3029 OKS_RENEW_PVT.Debug_Log
3030 (p_program_name => 'OKSCOPY'
3031 ,p_perf_msg =>'copy Hedader Billing Schedule Status '||l_return_status);
3032 END IF;
3033 END IF;
3034 l_return_status := 'S';---tO BE REMOVED LATER ON FOR TESTING PURPOSE ONLY
3035
3036
3037
3038 FOR check_top_line_exist_rec IN check_top_line_exist_csr (p_chr_id)
3039 LOOP
3040 l_old_cle_id := check_top_line_exist_rec.orig_system_id1;
3041
3042 --check if the topline is already created in the oks table
3043 -- this check is mandatory because okscopy is called twice in case
3044 -- of line copy to the same contract and that will duplicate the line
3045
3046 l_topline_count:=chk_topline_exists(check_top_line_exist_rec.id);
3047
3048 IF l_topline_count>0 Then
3049
3050 FOR top_lines_rec IN csr_top_lines(p_chr_id)
3051 LOOP
3052 UPDATE okc_k_lines_b set
3053 price_negotiated = (SELECT sum(price_negotiated) FROM okc_k_lines_b
3054 WHERE dnz_chr_id = p_chr_id AND chr_id is null
3055 AND cle_id = top_lines_rec.id)
3056 WHERE lse_id in (1, 19, 12) -- added lse id 12 for bug # 3534513
3057 AND chr_id = p_chr_id AND id = top_lines_rec.id;
3058
3059 l_sll_count:=chk_sll_exists(top_lines_rec.id);
3060 -- 8/5/05 hkamdar R12 added check for period start equal as per Partial Periods Change Request 001.
3061
3062 IF l_duration_match = 'T' and p_bill_profile_flag is null AND nvl(l_Period_start_equal,'Y') = 'Y' Then
3063 IF l_sll_count=0 THEN
3064
3065 get_billing_attr
3066 (p_chr_id => p_chr_id,
3067 p_cle_id => top_lines_rec.id,
3068 x_billing_schedule_type => l_billing_schedule_type,
3069 x_inv_rule_id => l_inv_rule_id,
3070 x_return_status => l_return_status
3071 );
3072
3073 get_strlvls
3074 (p_chr_id => p_chr_id,
3075 p_cle_id => top_lines_rec.id,
3076 p_billsch_type => l_billing_schedule_type,
3077 x_strlvl_tbl => l_strlvl_tbl,
3078 x_return_status => l_return_status
3079 );
3080
3081 oks_bill_sch.create_bill_sch_rules
3082 (
3083 p_billing_type => l_billing_schedule_type,
3084 p_sll_tbl => l_strlvl_tbl,
3085 p_invoice_rule_id => l_inv_rule_id,
3086 x_bil_sch_out_tbl => l_bil_sch_out_tbl,
3087 x_return_status => l_return_status);
3088
3089 OKS_RENEW_PVT.Debug_Log
3090 (p_program_name => 'OKSCOPY'
3091 ,p_perf_msg =>'copy Lines Billing Schedule Status '||l_return_status);
3092 END IF; --l_sll_count=0
3093 END IF; --l_duration_match = 'T'
3094 End loop;
3095 -- The check to see if this is a copy or it's being called the second time
3096 -- in renew was added because this API should only get called once and it should
3097 -- have the new contract dates.
3098 --IF (check_top_line_exist_rec.lse_id in (1,19) AND l_second_call) Then
3099 IF check_top_line_exist_rec.lse_id in (1,19) AND (l_second_call or l_do_copy) Then
3100 if not chk_coverage_exists( check_top_line_exist_rec.id) then
3101
3102 OKS_COVERAGES_PVT.Copy_Coverage
3103 (p_api_version => 1.0 ,
3104 p_init_msg_list => OKC_API.G_FALSE ,
3105 x_return_status => l_return_status ,
3106 x_msg_count => l_msg_count ,
3107 x_msg_data => l_msg_data ,
3108 p_contract_line_id => check_top_line_exist_rec.id );
3109
3110 OKS_RENEW_PVT.Debug_Log
3111 (p_program_name => 'OKSCOPY'
3112 ,p_perf_msg =>'Copy Coverage Status '||l_return_status);
3113
3114 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3115 x_return_status := l_return_status;
3116 Raise G_EXCEPTION_HALT_VALIDATION;
3117 End If;
3118 END IF;
3119 END IF;
3120
3121
3122 END IF; --l_topline_count>0
3123
3124
3125
3126 IF l_topline_count=0 then
3127
3128
3129 l_return_status := 'S';--assigning this for case of subscription subline
3130 --copy, the l_return_status becomes U ,because there is no subscription subline
3131 --and calls directly subcopy
3132
3133 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3134 copy_lines_attr
3135 (p_cle_id => check_top_line_exist_rec.orig_system_id1,
3136 p_new_cle_id => check_top_line_exist_rec.id,
3137 p_new_chr_id => check_top_line_exist_rec.dnz_chr_id,
3138 p_do_copy => l_do_copy,
3139 x_return_status => l_return_status);
3140
3141 END IF;
3142
3143 OKS_RENEW_PVT.Debug_Log
3144 (p_program_name => 'OKSCOPY'
3145 ,p_perf_msg =>'copy Lines Attr Status '||l_return_status);
3146
3147 --check item exists
3148 OPEN get_item_csr(check_top_line_exist_rec.id);
3149 FETCH get_item_csr INTO get_item_rec;
3150
3151 IF get_item_rec.cnt >0 THEN
3152 OPEN line_Billsch_type(check_top_line_exist_rec.id);
3153 FETCH line_Billsch_type INTO l_line_billsch_type;
3154 CLOSE line_Billsch_type;
3155
3156 --Calls sub_copy proc
3157 sub_copy
3158 (
3159 p_chr_id => p_chr_id,
3160 p_cle_id => check_top_line_exist_rec.id,
3161 p_start_date => check_top_line_exist_rec.start_date,
3162 p_upd_line_flag => p_upd_line_flag,
3163 p_billing_schedule_type => l_line_billsch_type,
3164 p_duration_match => l_duration_match,
3165 p_bill_profile_flag=>p_bill_profile_flag,
3166 p_do_copy => l_do_copy,
3167 x_return_status => l_return_status
3168 );
3169 -- Commented on 03/20/2002 as OKC has fixed the problem of sequencing -Anupama
3170 -- Uncommented on 11/03/2002 as OKC has changed its code. Bug#2462154 --mkhayer
3171 --Added as part of fix for bug 5002535
3172 IF (l_return_status = 'E') then
3173 RAISE G_EXCEPTION_HALT_VALIDATION;
3174 END IF;
3175
3176
3177
3178 --Calls procedure for updating credit card values
3179
3180 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3181 check_CCR_rule_line(p_chr_id => p_chr_id,
3182 p_cle_id => check_top_line_exist_rec.id,
3183 x_return_status => l_return_status);
3184 x_return_status := l_return_status;
3185
3186 OKS_RENEW_PVT.Debug_Log
3187 (p_program_name => 'OKSCOPY'
3188 ,p_perf_msg =>'check CCR Rule Status '||l_return_status);
3189
3190 END IF; --l_return_status = 'S'
3191
3192 --Calls procedure copy_revenue_distb
3193
3194 IF p_upd_line_flag IS NULL THEN
3195 copy_revenue_distb
3196 (p_cle_id => check_top_line_exist_rec.orig_system_id1,
3197 p_new_cle_id => check_top_line_exist_rec.id,
3198 p_new_chr_id => check_top_line_exist_rec.dnz_chr_id,
3199 x_return_status => l_return_status);
3200 x_return_status := l_return_status;
3201
3202 OKS_RENEW_PVT.Debug_Log
3203 (p_program_name => 'OKSCOPY'
3204 ,p_perf_msg =>'Copy Lines revenue dist Status '||l_return_status);
3205 END IF; --l_return_status = 'S'
3206
3207 --Calls procedure copy_line_sales_credits
3208
3209 IF p_upd_line_flag IS NULL THEN
3210 copy_line_sales_credits
3211 (p_cle_id => check_top_line_exist_rec.orig_system_id1,
3212 p_new_cle_id => check_top_line_exist_rec.id,
3213 p_new_chr_id => check_top_line_exist_rec.dnz_chr_id,
3214 x_return_status => l_return_status);
3215
3216 x_return_status := l_return_status;
3217
3218 OKS_RENEW_PVT.Debug_Log
3219 (p_program_name => 'OKSCOPY'
3220 ,p_perf_msg =>'Copy Line Sales Credit Status '||l_return_status);
3221
3222 END IF; --l_return_status = 'S'
3223
3224 -- The check to see if this is a copy or it's being called the second time
3225 -- in renew was added because this API should only get called once and it should
3226 -- have the new contract dates.
3227 IF (check_top_line_exist_rec.lse_id in (1,19) AND l_do_copy ) Then
3228
3229 OKS_COVERAGES_PVT.Copy_Coverage
3230 (p_api_version => 1.0 ,
3231 p_init_msg_list => OKC_API.G_FALSE ,
3232 x_return_status => l_return_status ,
3233 x_msg_count => l_msg_count ,
3234 x_msg_data => l_msg_data ,
3235 p_contract_line_id => check_top_line_exist_rec.id );
3236
3237 OKS_RENEW_PVT.Debug_Log
3238 (p_program_name => 'OKSCOPY'
3239 ,p_perf_msg =>'Copy Coverage Status '||l_return_status);
3240
3241 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3242 x_return_status := l_return_status;
3243 Raise G_EXCEPTION_HALT_VALIDATION;
3244 End If;
3245
3246 END IF;
3247
3248
3249 IF (p_upd_line_flag is NULL and check_top_line_exist_rec.lse_id = 46) THEN
3250 copy_subscr_inst(check_top_line_exist_rec.dnz_chr_id
3251 ,check_top_line_exist_rec.id
3252 ,null
3253 ,l_return_status);
3254
3255 OKS_RENEW_PVT.Debug_Log
3256 (p_program_name => 'OKSCOPY'
3257 ,p_perf_msg =>'Copy subscr_inst Status '||l_return_status);
3258
3259 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3260 x_return_status := l_return_status;
3261 Raise G_EXCEPTION_HALT_VALIDATION;
3262 End If;
3263
3264 END IF;
3265
3266 ELSE
3267
3268 --Calls delete line proc
3269 oks_coverages_pub.undo_line(
3270 p_api_version => l_api_version,
3271 p_init_msg_list => l_init_msg_list,
3272 x_return_status => l_return_status,
3273 x_msg_count => l_msg_count,
3274 x_msg_data => l_msg_data,
3275 p_Line_Id => check_top_line_exist_rec.id);
3276
3277 x_return_status := l_return_status;
3278
3279 END IF; --l_get_item_rec.cnt >0
3280 CLOSE get_item_csr;
3281 END IF;
3282 END LOOP; --for check_top_line_exist_csr
3283
3284 --2. copy on top line level
3285
3286 ELSIF (p_chr_id IS NOT NULL AND p_cle_id IS NOT NULL
3287 AND p_cle_id <> OKC_API.G_MISS_NUM) THEN
3288
3289 FOR cle_grp_rec IN cle_grp_csr (p_chr_id,p_cle_id)
3290 LOOP
3291 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3292 copy_lines_attr
3293 (p_cle_id => cle_grp_rec.orig_system_id1,
3294 p_new_cle_id => cle_grp_rec.id,
3295 p_new_chr_id => cle_grp_rec.dnz_chr_id,
3296 x_return_status => l_return_status);
3297
3298 OKS_RENEW_PVT.Debug_Log
3299 (p_program_name => 'OKSCOPY'
3300 ,p_perf_msg =>'Line Copy line_attr Status'||l_return_status);
3301
3302 END IF;
3303
3304 OPEN get_item_csr(p_cle_id);
3305 FETCH get_item_csr INTO get_item_rec;
3306
3307 IF get_item_rec.cnt >0 THEN
3308 OPEN line_Billsch_type(p_cle_id);
3309 FETCH line_Billsch_type INTO l_line_billsch_type;
3310 CLOSE line_Billsch_type;
3311
3312 -- Commented on 03/20/2002 as OKC has fixed the problem of sequencing -Anupama
3313 -- Uncommented on 11/03/2002 as OKC has changed its code. Bug#2462154 --mkhayer
3314
3315 --Calls procedure copy_revenue_distb
3316
3317 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3318 copy_revenue_distb
3319 (p_cle_id => cle_grp_rec.orig_system_id1,
3320 p_new_cle_id => cle_grp_rec.id,
3321 p_new_chr_id => cle_grp_rec.dnz_chr_id,
3322 x_return_status => l_return_status);
3323
3324 x_return_status := l_return_status;
3325 OKS_RENEW_PVT.Debug_Log
3326 (p_program_name => 'OKSCOPY'
3327 ,p_perf_msg =>'Line Copy line_revenue_dist Status'||l_return_status);
3328 END IF; --l_return_status = 'S'
3329
3330 --Calls procedure copy_line_sales_credits
3331
3332 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3333 copy_line_sales_credits
3334 (p_cle_id => cle_grp_rec.orig_system_id1,
3335 p_new_cle_id => cle_grp_rec.id,
3336 p_new_chr_id => cle_grp_rec.dnz_chr_id,
3337 x_return_status => l_return_status);
3338
3339 x_return_status := l_return_status;
3340
3341 OKS_RENEW_PVT.Debug_Log
3342 (p_program_name => 'OKSCOPY'
3343 ,p_perf_msg =>'Line Copy line_sales_credit Status'||l_return_status);
3344
3345 END IF; --l_return_status = 'S'
3346
3347 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3348 check_CCR_rule_line(p_chr_id => p_chr_id,
3349 p_cle_id => cle_grp_rec.id,
3350 x_return_status => l_return_status);
3351 x_return_status := l_return_status;
3352
3353 OKS_RENEW_PVT.Debug_Log
3354 (p_program_name => 'OKSCOPY'
3355 ,p_perf_msg =>'check CCR Rule Status'||l_return_status);
3356
3357 END IF; --l_return_status = 'S'
3358
3359 IF (p_upd_line_flag is null and cle_grp_rec.lse_id = 46) THEN
3360 copy_subscr_inst(cle_grp_rec.dnz_chr_id
3361 ,cle_grp_rec.id
3362 ,null
3363 ,l_return_status);
3364
3365 OKS_RENEW_PVT.Debug_Log
3366 (p_program_name => 'OKSCOPY'
3367 ,p_perf_msg =>'Line Copy line_subscr_inst Status'||l_return_status);
3368 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3369 x_return_status := l_return_status;
3370 Raise G_EXCEPTION_HALT_VALIDATION;
3371 End If;
3372
3373 END IF;--IF
3374
3375 /*
3376 If (cle_grp_rec.lse_id in (1,19) AND p_upd_line_flag IS NULL) Then
3377
3378 OKS_COVERAGES_PVT.Copy_Coverage
3379 (p_api_version => 1.0 ,
3380 p_init_msg_list => OKC_API.G_FALSE ,
3381 x_return_status => l_return_status ,
3382 x_msg_count => l_msg_count ,
3383 x_msg_data => l_msg_data ,
3384 p_contract_line_id => cle_grp_rec.id );
3385
3386 OKS_RENEW_PVT.Debug_Log
3387 (p_program_name => 'OKSCOPY'
3388 ,p_perf_msg =>'Copy Coverage Status'||l_return_status);
3389
3390 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3391 x_return_status := l_return_status;
3392 Raise G_EXCEPTION_HALT_VALIDATION;
3393 End If;
3394 END IF;
3395 */
3396 ELSE
3397
3398 --Calls delete line proc
3399 oks_coverages_pub.undo_line(
3400 p_api_version => l_api_version,
3401 p_init_msg_list => l_init_msg_list,
3402 x_return_status => l_return_status,
3403 x_msg_count => l_msg_count,
3404 x_msg_data => l_msg_data,
3405 p_Line_Id => p_cle_id);
3406 x_return_status := l_return_status;
3407
3408 END IF; --l_get_item_rec.cnt >0
3409 CLOSE get_item_csr;
3410
3411 END LOOP; --FOR cle_grp_rec IN cle_grp_csr
3412
3413 --3. if no parameter passed
3414
3415 ELSIF (p_chr_id IS NULL AND p_cle_id IS NULL) THEN
3416 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3417 raise G_EXCEPTION_HALT_VALIDATION;
3418
3419 END IF;-- IF (p_chr_id IS NOT NULL AND p_cle_id IS NULL)
3420
3421
3422
3423
3424
3425
3426 IF (l_return_status = 'S' AND p_upd_line_flag IS NULL) THEN
3427 IF not chk_party_exists(p_chr_id) then
3428 Party_cleanup(p_chr_id,l_return_status);
3429
3430 END IF;
3431 END IF;
3432
3433
3434
3435
3436
3437 -- Added for updating top line price_negotiated for copy
3438 IF p_upd_line_flag IS NULL and p_chr_id is not null THEN
3439
3440 -- Get the sum of all the top lines.
3441 update okc_k_headers_b set
3442 estimated_amount =
3443 (select sum(price_negotiated) from okc_k_lines_b
3444 where dnz_chr_id = p_chr_id and cle_id is null)
3445 where id = p_chr_id;
3446 End If;
3447
3448
3449 IF p_upd_line_flag IS NULL THEN
3450 l_update_top_line := false;
3451 -- means it's being called in renew
3452 If l_duration_match = 'F' Then
3453 Open is_terminated (l_oldchr_id);
3454 Fetch is_terminated into l_line_num;
3455 If is_terminated%FOUND Then
3456 l_update_top_line := true;
3457 End If;
3458 Close is_terminated ;
3459 End If;
3460 update_line_numbers (p_chr_id => p_chr_id
3461 ,p_update_top_line => l_update_top_line
3462 ,x_return_status => l_return_status);
3463
3464 OKS_RENEW_PVT.Debug_Log
3465 (p_program_name => 'OKSCOPY'
3466 ,p_perf_msg =>'Update Line Number Status'||l_return_status);
3467
3468 x_return_status := l_return_status;
3469 END IF;
3470
3471 -- Check if original contract has a price lock and if so check if price list is
3472 -- the same as the original contract. Price lock should get removed if
3473 -- the price list is not the same.
3474 If p_chr_id IS NOT NULL and p_upd_line_flag is not null then
3475 For get_lines_rec in get_all_lines_csr(p_chr_id) Loop
3476 l_old_cle_id := get_lines_rec.orig_system_id1;
3477 For get_oks_line_attr in get_lines_attr_csr(l_old_cle_id) Loop
3478 If get_oks_line_attr.LOCKED_PRICE_LIST_ID is not null Then
3479 Open get_price_list(l_old_cle_id);
3480 Fetch get_price_list into l_old_price_list_id;
3481 Close get_price_list;
3482 -- For renewal the price lock doesnt get copied in the first
3483 -- okscopy call. It gets copied in the second call only
3484 -- if the price list ids are the same.
3485 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3486 p_perf_msg =>'old price list id: ' || l_old_price_list_id);
3487 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3488 p_perf_msg =>'new price list id: ' || get_lines_rec.price_list_id);
3489 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3490 p_perf_msg =>'new contract lse id: ' || get_lines_rec.lse_id);
3491 If nvl(l_old_price_list_id, -99) = nvl(get_lines_rec.price_list_id, -99) Then
3492 Open get_contract_number(p_chr_id);
3493 Fetch get_contract_number into l_contract_number;
3494 Close get_contract_number;
3495 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3496 p_perf_msg =>'Calling QP API');
3497 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3498 p_perf_msg =>' get_oks_line_attr.LOCKED_PRICE_LIST_LINE_ID: ' || get_oks_line_attr.LOCKED_PRICE_LIST_LINE_ID);
3499 -- MKS Commented out.. Need to put back in once QP patch is ready.
3500 /*
3501 QP_LOCK_PRICELIST_GRP.Lock_Price(p_source_list_line_id => get_oks_line_attr.LOCKED_PRICE_LIST_LINE_ID,
3502 p_list_source_code => 'OKS',
3503 p_orig_system_header_ref => l_contract_number,
3504 x_locked_price_list_id => l_locked_price_list_id,
3505 x_locked_list_line_id => l_locked_price_list_line_id,
3506 x_return_status => l_return_status,
3507 x_msg_count => l_msg_count,
3508 x_msg_data => l_msg_data);
3509
3510 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) then
3511 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3512 p_perf_msg =>'QP failed.');
3513 RAISE G_EXCEPTION_HALT_VALIDATION;
3514 END IF;
3515 */
3516 select id
3517 into l_oks_line_id
3518 from oks_k_lines_b where cle_id = get_lines_rec.id;
3519
3520 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3521 p_perf_msg =>'After cursor call');
3522 Update oks_k_lines_b set
3523 LOCKED_PRICE_LIST_ID = l_locked_price_list_id,
3524 LOCKED_PRICE_LIST_LINE_ID = l_locked_price_list_line_id,
3525 break_uom = get_oks_line_attr.break_uom
3526 where id = l_oks_line_id;
3527 OKS_RENEW_PVT.Debug_Log(p_program_name => 'OKSCOPY',
3528 p_perf_msg =>'After update');
3529
3530 End If; -- if price list ids are different
3531 End If; -- if original contract has a price list id
3532 End Loop; -- loop through original contract OKS attributes
3533 End Loop; -- loop through new contracts
3534 End If; -- if being called in renew the second time
3535
3536
3537 OKS_RENEW_PVT.Debug_Log
3538 (p_program_name => 'OKSCOPY'
3539 ,p_perf_msg =>'OKSCOPY: end time= '||to_char(sysdate,'HH:MI:SS'));
3540
3541 x_return_status := l_return_status;
3542 EXCEPTION
3543 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3544 x_return_status := l_return_status;
3545
3546 WHEN OTHERS THEN
3547 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3548 OKC_API.set_message
3549 (G_APP_NAME,
3550 G_UNEXPECTED_ERROR,
3551 G_SQLCODE_TOKEN,
3552 SQLCODE,
3553 G_SQLERRM_TOKEN,
3554 SQLERRM);
3555
3556
3557 END Okscopy;
3558
3559
3560 -------------------------------------------------------------------------------
3561 -- Procedure: Update_line_numbers
3562 -- Purpose: This procedure updates the line numbers for the subline
3563 -- copied
3564 -- In Parameters: p_chr_id contract id
3565 -- In Parameters: p_cle_id line id
3566 -- Out Parameters: x_return_status standard return status
3567 -----------------------------------------------------------------------------
3568
3569
3570 /** Update_line_number procedure overloaded
3571 -- aiyengar
3572 -- 10/10/2001
3573 **/
3574
3575 PROCEDURE Update_Line_Numbers
3576 (
3577 p_chr_id IN NUMBER,
3578 p_cle_id IN NUMBER,
3579 x_return_status OUT NOCOPY VARCHAR2
3580 )
3581 IS
3582 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3583 -- will select all covered levels for given cle_id
3584
3585 CURSOR l_csr_okc_k_lines IS
3586 SELECT id
3587 FROM OKC_K_LINES_B
3588 where dnz_chr_id = p_chr_id
3589 and cle_id = p_cle_id
3590 and lse_id IN (7,8,9,10,11,35,13,18,25);
3591
3592 l_subline_id NUMBER;
3593 l_line_seq_no NUMBER;
3594
3595 yes_flag VARCHAR2(1);
3596
3597 BEGIN
3598 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3599 -- This code has been modified to add p_cle_id as input parameter
3600
3601 l_line_seq_no := 0;
3602
3603 OPEN l_csr_okc_k_lines;
3604 LOOP
3605 FETCH l_csr_okc_k_lines INTO l_subline_id;
3606 IF l_csr_okc_k_lines%FOUND THEN
3607
3608 l_line_seq_no := l_line_seq_no + 1;
3609 UPDATE okc_k_lines_b
3610 SET line_number = l_line_seq_no
3611 WHERE id = l_subline_id;
3612
3613 ELSE
3614 EXIT;
3615 END IF; -- End if subline not found
3616
3617 END LOOP; -- End of inner loop
3618 CLOSE l_csr_okc_k_lines;
3619 x_return_status := l_return_status;
3620 END ;
3621
3622 -------------------------------------------------------------------------------
3623 -- Procedure: update_line_numbers
3624 -- Purpose: This procedure updates the line_numbers of the contract
3625 -- id passed
3626 -- In Parameters: p_chr_id new contract id
3627 -- p_update_top_line flag that is asking us to update line number
3628 -- Out Parameters: x_return_status standard return status
3629 -----------------------------------------------------------------------------
3630
3631 PROCEDURE Update_Line_Numbers
3632 (
3633 p_chr_id IN NUMBER,
3634 p_update_top_line IN BOOLEAN,
3635 x_return_status OUT NOCOPY VARCHAR2
3636 )
3637 IS
3638
3639 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3640
3641 Cursor l_csr_header IS
3642 SELECT id from okc_k_headers_b
3643 WHERE scs_code in ('SERVICE','WARRANTY')
3644 AND id =p_chr_id;
3645
3646 Cursor l_csr_top_line(p_chr_id NUMBER) IS
3647 SELECT id
3648 FROM OKC_K_LINES_B
3649 Where dnz_chr_id = p_chr_id
3650 and lse_id IN (1,12,14,19, 46)
3651 order by line_number;
3652
3653
3654 CURSOR l_csr_okc_k_lines(p_top_line_id NUMBER) IS
3655 SELECT id
3656 FROM OKC_K_LINES_B
3657 where cle_id = p_top_line_id
3658 and lse_id IN (7,8,9,10,11,35,13,18,25);
3659
3660
3661 --CURSOR l_csr_process_control IS
3662 --SELECT processflag
3663 --FROM oks_process_control
3664 --WHERE RTRIM(LTRIM(UPPER(filename))) = 'OKSLNMIG';
3665
3666
3667 l_topline_id NUMBER;
3668 l_subline_id NUMBER;
3669 l_old_topline NUMBER;
3670 l_line_seq_no NUMBER;
3671 l_topline_seq NUMBER;
3672 l_contract_count NUMBER;
3673 l_chr_id NUMBER;
3674 l_old_chr_id NUMBER;
3675 l_process_flag VARCHAR2(1);
3676 tot_contracts NUMBER;
3677
3678 yes_flag VARCHAR2(1);
3679
3680 BEGIN
3681
3682 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3683 l_contract_count :=0;
3684
3685
3686 -- l_contract_count := l_contract_count + 1;
3687 l_topline_seq := 0;
3688 OPEN l_csr_top_line(p_chr_id);
3689 LOOP
3690 FETCH l_csr_top_line INTO l_topline_id;
3691 IF l_csr_top_line%NOTFOUND THEN
3692 EXIT;
3693 ELSE
3694 l_topline_seq := l_topline_seq + 1;
3695
3696 If p_update_top_line Then
3697 UPDATE OKC_K_LINES_B
3698 SET line_number = l_topline_seq
3699 WHERE id = l_topline_id;
3700 End If;
3701
3702
3703 l_line_seq_no := 0;
3704 OPEN l_csr_okc_k_lines(l_topline_id);
3705 LOOP
3706 FETCH l_csr_okc_k_lines INTO l_subline_id;
3707 IF l_csr_okc_k_lines%FOUND THEN
3708
3709 l_line_seq_no := l_line_seq_no + 1;
3710 UPDATE okc_k_lines_b
3711 SET line_number = l_line_seq_no
3712 WHERE id = l_subline_id;
3713
3714
3715 ELSE
3716 EXIT;
3717 END IF; -- End if subline not found
3718
3719 END LOOP; -- End of inner loop
3720 CLOSE l_csr_okc_k_lines;
3721 END IF; ---End if l_csr_topline not found
3722
3723 END LOOP; --- End of topline loop
3724 CLOSE l_csr_top_line;
3725
3726
3727
3728 x_return_status := l_return_status;
3729
3730
3731 EXCEPTION
3732 WHEN OTHERS THEN
3733 x_return_status := 'E';
3734
3735 END Update_Line_Numbers;
3736
3737
3738
3739
3740
3741 -------------------------------------------------------------------------------
3742 -- Procedure: get_qto_details
3743 -- Purpose: Build several records/tables that hold information to be
3744 -- used to pass to OC APIs
3745 -- In Parameters: p_new_chr_id new contract id
3746 -- Out Parameters: x_return_status standard return status
3747 -- x_flag yes no flag
3748 -----------------------------------------------------------------------------
3749
3750
3751
3752 PROCEDURE Get_QTO_Details
3753 (
3754 p_api_version IN Number,
3755 p_init_msg_list IN Varchar2,
3756 P_commit IN Varchar2,
3757 p_chr_id IN Number,
3758 p_type IN Varchar2,
3759 x_contact_dtl_rec OUT NOCOPY contact_dtl_rec,
3760 x_return_status OUT NOCOPY Varchar2,
3761 x_msg_count OUT NOCOPY Number,
3762 x_msg_data OUT NOCOPY Varchar2
3763 )
3764 Is
3765
3766 Cursor hdr_qto_csr is
3767 SELECT
3768 QUOTE_TO_CONTACT_ID
3769 ,QUOTE_TO_SITE_ID
3770 ,QUOTE_TO_EMAIL_ID
3771 ,QUOTE_TO_PHONE_ID
3772 ,QUOTE_TO_FAX_ID
3773 FROM OKS_K_HEADERS_B
3774 WHERE chr_id = p_chr_id;
3775
3776 Cursor get_org_values IS
3777 SELECT inv_organization_id,
3778 authoring_org_id
3779 FROM okc_k_headers_b
3780 WHERE id=p_chr_id;
3781
3782 Cursor l_hdr_svc_csr Is
3783 SELECT ctc.object1_id1 ,
3784 pt.name contact_name,
3785 pt.party_id,
3786 hz.party_name party_name
3787 FROM okc_contacts_v ctc,
3788 okx_party_contacts_v pt,
3789 hz_parties hz
3790 WHERE ctc.cro_code = 'SVC_ADMIN'
3791 AND ctc.dnz_chr_id = p_chr_id
3792 AND pt.id1 = ctc.object1_id1
3793 AND pt.id2 = ctc.object1_id2
3794 AND pt.party_id = hz.party_id;
3795 --And pt.party_id2 = hz.id2;
3796
3797 Cursor email_csr(p_email_id IN Number) is
3798 SELECT lower(email_address)
3799 FROM HZ_CONTACT_POINTS
3800 WHERE contact_point_id = p_email_id;
3801
3802 Cursor phone_csr(p_phone_id IN Number) is
3803 SELECT DECODE(PHONE_AREA_CODE,NULL,NULL,PHONE_AREA_CODE||'-')
3804 ||PHONE_NUMBER phone_number
3805 FROM HZ_CONTACT_POINTS
3806 WHERE contact_point_id = p_phone_id;
3807
3808 Cursor fax_csr(p_fax_id IN Number) is
3809 SELECT DECODE(PHONE_AREA_CODE, NULL,NULL,PHONE_AREA_CODE||'-')
3810 ||PHONE_NUMBER phone_number
3811 FROM HZ_CONTACT_POINTS
3812 WHERE contact_point_id = p_fax_id;
3813
3814 Cursor party_csr(p_contact_id IN Number) is
3815 SELECT b.party_name
3816 FROM okx_cust_contacts_v a, hz_parties b
3817 WHERE a.id1=p_contact_id
3818 AND a.party_id=b.party_id;
3819
3820 Cursor contact_name_csr(p_contact_id IN Number) is
3821 SELECT ltrim(rtrim(substr(pt.name,instr(pt.name,',')+1)))||' '||
3822 ltrim(rtrim(substr(pt.name,1,instr(pt.name,',')-1))) contact_name
3823 ,InitCap(ltrim(rtrim(SUBSTR(pt.name,INSTR(pt.name,',')+1)))) contact_first_name
3824 FROM okx_cust_contacts_v pt
3825 WHERE pt.id1 = p_contact_id;
3826
3827 cursor l_qtoadd_csr (l_site_id in Number) Is
3828 SELECT
3829 loc.ADDRESS1||''||loc.ADDRESS2||''||loc.ADDRESS3||''||loc.ADDRESS4 Address
3830 ,loc.CITY||' '||loc.state||' '||loc.postal_code city
3831 ,loc.country
3832 FROM okx_cust_sites_v loc
3833 WHERE id1 = l_site_id ;
3834
3835 l_hdr_svc_rec l_hdr_svc_csr%ROWTYPE;
3836 hdr_qto_rec hdr_qto_csr%ROWTYPE;
3837 l_site_use_id number;
3838 l_site_id number;
3839 l_contact_id number;
3840 l_email_id number;
3841 l_phone_id number;
3842 l_fax_id number;
3843 l_inv_org_id number;
3844 l_auth_org_id number;
3845
3846 ---l_site_use_id Number;
3847 Begin
3848
3849 Open hdr_qto_csr;
3850 Fetch hdr_qto_csr Into hdr_qto_rec;
3851 Close hdr_qto_csr;
3852
3853 OPEN get_org_values;
3854 FETCH get_org_values INTO l_inv_org_id, l_auth_org_id ;
3855 CLOSE get_org_values;
3856
3857 okc_context.set_okc_org_context(l_auth_org_id,l_inv_org_id);
3858
3859 x_contact_dtl_rec.contact_id := hdr_qto_rec.QUOTE_TO_CONTACT_ID;
3860 --- x_contact_dtl_rec.quote_site_id := hdr_qto_rec.object2_id1;
3861
3862 l_contact_id := hdr_qto_rec.QUOTE_TO_CONTACT_ID;
3863 l_email_id := hdr_qto_rec.QUOTE_TO_EMAIL_ID;
3864 l_phone_id := hdr_qto_rec.QUOTE_TO_PHONE_ID;
3865 l_fax_id := hdr_qto_rec.QUOTE_TO_FAX_ID;
3866 l_site_id := hdr_qto_rec.QUOTE_TO_SITE_ID;
3867
3868 Open party_csr(l_contact_id);
3869 Fetch party_csr into x_contact_dtl_rec.party_name;
3870 Close party_csr;
3871
3872 Open contact_name_csr(l_contact_id);
3873 Fetch contact_name_csr
3874 INTO x_contact_dtl_rec.contact_name
3875 ,x_contact_dtl_rec.contact_first_name ;
3876 Close contact_name_csr;
3877
3878 -- Get the contact's primary email address (if any)
3879 Open email_csr(l_email_id);
3880 Fetch email_csr into x_contact_dtl_rec.email;
3881 Close email_csr;
3882
3883 -- Get the contact's primary telephone number (if any)
3884 Open phone_csr(l_phone_id);
3885 Fetch phone_csr into x_contact_dtl_rec.phone;
3886 Close phone_csr;
3887
3888 -- Get the contact's fax number (if any)
3889 Open fax_csr(l_fax_id);
3890 Fetch fax_csr into x_contact_dtl_rec.fax;
3891 Close fax_csr;
3892
3893 Open l_qtoadd_csr ( l_site_id);
3894 Fetch l_qtoadd_csr Into
3895 x_contact_dtl_rec.quote_address,
3896 x_contact_dtl_rec.quote_city,
3897 x_contact_dtl_rec.quote_country;
3898 -- x_contact_dtl_rec.quote_site_id;
3899
3900 Close l_qtoadd_csr;
3901
3902
3903 EXCEPTION
3904 WHEN OTHERS THEN
3905 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3906 OKC_API.set_message
3907 (G_APP_NAME,
3908 G_UNEXPECTED_ERROR,
3909 G_SQLCODE_TOKEN,
3910 SQLCODE,
3911 G_SQLERRM_TOKEN,
3912 SQLERRM);
3913 END;
3914
3915
3916
3917 -- This Procedure creates a new QTO rule for a contract header. --
3918 -- Default QTO address, email, phone and fax are selected for --
3919 -- the customer contact specified. The OKC organization context --
3920 -- must be set before calling this procedure. --
3921 -- Procedure Author - Jacob K. --
3922 -- Created - 10/29/2001 --
3923
3924 PROCEDURE Create_Qto_Rule(p_api_version IN NUMBER,
3925 p_init_msg_list IN VARCHAR2,
3926 p_chr_id IN NUMBER,
3927 p_contact_id IN NUMBER,
3928 x_return_status OUT NOCOPY VARCHAR2,
3929 x_msg_count OUT NOCOPY NUMBER,
3930 x_msg_data OUT NOCOPY VARCHAR2) IS
3931 -- Contact address
3932 cursor address_cur_new is
3933 select a.id1
3934 from okx_cust_sites_v a,
3935 okx_cust_contacts_v b
3936 where b.id1 = p_contact_id
3937 and a.id1 = b.cust_acct_site_id;
3938
3939 -- Primary e-mail address
3940 cursor email_cur_new is
3941 select contact_point_id
3942 -- from okx_contact_points_v
3943 from hz_contact_points
3944 where contact_point_type = 'EMAIL'
3945 and primary_flag = 'Y'
3946 and owner_table_id = p_contact_id;
3947
3948 -- Primary telephone number
3949 cursor phone_cur_new is
3950 select contact_point_id
3951 from hz_contact_points
3952 where contact_point_type = 'PHONE'
3953 and NVL(phone_line_type,'GEN') = 'GEN'
3954 and primary_flag = 'Y'
3955 and owner_table_id = p_contact_id;
3956
3957 -- Any one fax number
3958 cursor fax_cur_new is
3959 select contact_point_id
3960 from hz_contact_points
3961 where contact_point_type = 'PHONE'
3962 and phone_line_type = 'FAX'
3963 and owner_table_id = p_contact_id;
3964
3965 l_return_status Varchar2(1);
3966 l_msg_count Number;
3967 l_msg_data Varchar2(2000);
3968 l_api_version Number := 1;
3969 l_init_msg_list Varchar2(1) := 'F';
3970 l_QUOTE_TO_CONTACT_ID NUMBER;
3971 l_QUOTE_TO_SITE_ID NUMBER;
3972 l_QUOTE_TO_EMAIL_ID NUMBER;
3973 l_QUOTE_TO_PHONE_ID NUMBER;
3974 l_QUOTE_TO_FAX_ID NUMBER;
3975
3976 l_khrv_tbl OKS_KHR_PVT.khrv_tbl_type;
3977 x_khrv_tbl OKS_KHR_PVT.khrv_tbl_type;
3978
3979 BEGIN
3980 x_return_status := 'E';
3981 If p_contact_id is not null Then
3982 --
3983 -- Get the default contact points and address
3984 --
3985
3986 -- Get the contact site address (if any)
3987 Open address_cur_new;
3988 Fetch address_cur_new into l_QUOTE_TO_site_id;
3989 Close address_cur_new;
3990
3991 -- Get the contact's primary email address (if any)
3992 Open email_cur_new;
3993 Fetch email_cur_new into l_QUOTE_TO_email_id;
3994 Close email_cur_new;
3995
3996 -- Get the contact's primary telephone number (if any)
3997 Open phone_cur_new;
3998 Fetch phone_cur_new into l_QUOTE_TO_phone_id;
3999 Close phone_cur_new;
4000
4001 -- Get the contact's fax number (if any)
4002 Open fax_cur_new;
4003 Fetch fax_cur_new into l_QUOTE_TO_fax_id;
4004 Close fax_cur_new;
4005
4006 l_khrv_tbl(1).chr_id := p_chr_id;
4007 l_khrv_tbl(1).QUOTE_TO_CONTACT_ID:=l_QUOTE_TO_CONTACT_ID;
4008 l_khrv_tbl(1).QUOTE_TO_SITE_ID :=l_QUOTE_TO_SITE_ID;
4009 l_khrv_tbl(1).QUOTE_TO_EMAIL_ID :=l_QUOTE_TO_EMAIL_ID;
4010 l_khrv_tbl(1).QUOTE_TO_PHONE_ID :=l_QUOTE_TO_PHONE_ID;
4011 l_khrv_tbl(1).QUOTE_TO_FAX_ID :=l_QUOTE_TO_FAX_ID;
4012
4013
4014 OKS_CONTRACT_HDR_PUB.update_header (
4015 p_api_version => l_api_version,
4016 p_init_msg_list => OKC_API.G_FALSE,
4017 x_return_status => l_return_status,
4018 x_msg_count => l_msg_count,
4019 x_msg_data => l_msg_data,
4020 p_khrv_tbl => l_khrv_tbl,
4021 x_khrv_tbl => x_khrv_tbl,
4022 p_validate_yn => 'Y');
4023
4024 End If; -- p_contact_id is not null
4025 EXCEPTION
4026 When Others Then
4027 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4028 OKC_API.set_message (G_APP_NAME,
4029 G_UNEXPECTED_ERROR,
4030 G_SQLCODE_TOKEN,
4031 SQLCODE,
4032 G_SQLERRM_TOKEN,
4033 SQLERRM);
4034 END Create_Qto_Rule;
4035
4036
4037 FUNCTION Resp_Org_id RETURN NUMBER IS
4038 Begin
4039 If fnd_profile.value('OKC_VIEW_K_BY_ORG') = 'Y' then
4040 return fnd_profile.value('ORG_ID');
4041 Else
4042 return null;
4043 End If;
4044 End Resp_Org_id;
4045
4046
4047 -- *************************************************************************************************
4048 PROCEDURE UNDO_COUNTERS(P_Kline_Id IN NUMBER,
4049 x_Return_Status OUT NOCOPY VARCHAR2,
4050 x_msg_data OUT NOCOPY VARCHAR2) IS
4051
4052 CURSOR Cur_Cgp (P_KLine_Id IN NUMBER) IS
4053 SELECT Counter_Group_id FROM OKX_Counter_Groups_V WHERE Source_Object_Id=P_KLine_Id
4054
4055 and Source_Object_Code='CONTRACT_LINE';
4056
4057 CURSOR Cur_OVN (P_CtrGrp_Id IN NUMBER) IS
4058 SELECT Object_Version_Number FROM Cs_Counter_Groups
4059 WHERE Counter_group_Id=P_CtrGrp_Id;
4060
4061 TYPE t_IdTable IS TABLE OF NUMBER(35)
4062 INDEX BY BINARY_Integer;
4063 l_cgp_tbl t_IdTable;
4064 c_Cgp Number:=1;
4065 l_Ctr_grp_id NUMBER;
4066 x_Object_Version_Number NUMBER;
4067 l_Object_Version_Number NUMBER;
4068 l_api_version CONSTANT NUMBER := 1.0;
4069 l_init_msg_list CONSTANT VARCHAR2(1):= 'T';
4070 l_return_status VARCHAR2(1);
4071 l_msg_count NUMBER;
4072 l_msg_data VARCHAR2(2000):=null;
4073 l_msg_index_out Number;
4074 l_api_name CONSTANT VARCHAR2(30) := 'UNDO COUNTERS';
4075 l_Commit Varchar2(3) ;
4076 l_Ctr_Grp_Rec CS_Counters_Pub.CtrGrp_Rec_Type;
4077 l_cascade_upd_to_instances Varchar2(1);
4078 BEGIN
4079 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4080 FOR Cgp_Rec IN Cur_Cgp(P_KLine_Id)
4081 LOOP
4082 l_cgp_tbl(c_Cgp):=Cgp_Rec.counter_group_Id;
4083 c_Cgp:=c_Cgp+1;
4084 FOR i in 1 .. l_Cgp_tbl.COUNT
4085 LOOP
4086 l_Ctr_grp_Id:=l_Cgp_tbl(i);
4087 l_Ctr_Grp_Rec.end_date_active:=sysdate;
4088 OPEN Cur_OVN(l_ctr_Grp_Id);
4089 FETCH Cur_OVN INTO l_Object_version_Number;
4090 CLOSE Cur_OVN;
4091 CS_Counters_PUB.Update_Ctr_Grp(
4092 p_api_version =>l_api_version,
4093 p_init_msg_list =>l_init_msg_list,
4094 p_commit =>l_commit,
4095 x_return_status =>l_return_status,
4096 x_msg_count =>l_msg_count,
4097 x_msg_data =>l_msg_data,
4098 p_ctr_grp_id =>l_ctr_grp_id,
4099 p_object_version_number => l_object_version_number,
4100 p_ctr_grp_rec =>l_ctr_grp_rec,
4101 p_cascade_upd_to_instances =>l_cascade_upd_to_instances,
4102 x_object_version_number => x_object_version_number
4103 );
4104 if l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4105 OKC_API.set_message(
4106 G_APP_NAME,
4107 G_UNEXPECTED_ERROR,
4108 G_SQLCODE_TOKEN,
4109 SQLCODE,
4110 G_SQLERRM_TOKEN,
4111 'Error in update counter.'
4112 );
4113 RAISE G_EXCEPTION_HALT_VALIDATION;
4114 end if;
4115
4116 END LOOP;
4117 END LOOP;
4118 x_Return_Status:=l_return_status;
4119
4120 EXCEPTION
4121 When G_EXCEPTION_HALT_VALIDATION Then
4122 x_Return_Status:=l_Return_status;
4123 WHEN OTHERS THEN
4124 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4125 OKC_API.set_message
4126 (G_APP_NAME,
4127 G_UNEXPECTED_ERROR,
4128 G_SQLCODE_TOKEN,
4129 SQLCODE,
4130 G_SQLERRM_TOKEN,
4131 SQLERRM);
4132 END Undo_Counters;
4133
4134 -------------------------------------------------------------------------------
4135 -- Procedure: Delete_OKS_Line
4136 -- Purpose: This procedure takes the okc line id and deletes all
4137 -- lines related to this line in oks_k_lines_b.
4138 --
4139 -- In Parameters: p_cle_id contract line id
4140 -- Out Parameters: x_return_status standard return status
4141 -----------------------------------------------------------------------------
4142 PROCEDURE Delete_OKS_Line(
4143 p_cle_id IN NUMBER,
4144 x_return_status OUT NOCOPY VARCHAR2
4145 ) IS
4146
4147 l_return_status VARCHAR2(1);
4148 l_api_version CONSTANT NUMBER := 1.0;
4149 l_init_msg_list CONSTANT VARCHAR2(1):= 'T';
4150 l_msg_count NUMBER;
4151 l_msg_data VARCHAR2(2000):=null;
4152 l_klnv_rec oks_contract_line_pub.klnv_rec_type;
4153 l_cle_id number;
4154 l_counter number := 1;
4155 l_temp_counter number := 1;
4156
4157 cursor get_child(l_cle_id number) is
4158 select id from okc_k_lines_b where cle_id = l_cle_id;
4159
4160 Type t_number_tbl is table of number Index by BINARY_INTEGER;
4161
4162
4163 l_cle_tbl t_number_tbl;
4164 l_temp_tbl t_number_tbl;
4165 l_first_index BINARY_INTEGER;
4166
4167 cursor get_oks_line(l_cle_id number) is
4168 select id from oks_k_lines_b where cle_id = l_cle_id;
4169
4170 Begin
4171 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4172
4173 l_temp_tbl(l_temp_counter) := p_cle_id;
4174 l_temp_counter := l_temp_counter + 1;
4175
4176 While (l_temp_tbl.count > 0) Loop
4177 l_first_index := l_temp_tbl.first;
4178 l_cle_tbl(l_counter) := l_temp_tbl(l_first_index);
4179 l_cle_id := l_temp_tbl(l_first_index);
4180 l_counter := l_counter + 1;
4181 l_temp_tbl.delete(l_first_index);
4182 For get_child_rec in get_child(l_cle_id) loop
4183 l_temp_tbl(l_temp_counter) := get_child_rec.id;
4184 l_temp_counter := l_temp_counter + 1;
4185 End Loop;
4186 End Loop;
4187
4188 While (l_cle_tbl.count > 0) Loop
4189 l_temp_counter := l_cle_tbl.first;
4190 Open get_oks_line(l_cle_tbl(l_temp_counter));
4191 Fetch get_oks_line into l_klnv_rec.id;
4192 Close get_oks_line;
4193
4194 oks_contract_line_pub.delete_line(
4195 p_api_version => l_api_version,
4196 p_init_msg_list => l_init_msg_list,
4197 x_return_status => l_return_status,
4198 x_msg_count => l_msg_count,
4199 x_msg_data => l_msg_data,
4200 p_klnv_rec => l_klnv_rec);
4201
4202 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4203 OKC_API.set_message(
4204 G_APP_NAME,
4205 G_UNEXPECTED_ERROR,
4206 G_SQLCODE_TOKEN,
4207 SQLCODE,
4208 G_SQLERRM_TOKEN,
4209 'Error while deleting OKS Line'
4210 );
4211 RAISE G_EXCEPTION_HALT_VALIDATION;
4212 End if;
4213
4214 l_cle_tbl.delete(l_temp_counter);
4215
4216 End loop;
4217
4218 x_return_status := l_return_status;
4219
4220 EXCEPTION
4221 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4222 x_return_status := l_return_status;
4223 WHEN OTHERS THEN
4224 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4225 OKC_API.set_message
4226 (G_APP_NAME,
4227 G_UNEXPECTED_ERROR,
4228 G_SQLCODE_TOKEN,
4229 SQLCODE,
4230 G_SQLERRM_TOKEN,
4231 SQLERRM);
4232
4233 End Delete_OKS_Line;
4234
4235 PROCEDURE Delete_Contract (
4236 p_api_version IN NUMBER,
4237 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4238 p_chr_id IN NUMBER,
4239 x_return_status OUT NOCOPY VARCHAR2,
4240 x_msg_count OUT NOCOPY NUMBER,
4241 x_msg_data OUT NOCOPY VARCHAR2) IS
4242
4243 -- Get all lines
4244 CURSOR Cur_Line (P_Chr_Id IN NUMBER) IS
4245 SELECT ID FROM OKC_K_Lines_b
4246 WHERE chr_ID=p_chr_Id;
4247
4248 CURSOR Cur_gov (P_chr_Id IN NUMBER) IS
4249 SELECT ID FROM OKC_GOVERNANCES
4250 WHERE dnz_chr_ID=p_chr_Id
4251 And cle_id Is Null;
4252
4253 CURSOR get_oks_hdr(p_chr_id number) IS
4254 select id from oks_k_headers_b where chr_id = p_chr_id;
4255
4256 cursor topline_csr(p_chr_id number) is
4257 select a.id, a.lse_id
4258 from okc_k_lines_b a
4259 where a.dnz_chr_id = p_chr_id and a.cle_id IS NULL;
4260
4261
4262 l_khrv_rec oks_contract_hdr_pub.khrv_rec_type;
4263 l_klnv_rec oks_contract_line_pub.klnv_rec_type;
4264
4265 l_chrv_rec okc_contract_pub.chrv_rec_type;
4266 l_Line_Id NUMBER;
4267 --
4268 l_api_version CONSTANT NUMBER := 1.0;
4269 l_init_msg_list CONSTANT VARCHAR2(1):= 'T';
4270 l_return_status VARCHAR2(1);
4271 l_msg_count NUMBER;
4272 l_msg_data VARCHAR2(2000):=null;
4273 l_msg_index_out Number;
4274 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract';
4275 --
4276 l_gvev_tbl_in okc_contract_pub.gvev_tbl_type;
4277 e_error Exception;
4278 n NUMBER;
4279 m NUMBER;
4280 v_Index NUMBER;
4281 TYPE line_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4282 l_Line_tbl line_tbl_Type;
4283 BEGIN
4284
4285 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4286
4287 -- Input Validation
4288 IF p_chr_id IS NULL THEN
4289 OKC_API.set_message(
4290 G_APP_NAME,
4291 G_UNEXPECTED_ERROR,
4292 G_SQLCODE_TOKEN,
4293 SQLCODE,
4294 G_SQLERRM_TOKEN,
4295 'Header id passed to Delete_Contract is Null'
4296 );
4297 l_return_status := OKC_API.G_RET_STS_ERROR;
4298 RAISE G_EXCEPTION_HALT_VALIDATION;
4299 END IF;
4300 ---------- Get all lines -------------
4301 n:=1;
4302 FOR Line_Rec IN Cur_Line(p_chr_id)
4303 LOOP
4304 l_line_tbl(n):=Line_Rec.Id;
4305 n:=n+1;
4306 END LOOP;
4307
4308 FOR topline_rec IN topline_csr(p_chr_id) LOOP
4309 -- will delete all the existing SLH, SLL and level elements for top line and its sub lines
4310 OKS_BILL_SCH.Del_Rul_Elements(p_top_line_id => topline_rec.id,
4311 x_return_status => l_return_status,
4312 x_msg_count => l_msg_count,
4313 x_msg_data => l_msg_data);
4314
4315 Delete_OKS_Line(
4316 p_cle_id => topline_rec.id,
4317 x_return_status => l_return_status);
4318
4319 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4320 OKC_API.set_message(
4321 G_APP_NAME,
4322 G_UNEXPECTED_ERROR,
4323 G_SQLCODE_TOKEN,
4324 SQLCODE,
4325 G_SQLERRM_TOKEN,
4326 'Error from Delete_OKS_Line'
4327 );
4328 RAISE G_EXCEPTION_HALT_VALIDATION;
4329 End if;
4330
4331 -- We only delete coverages for the top lines.
4332 -- Each top line has only one coverage
4333 If topline_rec.lse_id in (1, 14, 19) Then
4334 -- Deletes coverages and PM schedules and coverage lines
4335 OKS_COVERAGES_PVT.Undo_Line(
4336 p_api_version => l_api_version,
4337 p_init_msg_list => l_init_msg_list,
4338 p_validate_status => 'Y',
4339 x_return_status => l_return_status,
4340 x_msg_count => l_msg_count,
4341 x_msg_data => l_msg_data,
4342 P_Line_Id => topline_rec.id);
4343 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4344 OKC_API.set_message(
4345 G_APP_NAME,
4346 G_UNEXPECTED_ERROR,
4347 G_SQLCODE_TOKEN,
4348 SQLCODE,
4349 G_SQLERRM_TOKEN,
4350 'Error in deleting coverage lines.'
4351 );
4352 RAISE G_EXCEPTION_HALT_VALIDATION;
4353 End if;
4354 End If;
4355
4356
4357 End Loop;
4358 ---------- Get all Governances ---------
4359 n:=1;
4360 FOR Gov_Rec IN Cur_gov(p_chr_id)
4361 LOOP
4362 l_gvev_tbl_in(n).Id:=Gov_Rec.Id;
4363 n:=n+1;
4364 END LOOP;
4365
4366 ---------- Delete Governance --------------
4367 IF NOT l_gvev_tbl_In.COUNT=0
4368 THEN
4369 okc_Contract_pub.delete_governance(
4370 p_api_version => l_api_version,
4371 p_init_msg_list => l_init_msg_list,
4372 x_return_status => l_return_status,
4373 x_msg_count => l_msg_count,
4374 x_msg_data => l_msg_data,
4375 p_gvev_tbl => l_gvev_tbl_in);
4376 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4377 OKC_API.set_message(
4378 G_APP_NAME,
4379 G_UNEXPECTED_ERROR,
4380 G_SQLCODE_TOKEN,
4381 SQLCODE,
4382 G_SQLERRM_TOKEN,
4383 'Error while deleting governance'
4384 );
4385 RAISE G_EXCEPTION_HALT_VALIDATION;
4386 End if;
4387 END IF;
4388
4389
4390 --------------Undo counters --------------
4391 IF NOT l_line_tbl.COUNT=0 THEN
4392 --v_Index:=l_line_tbl.COUNT;
4393 FOR v_Index IN l_line_tbl.FIRST .. l_line_tbl.LAST
4394 LOOP
4395 l_Line_Id:=l_line_tbl(v_Index);
4396 Undo_Counters( P_KLine_Id => l_Line_Id,
4397 x_return_status => l_return_status,
4398 x_msg_data => l_msg_data);
4399 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4400 OKC_API.set_message(
4401 G_APP_NAME,
4402 G_UNEXPECTED_ERROR,
4403 G_SQLCODE_TOKEN,
4404 SQLCODE,
4405 G_SQLERRM_TOKEN,
4406 'Error while deleting Counters'
4407 );
4408 RAISE G_EXCEPTION_HALT_VALIDATION;
4409 End if;
4410 END LOOP;
4411 END IF;
4412
4413 ---------------- Delete OKS header and line -----------------------
4414 Open get_oks_hdr(p_chr_id);
4415 Fetch get_oks_hdr into l_khrv_rec.id;
4416 Close get_oks_hdr;
4417 oks_contract_hdr_pub.delete_header(
4418 p_api_version => l_api_version,
4419 p_init_msg_list => l_init_msg_list,
4420 x_return_status => l_return_status,
4421 x_msg_count => l_msg_count,
4422 x_msg_data => l_msg_data,
4423 p_khrv_rec => l_khrv_rec);
4424 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4425 OKC_API.set_message(
4426 G_APP_NAME,
4427 G_UNEXPECTED_ERROR,
4428 G_SQLCODE_TOKEN,
4429 SQLCODE,
4430 G_SQLERRM_TOKEN,
4431 'Error while deleting OKS Header'
4432 );
4433 RAISE G_EXCEPTION_HALT_VALIDATION;
4434 End if;
4435
4436 oks_contract_hdr_pub.delete_history(
4437 p_api_version =>l_api_version,
4438 p_init_msg_list =>l_init_msg_list,
4439 x_return_status =>l_return_status,
4440 x_msg_count =>l_msg_count,
4441 x_msg_data =>l_msg_data,
4442 p_chr_id => p_chr_id);
4443 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4444 OKC_API.set_message(
4445 G_APP_NAME,
4446 G_UNEXPECTED_ERROR,
4447 G_SQLCODE_TOKEN,
4448 SQLCODE,
4449 G_SQLERRM_TOKEN,
4450 'Error while deleting OKS History'
4451 );
4452 RAISE G_EXCEPTION_HALT_VALIDATION;
4453 End if;
4454
4455 ---------- Delete Contract from OKC (Header and Line) ----------
4456 l_chrv_rec.id := p_chr_id;
4457 OKC_DELETE_CONTRACT_PUB.delete_contract(
4458 p_api_version =>l_api_version,
4459 p_init_msg_list =>l_init_msg_list,
4460 x_return_status =>l_return_status,
4461 x_msg_count =>l_msg_count,
4462 x_msg_data =>l_msg_data,
4463 p_chrv_rec =>l_chrv_rec);
4464 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4465 OKC_API.set_message(
4466 G_APP_NAME,
4467 G_UNEXPECTED_ERROR,
4468 G_SQLCODE_TOKEN,
4469 SQLCODE,
4470 G_SQLERRM_TOKEN,
4471 'Error while deleting Contract'
4472 );
4473 RAISE G_EXCEPTION_HALT_VALIDATION;
4474
4475 End if;
4476
4477
4478 x_return_status:=l_return_status;
4479 EXCEPTION
4480 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4481 x_return_status:=l_return_status;
4482 WHEN OTHERS THEN
4483 OKC_API.set_message
4484 (G_APP_NAME,
4485 G_UNEXPECTED_ERROR,
4486 G_SQLCODE_TOKEN,
4487 SQLCODE,
4488 G_SQLERRM_TOKEN,
4489 SQLERRM);
4490 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4491
4492 End Delete_Contract;
4493
4494
4495 PROCEDURE Delete_Contract_Line(
4496 p_api_version IN NUMBER,
4497 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4498 p_line_id IN NUMBER,
4499 x_return_status OUT NOCOPY VARCHAR2,
4500 x_msg_count OUT NOCOPY NUMBER,
4501 x_msg_data OUT NOCOPY VARCHAR2) IS
4502
4503 l_return_status VARCHAR2(1);
4504 l_lse_id number;
4505 l_parent_id number;
4506 l_api_version CONSTANT NUMBER := 1.0;
4507 l_init_msg_list CONSTANT VARCHAR2(1):= 'T';
4508 l_msg_count NUMBER;
4509 l_msg_data VARCHAR2(2000):=null;
4510
4511 cursor get_line_type(l_line_id number) is
4512 select lse_id, cle_id
4513 from okc_k_lines_b
4514 where id = l_line_id;
4515
4516
4517 l_klnv_rec oks_contract_line_pub.klnv_rec_type;
4518
4519 TYPE line_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4520 l_line_tbl line_Tbl_Type;
4521
4522 n number;
4523 l_cov_line_id number;
4524 v_Index number;
4525 l_clev_rec OKC_CLE_PVT.clev_rec_type;
4526
4527 Begin
4528 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4529
4530 Open get_line_type(p_line_id);
4531 Fetch get_line_type into l_lse_id, l_parent_id;
4532 Close get_line_type;
4533
4534 -- We only delete coverages for the top lines.
4535 -- Each top line has only one coverage
4536 If l_lse_id in (1, 14, 19) Then
4537 -- Deletes coverages and PM schedules and coverage lines
4538 OKS_COVERAGES_PUB.DELETE_COVERAGE(
4539 p_api_version => l_api_version,
4540 p_init_msg_list => l_init_msg_list,
4541 x_return_status => l_return_status,
4542 x_msg_count => l_msg_count,
4543 x_msg_data => l_msg_data,
4544 P_service_Line_Id => p_line_id);
4545
4546 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4547 OKC_API.set_message(
4548 G_APP_NAME,
4549 G_UNEXPECTED_ERROR,
4550 G_SQLCODE_TOKEN,
4551 SQLCODE,
4552 G_SQLERRM_TOKEN,
4553 'Error in deleting coverage lines.'
4554 );
4555 RAISE G_EXCEPTION_HALT_VALIDATION;
4556 End if;
4557 End If;
4558
4559 -- Fix for deleting billing schedules for lines that do have billing schedules.
4560 If l_lse_id in (1,3, 8, 9, 10, 11, 12, 13, 14, 18, 19, 25, 35, 46) Then
4561 ----------- Delete OKS billing schedule ----------------------------
4562 If l_parent_id is null Then
4563
4564 -- will delete all the existing SLH, SLL and level elements for top line and its sub lines
4565 OKS_BILL_SCH.Del_Rul_Elements(p_top_line_id => p_line_id,
4566 x_return_status => l_return_status,
4567 x_msg_count => l_msg_count,
4568 x_msg_data => l_msg_data);
4569 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4570 OKC_API.set_message(
4571 G_APP_NAME,
4572 G_UNEXPECTED_ERROR,
4573 G_SQLCODE_TOKEN,
4574 SQLCODE,
4575 G_SQLERRM_TOKEN,
4576 'Error in deleting billing schedule (OKS_BILL_SCH.Del_Rul_Elements)'
4577 );
4578 RAISE G_EXCEPTION_HALT_VALIDATION;
4579 End if;
4580
4581 Else
4582 OKS_BILL_SCH.Del_subline_lvl_rule(p_top_line_id => l_parent_id,
4583 p_sub_line_id => p_line_id,
4584 x_return_status => l_return_status,
4585 x_msg_count => l_msg_count,
4586 x_msg_data => l_msg_data
4587 );
4588 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4589 OKC_API.set_message(
4590 G_APP_NAME,
4591 G_UNEXPECTED_ERROR,
4592 G_SQLCODE_TOKEN,
4593 SQLCODE,
4594 G_SQLERRM_TOKEN,
4595 'Error in deleting billing schedule (OKS_BILL_SCH.Del_subline_lvl_rule)'
4596 );
4597 RAISE G_EXCEPTION_HALT_VALIDATION;
4598 End if;
4599 End If;
4600
4601 End If;
4602
4603
4604 ---------------- Delete OKS line -----------------------
4605 Delete_OKS_Line(
4606 p_cle_id => p_line_id,
4607 x_return_status => l_return_status);
4608
4609 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4610 OKC_API.set_message(
4611 G_APP_NAME,
4612 G_UNEXPECTED_ERROR,
4613 G_SQLCODE_TOKEN,
4614 SQLCODE,
4615 G_SQLERRM_TOKEN,
4616 'Error from Delete_OKS_Line'
4617 );
4618 RAISE G_EXCEPTION_HALT_VALIDATION;
4619 End if;
4620
4621 --l_clev_rec.id := p_line_id;
4622 OKC_CONTRACT_PVT.delete_contract_line(
4623 p_api_version => l_api_version,
4624 p_init_msg_list => l_init_msg_list,
4625 x_return_status => l_return_status,
4626 x_msg_count => l_msg_count,
4627 x_msg_data => l_msg_data,
4628 --p_clev_rec => l_clev_rec);
4629 p_line_id => p_line_id);
4630 If l_return_status <> OKC_API.G_RET_STS_SUCCESS then
4631 OKC_API.set_message(
4632 G_APP_NAME,
4633 G_UNEXPECTED_ERROR,
4634 G_SQLCODE_TOKEN,
4635 SQLCODE,
4636 G_SQLERRM_TOKEN,
4637 'Error in deleting contract lines.'
4638 );
4639 RAISE G_EXCEPTION_HALT_VALIDATION;
4640 End if;
4641
4642
4643
4644 x_return_status := l_return_status;
4645
4646 EXCEPTION
4647 WHEN G_EXCEPTION_HALT_VALIDATION THEN
4648 x_return_status := l_return_status;
4649 WHEN OTHERS THEN
4650 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4651 OKC_API.set_message
4652 (G_APP_NAME,
4653 G_UNEXPECTED_ERROR,
4654 G_SQLCODE_TOKEN,
4655 SQLCODE,
4656 G_SQLERRM_TOKEN,
4657 SQLERRM);
4658 End Delete_Contract_Line;
4659
4660 -- Line Cancellation --
4661 -- New procedure added to find if a contract thats going to be deleted
4662 -- has lines or covered levels that has been renewed on another contract
4663 PROCEDURE Delete_Transfer_Contract(
4664 p_api_version IN NUMBER,
4665 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4666 p_chr_id IN NUMBER,
4667 p_cle_id IN NUMBER DEFAULT NULL,
4668 p_intent IN VARCHAR2, -- new
4669 x_contract_number OUT NOCOPY VARCHAR2,
4670 x_return_status OUT NOCOPY VARCHAR2,
4671 x_msg_count OUT NOCOPY NUMBER,
4672 x_msg_data OUT NOCOPY VARCHAR2) IS
4673
4674 CURSOR cur_has_lines_been_renewed_h IS -- header
4675 SELECT 1
4676 FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c
4677 where a.object_chr_id= p_chr_id and
4678 c.id=b.cop_id and
4679 c.opn_code in('RENEWAL', 'REN_CON') and
4680 a.oie_id=b.id and
4681 a.active_yn='Y';
4682
4683
4684 CURSOR cur_has_lines_been_renewed_l IS -- topline
4685 SELECT subject_cle_id
4686 FROM okc_operation_lines a,
4687 okc_operation_instances b,
4688 okc_class_operations c,
4689 okc_k_lines_b d
4690 where a.object_cle_id = d.id and
4691 d.cle_id = p_cle_id and -- should be a top line id
4692 a.object_chr_id = p_chr_id and
4693 c.id=b.cop_id and
4694 c.opn_code in('RENEWAL', 'REN_CON') and
4695 a.oie_id=b.id and
4696 a.active_yn='Y' and
4697 a.object_chr_id = d.dnz_chr_id;
4698
4699
4700 CURSOR cur_has_lines_been_renewed_s IS -- Subline
4701 SELECT subject_chr_id
4702 FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c
4703 where a.object_cle_id= p_cle_id and -- subline id
4704 c.id=b.cop_id and
4705 c.opn_code in('RENEWAL', 'REN_CON') and
4706 a.oie_id=b.id and
4707 a.active_yn='Y' and
4708 a.object_chr_id = p_chr_id;
4709
4710
4711
4712 Cursor cur_get_contract_number(p_subject_chr_id number) IS
4713 SELECT contract_number , contract_number_modifier
4714 From okc_k_headers_b b
4715 where id = p_subject_chr_id;
4716
4717 l_result NUMBER;
4718 l_sub_cle_id okc_operation_lines.subject_cle_id%TYPE;
4719 l_sub_chr_id okc_operation_lines.subject_chr_id%TYPE;
4720 l_contract_number OKC_K_HEADERS_B.contract_number%type;
4721 l_contract_modifier OKC_K_HEADERS_B.CONTRACT_NUMBER_MODIFIER%type;
4722
4723 BEGIN
4724 IF p_chr_id IS NOT NULL AND p_cle_id IS NULL
4725 THEN
4726 Open cur_has_lines_been_renewed_h;
4727 fetch cur_has_lines_been_renewed_h into l_result;
4728 IF cur_has_lines_been_renewed_h%NOTFOUND
4729 THEN
4730 x_return_status:='S';
4731 ELSE
4732 x_return_status:='W';
4733 END IF;
4734 Close cur_has_lines_been_renewed_h;
4735 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NOT NULL
4736 THEN
4737 -- Called from Topline
4738 IF nvl(p_intent, 'X') = 'T' -- Topline
4739 THEN
4740 Open cur_has_lines_been_renewed_l;
4741 Fetch cur_has_lines_been_renewed_l into l_sub_cle_id;
4742 IF cur_has_lines_been_renewed_l%NOTFOUND
4743 THEN
4744 x_return_status :='S';
4745 ELSE
4746 x_return_status :='W';
4747 END IF;
4748 Close cur_has_lines_been_renewed_l;
4749 ELSIF nvl(p_intent, 'X') = 'S' --Subline
4750 THEN
4751 Open cur_has_lines_been_renewed_s;
4752 Fetch cur_has_lines_been_renewed_s into l_sub_chr_id;
4753 IF cur_has_lines_been_renewed_s%NOTFOUND
4754 THEN
4755 x_return_status :='S';
4756 ELSE
4757
4758 open cur_get_contract_number(l_sub_chr_id);
4759 fetch cur_get_contract_number Into l_contract_number,l_contract_modifier;
4760 close cur_get_contract_number;
4761
4762 IF l_contract_modifier is NOT NULL
4763 THEN
4764 x_contract_number:=l_contract_number || '-' || l_contract_modifier;
4765 ELSE
4766 x_contract_number:=l_contract_number;
4767 END IF;
4768
4769 x_return_status :='W';
4770 END IF;
4771
4772 Close cur_has_lines_been_renewed_s;
4773
4774 END IF; -- IF nvl(p_intent, 'X') = 'T'
4775 END IF;
4776 EXCEPTION
4777 WHEN OTHERS THEN
4778 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4779 OKC_API.set_message
4780 (G_APP_NAME,
4781 G_UNEXPECTED_ERROR,
4782 G_SQLCODE_TOKEN,
4783 SQLCODE,
4784 G_SQLERRM_TOKEN,
4785 SQLERRM);
4786 END Delete_Transfer_Contract;
4787 -- Line Cancellation --
4788
4789 /*
4790 New procedure to delete toplines an sublines for OKS. This builds on
4791 OKS_SETUP_UTIL_PUB.Delete_Contract_Line and adds stuff that authoring does and some other
4792 stuff that nobody seems to be doing
4793
4794 Parameters
4795 p_line_id : id of the top line/subline from OKC_K_LINES_B table
4796 */
4797
4798 PROCEDURE DELETE_TOP_SUB_LINE
4799 (
4800 p_api_version IN NUMBER,
4801 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4802 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
4803 p_line_id IN NUMBER,
4804 x_return_status OUT NOCOPY VARCHAR2,
4805 x_msg_count OUT NOCOPY NUMBER,
4806 x_msg_data OUT NOCOPY VARCHAR2
4807 )
4808 IS
4809
4810 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TOP_SUB_LINE';
4811 l_api_version CONSTANT NUMBER := 1;
4812 l_mod_name VARCHAR2(256) := lower(G_OKS_APP_NAME) || '.plsql.' || G_PKG_NAME || '.' || l_api_name;
4813 l_error_text VARCHAR2(512);
4814
4815 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4816
4817 CURSOR c_chk_transfer(cp_line_id IN NUMBER) IS
4818 SELECT id
4819 FROM okc_k_lines_b a
4820 WHERE (a.id = cp_line_id OR a.cle_id = cp_line_id)
4821 AND lse_id IN (1,12,14,19,46, 7,8,9,10,11,35, 13, 18, 25)
4822 AND nvl(term_cancel_source, 'X') IN ('IBTRANSFER', 'IBTERMINATE', 'IBRETURN');
4823
4824 CURSOR c_get_line_type(cp_line_id IN NUMBER) IS
4825 SELECT a.lse_id, a.cle_id, a.cust_acct_id, a.bill_to_site_use_id,
4826 b.locked_price_list_line_id, b.trxn_extension_id
4827 FROM okc_k_lines_b a, oks_k_lines_b b
4828 WHERE a.id = cp_line_id
4829 AND b.cle_id = a.id;
4830
4831 CURSOR c_usage_sub_lines(cp_line_id IN NUMBER) IS
4832 SELECT b.locked_price_list_line_id
4833 FROM okc_k_lines_b a, oks_k_lines_b b
4834 WHERE a.cle_id = cp_line_id
4835 AND b.cle_id = a.id;
4836
4837 CURSOR c_get_child_lines(cp_line_id IN NUMBER) IS
4838 SELECT id
4839 FROM okc_k_lines_b
4840 CONNECT BY PRIOR id = cle_id
4841 START WITH id = cp_line_id;
4842
4843 CURSOR c_party_from_billto(cp_bill_to_site_use_id IN NUMBER) IS
4844 SELECT cas.cust_account_id cust_account_id, ca.party_id party_id
4845 FROM hz_cust_site_uses_all csu, hz_cust_acct_sites_all cas, hz_cust_accounts_all ca
4846 WHERE csu.site_use_id = cp_bill_to_site_use_id
4847 AND cas.cust_acct_site_id = csu.cust_acct_site_id
4848 AND ca.cust_account_id = cas.cust_account_id;
4849
4850 CURSOR c_party_from_cust(cp_cust_acct_id IN NUMBER) IS
4851 SELECT ca.party_id party_id
4852 FROM hz_cust_accounts_all ca
4853 WHERE ca.cust_account_id = cp_cust_acct_id;
4854
4855 CURSOR c_get_notes(cp_source_object_id IN NUMBER) IS
4856 SELECT jtf_note_id
4857 FROM JTF_NOTES_VL
4858 WHERE source_object_id = cp_source_object_id
4859 AND source_object_code = 'OKS_COV_NOTE';
4860
4861 l_lse_id NUMBER;
4862 l_parent_id NUMBER;
4863 l_lock_pl_line_id NUMBER;
4864 l_trxn_extension_id NUMBER;
4865 l_cust_account_id NUMBER;
4866 l_bill_to_site_use_id NUMBER;
4867
4868 l_transfer_id NUMBER;
4869 l_id_tbl num_tbl_type;
4870 l_lock_pl_line_id_tbl num_tbl_type;
4871 l_jtf_note_id_tbl num_tbl_type;
4872
4873 l_payer IBY_FNDCPT_COMMON_PUB.payercontext_rec_type;
4874 l_response IBY_FNDCPT_COMMON_PUB.result_rec_type;
4875 l_party_id NUMBER;
4876 l_del_trxn BOOLEAN;
4877
4878 BEGIN
4879 --log key input parameters
4880 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
4881 IF (FND_LOG.test(FND_LOG.level_procedure, l_mod_name)) THEN
4882 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.begin', 'p_api_version=' || p_api_version ||' ,p_commit='|| p_commit ||' ,p_line_id='|| p_line_id);
4883 END IF;
4884 END IF;
4885
4886 --standard api initilization and checks
4887 SAVEPOINT delete_top_sub_line_PUB;
4888 IF NOT FND_API.compatible_api_call (l_api_version, p_api_version, l_api_name, G_PKG_NAME)THEN
4889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4890 END IF;
4891 IF FND_API.to_boolean(p_init_msg_list ) THEN
4892 FND_MSG_PUB.initialize;
4893 END IF;
4894 x_return_status := FND_API.G_RET_STS_SUCCESS;
4895
4896 OPEN c_get_line_type(p_line_id);
4897 FETCH c_get_line_type INTO l_lse_id, l_parent_id, l_cust_account_id, l_bill_to_site_use_id,
4898 l_lock_pl_line_id, l_trxn_extension_id;
4899 CLOSE c_get_line_type;
4900
4901 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
4902 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.line_dtls', 'l_lse_id='||l_lse_id||' ,l_parent_id='||l_parent_id||' ,l_cust_account_id='||l_cust_account_id||' ,l_bill_to_site_use_id='||l_bill_to_site_use_id||
4903 ' ,l_lock_pl_line_id='||l_lock_pl_line_id||' ,l_trxn_extension_id='||l_trxn_extension_id);
4904 END IF;
4905
4906
4907 IF (l_lse_id IS NULL) THEN
4908 --nothing to delete!!, also serves as a check for p_line_id invalid or null
4909 RETURN;
4910 END IF;
4911
4912 --these are valid top line and sub line lse ids
4913 IF (l_lse_id NOT IN (1,12,14,19,46, 7,8,9,10,11,35, 13, 18, 25)) THEN
4914 --we will not delete any other line types
4915 RETURN;
4916 END IF;
4917
4918 --check if the line or any of it's sublines have been transfered/terminated/returned
4919 OPEN c_chk_transfer(p_line_id);
4920 FETCH c_chk_transfer INTO l_transfer_id;
4921 CLOSE c_chk_transfer;
4922
4923 IF (l_transfer_id IS NOT NULL) THEN
4924 FND_MESSAGE.set_NAME(G_OKS_APP_NAME, 'OKS_TRANSFER_LINE_NO_DELETE');
4925 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
4926 FND_LOG.message(FND_LOG.level_error, l_mod_name || '.transfer_check', FALSE);
4927 END IF;
4928 FND_MSG_PUB.ADD;
4929 RAISE FND_API.g_exc_error;
4930 END IF;
4931
4932
4933 --first delete the trxn_extension_id (credit card) if present
4934 IF l_trxn_extension_id IS NOT NULL THEN
4935
4936 l_del_trxn := TRUE;
4937
4938 --get the payer information from cust_acct or bill_to_site
4939 IF (l_cust_account_id IS NOT NULL) THEN
4940 OPEN c_party_from_cust(l_cust_account_id);
4941 FETCH c_party_from_cust INTO l_party_id;
4942 CLOSE c_party_from_cust;
4943 ELSIF (l_bill_to_site_use_id IS NOT NULL) THEN
4944 OPEN c_party_from_billto(l_bill_to_site_use_id);
4945 FETCH c_party_from_billto INTO l_cust_account_id, l_party_id;
4946 CLOSE c_party_from_billto;
4947 ELSE
4948 --cannot delete the trxn_extn without payer info
4949 l_del_trxn := FALSE;
4950 END IF;
4951
4952 IF (l_del_trxn) THEN
4953
4954 l_payer.payment_function := IBY_FNDCPT_COMMON_PUB.G_PMT_FUNCTION_CUST_PMT; --CUSTOMER_PAYMENT
4955 l_payer.party_id := l_party_id;
4956 l_payer.cust_account_id := l_cust_account_id;
4957
4958 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
4959 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.del_trxn_extn', 'calling IBY_FNDCPT_TRXN_PUB.delete_transaction_extension, p_payer.party_id='||l_party_id||' ,p_payer.cust_account_id='||l_cust_account_id||
4960 ' ,p_entity_id='||l_trxn_extension_id);
4961 END IF;
4962
4963 IBY_FNDCPT_TRXN_PUB.delete_transaction_extension(
4964 p_api_version => 1.0,
4965 p_init_msg_list => FND_API.G_FALSE,
4966 p_commit => FND_API.G_FALSE,
4967 x_return_status => x_return_status,
4968 x_msg_count => x_msg_count,
4969 x_msg_data => x_msg_data,
4970 p_payer => l_payer,
4971 --p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD, -- UPWARD
4972 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_FULL, -- FULL, bug 5439978
4973 p_entity_id => l_trxn_extension_id,
4974 x_response => l_response);
4975
4976 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
4977 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.del_trxn_extn', 'after call to IBY_FNDCPT_TRXN_PUB.delete_transaction_extension, x_return_status='||x_return_status||
4978 ' ,result_code='||l_response.result_code||' ,result_category='||l_response.result_category||' ,result_message='||l_response.result_message);
4979 END IF;
4980
4981 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
4982 RAISE FND_API.g_exc_unexpected_error;
4983 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
4984 RAISE FND_API.g_exc_error;
4985 END IF;
4986
4987 --also check the pmt api result code
4988 IF (l_response.result_code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
4989 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_response.result_message||'('||l_response.result_code||':'||l_response.result_category||')');
4990 RAISE FND_API.g_exc_error;
4991 END IF;
4992
4993 END IF;
4994 END IF;
4995
4996 --we only delete coverages for the top lines.
4997 IF l_lse_id IN (1, 14, 19) THEN
4998 -- Deletes coverages and coverage entities
4999 OKS_COVERAGES_PUB.delete_coverage(
5000 p_api_version => 1.0,
5001 p_init_msg_list => FND_API.G_FALSE,
5002 x_return_status => x_return_status,
5003 x_msg_count => x_msg_count,
5004 x_msg_data => x_msg_data,
5005 p_service_line_id => p_line_id);
5006
5007 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5008 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_coverage', 'after call to OKS_COVERAGES_PUB.delete_coverage, x_return_status='||x_return_status);
5009 END IF;
5010
5011 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5012 RAISE FND_API.g_exc_unexpected_error;
5013 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5014 RAISE FND_API.g_exc_error;
5015 END IF;
5016
5017 --delete coverage notes if any
5018 OPEN c_get_notes(p_line_id);
5019 LOOP
5020 FETCH c_get_notes BULK COLLECT INTO l_jtf_note_id_tbl LIMIT G_BULK_FETCH_LIMIT;
5021
5022 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5023 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.c_get_notes_bulk_fetch', 'l_jtf_note_id_tbl.count=,'||l_jtf_note_id_tbl.count);
5024 END IF;
5025
5026 EXIT WHEN (l_jtf_note_id_tbl.count = 0);
5027
5028 -- Call API to delete coverage notes, if exists **/
5029 FOR i in l_jtf_note_id_tbl.first..l_jtf_note_id_tbl.last LOOP
5030
5031 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5032 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'calling JTF_NOTES_PUB.secure_delete_note, p_jtf_note_id='||l_jtf_note_id_tbl(i));
5033 END IF;
5034
5035 JTF_NOTES_PUB.secure_delete_note(
5036 p_api_version => 1.0,
5037 p_init_msg_list => FND_API.G_FALSE,
5038 p_commit => FND_API.G_FALSE,
5039 p_validation_level => 100,
5040 x_return_status => x_return_status,
5041 x_msg_count => x_msg_count,
5042 x_msg_data => x_msg_data ,
5043 p_jtf_note_id => l_jtf_note_id_tbl(i),
5044 p_use_AOL_security => FND_API.G_FALSE);
5045
5046
5047 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5048 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'after call to JTF_NOTES_PUB.secure_delete_note, x_return_status='||x_return_status);
5049 END IF;
5050
5051 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5052 RAISE FND_API.g_exc_unexpected_error;
5053 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5054 RAISE FND_API.g_exc_error;
5055 END IF;
5056
5057 END LOOP; --of FOR i in l_jtf_note_id_tbl.first..
5058
5059 END LOOP; --c_get_notes bulk fetch loop
5060 CLOSE c_get_notes;
5061
5062
5063 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5064 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'calling OKS_PM_PROGRAMS_PVT.undo_pm_line, p_cle_id='||p_line_id);
5065 END IF;
5066
5067 --delete PM schedules
5068 OKS_PM_PROGRAMS_PVT.undo_pm_line(
5069 p_api_version => 1.0,
5070 p_init_msg_list => FND_API.G_FALSE,
5071 x_return_status => x_return_status,
5072 x_msg_count => x_msg_count,
5073 x_msg_data => x_msg_data,
5074 p_cle_id => p_line_id);
5075
5076 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5077 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_cov_notes', 'after call to OKS_PM_PROGRAMS_PVT.undo_pm_line, x_return_status='||x_return_status);
5078 END IF;
5079
5080 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5081 RAISE FND_API.g_exc_unexpected_error;
5082 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5083 RAISE FND_API.g_exc_error;
5084 END IF;
5085
5086 END IF; --of IF l_lse_id IN (1, 14, 19) THEN
5087
5088 --delete usage price breaks
5089 IF l_lse_id IN (12, 13) THEN
5090
5091 -- Call API to delete locked price breaks, if exists **/
5092 IF l_lock_pl_line_id IS NOT NULL THEN
5093 OKS_QP_PKG.delete_locked_pricebreaks(
5094 p_api_version => 1.0,
5095 p_list_line_id => l_lock_pl_line_id,
5096 p_init_msg_list => FND_API.G_FALSE,
5097 x_return_status => x_return_status,
5098 x_msg_count => x_msg_count,
5099 x_msg_data => x_msg_data);
5100
5101 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5102 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_locked_pricebreaks', 'after call to OKS_QP_PKG.delete_locked_pricebreaks, x_return_status='||x_return_status);
5103 END IF;
5104
5105 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5106 RAISE FND_API.g_exc_unexpected_error;
5107 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5108 RAISE FND_API.g_exc_error;
5109 END IF;
5110 END IF;
5111
5112 --if usage top line, then delete price breaks for all usage sublines also
5113 IF (l_lse_id = 12) THEN
5114
5115 OPEN c_usage_sub_lines(p_line_id);
5116 LOOP
5117 FETCH c_usage_sub_lines BULK COLLECT INTO l_lock_pl_line_id_tbl LIMIT G_BULK_FETCH_LIMIT;
5118
5119 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5120 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.c_usage_sub_lines_bulk_fetch', 'l_lock_pl_line_id_tbl.count=,'||l_lock_pl_line_id_tbl.count);
5121 END IF;
5122
5123 EXIT WHEN (l_lock_pl_line_id_tbl.count = 0);
5124
5125 -- Call API to delete locked price breaks, if exists **/
5126 FOR i in l_lock_pl_line_id_tbl.first..l_lock_pl_line_id_tbl.last LOOP
5127 IF (l_lock_pl_line_id_tbl(i) IS NOT NULL) THEN
5128 OKS_QP_PKG.delete_locked_pricebreaks(
5129 p_api_version => 1.0,
5130 p_list_line_id => l_lock_pl_line_id_tbl(i),
5131 p_init_msg_list => FND_API.G_FALSE,
5132 x_return_status => x_return_status,
5133 x_msg_count => x_msg_count,
5134 x_msg_data => x_msg_data);
5135
5136 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5137 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_locked_pricebreaks_sub', 'after call to OKS_QP_PKG.delete_locked_pricebreaks, x_return_status='||x_return_status);
5138 END IF;
5139
5140 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5141 RAISE FND_API.g_exc_unexpected_error;
5142 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5143 RAISE FND_API.g_exc_error;
5144 END IF;
5145
5146 END IF; --of IF (l_lock_pl_line_id IS NOT NULL) THEN
5147
5148 END LOOP; --of FOR i in l_lock_pl_line_id_tbl.first..
5149
5150 END LOOP; --usage subline bulk fetch loop
5151 CLOSE c_usage_sub_lines;
5152
5153 END IF; --of IF (l_lse_id = 12) THEN
5154
5155 END IF; --of IF l_lse_id IN (12, 13) THEN
5156
5157 --delete subscription information
5158 IF (l_lse_id = 46) THEN
5159 OKS_SUBSCRIPTION_PUB.undo_subscription(
5160 p_api_version => 1.0,
5161 p_init_msg_list => FND_API.G_FALSE,
5162 x_return_status => x_return_status,
5163 x_msg_count => x_msg_count,
5164 x_msg_data => x_msg_data,
5165 p_cle_id => p_line_id);
5166
5167 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5168 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.Undo_Subscription', 'after call to OKS_SUBSCRIPTION_PUB.Undo_Subscription, x_return_status='||x_return_status);
5169 END IF;
5170
5171 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5172 RAISE FND_API.g_exc_unexpected_error;
5173 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5174 RAISE FND_API.g_exc_error;
5175 END IF;
5176
5177 END IF;
5178
5179 -- Fix for deleting billing schedules for lines that do have billing schedules.
5180 IF l_lse_id IN (1, 7, 8, 9, 10, 11, 12, 13, 14, 18, 19, 25, 35, 46) THEN
5181 --delete OKS billing schedule
5182 IF l_parent_id IS NULL THEN
5183
5184 -- will delete all the existing SLH, SLL and level elements for top line and its sub lines
5185 OKS_BILL_SCH.del_rul_elements(
5186 p_top_line_id => p_line_id,
5187 x_return_status => x_return_status,
5188 x_msg_count => x_msg_count,
5189 x_msg_data => x_msg_data);
5190
5191 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5192 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.del_rul_elements', 'after call to OKS_BILL_SCH.del_rul_elements, x_return_status='||x_return_status);
5193 END IF;
5194
5195 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5196 RAISE FND_API.g_exc_unexpected_error;
5197 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5198 RAISE FND_API.g_exc_error;
5199 END IF;
5200
5201 ELSE
5202
5203 OKS_BILL_SCH.del_subline_lvl_rule(
5204 p_top_line_id => l_parent_id,
5205 p_sub_line_id => p_line_id,
5206 x_return_status => x_return_status,
5207 x_msg_count => x_msg_count,
5208 x_msg_data => x_msg_data);
5209
5210 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5211 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.del_subline_lvl_rule', 'after call to OKS_BILL_SCH.del_subline_lvl_rule, x_return_status='||x_return_status);
5212 END IF;
5213
5214 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5215 RAISE FND_API.g_exc_unexpected_error;
5216 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5217 RAISE FND_API.g_exc_error;
5218 END IF;
5219
5220 END IF; --of IF l_parent_id IS NULL THEN
5221 END IF; --of IF l_lse_id IN (1, 7, 8, 9, 10, 11, 12, 13, 14, 18, 19, 25, 35, 46) THEN
5222
5223
5224 --delete all oks lines and it's entities
5225 --child OKS lines (b and tl tables), child entities such as OKS sales credits,
5226 --rev distributions, qaulifiers
5227 OPEN c_get_child_lines(p_line_id);
5228 LOOP
5229 FETCH c_get_child_lines BULK COLLECT INTO l_id_tbl LIMIT G_BULK_FETCH_LIMIT;
5230 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5231 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
5232 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.c_get_child_lines_bulk_fetch', 'l_id_tbl.count='||l_id_tbl.count);
5233 END IF;
5234 END IF;
5235 EXIT WHEN (l_id_tbl.count = 0);
5236
5237 --delete all OKS_K_LINES_TL records
5238 FORALL i IN l_id_tbl.first..l_id_tbl.last
5239 DELETE FROM oks_k_lines_tl WHERE id IN
5240 (SELECT id FROM oks_k_lines_b WHERE cle_id = l_id_tbl(i));
5241
5242 --delete all OKS_K_LINES_B records
5243 FORALL i IN l_id_tbl.first..l_id_tbl.last
5244 DELETE FROM oks_k_lines_b WHERE cle_id = l_id_tbl(i);
5245
5246 --delete all OKS_K_SALES_CREDITS records
5247 FORALL i IN l_id_tbl.first..l_id_tbl.last
5248 DELETE FROM oks_k_sales_credits WHERE cle_id = l_id_tbl(i);
5249
5250 --delete all OKS_REV_DISTRIBUTIONS records
5251 FORALL i IN l_id_tbl.first..l_id_tbl.last
5252 DELETE FROM oks_rev_distributions WHERE cle_id = l_id_tbl(i);
5253
5254 --delete all OKS_QUALIFIERS records
5255 FORALL i IN l_id_tbl.first..l_id_tbl.last
5256 DELETE FROM OKS_QUALIFIERS WHERE list_line_id = l_id_tbl(i);
5257
5258 END LOOP;
5259 CLOSE c_get_child_lines;
5260
5261 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5262 IF (FND_LOG.test(FND_LOG.level_statement, l_mod_name)) THEN
5263 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_oks_entities', 'done');
5264 END IF;
5265 END IF;
5266
5267 --call OKC API to delete all OKC lines and entities. This deletes all OKC entities and
5268 --the OKC sublines
5269 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5270 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_okc_entities', 'calling OKC_CONTRACT_PVT.delete_contract_line, p_line_id='||p_line_id);
5271 END IF;
5272 OKC_CONTRACT_PVT.delete_contract_line(
5273 p_api_version => 1.0,
5274 p_init_msg_list => FND_API.G_FALSE,
5275 x_return_status => x_return_status,
5276 x_msg_count => x_msg_count,
5277 x_msg_data => x_msg_data,
5278 p_line_id => p_line_id);
5279
5280 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5281 FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_okc_entities', 'after call to OKC_CONTRACT_PVT.delete_contract_line, x_return_status='||x_return_status);
5282 END IF;
5283
5284 IF x_return_status = FND_API.g_ret_sts_unexp_error THEN
5285 RAISE FND_API.g_exc_unexpected_error;
5286 ELSIF x_return_status = FND_API.g_ret_sts_error THEN
5287 RAISE FND_API.g_exc_error;
5288 END IF;
5289
5290
5291 --standard check of p_commit
5292 IF FND_API.to_boolean( p_commit ) THEN
5293 COMMIT;
5294 END IF;
5295 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
5296 FND_LOG.string(FND_LOG.level_procedure, l_mod_name || '.end', ' x_return_status='|| x_return_status);
5297 END IF;
5298 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
5299
5300 EXCEPTION
5301 WHEN FND_API.g_exc_error THEN
5302 ROLLBACK TO delete_top_sub_line_PUB;
5303 x_return_status := FND_API.g_ret_sts_error ;
5304
5305 IF (FND_LOG.level_error >= FND_LOG.g_current_runtime_level) THEN
5306 FND_LOG.string(FND_LOG.level_error, l_mod_name || '.end_error', 'x_return_status=' || x_return_status);
5307 END IF;
5308 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
5309
5310 IF (c_get_line_type%isopen) THEN
5311 CLOSE c_get_line_type;
5312 END IF;
5313 IF (c_get_child_lines%isopen) THEN
5314 CLOSE c_get_child_lines;
5315 END IF;
5316 IF (c_usage_sub_lines%isopen) THEN
5317 CLOSE c_usage_sub_lines;
5318 END IF;
5319 IF (c_chk_transfer%isopen) THEN
5320 CLOSE c_chk_transfer;
5321 END IF;
5322 IF (c_party_from_cust%isopen) THEN
5323 CLOSE c_party_from_cust;
5324 END IF;
5325 IF (c_party_from_billto%isopen) THEN
5326 CLOSE c_party_from_billto;
5327 END IF;
5328 IF (c_get_notes%isopen) THEN
5329 CLOSE c_get_notes;
5330 END IF;
5331
5332 WHEN FND_API.g_exc_unexpected_error THEN
5333 ROLLBACK TO delete_top_sub_line_PUB;
5334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5335
5336 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
5337 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_unexpected_error', 'x_return_status=' || x_return_status);
5338 END IF;
5339 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
5340
5341 IF (c_get_line_type%isopen) THEN
5342 CLOSE c_get_line_type;
5343 END IF;
5344 IF (c_get_child_lines%isopen) THEN
5345 CLOSE c_get_child_lines;
5346 END IF;
5347 IF (c_usage_sub_lines%isopen) THEN
5348 CLOSE c_usage_sub_lines;
5349 END IF;
5350 IF (c_chk_transfer%isopen) THEN
5351 CLOSE c_chk_transfer;
5352 END IF;
5353 IF (c_party_from_cust%isopen) THEN
5354 CLOSE c_party_from_cust;
5355 END IF;
5356 IF (c_party_from_billto%isopen) THEN
5357 CLOSE c_party_from_billto;
5358 END IF;
5359 IF (c_get_notes%isopen) THEN
5360 CLOSE c_get_notes;
5361 END IF;
5362
5363 WHEN OTHERS THEN
5364 ROLLBACK TO delete_top_sub_line_PUB;
5365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5366
5367 IF (FND_LOG.level_unexpected >= FND_LOG.g_current_runtime_level) THEN
5368 --first log the sqlerrm
5369 l_error_text := substr (SQLERRM, 1, 240);
5370 FND_LOG.string(FND_LOG.level_unexpected, l_mod_name || '.end_other_error', l_error_text);
5371 --then add it to the message api list
5372 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name, l_error_text);
5373 END IF;
5374 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data );
5375
5376 IF (c_get_line_type%isopen) THEN
5377 CLOSE c_get_line_type;
5378 END IF;
5379 IF (c_get_child_lines%isopen) THEN
5380 CLOSE c_get_child_lines;
5381 END IF;
5382 IF (c_usage_sub_lines%isopen) THEN
5383 CLOSE c_usage_sub_lines;
5384 END IF;
5385 IF (c_chk_transfer%isopen) THEN
5386 CLOSE c_chk_transfer;
5387 END IF;
5388 IF (c_party_from_cust%isopen) THEN
5389 CLOSE c_party_from_cust;
5390 END IF;
5391 IF (c_party_from_billto%isopen) THEN
5392 CLOSE c_party_from_billto;
5393 END IF;
5394 IF (c_get_notes%isopen) THEN
5395 CLOSE c_get_notes;
5396 END IF;
5397
5398 END DELETE_TOP_SUB_LINE;
5399
5400 --Npalepu added on 30-nov-2005 for bug # 4768227.
5401 --New Function Get_Annualized_Factor is added to calculate the Annualized_Factor provided start_date,end_date and lse_id.
5402 FUNCTION Get_Annualized_Factor(p_start_date IN DATE,
5403 p_end_date IN DATE,
5404 p_lse_id IN NUMBER)
5405 RETURN NUMBER
5406 AS
5407 l_annualized_factor NUMBER;
5408
5409 CURSOR Cal_Annualized_Factor_csr(v_start_date IN DATE,v_end_date IN DATE,v_lse_id IN NUMBER) IS
5410 SELECT (ADD_MONTHS(v_start_date, (nyears+1)*12) - v_start_date -
5411 DECODE(ADD_MONTHS(v_end_date, -12),( v_end_date-366), 0,
5412 DECODE(ADD_MONTHS(v_start_date, (nyears+1)*12) - ADD_MONTHS(v_start_date, nyears*12), 366, 1, 0)))
5413 / (nyears+1) /(v_end_date-v_start_date+1)
5414 FROM (SELECT trunc(MONTHS_BETWEEN(v_end_date, v_start_date)/12) nyears FROM dual) dual ;
5415
5416 BEGIN
5417
5418 IF p_lse_id in (1,12,14,19,46,7,8,9,10,11,13,18,25,35) THEN
5419 OPEN Cal_Annualized_Factor_csr(p_start_date,p_end_date,p_lse_id);
5420 FETCH Cal_Annualized_Factor_csr into l_annualized_factor;
5421 CLOSE Cal_Annualized_Factor_csr;
5422 ELSE
5423 l_annualized_factor := 0;
5424 END IF;
5425
5426 RETURN l_annualized_factor;
5427
5428 EXCEPTION
5429 WHEN NO_DATA_FOUND THEN
5430 l_annualized_factor := 0;
5431 RETURN l_annualized_factor;
5432 WHEN OTHERS THEN
5433 l_annualized_factor := 0;
5434 RETURN l_annualized_factor;
5435
5436 END Get_Annualized_Factor;
5437 --end bug # 4768227
5438
5439 --npalepu added on 15-dec-2005 for bug # 4886786
5440 PROCEDURE Update_Annualized_Factor_BMGR(X_errbuf out NOCOPY varchar2,
5441 X_retcode out NOCOPY varchar2,
5442 P_batch_size in number,
5443 P_Num_Workers in number)
5444 IS
5445 BEGIN
5446 --
5447 -- Manager processing for OKC_K_LINES_B table
5448 --
5449 fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Annualized_Factor_BMGR ');
5450 fnd_file.put_line(FND_FILE.LOG, ' P_batch_size : '||P_batch_size);
5451 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
5452
5453 fnd_file.put_line(FND_FILE.LOG, 'starting okc_k_lines_b update worker ');
5454
5455 AD_CONC_UTILS_PKG.submit_subrequests(X_errbuf,
5456 X_retcode,
5457 'OKS',
5458 'OKSUBAFWKR',
5459 P_batch_size,
5460 P_Num_Workers);
5461
5462 fnd_file.put_line(FND_FILE.LOG, 'X_errbuf : '||X_errbuf);
5463 fnd_file.put_line(FND_FILE.LOG, 'X_retcode : '||X_retcode);
5464
5465 END Update_Annualized_Factor_BMGR;
5466
5467 PROCEDURE Update_Annualized_Factor_HMGR(X_errbuf out NOCOPY varchar2,
5468 X_retcode out NOCOPY varchar2,
5469 P_batch_size in number,
5470 P_Num_Workers in number)
5471 IS
5472 BEGIN
5473 --
5474 -- Manager processing for OKC_K_LINES_BH table
5475 --
5476 fnd_file.put_line(FND_FILE.LOG, 'Start of Update_Annualized_Factor_HMGR ');
5477 fnd_file.put_line(FND_FILE.LOG, ' P_batch_size : '||P_batch_size);
5478 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
5479
5480 fnd_file.put_line(FND_FILE.LOG, 'starting okc_k_lines_bh update worker ');
5481
5482 AD_CONC_UTILS_PKG.submit_subrequests(X_errbuf,
5483 X_retcode,
5484 'OKS',
5485 'OKSUHAFWKR',
5486 P_batch_size,
5487 P_Num_Workers);
5488
5489 fnd_file.put_line(FND_FILE.LOG, 'X_errbuf : '||X_errbuf);
5490 fnd_file.put_line(FND_FILE.LOG, 'X_retcode : '||X_retcode);
5491
5492 END Update_Annualized_Factor_HMGR;
5493
5494 PROCEDURE Update_Annualized_Factor_BWKR(X_errbuf out NOCOPY varchar2,
5495 X_retcode out NOCOPY varchar2,
5496 P_batch_size in number,
5497 P_Worker_Id in number,
5498 P_Num_Workers in number)
5499 IS
5500 l_worker_id number;
5501 l_product varchar2(30) := 'OKC';
5502 l_table_name varchar2(30) := 'OKC_K_HEADERS_ALL_B';
5503 l_update_name varchar2(30) := 'OKCLNUPG_CP';
5504 l_status varchar2(30);
5505 l_industry varchar2(30);
5506 l_retstatus boolean;
5507 l_table_owner varchar2(30);
5508 l_any_rows_to_process boolean;
5509 l_start_rowid rowid;
5510 l_end_rowid rowid;
5511 l_rows_processed number;
5512 BEGIN
5513 --
5514 -- get schema name of the table for ROWID range processing
5515 --
5516 l_retstatus := fnd_installation.get_app_info(l_product,
5517 l_status,
5518 l_industry,
5519 l_table_owner);
5520 if ((l_retstatus = FALSE) OR (l_table_owner is null))
5521 then
5522 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
5523 end if;
5524
5525 fnd_file.put_line(FND_FILE.LOG, 'Start of upgrade script for OKC_K_LINES_B table ');
5526 fnd_file.put_line(FND_FILE.LOG, ' P_Worker_Id : '||P_Worker_Id);
5527 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
5528
5529 --
5530 -- Worker processing
5531 --
5532 BEGIN
5533 ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
5534 l_table_owner,
5535 l_table_name,
5536 l_update_name,
5537 P_worker_id,
5538 P_num_workers,
5539 P_batch_size,
5540 0);
5541 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
5542 l_end_rowid,
5543 l_any_rows_to_process,
5544 P_batch_size,
5545 TRUE);
5546 while (l_any_rows_to_process = TRUE)
5547 loop
5548
5549 UPDATE (Select /*+ rowid(hdr) leading(hdr) use_nl_with_index(cle) */
5550 cle.payment_instruction_type,
5551 cle.annualized_factor,
5552 hdr.payment_instruction_type hdr_payment_instruction_type,
5553 case
5554 when cle.lse_id in (1, 12, 14, 19, 46, 7, 8, 9, 10, 11, 13, 18, 25, 35)
5555 then (add_months (cle.start_date, (trunc (months_between
5556 (cle.end_date, cle.start_date) / 12) + 1) * 12) -
5557 cle.start_date - decode (add_months (cle.end_date, -12),
5558 (cle.end_date-366), 0, decode ( add_months(cle.start_date,
5559 (trunc(months_between(cle.end_date, cle.start_date)
5560 / 12) + 1) * 12) - add_months(cle.start_date,
5561 trunc(months_between(cle.end_date, cle.start_date) / 12)
5562 * 12), 366, 1, 0)))
5563 / (trunc (months_between (cle.end_date, cle.start_date) / 12) + 1)
5564 / (cle.end_date - cle.start_date + 1)
5565 ELSE cle.annualized_factor
5566 end new_annualized_factor
5567 from okc_k_headers_all_b hdr,
5568 okc_k_lines_b cle
5569 where hdr.rowid between l_start_rowid and l_end_rowid
5570 and hdr.id = cle.dnz_chr_id
5571 and hdr.scs_code in ('SERVICE', 'WARRANTY', 'SUBSCRIPTION'))
5572 set payment_instruction_type = hdr_payment_instruction_type,
5573 annualized_factor = new_annualized_factor;
5574
5575 l_rows_processed := SQL%ROWCOUNT;
5576 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
5577 l_end_rowid);
5578 commit;
5579 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
5580 l_end_rowid,
5581 l_any_rows_to_process,
5582 P_batch_size,
5583 FALSE);
5584 end loop;
5585 fnd_file.put_line(FND_FILE.LOG,'Upgrade for OKC_K_LINES_B table completed successfully');
5586 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
5587 X_errbuf := ' ';
5588 EXCEPTION
5589 WHEN OTHERS THEN
5590 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
5591 X_errbuf := SQLERRM;
5592 fnd_file.put_line(FND_FILE.LOG,'X_errbuf : '||X_errbuf);
5593 fnd_file.put_line(FND_FILE.LOG,' ');
5594 raise;
5595 END;
5596 END Update_Annualized_Factor_BWKR;
5597
5598 PROCEDURE Update_Annualized_Factor_HWKR(X_errbuf out NOCOPY varchar2,
5599 X_retcode out NOCOPY varchar2,
5600 P_batch_size in number,
5601 P_Worker_Id in number,
5602 P_Num_Workers in number)
5603 IS
5604 l_worker_id number;
5605 l_product varchar2(30) := 'OKC';
5606 l_table_name varchar2(30) := 'OKC_K_HEADERS_ALL_BH';
5607 l_update_name varchar2(30) := 'OKCLNUPH_CP';
5608 l_status varchar2(30);
5609 l_industry varchar2(30);
5610 l_retstatus boolean;
5611 l_table_owner varchar2(30);
5612 l_any_rows_to_process boolean;
5613 l_start_rowid rowid;
5614 l_end_rowid rowid;
5615 l_rows_processed number;
5616 BEGIN
5617 --
5618 -- get schema name of the table for ROWID range processing
5619 --
5620 l_retstatus := fnd_installation.get_app_info(l_product,
5621 l_status,
5622 l_industry,
5623 l_table_owner);
5624 if ((l_retstatus = FALSE) OR (l_table_owner is null))
5625 then
5626 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
5627 end if;
5628
5629 fnd_file.put_line(FND_FILE.LOG, 'Start of upgrade script for OKC_K_LINES_BH table ');
5630 fnd_file.put_line(FND_FILE.LOG, ' P_Worker_Id : '||P_Worker_Id);
5631 fnd_file.put_line(FND_FILE.LOG, 'P_Num_Workers : '||P_Num_Workers);
5632
5633 --
5634 -- Worker processing
5635 --
5636 BEGIN
5637 ad_parallel_updates_pkg.initialize_rowid_range(ad_parallel_updates_pkg.ROWID_RANGE,
5638 l_table_owner,
5639 l_table_name,
5640 l_update_name,
5641 P_worker_id,
5642 P_num_workers,
5643 P_batch_size,
5644 0);
5645 ad_parallel_updates_pkg.get_rowid_range( l_start_rowid,
5646 l_end_rowid,
5647 l_any_rows_to_process,
5648 P_batch_size,
5649 TRUE);
5650 while (l_any_rows_to_process = TRUE)
5651 loop
5652 UPDATE (Select /*+ rowid(hdr) leading(hdr) use_nl_with_index(cle) */
5653 cle.payment_instruction_type,
5654 cle.annualized_factor,
5655 hdr.payment_instruction_type hdr_payment_instruction_type,
5656 case
5657 when cle.lse_id in (1, 12, 14, 19, 46, 7, 8, 9, 10, 11, 13, 18, 25, 35)
5658 then (add_months (cle.start_date, (trunc (months_between
5659 (cle.end_date, cle.start_date) / 12) + 1) * 12) -
5660 cle.start_date - decode (add_months (cle.end_date, -12),
5661 (cle.end_date-366), 0, decode ( add_months(cle.start_date,
5662 (trunc(months_between(cle.end_date, cle.start_date)
5663 / 12) + 1) * 12) - add_months(cle.start_date,
5664 trunc(months_between(cle.end_date, cle.start_date) / 12)
5665 * 12), 366, 1, 0)))
5666 / (trunc (months_between (cle.end_date, cle.start_date) / 12) + 1)
5667 / (cle.end_date - cle.start_date + 1)
5668 ELSE cle.annualized_factor
5669 end new_annualized_factor
5670 from okc_k_headers_all_bh hdr,
5671 okc_k_lines_bh cle
5672 where hdr.rowid between l_start_rowid and l_end_rowid
5673 and hdr.id = cle.dnz_chr_id
5674 and hdr.major_version = cle.major_version
5675 and hdr.scs_code in ('SERVICE', 'WARRANTY', 'SUBSCRIPTION'))
5676 set payment_instruction_type = hdr_payment_instruction_type,
5677 annualized_factor = new_annualized_factor;
5678
5679 l_rows_processed := SQL%ROWCOUNT;
5680 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed,
5681 l_end_rowid);
5682 commit;
5683 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
5684 l_end_rowid,
5685 l_any_rows_to_process,
5686 P_batch_size,
5687 FALSE);
5688 end loop;
5689 fnd_file.put_line(FND_FILE.LOG,'Upgrade for OKC_K_LINES_BH table completed successfully');
5690 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
5691 X_errbuf := ' ';
5692 EXCEPTION
5693 WHEN OTHERS THEN
5694 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
5695 X_errbuf := SQLERRM;
5696 fnd_file.put_line(FND_FILE.LOG,'X_errbuf : '||X_errbuf);
5697 fnd_file.put_line(FND_FILE.LOG,' ');
5698 raise;
5699 END;
5700 END Update_Annualized_Factor_HWKR;
5701 --end npalepu
5702
5703
5704 END OKS_SETUP_UTIL_PUB;