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