[Home] [Help]
PACKAGE BODY: APPS.OKC_STATUS_CHANGE_PVT
Source
1 package body okc_status_change_pvt as
2 /* $Header: OKCRSTSB.pls 120.5 2007/03/26 13:31:25 skgoud ship $*/
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7
8 --variables declared here
9
10
11
12 p_hdr_errors NUMBER := 0;
13 p_line_errors NUMBER := 0;
14 p_hdr_count NUMBER := 0;
15 p_line_count NUMBER := 0;
16 --
17 v_active VARCHAR2(30) := 'ACTIVE';
18 v_expired VARCHAR2(30) := 'EXPIRED';
19 v_terminated VARCHAR2(30) := 'TERMINATED';
20 v_signed VARCHAR2(30) := 'SIGNED';
21
22 -- The following are used in case there is some goof up in the sts cursor
23
24 v_active_m VARCHAR2(90) := 'Active';
25 v_expired_m VARCHAR2(90) := 'Expired';
26 v_terminated_m VARCHAR2(90) := 'Terminated';
27 v_signed_m VARCHAR2(90) := 'Signed';
28
29 cursor sts(p_status_type varchar2) is
30 select code, meaning
31 from okc_statuses_v
32 where ste_code = p_status_type
33 and default_yn = 'Y';
34
35
36 h_new_status VARCHAR2(30);
37 h_new_status_m VARCHAR2(90);
38 h_status VARCHAR2(30);
39 h_status_m VARCHAR2(90);
40 h_status_type VARCHAR2(30);
41
42 l_new_status VARCHAR2(30);
43 l_new_status_m VARCHAR2(90);
44 --
45 v_id NUMBER := 0;
46 v_termination_reason fnd_lookups.meaning%type := NULL ;
47
48 -- Global var holding the User Id
49 user_id NUMBER;
50
51 -- Global var to hold the ERROR value
52 ERROR NUMBER := 1;
53
54 -- Global var to hold the SUCCESS value
55 SUCCESS NUMBER := 0;
56
57 -- Global var holding the Current Error code for the error encountered
58 Current_Error_Code Varchar2(20) := NULL;
59
60 -- Global var to hold the Concurrent Process return values
61 conc_ret_code NUMBER := 0; --SUCCESS;
62 v_retcode NUMBER := 0; --SUCCESS;
63 CONC_STATUS BOOLEAN;
64
65 l_last_rundate DATE;
66 -- Cursors -- Contract Header Level
67
68 T number;
69
70 ----------------------------------------------------------------
71 -- End of global variables --
72 ----------------------------------------------------------------
73 PROCEDURE get_fnd_msg_stack(p_msg_data IN VARCHAR2) IS
74 BEGIN
75 -- IF FND_MSG_PUB.Count_Msg > 1 Then
76 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
77 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
78 END LOOP;
79 -- ELSE
80 --FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MSG_PUB.Get);
81 -- FND_FILE.PUT_LINE(FND_FILE.LOG,p_msg_data);
82 -- END IF;
83 FND_MSG_PUB.initialize;
84
85 END get_fnd_msg_stack;
86
87 FUNCTION Update_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
88 l_api_version NUMBER := 1;
89 l_init_msg_list VARCHAR2(1) := 'F';
90 x_return_status VARCHAR2(1);
91 x_msg_count NUMBER;
92 x_msg_data VARCHAR2(2000);
93 x_out_rec OKC_CVM_PVT.cvmv_rec_type;
94 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
95 BEGIN
96
97 -- initialize return status
98 x_return_status := OKC_API.G_RET_STS_SUCCESS;
99
100 -- assign/populate contract header id
101 l_cvmv_rec.chr_id := p_chr_id;
102 OKC_CVM_PVT.update_row(
103 l_api_version,
104 l_init_msg_list,
105 x_return_status,
106 x_msg_count,
107 x_msg_data,
108 l_cvmv_rec,
109 x_out_rec);
110
111 return (x_return_status);
112 EXCEPTION
113 when OTHERS then
114 -- notify caller of an error
115 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
116
117 -- store SQL error message on message stack
118 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
119 p_msg_name => g_unexpected_error,
120 p_token1 => g_sqlcode_token,
121 p_token1_value => sqlcode,
122 p_token2 => g_sqlerrm_token,
123 p_token2_value => sqlerrm);
124
125 return (x_return_status);
126 END;
127
128 PROCEDURE update_contract_header(
129 p_api_version IN NUMBER,
130 p_init_msg_list IN VARCHAR2 ,
131 x_return_status OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2,
134 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
135 x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type) IS
136
137 BEGIN
138 x_return_status := OKC_API.G_RET_STS_SUCCESS;
139 x_chrv_rec := p_chrv_rec;
140 UPDATE OKC_K_HEADERS_B
141 SET STS_CODE = p_chrv_rec.sts_code,
142 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
143 PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),PROGRAM_ID),
144 REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),REQUEST_ID),
145 PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,SYSDATE),
146 PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),PROGRAM_APPLICATION_ID),
147 LAST_UPDATE_DATE = SYSDATE,
148 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
149 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
150 WHERE ID = p_chrv_rec.id;
151
152 FND_FILE.PUT_LINE( FND_FILE.LOG,'old_sts_code'||p_chrv_rec.old_sts_code);
153 FND_FILE.PUT_LINE( FND_FILE.LOG,'old_ste_code'||p_chrv_rec.old_ste_code);
154 FND_FILE.PUT_LINE( FND_FILE.LOG,'new_sts_code'||p_chrv_rec.new_sts_code);
155 FND_FILE.PUT_LINE( FND_FILE.LOG,'new_ste_code'||p_chrv_rec.new_ste_code);
156 -- Call action assembler if status is changed
157 If p_chrv_rec.old_sts_code is not null AND
158 p_chrv_rec.new_sts_code is not null AND
159 p_chrv_rec.old_ste_code is not null AND
160 p_chrv_rec.new_ste_code is not null AND
161 (p_chrv_rec.old_sts_code <> p_chrv_rec.new_sts_code OR
162 p_chrv_rec.old_ste_code <> p_chrv_rec.new_ste_code)
163 Then
164 OKC_K_STS_CHG_ASMBLR_PVT.Acn_Assemble(
165 p_api_version => p_api_version,
166 p_init_msg_list => p_init_msg_list,
167 x_return_status => x_return_status,
168 x_msg_count => x_msg_count,
169 x_msg_data => x_msg_data,
170 p_k_id => p_chrv_rec.id,
171 p_k_number => p_chrv_rec.contract_number,
172 p_k_nbr_mod => p_chrv_rec.contract_number_modifier,
173 p_k_cur_sts_code => p_chrv_rec.new_sts_code,
174 p_k_cur_sts_type => p_chrv_rec.new_ste_code,
175 p_k_pre_sts_code => p_chrv_rec.old_sts_code,
176 p_k_pre_sts_type => p_chrv_rec.old_ste_code,
177 p_k_source_system_code => p_chrv_rec.ORIG_SYSTEM_SOURCE_CODE);
178 End If;
179 -- Update minor version
180 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
181 x_return_status := Update_Minor_Version(p_chrv_rec.id);
182
183 End If;
184 exception
185 when OTHERS then
186 -- store SQL error message on message stack
187 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
188 p_msg_name => g_unexpected_error,
189 p_token1 => g_sqlcode_token,
190 p_token1_value => sqlcode,
191 p_token2 => g_sqlerrm_token,
192 p_token2_value => sqlerrm);
193
194 -- notify caller of an UNEXPETED error
195 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
196 END update_contract_header;
197
198 PROCEDURE update_contract_line(
199 p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2 ,
201 p_update_minor_version IN VARCHAR2 ,
202 p_contract_number IN VARCHAR2,
203 p_contract_number_modifier IN VARCHAR2,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
208 x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
209
210 l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Line';
211
212 BEGIN
213
214 x_return_status := OKC_API.G_RET_STS_SUCCESS;
215 x_clev_rec := p_clev_rec;
216 UPDATE OKC_K_LINES_B
217 SET STS_CODE = p_clev_rec.sts_code,
218 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
219 PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),PROGRAM_ID),
220 REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),REQUEST_ID),
221 PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,SYSDATE),
222 PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),PROGRAM_APPLICATION_ID),
223 LAST_UPDATE_DATE = SYSDATE,
224 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
225 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
226 WHERE ID = p_clev_rec.id;
227 -- Call action assembler if status is changed
228 If NVL(p_clev_rec.Call_Action_Asmblr,'Y') = 'Y' AND
229 p_clev_rec.old_sts_code is not null AND
230 p_clev_rec.new_sts_code is not null AND
231 p_clev_rec.old_ste_code is not null AND
232 p_clev_rec.new_ste_code is not null AND
233 (p_clev_rec.old_sts_code <> p_clev_rec.new_sts_code OR
234 p_clev_rec.old_ste_code <> p_clev_rec.new_ste_code)
235 Then
236
237 OKC_KL_STS_CHG_ASMBLR_PVT.Acn_Assemble(
238 p_api_version => p_api_version,
239 p_init_msg_list => p_init_msg_list,
240 x_return_status => x_return_status,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data,
243 p_k_id => p_clev_rec.dnz_chr_id,
244 p_kl_id => p_clev_rec.id,
245 p_k_number => p_contract_number,
246 p_k_nbr_mod => p_contract_number_modifier,
247 p_kl_number => p_clev_rec.line_number,
248 p_kl_cur_sts_code => p_clev_rec.new_sts_code,
249 p_kl_cur_sts_type => p_clev_rec.new_ste_code,
250 p_kl_pre_sts_code => p_clev_rec.old_sts_code,
251 p_kl_pre_sts_type => p_clev_rec.old_ste_code,
252 p_kl_source_system_code => p_clev_rec.ORIG_SYSTEM_SOURCE_CODE);
253 End If;
254
255 -- Update minor version
256 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
257 If p_update_minor_version ='Y' Then
258 x_return_status := Update_Minor_Version(p_clev_rec.dnz_chr_id);
259
260 End If;
261 End If;
262 exception
263 when OTHERS then
264
265 -- store SQL error message on message stack
266 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
267 p_msg_name => g_unexpected_error,
268 p_token1 => g_sqlcode_token,
269 p_token1_value => sqlcode,
270 p_token2 => g_sqlerrm_token,
271 p_token2_value => sqlerrm);
272
273 -- notify caller of an UNEXPETED error
274 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
275 END update_contract_line;
276 -----------------------------------------------------------------
277 -- Begin LINE Status change procedure
278 -----------------------------------------------------------------
279
280 Procedure line_message(p_knum_and_mod IN VARCHAR2,
281 p_line_number IN VARCHAR2,
282 p_old_status IN VARCHAR2 DEFAULT NULL,
283 p_status IN VARCHAR2 DEFAULT NULL,
284 p_msg_data IN VARCHAR2 DEFAULT NULL,
285 p_type IN VARCHAR2) IS
286 BEGIN
287 if p_type='S' Then
288 /*
289 FND_MESSAGE.set_name('OKC','OKC_LINE_STS_CHANGE_SUCCESS');
290 FND_MESSAGE.set_token('CONTRACT_NUMBER',p_knum_and_mod);
291 FND_MESSAGE.set_token('LINE_NUMBER',p_line_number);
292 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
293 FND_MESSAGE.set_token('STATUS', p_status);
294 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
295 */
296 NULL;
297 elsif p_type='E'Then
298 get_fnd_msg_stack(p_msg_data);
299 FND_MESSAGE.set_name('OKC','OKC_LINE_STS_CHANGE_FAILURE');
300 FND_MESSAGE.set_token('CONTRACT_NUMBER',p_knum_and_mod);
301 FND_MESSAGE.set_token('LINE_NUMBER',p_line_number);
302 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
303 FND_MESSAGE.set_token('STATUS', p_status);
304 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
305 elsif p_type='U'Then
306 get_fnd_msg_stack(p_msg_data);
307 FND_MESSAGE.set_name('OKC',G_UNEXPECTED_ERROR);
308 FND_MESSAGE.set_token(G_SQLCODE_TOKEN,SQLCODE);
309 FND_MESSAGE.set_token(G_SQLERRM_TOKEN,SQLERRM);
310 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
311 FND_MESSAGE.set_name('OKC','OKC_LINE_STS_CHANGE_FAILURE');
312 FND_MESSAGE.set_token('CONTRACT_NUMBER',p_knum_and_mod);
313 FND_MESSAGE.set_token('LINE_NUMBER',p_line_number);
314 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
315 FND_MESSAGE.set_token('STATUS', p_status);
316 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
317 end if;
318 end line_message;
319
320
321 -- NEW --
322 -- BUG 4285665 --
323 -- GCHADHA --
324 -- Added the fix done for the bug 3967643 --
325 Procedure line_status_change (p_kid IN NUMBER DEFAULT NULL,
326 p_cls_code IN okc_subclasses_b.cls_code%TYPE DEFAULT NULL,
327 p_scs_code IN okc_k_headers_b.scs_code%TYPE DEFAULT NULL,
328 p_from_k IN VARCHAR2 DEFAULT NULL,
329 p_to_k IN VARCHAR2 DEFAULT NULL,
330 p_from_m IN VARCHAR2 DEFAULT NULL,
331 p_to_m IN VARCHAR2 DEFAULT NULL,
332 p_k_num IN okc_k_headers_b.contract_number%TYPE DEFAULT NULL,
333 p_k_num_mod IN okc_k_headers_b.contract_number_modifier%TYPE DEFAULT NULL,
334 p_update_minor_version IN VARCHAR2 DEFAULT 'Y',
335 x_return_status OUT NOCOPY VARCHAR2) IS
336 C number := 0;
337
338 l_knum_and_mod VARCHAR2(240) := p_k_num||' '||p_k_num_mod;
339 l_line_number VARCHAR2(150);
340 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
341 p_init_msg_list VARCHAR2(200) := okc_api.g_true;
342 x_msg_count NUMBER := okc_api.g_miss_num;
343 x_msg_data VARCHAR2(2000) := okc_api.g_miss_char;
344
345 l_cle_rec okc_contract_pub.clev_rec_type;
346 x_cle_rec okc_contract_pub.clev_rec_type;
347
348 TYPE line_rec_type IS RECORD (
349 contract_number okc_k_headers_b.contract_number%TYPE,
350 contract_number_modifier okc_k_headers_b.contract_number_modifier%TYPE,
351 ID okc_k_lines_b.id%TYPE,
352 OBJECT_VERSION_NUMBER okc_k_lines_b.object_version_number%TYPE,
353 STS_CODE okc_k_lines_b.sts_code%TYPE,
354 DATE_TERMINATED okc_k_lines_b.date_terminated%TYPE,
355 START_DATE okc_k_lines_b.start_date%TYPE,
356 END_DATE okc_k_lines_b.end_date%TYPE,
357 LINE_NUMBER okc_k_lines_b.line_number%TYPE,
358 PRICE_NEGOTIATED okc_k_lines_b.price_negotiated%TYPE,
359 dnz_chr_id okc_k_lines_b.dnz_chr_id%TYPE,
360 TERMINATION_REASON fnd_lookups.meaning%TYPE,
361 CODE okc_statuses_b.code%TYPE,
362 STE_CODE okc_statuses_b.ste_code%TYPE,
363 meaning okc_statuses_v.meaning%TYPE);
364
365 line_rec line_rec_type;
366 r_terminate_line_rec line_rec_type;
367 r_expired_line_rec line_rec_type;
368 r_active_line_rec line_rec_type;
369 r_signed_line_rec line_rec_type;
370
371 -- GCHADHA --
372 -- 4285665 --
373 TYPE line_Table_Type IS TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
374 r_terminate_line_tbl line_Table_Type;
375 r_expired_line_tbl line_Table_Type;
376 r_active_line_tbl line_Table_Type;
377 r_signed_line_tbl line_Table_Type;
378 Type Num_Tbl_Type is table of NUMBER index by BINARY_INTEGER ;
379 Type Num9_Tbl_Type is table of NUMBER(9,0) index by BINARY_INTEGER ;
380 TYPE VC30_Tbl_Type is TABLE of VARCHAR2(30) index by BINARY_INTEGER ;
381 TYPE VC120_Tbl_Type is TABLE of VARCHAR2(120) index by BINARY_INTEGER ;
382 TYPE VC150_Tbl_Type is TABLE of VARCHAR2(150) index by BINARY_INTEGER ;
383 TYPE VC80_Tbl_Type is TABLE of VARCHAR2(80) index by BINARY_INTEGER ;
384 TYPE Date_Tbl_Type is TABLE of DATE index by BINARY_INTEGER ;
385
386 l_contract_number_tbl VC120_Tbl_Type;
387 l_contract_number_modifier_tbl VC120_Tbl_Type;
388 l_id_tbl Num_Tbl_Type;
389 l_object_version_number_tbl Num9_Tbl_Type;
390 l_sts_code_tbl VC120_Tbl_Type;
391 l_date_terminated_tbl Date_Tbl_Type;
392 l_start_date_tbl Date_Tbl_Type;
393 l_end_date_tbl Date_Tbl_Type;
394 l_line_number_tbl VC150_Tbl_Type;
395 l_price_negotiated_tbl Num_Tbl_Type;
396 l_dnz_chr_id_tbl Num_Tbl_Type;
397 l_termination_reason_tbl VC80_Tbl_Type;
398 l_code_tbl VC30_Tbl_Type;
399 l_ste_code_tbl VC30_Tbl_Type;
400 l_meaning_tbl VC30_Tbl_Type;
401 -- Used as Counters --
402 i NUMBER := 0;
403 x_num NUMBER := 0;
404
405 -- END GCHADHA --
406
407 -- Cursors -- Contract LINE Level
408
409 -- From ACTIVE/SIGNED to Terminated.
410 /* Commented for Bug #3967643. Split cursor into 3 parts based on
411 parameters as suggested by Appsperf Team
412 CURSOR c_terminate_line_all IS
413 SELECT chr.contract_number,
414 chr.contract_number_modifier,
415 line.ID ,
416 line.OBJECT_VERSION_NUMBER,
417 line.STS_CODE,
418 line.DATE_TERMINATED ,
419 line.START_DATE ,
420 line.END_DATE,
421 line.LINE_NUMBER,
422 line.PRICE_NEGOTIATED,
423 line.dnz_chr_id,
424 fnd.meaning TERMINATION_REASON,
425 status.CODE,
426 status.STE_CODE,
427 status.meaning
428 FROM OKC_K_LINES_B line,
429 OKC_STATUSES_V status,
430 fnd_lookups fnd,
431 okc_k_headers_b chr,
432 okc_subclasses_b scs
433 WHERE line.STS_CODE = status.CODE
434 AND line.dnz_chr_id = chr.id
435 AND chr.scs_code = scs.code
436 AND scs.cls_code <> 'OKL'
437 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
438 AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
439 AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
440 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
441 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
442 AND status.ste_code IN ('ACTIVE','HOLD','SIGNED')
443 AND line.date_terminated <= trunc(sysdate) + 0.99999
444 AND LINE.date_terminated >= trunc(l_last_rundate)
445 AND line.trn_code = fnd.lookup_code
446 AND fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON';
447 */
448 -- Bug #3967643. Split cursors into 3 parts as suggested by Appsperf Team
449 -- Hint added as suggested by Appsperf Team
450 -- From ACTIVE/SIGNED to Terminated
451
452 -- When only the From-to Contract number range is provided with optional Contract number modifier range
453 -- and optional Category code
454 CURSOR c_termnt_line_all_cntr IS
455 SELECT
456 CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
457 LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
458 LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
459 FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
460 FROM OKC_K_LINES_B LINE,
461 OKC_STATUSES_TL stst,
462 OKC_STATUSES_B stsb,
463 FND_LOOKUPS FND,
464 OKC_K_HEADERS_B CHR,
465 OKC_SUBCLASSES_B SCS
466 WHERE LINE.STS_CODE = STST.CODE
467 AND LINE.DNZ_CHR_ID = CHR.ID
468 AND stst.code = stsb.code
469 AND STST.LANGUAGE = USERENV('LANG')
470 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
471 AND CHR.CONTRACT_NUMBER >= p_from_k
472 AND CHR.CONTRACT_NUMBER <= p_to_k
473 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
474 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
475 --BUG 4915692 Gchadha
476 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
477 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
478 AND stsb.ste_code IN ('ACTIVE','SIGNED')
479 --END BUG 4915692 Gchadha
480 AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
481 AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
482 AND LINE.TRN_CODE = FND.LOOKUP_CODE
483 AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
484 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
485
486 -- When only Category is provided
487 CURSOR c_termnt_line_all_category IS
488 SELECT
489 CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
490 LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
491 LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
492 FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
493 FROM OKC_K_LINES_B LINE,
494 OKC_STATUSES_TL stst,
495 OKC_STATUSES_B stsb,
496 FND_LOOKUPS FND,
497 OKC_K_HEADERS_B CHR,
498 OKC_SUBCLASSES_B SCS
499 WHERE LINE.STS_CODE = STST.CODE
500 AND LINE.DNZ_CHR_ID = CHR.ID
501 AND stst.code = stsb.code
502 AND STST.LANGUAGE = USERENV('LANG')
503 AND chr.scs_code = p_scs_code
504 --BUG 4915692 Gchadha
505 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
506 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
507 AND stsb.ste_code IN ('ACTIVE','SIGNED')
508 --BUG 4915692 Gchadha
509 AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
510 AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
511 AND LINE.TRN_CODE = FND.LOOKUP_CODE
512 AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
513 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
514
515 -- When no parameters are provided
516 CURSOR c_terminate_line_all IS
517 SELECT
518 CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
519 LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
520 LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
521 FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
522 FROM OKC_K_LINES_B LINE,
523 OKC_STATUSES_TL stst,
524 OKC_STATUSES_B stsb,
525 FND_LOOKUPS FND,
526 OKC_K_HEADERS_B CHR,
527 OKC_SUBCLASSES_B SCS
528 WHERE LINE.STS_CODE = STST.CODE
529 AND LINE.DNZ_CHR_ID = CHR.ID
530 AND stst.code = stsb.code
531 AND STST.LANGUAGE = USERENV('LANG')
532 -- BUG 4915692 Gchadha
533 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
534 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
535 AND stsb.ste_code IN ('ACTIVE','SIGNED')
536 -- BUG 4915692 Gchadha
537 AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
538 AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
539 AND LINE.TRN_CODE = FND.LOOKUP_CODE
540 AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
541 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
542
543 -- From ACTIVE/SIGNED/HOLD to EXPIRED
544 -- Hint added as per Bug 2563108 (Suggested by Appsperf Team)
545 -- Commented for Bug #3967643. Split cursor into 3 parts based on parameters
546 -- as suggested by Appsperf Team
547 --CURSOR c_expired_line_all IS
548 --SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
549 /* chr.contract_number,
550 chr.contract_number_modifier,
551 line.ID ,
552 line.OBJECT_VERSION_NUMBER,
553 line.STS_CODE,
554 line.DATE_TERMINATED ,
555 line.START_DATE ,
556 line.END_DATE,
557 line.LINE_NUMBER,
558 line.PRICE_NEGOTIATED,
559 line.dnz_chr_id,
560 status.CODE,
561 status.STE_CODE,
562 status.meaning
563 FROM OKC_K_LINES_B line,
564 OKC_STATUSES_V status,
565 OKC_K_HEADERS_B chr,
566 okc_subclasses_b scs
567 WHERE line.STS_CODE = status.CODE
568 AND line.dnz_chr_id = chr.id
569 AND chr.scs_code = scs.code
570 AND scs.cls_code <> 'OKL'
571 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
572 AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
573 AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
574 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
575 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
576 and status.ste_code in ('ACTIVE','SIGNED','HOLD') -- <> 'EXPIRED'
577 --
578 -- Bug 2672565 - Removed time component and changed from <= to <
579 --
580 --AND line.end_date <= trunc(sysdate) + 0.99999
581 AND line.end_date < trunc(sysdate)
582 AND line.end_date >= trunc(l_last_rundate)
583 AND (line.date_terminated IS NULL
584 OR line.date_terminated >= trunc(sysdate)); */
585
586 -- Bug #3967643. Split cursors into 3 parts as suggested by Appsperf Team
587 -- Hint added as suggested by Appsperf Team
588 -- From ACTIVE/SIGNED to EXPIRED
589
590 -- When only the From-to Contract number range is provided with optional Contract number modifier range
591 -- and optional Category code
592 CURSOR c_expr_line_all_cntr IS
593 SELECT
594 chr.contract_number,
595 chr.contract_number_modifier,
596 line.ID ,
597 line.OBJECT_VERSION_NUMBER,
598 line.STS_CODE,
599 line.DATE_TERMINATED ,
600 line.START_DATE ,
601 line.END_DATE,
602 line.LINE_NUMBER,
603 line.PRICE_NEGOTIATED,
604 line.dnz_chr_id,
605 stsb.CODE,
606 stsb.STE_CODE,
607 stst.meaning
608 FROM OKC_K_LINES_B line,
609 OKC_STATUSES_TL stst,
610 OKC_STATUSES_B stsb,
611 OKC_K_HEADERS_B chr
612 -- okc_subclasses_b scs
613 WHERE line.STS_CODE = stst.CODE
614 AND line.dnz_chr_id = chr.id
615 AND stst.code = stsb.code
616 AND STST.LANGUAGE = USERENV('LANG')
617 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
618 AND CHR.CONTRACT_NUMBER >= p_from_k
619 AND CHR.CONTRACT_NUMBER <= p_to_k
620 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
621 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
622 -- BUG 4915692 Gchadha
623 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
624 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
625 AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
626 --BUG 4915692 Gchadha
627 --
628 -- Bug 2672565 - Removed time component and changed from <= to <
629 --
630 --AND line.end_date <= trunc(sysdate) + 0.99999
631 AND line.end_date < trunc(sysdate)
632 AND line.end_date >= trunc(l_last_rundate)
633 AND (line.date_terminated IS NULL
634 OR line.date_terminated >= trunc(sysdate))
635 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
636
637
638 -- When only Category is provided
639 CURSOR c_expr_line_all_category IS
640 SELECT
641 chr.contract_number,
642 chr.contract_number_modifier,
643 line.ID ,
644 line.OBJECT_VERSION_NUMBER,
645 line.STS_CODE,
646 line.DATE_TERMINATED ,
647 line.START_DATE ,
648 line.END_DATE,
649 line.LINE_NUMBER,
650 line.PRICE_NEGOTIATED,
651 line.dnz_chr_id,
652 stsb.CODE,
653 stsb.STE_CODE,
654 stst.meaning
655 FROM OKC_K_LINES_B line,
656 -- OKC_STATUSES_V status,
657 OKC_STATUSES_TL stst,
658 OKC_STATUSES_B stsb,
659 OKC_K_HEADERS_B chr
660 -- okc_subclasses_b scs
661 WHERE line.STS_CODE = stst.CODE
662 AND line.dnz_chr_id = chr.id
663 AND stst.code = stsb.code
664 AND STST.LANGUAGE = USERENV('LANG')
665 AND chr.scs_code = p_scs_code
666 -- Bug 4915692 --
667 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
668 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
669 AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
670 -- Bug 4915692 --
671 --
672 -- Bug 2672565 - Removed time component and changed from <= to <
673 --
674 --AND line.end_date <= trunc(sysdate) + 0.99999
675 AND line.end_date < trunc(sysdate)
676 AND line.end_date >= trunc(l_last_rundate)
677 AND (line.date_terminated IS NULL
678 OR line.date_terminated >= trunc(sysdate))
679 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
680
681 -- When no parameters are is provided
682 CURSOR c_expired_line_all IS
683 SELECT
684 chr.contract_number,
685 chr.contract_number_modifier,
686 line.ID ,
687 line.OBJECT_VERSION_NUMBER,
688 line.STS_CODE,
689 line.DATE_TERMINATED ,
690 line.START_DATE ,
691 line.END_DATE,
692 line.LINE_NUMBER,
693 line.PRICE_NEGOTIATED,
694 line.dnz_chr_id,
695 stsb.CODE,
696 stsb.STE_CODE,
697 stst.meaning
698 FROM OKC_K_LINES_B line,
699 OKC_STATUSES_TL stst,
700 OKC_STATUSES_B stsb,
701 OKC_K_HEADERS_B chr
702 -- okc_subclasses_b scs
703 WHERE line.STS_CODE = stst.CODE
704 AND line.dnz_chr_id = chr.id
705 AND stst.code = stsb.code
706 AND STST.LANGUAGE = USERENV('LANG')
707 -- Bug 4915692 --
708 --AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
709 AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
710
711 -- Bug 4915692 --
712 --
713 -- Bug 2672565 - Removed time component and changed from <= to <
714 --
715 --AND line.end_date <= trunc(sysdate) + 0.99999
716 AND line.end_date < trunc(sysdate)
717 AND line.end_date >= trunc(l_last_rundate)
718 AND (line.date_terminated IS NULL
719 OR line.date_terminated >= trunc(sysdate))
720 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
721
722 -- LINE from SIGNED to ACTIVE
723 /* Commented for Bug #3967643. Split cursor into 3 parts based on
724 parameters as suggested by Appsperf Team
725 CURSOR c_active_line_all IS
726 SELECT chr.contract_number,
727 chr.contract_number_modifier,
728 line.ID ,
729 line.OBJECT_VERSION_NUMBER,
730 line.STS_CODE,
731 line.DATE_TERMINATED ,
732 line.START_DATE ,
733 line.END_DATE,
734 line.LINE_NUMBER,
735 line.PRICE_NEGOTIATED,
736 line.dnz_chr_id,
737 status.CODE,
738 status.STE_CODE,
739 status.meaning
740 FROM OKC_K_LINES_B line,
741 OKC_STATUSES_V status,
742 OKC_STATUSES_B status1,
743 okc_k_headers_b chr,
744 okc_subclasses_b scs
745 WHERE line.STS_CODE = status.CODE
746 AND line.dnz_chr_id = chr.id
747 AND chr.scs_code = scs.code
748 AND scs.cls_code <> 'OKL'
749 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
750 AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
751 AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
752 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
753 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
754 AND status.ste_code = 'SIGNED'
755 AND chr.STS_CODE = status1.CODE
756 AND status1.ste_code = 'ACTIVE'
757 AND line.start_date >= trunc(l_last_rundate) AND
758 line.start_date <= trunc(sysdate) + 0.99999
759 AND (line.date_terminated IS NULL
760 or line.date_terminated >= trunc(sysdate));
761 */
762 -- Bug #3967643. Split cursors into 3 parts as suggested by Appsperf Team
763 -- Hint added as suggested by Appsperf Team
764 -- LINE from SIGNED to ACTIVE
765
766 -- When only the From-to Contract number range is provided with optional Contract number modifier range
767 -- and optional Category code
768 CURSOR c_actv_line_all_cntr IS
769 SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
770 chr.contract_number,
771 chr.contract_number_modifier,
772 line.ID ,
773 line.OBJECT_VERSION_NUMBER,
774 line.STS_CODE,
775 line.DATE_TERMINATED ,
776 line.START_DATE ,
777 line.END_DATE,
778 line.LINE_NUMBER,
779 line.PRICE_NEGOTIATED,
780 line.dnz_chr_id,
781 status.CODE,
782 status.STE_CODE,
783 status.meaning
784 FROM OKC_K_LINES_B line,
785 OKC_STATUSES_V status,
786 OKC_STATUSES_B status1,
787 okc_k_headers_b chr,
788 okc_subclasses_b scs
789 WHERE line.STS_CODE = status.CODE
790 AND line.dnz_chr_id = chr.id
791 AND chr.scs_code = scs.code
792 AND scs.cls_code <> 'OKL'
793 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
794 AND chr.CONTRACT_NUMBER >= p_from_k
795 AND chr.CONTRACT_NUMBER <= p_to_k
796 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
797 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
798 AND status.ste_code = 'SIGNED'
799 AND chr.STS_CODE = status1.CODE
800 AND status1.ste_code = 'ACTIVE'
801 AND line.start_date >= trunc(l_last_rundate)
802 AND line.start_date <= trunc(sysdate) + 0.99999
803 AND (line.date_terminated IS NULL
804 or line.date_terminated >= trunc(sysdate));
805
806 -- When only Category is provided
807 CURSOR c_actv_line_all_category IS
808 SELECT /*+leading(SCS) USE_NL(SCS LINE CHR FND.LV STATUS.STSB STATUS.STST))*/
809 chr.contract_number,
810 chr.contract_number_modifier,
811 line.ID ,
812 line.OBJECT_VERSION_NUMBER,
813 line.STS_CODE,
814 line.DATE_TERMINATED ,
815 line.START_DATE ,
816 line.END_DATE,
817 line.LINE_NUMBER,
818 line.PRICE_NEGOTIATED,
819 line.dnz_chr_id,
820 status.CODE,
821 status.STE_CODE,
822 status.meaning
823 FROM OKC_K_LINES_B line,
824 OKC_STATUSES_V status,
825 OKC_STATUSES_B status1,
826 okc_k_headers_b chr,
827 okc_subclasses_b scs
828 WHERE line.STS_CODE = status.CODE
829 AND line.dnz_chr_id = chr.id
830 AND chr.scs_code = scs.code
831 AND scs.cls_code <> 'OKL'
832 AND CHR.SCS_CODE = p_scs_code
833 AND status.ste_code = 'SIGNED'
834 AND chr.STS_CODE = status1.CODE
835 AND status1.ste_code = 'ACTIVE'
836 AND line.start_date >= trunc(l_last_rundate)
837 AND line.start_date <= trunc(sysdate) + 0.99999
838 AND (line.date_terminated IS NULL
839 or line.date_terminated >= trunc(sysdate));
840
841 -- When no parameters are provided
842 CURSOR c_active_line_all IS
843 SELECT /*+leading(LINE) USE_NL(LINE CHR SCS FND.LV STATUS.STSB STATUS.STST))*/
844 chr.contract_number,
845 chr.contract_number_modifier,
846 line.ID ,
847 line.OBJECT_VERSION_NUMBER,
848 line.STS_CODE,
849 line.DATE_TERMINATED ,
850 line.START_DATE ,
851 line.END_DATE,
852 line.LINE_NUMBER,
853 line.PRICE_NEGOTIATED,
854 line.dnz_chr_id,
855 status.CODE,
856 status.STE_CODE,
857 status.meaning
858 FROM OKC_K_LINES_B line,
859 OKC_STATUSES_V status,
860 OKC_STATUSES_B status1,
861 okc_k_headers_b chr,
862 okc_subclasses_b scs
863 WHERE line.STS_CODE = status.CODE
864 AND line.dnz_chr_id = chr.id
865 AND chr.scs_code = scs.code
866 AND scs.cls_code <> 'OKL'
867 AND status.ste_code = 'SIGNED'
868 AND chr.STS_CODE = status1.CODE
869 AND status1.ste_code = 'ACTIVE'
870 AND line.start_date >= trunc(l_last_rundate)
871 AND line.start_date <= trunc(sysdate) + 0.99999
872 AND (line.date_terminated IS NULL
873 or line.date_terminated >= trunc(sysdate));
874
875 --bug 5930684
876 -- LINE from ACTIVE to SIGNED
877
878 -- When only the From-to Contract number range is provided with optional Contract number modifier range
879 -- and optional Category code
880 CURSOR c_sign_line_all_cntr IS
881 SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
882 chr.contract_number,
883 chr.contract_number_modifier,
884 line.ID ,
885 line.OBJECT_VERSION_NUMBER,
886 line.STS_CODE,
887 line.DATE_TERMINATED ,
888 line.START_DATE ,
889 line.END_DATE,
890 line.LINE_NUMBER,
891 line.PRICE_NEGOTIATED,
892 line.dnz_chr_id,
893 status.CODE,
894 status.STE_CODE,
895 status.meaning
896 FROM OKC_K_LINES_B line,
897 OKC_STATUSES_V status,
898 OKC_STATUSES_B status1,
899 okc_k_headers_b chr,
900 okc_subclasses_b scs
901 WHERE line.STS_CODE = status.CODE
902 AND line.dnz_chr_id = chr.id
903 AND chr.scs_code = scs.code
904 AND scs.cls_code <> 'OKL'
905 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
906 AND chr.CONTRACT_NUMBER >= p_from_k
907 AND chr.CONTRACT_NUMBER <= p_to_k
908 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
909 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
910 AND status.ste_code = 'ACTIVE'
911 AND chr.STS_CODE = status1.CODE
912 AND status1.ste_code = 'SIGNED'
913 AND line.start_date >= trunc(l_last_rundate)
914 AND line.start_date >= trunc(sysdate) + 0.99999
915 AND (line.date_terminated IS NULL
916 or line.date_terminated >= trunc(sysdate));
917
918 -- When only Category is provided
919 CURSOR c_sign_line_all_category IS
920 SELECT /*+leading(SCS) USE_NL(SCS LINE CHR FND.LV STATUS.STSB STATUS.STST))*/
921 chr.contract_number,
922 chr.contract_number_modifier,
923 line.ID ,
924 line.OBJECT_VERSION_NUMBER,
925 line.STS_CODE,
926 line.DATE_TERMINATED ,
927 line.START_DATE ,
928 line.END_DATE,
929 line.LINE_NUMBER,
930 line.PRICE_NEGOTIATED,
931 line.dnz_chr_id,
932 status.CODE,
933 status.STE_CODE,
934 status.meaning
935 FROM OKC_K_LINES_B line,
936 OKC_STATUSES_V status,
937 OKC_STATUSES_B status1,
938 okc_k_headers_b chr,
939 okc_subclasses_b scs
940 WHERE line.STS_CODE = status.CODE
941 AND line.dnz_chr_id = chr.id
942 AND chr.scs_code = scs.code
943 AND scs.cls_code <> 'OKL'
944 AND CHR.SCS_CODE = p_scs_code
945 AND status.ste_code = 'ACTIVE'
946 AND chr.STS_CODE = status1.CODE
947 AND status1.ste_code = 'SIGNED'
948 AND line.start_date >= trunc(l_last_rundate)
949 AND line.start_date >= trunc(sysdate) + 0.99999
950 AND (line.date_terminated IS NULL
951 or line.date_terminated >= trunc(sysdate));
952
953 -- When no parameters are provided
954 CURSOR c_signed_line_all IS
955 SELECT /*+leading(LINE) USE_NL(LINE CHR SCS FND.LV STATUS.STSB STATUS.STST))*/
956 chr.contract_number,
957 chr.contract_number_modifier,
958 line.ID ,
959 line.OBJECT_VERSION_NUMBER,
960 line.STS_CODE,
961 line.DATE_TERMINATED ,
962 line.START_DATE ,
963 line.END_DATE,
964 line.LINE_NUMBER,
965 line.PRICE_NEGOTIATED,
966 line.dnz_chr_id,
967 status.CODE,
968 status.STE_CODE,
969 status.meaning
970 FROM OKC_K_LINES_B line,
971 OKC_STATUSES_V status,
972 OKC_STATUSES_B status1,
973 okc_k_headers_b chr,
974 okc_subclasses_b scs
975 WHERE line.STS_CODE = status.CODE
976 AND line.dnz_chr_id = chr.id
977 AND chr.scs_code = scs.code
978 AND scs.cls_code <> 'OKL'
979 AND status.ste_code = 'ACTIVE'
980 AND chr.STS_CODE = status1.CODE
981 AND status1.ste_code = 'SIGNED'
982 AND line.start_date >= trunc(l_last_rundate)
983 AND line.start_date >= trunc(sysdate) + 0.99999
984 AND (line.date_terminated IS NULL
985 or line.date_terminated >= trunc(sysdate));
986 -- end of bug 5930684.
987
988 -- From ACTIVE/SIGNED to Terminated.
989 CURSOR c_terminate_line_k IS
990 SELECT line.ID ,
991 line.OBJECT_VERSION_NUMBER,
992 line.STS_CODE,
993 line.DATE_TERMINATED ,
994 line.START_DATE ,
995 line.END_DATE,
996 line.LINE_NUMBER,
997 line.PRICE_NEGOTIATED,
998 line.dnz_chr_id,
999 fnd.meaning TERMINATION_REASON,
1000 status.CODE,
1001 status.STE_CODE,
1002 status.meaning
1003 FROM OKC_K_LINES_B line,
1004 OKC_STATUSES_V status,
1005 fnd_lookups fnd
1006 WHERE line.STS_CODE = status.CODE
1007 AND line.dnz_chr_id = p_kid
1008 --BUG 4915692 --
1009 --AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
1010 AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
1011 --Bug 4915692 --
1012 AND line.date_terminated <= trunc(sysdate) + 0.99999
1013 AND LINE.date_terminated >= trunc(l_last_rundate)
1014 AND line.trn_code = fnd.lookup_code
1015 AND fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON';
1016
1017 -- From ACTIVE/SIGNED to EXPIRED
1018 CURSOR c_expired_line_k IS
1019 SELECT line.ID ,
1020 line.OBJECT_VERSION_NUMBER,
1021 line.STS_CODE,
1022 line.DATE_TERMINATED ,
1023 line.START_DATE ,
1024 line.END_DATE,
1025 line.LINE_NUMBER,
1026 line.PRICE_NEGOTIATED,
1027 line.dnz_chr_id,
1028 status.CODE,
1029 status.STE_CODE,
1030 status.meaning
1031 FROM OKC_K_LINES_B line,
1032 OKC_STATUSES_V status
1033 WHERE line.STS_CODE = status.CODE
1034 AND line.dnz_chr_id = p_kid
1035 --BUG 4915692 --
1036 --AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
1037 AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
1038 -- Bug 4915692 --
1039 --
1040 -- Bug 2672565 - Removed time component and changed <= to <
1041 --AND line.end_date <= trunc(sysdate) + 0.99999
1042 --
1043 AND line.end_date < trunc(sysdate)
1044 AND line.end_date >= trunc(l_last_rundate)
1045 AND (line.date_terminated IS NULL
1046 OR line.date_terminated >= trunc(sysdate));
1047
1048
1049 -- LINE from SIGNED to ACTIVE
1050 CURSOR c_active_line_k IS
1051 SELECT line.ID ,
1052 line.OBJECT_VERSION_NUMBER,
1053 line.STS_CODE,
1054 line.DATE_TERMINATED ,
1055 line.START_DATE ,
1056 line.END_DATE,
1057 line.LINE_NUMBER,
1058 line.PRICE_NEGOTIATED,
1059 line.dnz_chr_id,
1060 status.CODE,
1061 status.STE_CODE,
1062 status.meaning
1063 FROM OKC_K_LINES_B line,
1064 OKC_STATUSES_V status
1065 WHERE line.STS_CODE = status.CODE
1066 AND line.dnz_chr_id = p_kid
1067 AND status.ste_code = 'SIGNED'
1068 AND line.start_date >= trunc(l_last_rundate) AND
1069 line.start_date <= trunc(sysdate) + 0.99999
1070 AND (line.date_terminated IS NULL
1071 or line.date_terminated >= trunc(sysdate));
1072
1073 --bug 5930684
1074 -- LINE from ACTIVE to SIGNED
1075 CURSOR c_signed_line_k IS
1076 SELECT line.ID ,
1077 line.OBJECT_VERSION_NUMBER,
1078 line.STS_CODE,
1079 line.DATE_TERMINATED ,
1080 line.START_DATE ,
1081 line.END_DATE,
1082 line.LINE_NUMBER,
1083 line.PRICE_NEGOTIATED,
1084 line.dnz_chr_id,
1085 status.CODE,
1086 status.STE_CODE,
1087 status.meaning
1088 FROM OKC_K_LINES_B line,
1089 OKC_STATUSES_V status
1090 WHERE line.STS_CODE = status.CODE
1091 AND line.dnz_chr_id = p_kid
1092 AND status.ste_code = 'ACTIVE'
1093 AND line.start_date >= trunc(l_last_rundate) AND
1094 line.start_date >= trunc(sysdate) + 0.99999
1095 AND (line.date_terminated IS NULL
1096 or line.date_terminated >= trunc(sysdate));
1097 --end of bug 5930684
1098
1099 PROCEDURE line_terminate(p_term_line_rec IN line_rec_type) IS
1100 BEGIN
1101
1102
1103 if ((C >= T) and p_kid IS NULL) then
1104 commit;
1105 c := 0;
1106 end if;
1107 savepoint H_STATUS;
1108 p_line_count:= p_line_count + 1;
1109 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1110 l_knum_and_mod := p_term_line_rec.contract_number||' '||p_term_line_rec.contract_number_modifier;
1111 l_new_status := null;
1112 l_new_status_m := null;
1113 l_line_number := p_term_line_rec.line_number;
1114 if (h_status_type = 'TERMINATED') then
1115 l_new_status := h_status;
1116 l_new_status_m := h_status_m;
1117 else
1118 l_new_status := v_terminated ;
1119 l_new_status_m := v_terminated_m ;
1120 end if;
1121
1122 l_cle_rec.id := p_term_line_rec.id ;
1123 l_cle_rec.object_version_number := p_term_line_rec.object_version_number ;
1124 l_cle_rec.sts_code := l_new_status;
1125 l_cle_rec.dnz_chr_id := p_term_line_rec.dnz_chr_id;
1126 --
1127 -- Assign values if and only if the change in line status is not resulting
1128 -- from the change in header status
1129 ---- Bug# 1405237
1130 l_cle_rec.call_action_asmblr := 'N';
1131 --
1132 IF h_new_status is null and l_new_status is not null then
1133 l_cle_rec.old_sts_code := p_term_line_rec.sts_code;
1134 l_cle_rec.old_ste_code := p_term_line_rec.ste_code;
1135 l_cle_rec.new_sts_code := l_new_status;
1136 l_cle_rec.new_ste_code := 'TERMINATED';
1137 l_cle_rec.call_action_asmblr := 'Y';
1138 END IF;
1139
1140 --
1141 -- lock added not to depend on update implementation
1142 --
1143
1144
1145 okc_contract_pub.lock_contract_line(
1146 p_api_version => 1.0,
1147 p_init_msg_list => p_init_msg_list,
1148 x_return_status => l_return_status,
1149 x_msg_count => x_msg_count,
1150 x_msg_data => x_msg_data,
1151 p_clev_rec => l_cle_rec);
1152
1153 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1154 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1155 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1156 raise OKC_API.G_EXCEPTION_ERROR;
1157 END IF;
1158
1159 update_contract_line (
1160 p_api_version => 1.0,
1161 p_init_msg_list => p_init_msg_list,
1162 p_contract_number => p_term_line_rec.contract_number,
1163 p_contract_number_modifier => p_term_line_rec.contract_number_modifier,
1164 p_update_minor_version => p_update_minor_version,
1165 x_return_status => l_return_status,
1166 x_msg_count => x_msg_count,
1167 x_msg_data => x_msg_data,
1168 p_clev_rec => l_cle_rec,
1169 x_clev_rec => x_cle_rec);
1170
1171 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1172 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1173 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1174 raise OKC_API.G_EXCEPTION_ERROR;
1175 END IF;
1176
1177
1178 OKC_KL_TERM_ASMBLR_PVT.acn_assemble(
1179 p_api_version => 1.0 ,
1180 p_init_msg_list => p_init_msg_list,
1181 x_return_status => l_return_status,
1182 x_msg_count => x_msg_count,
1183 x_msg_data => x_msg_data,
1184 p_k_class => p_cls_code,
1185 p_k_id => p_kid,
1186 p_kl_id => p_term_line_rec.id,
1187 p_kl_term_date => p_term_line_rec.date_terminated,
1188 p_kl_term_reason => p_term_line_rec.termination_reason,
1189 p_k_number => p_term_line_rec.contract_number,
1190 p_k_nbr_mod => p_term_line_rec.contract_number_modifier,
1191 p_k_subclass => p_scs_code,
1192 P_KL_STATUS_CODE => p_term_line_rec.STS_CODE,
1193 p_estimated_amount => p_term_line_rec.price_negotiated );
1194 --
1195 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1196 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1197 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1198 raise OKC_API.G_EXCEPTION_ERROR;
1199 END IF;
1200
1201 OKC_TIME_RES_PUB.res_time_termnt_k(
1202 P_CHR_ID => NULL,
1203 P_CLE_ID => p_term_line_rec.id,
1204 P_END_DATE => p_term_line_rec.DATE_TERMINATED,
1205 P_API_VERSION => 1.0 ,
1206 p_init_msg_list => p_init_msg_list,
1207 x_return_status => l_return_status
1208 );
1209 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1210 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1211 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1212 raise OKC_API.G_EXCEPTION_ERROR;
1213 END IF;
1214 --
1215 if p_kid is NULL then
1216 c := c+1;
1217 --
1218
1219 line_message(p_knum_and_mod =>l_knum_and_mod,
1220 p_line_number=>l_line_number,
1221 p_old_status =>p_term_line_rec.meaning,
1222 p_status =>l_new_status,
1223 p_type =>'S');
1224 end if;
1225
1226
1227 EXCEPTION
1228 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1229 line_message(p_knum_and_mod =>l_knum_and_mod,
1230 p_line_number=>l_line_number,
1231 p_old_status =>p_term_line_rec.meaning,
1232 p_status =>l_new_status,
1233 p_msg_data => x_msg_data,
1234 p_type =>'U');
1235 p_line_errors := p_line_errors +1 ;
1236 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1237 rollback to H_STATUS;
1238 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1239 line_message(p_knum_and_mod =>l_knum_and_mod,
1240 p_line_number=>l_line_number,
1241 p_old_status =>p_term_line_rec.meaning,
1242 p_status =>l_new_status,
1243 p_msg_data => x_msg_data,
1244 p_type =>'E');
1245 p_line_errors := p_line_errors +1 ;
1246 x_return_status := OKC_API.G_RET_STS_ERROR;
1247 rollback to H_STATUS;
1248 WHEN OTHERS then
1249 line_message(p_knum_and_mod =>l_knum_and_mod,
1250 p_line_number=>l_line_number,
1251 p_old_status =>p_term_line_rec.meaning,
1252 p_status =>l_new_status,
1253 p_msg_data => x_msg_data,
1254 p_type =>'U');
1255 p_line_errors := p_line_errors +1 ;
1256 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1257 rollback to H_STATUS;
1258 END line_terminate;
1259
1260 PROCEDURE line_expire(p_exp_line_rec IN line_rec_type) IS
1261 BEGIN
1262
1263 if ((C >= T) and p_kid IS NULL) then
1264 commit;
1265 c := 0;
1266 end if;
1267 savepoint H_STATUS;
1268 p_line_count:= p_line_count + 1;
1269 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1270 l_knum_and_mod := p_exp_line_rec.contract_number||' '||p_exp_line_rec.contract_number_modifier;
1271 l_new_status := null;
1272 l_new_status_m := null;
1273
1274 l_line_number := p_exp_line_rec.line_number;
1275 if (h_status_type = 'EXPIRED') then
1276 l_new_status := h_status;
1277 l_new_status_m := h_status_m;
1278 else
1279 l_new_status := v_expired ;
1280 l_new_status_m := v_expired_m ;
1281 end if;
1282
1283 l_cle_rec.id := p_exp_line_rec.id ;
1284 l_cle_rec.object_version_number := p_exp_line_rec.object_version_number ;
1285 l_cle_rec.sts_code := l_new_status;
1286 l_cle_rec.dnz_chr_id := p_exp_line_rec.dnz_chr_id;
1287 --
1288 --
1289 -- Start: Added for Status Change Action Assembler Changes 10/19/2000
1290 --
1291 -- Assign values if and oly if the change in line status is not resulting
1292 -- from the change in header status
1293 --
1294 l_cle_rec.call_action_asmblr := 'N';
1295 IF h_new_status is null and l_new_status is not null then
1296 l_cle_rec.old_sts_code := p_exp_line_rec.sts_code;
1297 l_cle_rec.old_ste_code := p_exp_line_rec.ste_code;
1298 l_cle_rec.new_sts_code := l_new_status;
1299 l_cle_rec.new_ste_code := 'EXPIRED';
1300 l_cle_rec.call_action_asmblr := 'Y';
1301 END IF;
1302
1303 -- lock added not to depend on update implementation
1304 --
1305
1306 okc_contract_pub.lock_contract_line(
1307 p_api_version => 1.0,
1308 p_init_msg_list => p_init_msg_list,
1309 x_return_status => l_return_status,
1310 x_msg_count => x_msg_count,
1311 x_msg_data => x_msg_data,
1312 p_clev_rec => l_cle_rec);
1313
1314
1315 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1316 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1317 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1318 raise OKC_API.G_EXCEPTION_ERROR;
1319 END IF;
1320 --
1321
1322 update_contract_line(
1323 p_api_version => 1.0,
1324 p_init_msg_list => p_init_msg_list,
1325 p_update_minor_version => p_update_minor_version,
1326 p_contract_number => p_exp_line_rec.contract_number,
1327 p_contract_number_modifier => p_exp_line_rec.contract_number_modifier,
1328 x_return_status => l_return_status,
1329 x_msg_count => x_msg_count,
1330 x_msg_data => x_msg_data,
1331 p_clev_rec => l_cle_rec,
1332 x_clev_rec => x_cle_rec);
1333
1334
1335 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1336 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1337 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1338 raise OKC_API.G_EXCEPTION_ERROR;
1339 END IF;
1340 --
1341 if p_kid is NULL then
1342 c := c+1;
1343 --
1344
1345
1346 line_message(p_knum_and_mod =>l_knum_and_mod,
1347 p_line_number=>l_line_number,
1348 p_status =>l_new_status,
1349 p_old_status =>p_exp_line_rec.meaning,
1350 p_type =>'S');
1351 end if;
1352
1353 EXCEPTION
1354 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1355 line_message(p_knum_and_mod =>l_knum_and_mod,
1356 p_line_number=>l_line_number,
1357 p_status =>l_new_status,
1358 p_old_status =>p_exp_line_rec.meaning,
1359 p_msg_data => x_msg_data,
1360 p_type =>'U');
1361 p_line_errors := p_line_errors +1 ;
1362 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1363 rollback to H_STATUS;
1364 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1365 line_message(p_knum_and_mod =>l_knum_and_mod,
1366 p_line_number=>l_line_number,
1367 p_status =>l_new_status,
1368 p_old_status =>p_exp_line_rec.meaning,
1369 p_msg_data => x_msg_data,
1370 p_type =>'E');
1371 p_line_errors := p_line_errors +1 ;
1372 x_return_status := OKC_API.G_RET_STS_ERROR;
1373 rollback to H_STATUS;
1374 WHEN OTHERS then
1375 line_message(p_knum_and_mod =>l_knum_and_mod,
1376 p_line_number=>l_line_number,
1377 p_status =>l_new_status,
1378 p_old_status =>p_exp_line_rec.meaning,
1379 p_msg_data => x_msg_data,
1380 p_type =>'U');
1381 p_line_errors := p_line_errors +1 ;
1382 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1383 rollback to H_STATUS;
1384 END line_expire;
1385
1386 PROCEDURE line_active(p_active_line_rec IN line_rec_type) IS
1387 BEGIN
1388
1389
1390 if ((C >= T) and p_kid IS NULL) then
1391 commit;
1392 c := 0;
1393 end if;
1394 savepoint H_STATUS;
1395 p_line_count:= p_line_count + 1;
1396 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1397 l_knum_and_mod := p_active_line_rec.contract_number||' '||p_active_line_rec.contract_number_modifier;
1398 l_new_status := null;
1399 l_new_status_m := null;
1400
1401 l_line_number := p_active_line_rec.line_number;
1402 if (h_status_type = 'ACTIVE') then
1403 l_new_status := h_status;
1404 l_new_status_m := h_status_m;
1405 else
1406 l_new_status := v_active;
1407 l_new_status_m := v_active_m ;
1408 end if;
1409
1410 l_cle_rec.id := p_active_line_rec.id ;
1411 l_cle_rec.object_version_number := p_active_line_rec.object_version_number ;
1412 l_cle_rec.sts_code := l_new_status;
1413 l_cle_rec.dnz_chr_id := p_active_line_rec.dnz_chr_id;
1414 --
1415 --
1416 -- Assign values if and oly if the change in line status is not resulting
1417 -- from the change in header status
1418 --
1419 l_cle_rec.call_action_asmblr := 'N';
1420 --
1421 IF h_new_status is null and l_new_status is not null then
1422 l_cle_rec.old_sts_code := p_active_line_rec.sts_code;
1423 l_cle_rec.old_ste_code := p_active_line_rec.ste_code;
1424 l_cle_rec.new_sts_code := l_new_status;
1425 l_cle_rec.new_ste_code := 'ACTIVE';
1426 l_cle_rec.call_action_asmblr := 'Y';
1427 END IF;
1428
1429 --
1430 -- lock added not to depend on update implementation
1431 --
1432
1433 okc_contract_pub.lock_contract_line(
1434 p_api_version => 1.0,
1435 p_init_msg_list => p_init_msg_list,
1436 x_return_status => l_return_status,
1437 x_msg_count => x_msg_count,
1438 x_msg_data => x_msg_data,
1439 p_clev_rec => l_cle_rec);
1440
1441
1442 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1443 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1444 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1445 raise OKC_API.G_EXCEPTION_ERROR;
1446 END IF;
1447 --
1448
1449 update_contract_line (
1450 p_api_version => 1.0,
1451 p_init_msg_list => p_init_msg_list,
1452 p_update_minor_version => p_update_minor_version,
1453 p_contract_number => p_active_line_rec.contract_number,
1454 p_contract_number_modifier => p_active_line_rec.contract_number_modifier,
1455 x_return_status => l_return_status,
1456 x_msg_count => x_msg_count,
1457 x_msg_data => x_msg_data,
1458 p_clev_rec => l_cle_rec,
1459 x_clev_rec => x_cle_rec);
1460
1461
1462 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1463 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1464 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1465 raise OKC_API.G_EXCEPTION_ERROR;
1466 END IF;
1467 --
1468 if p_kid is NULL then
1469 c := c+1;
1470
1471 line_message(p_knum_and_mod =>l_knum_and_mod,
1472 p_line_number=>l_line_number,
1473 p_status =>l_new_status,
1474 p_old_status =>p_active_line_rec.meaning,
1475 p_type =>'S');
1476 end if;
1477
1478
1479 EXCEPTION
1480 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1481 line_message(p_knum_and_mod =>l_knum_and_mod,
1482 p_line_number=>l_line_number,
1483 p_status =>l_new_status,
1484 p_old_status =>p_active_line_rec.meaning,
1485 p_msg_data => x_msg_data,
1486 p_type =>'U');
1487 p_line_errors := p_line_errors +1 ;
1488 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1489 rollback to H_STATUS;
1490 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1491 line_message(p_knum_and_mod =>l_knum_and_mod,
1492 p_line_number=>l_line_number,
1493 p_status =>l_new_status,
1494 p_old_status =>p_active_line_rec.meaning,
1495 p_msg_data => x_msg_data,
1496 p_type =>'E');
1497 p_line_errors := p_line_errors +1 ;
1498 x_return_status := OKC_API.G_RET_STS_ERROR;
1499 rollback to H_STATUS;
1500 WHEN OTHERS then
1501 line_message(p_knum_and_mod =>l_knum_and_mod,
1502 p_line_number=>l_line_number,
1503 p_status =>l_new_status,
1504 p_old_status =>p_active_line_rec.meaning,
1505 p_msg_data => x_msg_data,
1506 p_type =>'U');
1507 p_line_errors := p_line_errors +1 ;
1508 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1509 rollback to H_STATUS;
1510 END line_active;
1511
1512 --bug 5930684
1513 PROCEDURE line_signed(p_signed_line_rec IN line_rec_type) IS
1514 BEGIN
1515
1516
1517 if ((C >= T) and p_kid IS NULL) then
1518 commit;
1519 c := 0;
1520 end if;
1521 savepoint H_STATUS;
1522 p_line_count:= p_line_count + 1;
1523 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1524 l_knum_and_mod := p_signed_line_rec.contract_number||' '||p_signed_line_rec.contract_number_modifier;
1525 l_new_status := null;
1526 l_new_status_m := null;
1527
1528 l_line_number := p_signed_line_rec.line_number;
1529 if (h_status_type = 'SIGNED') then
1530 l_new_status := h_status;
1531 l_new_status_m := h_status_m;
1532 else
1533 l_new_status := v_signed;
1534 l_new_status_m := v_signed_m ;
1535 end if;
1536
1537 l_cle_rec.id := p_signed_line_rec.id ;
1538 l_cle_rec.object_version_number := p_signed_line_rec.object_version_number ;
1539 l_cle_rec.sts_code := l_new_status;
1540 l_cle_rec.dnz_chr_id := p_signed_line_rec.dnz_chr_id;
1541 --
1542 --
1543 -- Assign values if and oly if the change in line status is not resulting
1544 -- from the change in header status
1545 --
1546 l_cle_rec.call_action_asmblr := 'N';
1547 --
1548 IF h_new_status is null and l_new_status is not null then
1549 l_cle_rec.old_sts_code := p_signed_line_rec.sts_code;
1550 l_cle_rec.old_ste_code := p_signed_line_rec.ste_code;
1551 l_cle_rec.new_sts_code := l_new_status;
1552 l_cle_rec.new_ste_code := 'SIGNED';
1553 l_cle_rec.call_action_asmblr := 'Y';
1554 END IF;
1555
1556 --
1557 -- lock added not to depend on update implementation
1558 --
1559
1560 okc_contract_pub.lock_contract_line(
1561 p_api_version => 1.0,
1562 p_init_msg_list => p_init_msg_list,
1563 x_return_status => l_return_status,
1564 x_msg_count => x_msg_count,
1565 x_msg_data => x_msg_data,
1566 p_clev_rec => l_cle_rec);
1567
1568
1569 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1570 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1571 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1572 raise OKC_API.G_EXCEPTION_ERROR;
1573 END IF;
1574 --
1575
1576 update_contract_line (
1577 p_api_version => 1.0,
1578 p_init_msg_list => p_init_msg_list,
1579 p_update_minor_version => p_update_minor_version,
1580 p_contract_number => p_signed_line_rec.contract_number,
1581 p_contract_number_modifier => p_signed_line_rec.contract_number_modifier,
1582 x_return_status => l_return_status,
1583 x_msg_count => x_msg_count,
1584 x_msg_data => x_msg_data,
1585 p_clev_rec => l_cle_rec,
1586 x_clev_rec => x_cle_rec);
1587
1588
1589 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1590 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1591 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1592 raise OKC_API.G_EXCEPTION_ERROR;
1593 END IF;
1594 --
1595 if p_kid is NULL then
1596 c := c+1;
1597
1598 line_message(p_knum_and_mod =>l_knum_and_mod,
1599 p_line_number=>l_line_number,
1600 p_status =>l_new_status,
1601 p_old_status =>p_signed_line_rec.meaning,
1602 p_type =>'S');
1603 end if;
1604
1605
1606 EXCEPTION
1607 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1608 line_message(p_knum_and_mod =>l_knum_and_mod,
1609 p_line_number=>l_line_number,
1610 p_status =>l_new_status,
1611 p_old_status =>p_signed_line_rec.meaning,
1612 p_msg_data => x_msg_data,
1613 p_type =>'U');
1614 p_line_errors := p_line_errors +1 ;
1615 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1616 rollback to H_STATUS;
1617 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1618 line_message(p_knum_and_mod =>l_knum_and_mod,
1619 p_line_number=>l_line_number,
1620 p_status =>l_new_status,
1621 p_old_status =>p_signed_line_rec.meaning,
1622 p_msg_data => x_msg_data,
1623 p_type =>'E');
1624 p_line_errors := p_line_errors +1 ;
1625 x_return_status := OKC_API.G_RET_STS_ERROR;
1626 rollback to H_STATUS;
1627 WHEN OTHERS then
1628 line_message(p_knum_and_mod =>l_knum_and_mod,
1629 p_line_number=>l_line_number,
1630 p_status =>l_new_status,
1631 p_old_status =>p_signed_line_rec.meaning,
1632 p_msg_data => x_msg_data,
1633 p_type =>'U');
1634 p_line_errors := p_line_errors +1 ;
1635 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1636 rollback to H_STATUS;
1637 END line_signed;
1638 -- end of bug 5930684
1639
1640 -- New procedure Added to Delete the --
1641 -- table type after each iteration --
1642 Procedure delete_table_type IS
1643 BEGIN
1644 l_Contract_number_tbl.delete;
1645 l_Contract_number_modifier_tbl.delete;
1646 l_Id_tbl.delete;
1647 l_Object_version_number_tbl.delete;
1648 l_sts_code_tbl.delete;
1649 l_date_terminated_tbl.delete;
1650 l_start_date_tbl.delete;
1651 l_end_date_tbl.delete;
1652 l_line_number_tbl.delete;
1653 l_price_negotiated_tbl.delete;
1654 l_dnz_chr_id_tbl.delete;
1655 l_termination_reason_tbl.delete;
1656 l_code_tbl.delete;
1657 l_ste_code_tbl.delete;
1658 l_meaning_tbl.delete;
1659 END delete_table_type;
1660
1661
1662 BEGIN
1663
1664
1665 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1666 --added for bug fix 5285247
1667 Savepoint H_STATUS;
1668 l_new_status := null;
1669 l_new_status_m := null;
1670 -- terminate line
1671
1672 IF (h_status = 'TERMINATED') OR (h_status IS NULL) THEN
1673 IF p_kid IS NULL THEN
1674
1675 /* Commented for Bug #3967643.
1676 FOR r_terminate_line in c_terminate_line_all LOOP
1677 r_terminate_line_rec.contract_number := r_terminate_line.contract_number;
1678 r_terminate_line_rec.contract_number_modifier := r_terminate_line.contract_number_modifier;
1679 r_terminate_line_rec.id := r_terminate_line.id;
1680 r_terminate_line_rec.object_version_number := r_terminate_line.object_version_number;
1681 r_terminate_line_rec.sts_code := r_terminate_line.sts_code;
1682 r_terminate_line_rec.date_terminated := r_terminate_line.date_terminated;
1683 r_terminate_line_rec.start_date := r_terminate_line.start_date;
1684 r_terminate_line_rec.end_date := r_terminate_line.end_date;
1685 r_terminate_line_rec.line_number := r_terminate_line.line_number;
1686 r_terminate_line_rec.price_negotiated := r_terminate_line.price_negotiated;
1687 r_terminate_line_rec.dnz_chr_id := r_terminate_line.dnz_chr_id;
1688 r_terminate_line_rec.termination_reason := r_terminate_line.termination_reason;
1689 r_terminate_line_rec.code := r_terminate_line.code;
1690 r_terminate_line_rec.ste_code := r_terminate_line.ste_code;
1691 r_terminate_line_rec.meaning := r_terminate_line.meaning;
1692 line_terminate(r_terminate_line_rec);
1693 END LOOP; -- r_line_cursor_AHS
1694 commit;
1695 c:= 0; */
1696 -- Added for BUG #3967643
1697 IF p_from_k IS NOT NULL THEN
1698
1699 -- When only the From-to Contract number range is provided
1700 x_num :=0;
1701 open c_termnt_line_all_cntr;
1702 LOOP -- I
1703 FETCH c_termnt_line_all_cntr BULK COLLECT INTO
1704 l_Contract_number_tbl,
1705 l_Contract_number_modifier_tbl,
1706 l_Id_tbl,
1707 l_Object_version_number_tbl,
1708 l_sts_code_tbl,
1709 l_date_terminated_tbl,
1710 l_start_date_tbl,
1711 l_end_date_tbl,
1712 l_line_number_tbl,
1713 l_price_negotiated_tbl,
1714 l_dnz_chr_id_tbl,
1715 l_termination_reason_tbl,
1716 l_code_tbl,
1717 l_ste_code_tbl,
1718 l_meaning_tbl
1719 LIMIT 1000;
1720 IF (l_Id_tbl.COUNT < 1) THEN
1721 EXIT;
1722 END IF;
1723 IF (l_Id_tbl.COUNT > 0) THEN
1724
1725 i := l_Id_tbl.FIRST;
1726 LOOP --II
1727 r_terminate_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
1728 r_terminate_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
1729 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1730 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1731 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1732 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1733 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1734 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1735 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1736 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1737 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1738 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1739 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1740 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1741 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1742 -- line_terminate(r_terminate_line_rec);
1743 x_num :=x_num+1;
1744 EXIT WHEN (i = l_Id_tbl.LAST);
1745 i := l_Id_tbl.NEXT(i);
1746 END LOOP; --II
1747
1748 END IF;
1749 Exit when c_termnt_line_all_cntr%NOTFOUND;
1750 END LOOP; --I
1751 IF(r_terminate_line_tbl.COUNT > 0) Then
1752 i := r_terminate_line_tbl.FIRST;
1753 LOOP
1754 line_terminate(r_terminate_line_tbl(i));
1755 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1756 i := r_terminate_line_tbl.NEXT(i);
1757 END LOOP;
1758 END IF;
1759 commit;
1760 c:=0;
1761 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
1762 -- When only Category is provided
1763 x_num :=0;
1764 open c_termnt_line_all_category;
1765 LOOP -- I
1766 FETCH c_termnt_line_all_category BULK COLLECT INTO
1767 l_Contract_number_tbl,
1768 l_Contract_number_modifier_tbl,
1769 l_Id_tbl,
1770 l_Object_version_number_tbl,
1771 l_sts_code_tbl,
1772 l_date_terminated_tbl,
1773 l_start_date_tbl,
1774 l_end_date_tbl,
1775 l_line_number_tbl,
1776 l_price_negotiated_tbl,
1777 l_dnz_chr_id_tbl,
1778 l_termination_reason_tbl,
1779 l_code_tbl,
1780 l_ste_code_tbl,
1781 l_meaning_tbl
1782 LIMIT 1000;
1783 IF (l_Id_tbl.COUNT < 1) THEN
1784 EXIT;
1785 END IF;
1786 IF (l_Id_tbl.COUNT > 0) THEN
1787 i := l_Id_tbl.FIRST;
1788 LOOP -- II
1789 r_terminate_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
1790 r_terminate_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
1791 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1792 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1793 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1794 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1795 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1796 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1797 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1798 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1799 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1800 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1801 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1802 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1803 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1804 -- line_terminate(r_terminate_line_rec);
1805 x_num :=x_num+1;
1806 EXIT WHEN (i = l_Id_tbl.LAST);
1807 i := l_Id_tbl.NEXT(i);
1808 END LOOP; --II
1809 END IF;
1810 Exit when c_termnt_line_all_category%NOTFOUND;
1811 END LOOP; --I
1812 IF(r_terminate_line_tbl.COUNT > 0) Then
1813 i := r_terminate_line_tbl.FIRST;
1814 LOOP
1815 line_terminate(r_terminate_line_tbl(i));
1816 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1817 i := r_terminate_line_tbl.NEXT(i);
1818 END LOOP;
1819 END IF;
1820 commit;
1821 c:=0;
1822 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
1823 -- When no parameters are provided
1824 x_num :=0;
1825 open c_terminate_line_all;
1826 LOOP -- I
1827 FETCH c_terminate_line_all BULK COLLECT INTO
1828 l_Contract_number_tbl,
1829 l_Contract_number_modifier_tbl,
1830 l_Id_tbl,
1831 l_Object_version_number_tbl,
1832 l_sts_code_tbl,
1833 l_date_terminated_tbl,
1834 l_start_date_tbl,
1835 l_end_date_tbl,
1836 l_line_number_tbl,
1837 l_price_negotiated_tbl,
1838 l_dnz_chr_id_tbl,
1839 l_termination_reason_tbl,
1840 l_code_tbl,
1841 l_ste_code_tbl,
1842 l_meaning_tbl
1843 LIMIT 1000;
1844 IF (l_Id_tbl.COUNT < 1) THEN
1845 EXIT;
1846 END IF;
1847 IF (l_Id_tbl.COUNT > 0) THEN
1848 i := l_Id_tbl.FIRST;
1849 LOOP --II
1850 r_terminate_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
1851 r_terminate_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
1852 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1853 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1854 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1855 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1856 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1857 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1858 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1859 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1860 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1861 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1862 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1863 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1864 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1865 -- line_terminate(r_terminate_line_rec);
1866 x_num :=x_num+1;
1867 EXIT WHEN (i = l_Id_tbl.LAST);
1868 i := l_Id_tbl.NEXT(i);
1869 END LOOP; --II
1870 END IF;
1871 Exit when c_terminate_line_all%NOTFOUND;
1872 END LOOP; --I
1873 IF(r_terminate_line_tbl.COUNT > 0) Then
1874 i := r_terminate_line_tbl.FIRST;
1875 LOOP
1876 line_terminate(r_terminate_line_tbl(i));
1877 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1878 i := r_terminate_line_tbl.NEXT(i);
1879 END LOOP;
1880 END IF;
1881 commit;
1882 c:=0;
1883 END IF;-- Added for BUG #3967643
1884 ELSE -- p_kid IS NULL
1885 x_num :=0;
1886 open c_terminate_line_k;
1887 LOOP -- I
1888 FETCH c_terminate_line_k BULK COLLECT INTO
1889 l_Id_tbl,
1890 l_Object_version_number_tbl,
1891 l_sts_code_tbl,
1892 l_date_terminated_tbl,
1893 l_start_date_tbl,
1894 l_end_date_tbl,
1895 l_line_number_tbl,
1896 l_price_negotiated_tbl,
1897 l_dnz_chr_id_tbl,
1898 l_termination_reason_tbl,
1899 l_code_tbl,
1900 l_ste_code_tbl,
1901 l_meaning_tbl
1902 LIMIT 1000;
1903 IF (l_Id_tbl.COUNT < 1) THEN
1904 EXIT;
1905 END IF;
1906 IF (l_Id_tbl.COUNT > 0) THEN
1907 i := l_Id_tbl.FIRST;
1908 LOOP -- II
1909 r_terminate_line_tbl(x_num).contract_number := p_k_num;
1910 r_terminate_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
1911 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1912 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1913 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1914 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1915 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1916 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1917 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1918 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1919 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1920 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1921 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1922 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1923 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1924 x_num :=x_num+1;
1925 EXIT WHEN (i = l_Id_tbl.LAST);
1926 i := l_Id_tbl.NEXT(i);
1927 END LOOP; --II
1928 END IF;
1929 Exit when c_terminate_line_k%NOTFOUND;
1930 END LOOP; --I
1931 IF(r_terminate_line_tbl.COUNT > 0) Then
1932 i := r_terminate_line_tbl.FIRST;
1933 LOOP
1934 line_terminate(r_terminate_line_tbl(i));
1935 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1936 i := r_terminate_line_tbl.NEXT(i);
1937 END LOOP;
1938 END IF;
1939 END IF;
1940 END IF; -- End if for termination condition.
1941
1942 l_new_status := null;
1943 l_new_status_m := null;
1944 l_knum_and_mod := null;
1945 l_line_number := null;
1946
1947 delete_table_type;
1948 ---------------------------------------------------------------------------------
1949 --Added for bug 5402421
1950 Savepoint H_STATUS;
1951
1952 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1953 -- From ACTIVE/SIGNED to Expired
1954 IF (h_status = 'EXPIRED') OR (h_status IS NULL) THEN
1955 IF p_kid IS NULL THEN
1956 /* Commented for Bug #3967643.
1957 FOR r_expired_line in c_expired_line_all LOOP
1958 r_expired_line_rec.contract_number := r_expired_line.contract_number;
1959 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
1960 r_expired_line_rec.id := r_expired_line.id;
1961 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
1962 r_expired_line_rec.sts_code := r_expired_line.sts_code;
1963 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
1964 r_expired_line_rec.start_date := r_expired_line.start_date;
1965 r_expired_line_rec.end_date := r_expired_line.end_date;
1966 r_expired_line_rec.line_number := r_expired_line.line_number;
1967 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
1968 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
1969 r_expired_line_rec.termination_reason := NULL;
1970 r_expired_line_rec.code := r_expired_line.code;
1971 r_expired_line_rec.ste_code := r_expired_line.ste_code;
1972 r_expired_line_rec.meaning := r_expired_line.meaning;
1973 line_expire(r_expired_line_rec);
1974 END LOOP; -- r_line_cursor_AHS
1975 commit;
1976 c:= 0;*/
1977 -- Added for BUG #3967643
1978 IF p_from_k IS NOT NULL THEN
1979
1980 -- When only the From-to Contract number range is provided
1981 /* FOR r_expired_line in c_expr_line_all_cntr LOOP
1982
1983
1984 r_expired_line_rec.contract_number := r_expired_line.contract_number;
1985 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
1986 r_expired_line_rec.id := r_expired_line.id;
1987 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
1988 r_expired_line_rec.sts_code := r_expired_line.sts_code;
1989 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
1990 r_expired_line_rec.start_date := r_expired_line.start_date;
1991 r_expired_line_rec.end_date := r_expired_line.end_date;
1992 r_expired_line_rec.line_number := r_expired_line.line_number;
1993 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
1994 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
1995 r_expired_line_rec.termination_reason := NULL;
1996 r_expired_line_rec.code := r_expired_line.code;
1997 r_expired_line_rec.ste_code := r_expired_line.ste_code;
1998 r_expired_line_rec.meaning := r_expired_line.meaning;
1999
2000 line_expire(r_expired_line_rec);
2001 END LOOP; -- r_line_cursor_AHS
2002 commit;
2003 c:= 0;*/
2004 x_num :=0;
2005 open c_expr_line_all_cntr;
2006 LOOP -- I
2007 FETCH c_expr_line_all_cntr BULK COLLECT INTO
2008
2009 l_Contract_number_tbl,
2010 l_Contract_number_modifier_tbl,
2011 l_Id_tbl,
2012 l_Object_version_number_tbl,
2013 l_sts_code_tbl,
2014 l_date_terminated_tbl,
2015 l_start_date_tbl,
2016 l_end_date_tbl,
2017 l_line_number_tbl,
2018 l_price_negotiated_tbl,
2019 l_dnz_chr_id_tbl,
2020 l_code_tbl,
2021 l_ste_code_tbl,
2022 l_meaning_tbl
2023 LIMIT 1000;
2024 IF (l_Id_tbl.COUNT < 1) THEN
2025 EXIT;
2026 END IF;
2027 IF (l_Id_tbl.COUNT > 0) THEN
2028 i := l_Id_tbl.FIRST;
2029 LOOP -- II
2030 r_expired_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2031 r_expired_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2032 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2033 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2034 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2035 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2036 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2037 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2038 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2039 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2040 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2041 r_expired_line_tbl(x_num).termination_reason :=NULL;
2042 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2043 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2044 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2045 x_num :=x_num+1;
2046 EXIT WHEN (i = l_Id_tbl.LAST);
2047 i := l_Id_tbl.NEXT(i);
2048 END LOOP; --II
2049 END IF;
2050 Exit when c_expr_line_all_cntr%NOTFOUND;
2051 END LOOP; --I
2052 IF(r_expired_line_tbl.COUNT > 0) Then
2053 i := r_expired_line_tbl.FIRST;
2054 LOOP
2055 line_expire(r_expired_line_tbl(i));
2056 EXIT WHEN (i = r_expired_line_tbl.LAST);
2057 i := r_expired_line_tbl.NEXT(i);
2058 END LOOP;
2059 END IF;
2060 commit;
2061 c:=0;
2062 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
2063
2064 -- When only Category is provided
2065 /*FOR r_expired_line in c_expr_line_all_category LOOP
2066
2067 r_expired_line_rec.contract_number := r_expired_line.contract_number;
2068 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
2069 r_expired_line_rec.id := r_expired_line.id;
2070 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2071 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2072 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2073 r_expired_line_rec.start_date := r_expired_line.start_date;
2074 r_expired_line_rec.end_date := r_expired_line.end_date;
2075 r_expired_line_rec.line_number := r_expired_line.line_number;
2076 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2077 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2078 r_expired_line_rec.termination_reason := NULL;
2079 r_expired_line_rec.code := r_expired_line.code;
2080 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2081 r_expired_line_rec.meaning := r_expired_line.meaning;
2082
2083 line_expire(r_expired_line_rec);
2084
2085 END LOOP; -- r_line_cursor_AHS */
2086 x_num :=0;
2087 open c_expr_line_all_category;
2088 LOOP --I
2089 FETCH c_expr_line_all_category BULK COLLECT INTO
2090 l_Contract_number_tbl,
2091 l_Contract_number_modifier_tbl,
2092 l_Id_tbl,
2093 l_Object_version_number_tbl,
2094 l_sts_code_tbl,
2095 l_date_terminated_tbl,
2096 l_start_date_tbl,
2097 l_end_date_tbl,
2098 l_line_number_tbl,
2099 l_price_negotiated_tbl,
2100 l_dnz_chr_id_tbl,
2101 l_code_tbl,
2102 l_ste_code_tbl,
2103 l_meaning_tbl
2104 LIMIT 1000;
2105 IF (l_Id_tbl.COUNT < 1) THEN
2106 EXIT;
2107 END IF;
2108 IF (l_Id_tbl.COUNT > 0) THEN
2109 i := l_Id_tbl.FIRST;
2110 LOOP --II
2111 r_expired_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2112 r_expired_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2113 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2114 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2115 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2116 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2117 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2118 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2119 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2120 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2121 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2122 r_expired_line_tbl(x_num).termination_reason := NULL;
2123 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2124 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2125 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2126 x_num :=x_num+1;
2127 EXIT WHEN (i = l_Id_tbl.LAST);
2128 i := l_Id_tbl.NEXT(i);
2129 END LOOP; --II
2130 END IF;
2131 Exit when c_expr_line_all_category%NOTFOUND;
2132 END LOOP; --I
2133 IF(r_expired_line_tbl.COUNT > 0) Then
2134 i := r_expired_line_tbl.FIRST;
2135 LOOP
2136 line_expire(r_expired_line_tbl(i));
2137 EXIT WHEN (i = r_expired_line_tbl.LAST);
2138 i := r_expired_line_tbl.NEXT(i);
2139 END LOOP;
2140 END IF;
2141 commit;
2142 c:= 0;
2143 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
2144
2145 -- When no parameters are provided
2146 /* FOR r_expired_line in c_expired_line_all LOOP
2147
2148 r_expired_line_rec.contract_number := r_expired_line.contract_number;
2149 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
2150 r_expired_line_rec.id := r_expired_line.id;
2151 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2152 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2153 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2154 r_expired_line_rec.start_date := r_expired_line.start_date;
2155 r_expired_line_rec.end_date := r_expired_line.end_date;
2156 r_expired_line_rec.line_number := r_expired_line.line_number;
2157 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2158 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2159 r_expired_line_rec.termination_reason := NULL;
2160 r_expired_line_rec.code := r_expired_line.code;
2161 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2162 r_expired_line_rec.meaning := r_expired_line.meaning;
2163
2164
2165 line_expire(r_expired_line_rec);
2166 END LOOP; -- r_line_cursor_AHS
2167 commit;
2168 c:= 0; */
2169 x_num :=0;
2170 open c_expired_line_all;
2171 LOOP --I
2172 FETCH c_expired_line_all BULK COLLECT INTO
2173 l_Contract_number_tbl,
2174 l_Contract_number_modifier_tbl,
2175 l_Id_tbl,
2176 l_Object_version_number_tbl,
2177 l_sts_code_tbl,
2178 l_date_terminated_tbl,
2179 l_start_date_tbl,
2180 l_end_date_tbl,
2181 l_line_number_tbl,
2182 l_price_negotiated_tbl,
2183 l_dnz_chr_id_tbl,
2184 l_code_tbl,
2185 l_ste_code_tbl,
2186 l_meaning_tbl
2187 LIMIT 1000;
2188 IF (l_Id_tbl.COUNT < 1) THEN
2189 EXIT;
2190 END IF;
2191 IF (l_Id_tbl.COUNT > 0) THEN
2192 i := l_Id_tbl.FIRST;
2193 LOOP --II
2194 r_expired_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2195 r_expired_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2196 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2197 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2198 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2199 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2200 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2201 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2202 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2203 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2204 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2205 r_expired_line_tbl(x_num).termination_reason :=NULL;
2206 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2207 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2208 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2209 x_num :=x_num+1;
2210 EXIT WHEN (i = l_Id_tbl.LAST);
2211 i := l_Id_tbl.NEXT(i);
2212 END LOOP; --II
2213 END IF;
2214 Exit when c_expired_line_all%NOTFOUND;
2215 END LOOP; --I
2216 IF(r_expired_line_tbl.COUNT > 0) Then
2217 i := r_expired_line_tbl.FIRST;
2218 LOOP
2219 line_expire(r_expired_line_tbl(i));
2220 EXIT WHEN (i = r_expired_line_tbl.LAST);
2221 i := r_expired_line_tbl.NEXT(i);
2222 END LOOP;
2223 END IF;
2224 commit;
2225 c:= 0;
2226
2227 END IF;-- Added for BUG #3967643
2228 ELSE -- p_kid IS NULL
2229
2230 /* FOR r_expired_line in c_expired_line_k LOOP
2231
2232
2233 r_expired_line_rec.contract_number := p_k_num;
2234 r_expired_line_rec.contract_number_modifier := p_k_num_mod;
2235 r_expired_line_rec.id := r_expired_line.id;
2236 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2237 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2238 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2239 r_expired_line_rec.start_date := r_expired_line.start_date;
2240 r_expired_line_rec.end_date := r_expired_line.end_date;
2241 r_expired_line_rec.line_number := r_expired_line.line_number;
2242 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2243 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2244 r_expired_line_rec.termination_reason := NULL;
2245 r_expired_line_rec.code := r_expired_line.code;
2246 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2247 r_expired_line_rec.meaning := r_expired_line.meaning;
2248
2249 line_expire(r_expired_line_rec);
2250 END LOOP; -- r_line_cursor_AHS */
2251 x_num :=0;
2252 open c_expired_line_k;
2253 LOOP -- I
2254 FETCH c_expired_line_k BULK COLLECT INTO
2255 l_Id_tbl,
2256 l_Object_version_number_tbl,
2257 l_sts_code_tbl,
2258 l_date_terminated_tbl,
2259 l_start_date_tbl,
2260 l_end_date_tbl,
2261 l_line_number_tbl,
2262 l_price_negotiated_tbl,
2263 l_dnz_chr_id_tbl,
2264 l_code_tbl,
2265 l_ste_code_tbl,
2266 l_meaning_tbl
2267 LIMIT 1000;
2268 IF (l_Id_tbl.COUNT < 1) THEN
2269 EXIT;
2270 END IF;
2271 IF (l_Id_tbl.COUNT > 0) THEN
2272 i := l_Id_tbl.FIRST;
2273 LOOP --II
2274 r_expired_line_tbl(x_num).contract_number := p_k_num;
2275 r_expired_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
2276 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2277 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2278 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2279 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2280 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2281 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2282 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2283 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2284 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2285 r_expired_line_tbl(x_num).termination_reason := NULL;
2286 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2287 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2288 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2289 x_num :=x_num+1;
2290 EXIT WHEN (i = l_Id_tbl.LAST);
2291 i := l_Id_tbl.NEXT(i);
2292 END LOOP; --II
2293 END IF;
2294 Exit when c_expired_line_k%NOTFOUND;
2295 END LOOP; --I
2296 IF(r_expired_line_tbl.COUNT > 0) Then
2297 i := r_expired_line_tbl.FIRST;
2298 LOOP
2299 line_expire(r_expired_line_tbl(i));
2300 EXIT WHEN (i = r_expired_line_tbl.LAST);
2301 i := r_expired_line_tbl.NEXT(i);
2302 END LOOP;
2303 END IF;
2304 END IF;
2305 END IF; --
2306 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2307 l_new_status := null;
2308 l_new_status_m := null;
2309 l_knum_and_mod := null;
2310 l_line_number := null;
2311
2312 delete_table_type;
2313
2314 --added for bug fix 5402421
2315 Savepoint H_STATUS;
2316
2317 -- From Signed to Active
2318 IF h_status = 'ACTIVE' OR h_status IS NULL THEN
2319 IF p_kid IS NULL THEN
2320 /* Commented for Bug #3967643.
2321 FOR r_active_line in c_active_line_all LOOP
2322 r_active_line_rec.contract_number := r_active_line.contract_number;
2323 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2324 r_active_line_rec.id := r_active_line.id;
2325 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2326 r_active_line_rec.sts_code := r_active_line.sts_code;
2327 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2328 r_active_line_rec.start_date := r_active_line.start_date;
2329 r_active_line_rec.end_date := r_active_line.end_date;
2330 r_active_line_rec.line_number := r_active_line.line_number;
2331 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2332 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2333 r_active_line_rec.termination_reason := NULL;
2334 r_active_line_rec.code := r_active_line.code;
2335 r_active_line_rec.ste_code := r_active_line.ste_code;
2336 r_active_line_rec.meaning := r_active_line.meaning;
2337 line_active(r_active_line_rec);
2338 END LOOP; -- r_line_cursor_AHS
2339 commit;
2340 c:= 0;*/
2341 -- Added for BUG #3967643
2342 IF p_from_k IS NOT NULL THEN
2343 -- When only the From-to Contract number range is provided
2344 /* FOR r_active_line in c_actv_line_all_cntr LOOP
2345 r_active_line_rec.contract_number := r_active_line.contract_number;
2346 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2347 r_active_line_rec.id := r_active_line.id;
2348 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2349 r_active_line_rec.sts_code := r_active_line.sts_code;
2350 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2351 r_active_line_rec.start_date := r_active_line.start_date;
2352 r_active_line_rec.end_date := r_active_line.end_date;
2353 r_active_line_rec.line_number := r_active_line.line_number;
2354 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2355 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2356 r_active_line_rec.termination_reason := NULL;
2357 r_active_line_rec.code := r_active_line.code;
2358 r_active_line_rec.ste_code := r_active_line.ste_code;
2359 r_active_line_rec.meaning := r_active_line.meaning;
2360
2361
2362 line_active(r_active_line_rec);
2363 END LOOP; -- r_line_cursor_AHS
2364 commit;
2365 c:= 0; */
2366 x_num :=0;
2367 open c_actv_line_all_cntr;
2368 LOOP --I
2369 FETCH c_actv_line_all_cntr BULK COLLECT INTO
2370 l_Contract_number_tbl,
2371 l_Contract_number_modifier_tbl,
2372 l_Id_tbl,
2373 l_Object_version_number_tbl,
2374 l_sts_code_tbl,
2375 l_date_terminated_tbl,
2376 l_start_date_tbl,
2377 l_end_date_tbl,
2378 l_line_number_tbl,
2379 l_price_negotiated_tbl,
2380 l_dnz_chr_id_tbl,
2381 l_code_tbl,
2382 l_ste_code_tbl,
2383 l_meaning_tbl
2384 LIMIT 1000;
2385 IF (l_Id_tbl.COUNT < 1) THEN
2386 EXIT;
2387 END IF;
2388 IF (l_Id_tbl.COUNT > 0) THEN
2389 i := l_Id_tbl.FIRST;
2390 LOOP --II
2391 r_active_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2392 r_active_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2393 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2394 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2395 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2396 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2397 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2398 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2399 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2400 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2401 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2402 r_active_line_tbl(x_num).termination_reason := NULL;
2403 r_active_line_tbl(x_num).code := l_code_tbl(i);
2404 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2405 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2406 x_num :=x_num+1;
2407 EXIT WHEN (i = l_Id_tbl.LAST);
2408 i := l_Id_tbl.NEXT(i);
2409 END LOOP; --II
2410 END IF;
2411 Exit when c_actv_line_all_cntr%NOTFOUND;
2412 END LOOP; --I
2413 IF(r_active_line_tbl.COUNT > 0) Then
2414 i := r_active_line_tbl.FIRST;
2415 LOOP
2416 line_active(r_active_line_tbl(i));
2417 EXIT WHEN (i = r_active_line_tbl.LAST);
2418 i := r_active_line_tbl.NEXT(i);
2419 END LOOP;
2420 END IF;
2421 commit;
2422 c:= 0;
2423
2424 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
2425 -- When only Category is provided
2426 /* FOR r_active_line in c_actv_line_all_category LOOP
2427
2428
2429 r_active_line_rec.contract_number := r_active_line.contract_number;
2430 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2431 r_active_line_rec.id := r_active_line.id;
2432 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2433 r_active_line_rec.sts_code := r_active_line.sts_code;
2434 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2435 r_active_line_rec.start_date := r_active_line.start_date;
2436 r_active_line_rec.end_date := r_active_line.end_date;
2437 r_active_line_rec.line_number := r_active_line.line_number;
2438 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2439 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2440 r_active_line_rec.termination_reason := NULL;
2441 r_active_line_rec.code := r_active_line.code;
2442 r_active_line_rec.ste_code := r_active_line.ste_code;
2443 r_active_line_rec.meaning := r_active_line.meaning;
2444
2445 line_active(r_active_line_rec);
2446 END LOOP; -- r_line_cursor_AHS
2447 commit;
2448 c:= 0;*/
2449 x_num :=0;
2450 open c_actv_line_all_category;
2451 LOOP --I
2452 FETCH c_actv_line_all_category BULK COLLECT INTO
2453 l_Contract_number_tbl,
2454 l_Contract_number_modifier_tbl,
2455 l_Id_tbl,
2456 l_Object_version_number_tbl,
2457 l_sts_code_tbl,
2458 l_date_terminated_tbl,
2459 l_start_date_tbl,
2460 l_end_date_tbl,
2461 l_line_number_tbl,
2462 l_price_negotiated_tbl,
2463 l_dnz_chr_id_tbl,
2464 l_code_tbl,
2465 l_ste_code_tbl,
2466 l_meaning_tbl
2467 LIMIT 1000;
2468 IF (l_Id_tbl.COUNT < 1) THEN
2469 EXIT;
2470 END IF;
2471 IF (l_Id_tbl.COUNT > 0) THEN
2472 i := l_Id_tbl.FIRST;
2473 LOOP --II
2474 r_active_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2475 r_active_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2476 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2477 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2478 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2479 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2480 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2481 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2482 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2483 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2484 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2485 r_active_line_tbl(x_num).termination_reason := NULL;
2486 r_active_line_tbl(x_num).code := l_code_tbl(i);
2487 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2488 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2489 x_num :=x_num+1;
2490 EXIT WHEN (i = l_Id_tbl.LAST);
2491 i := l_Id_tbl.NEXT(i);
2492 END LOOP; --II
2493 END IF;
2494 Exit when c_actv_line_all_category%NOTFOUND;
2495 END LOOP; --I
2496 IF(r_active_line_tbl.COUNT > 0) Then
2497 i := r_active_line_tbl.FIRST;
2498 LOOP
2499 line_active(r_active_line_tbl(i));
2500 EXIT WHEN (i = r_active_line_tbl.LAST);
2501 i := r_active_line_tbl.NEXT(i);
2502 END LOOP;
2503 END IF;
2504 commit;
2505 c:= 0;
2506 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
2507 -- When no parameters are provided
2508 /* FOR r_active_line in c_active_line_all LOOP
2509
2510
2511 r_active_line_rec.contract_number := r_active_line.contract_number;
2512 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2513 r_active_line_rec.id := r_active_line.id;
2514 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2515 r_active_line_rec.sts_code := r_active_line.sts_code;
2516 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2517 r_active_line_rec.start_date := r_active_line.start_date;
2518 r_active_line_rec.end_date := r_active_line.end_date;
2519 r_active_line_rec.line_number := r_active_line.line_number;
2520 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2521 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2522 r_active_line_rec.termination_reason := NULL;
2523 r_active_line_rec.code := r_active_line.code;
2524 r_active_line_rec.ste_code := r_active_line.ste_code;
2525 r_active_line_rec.meaning := r_active_line.meaning;
2526
2527 line_active(r_active_line_rec);
2528 END LOOP; -- r_line_cursor_AHS */
2529 x_num :=0;
2530 open c_active_line_all;
2531 LOOP --I
2532 FETCH c_active_line_all BULK COLLECT INTO
2533 l_Contract_number_tbl,
2534 l_Contract_number_modifier_tbl,
2535 l_Id_tbl,
2536 l_Object_version_number_tbl,
2537 l_sts_code_tbl,
2538 l_date_terminated_tbl,
2539 l_start_date_tbl,
2540 l_end_date_tbl,
2541 l_line_number_tbl,
2542 l_price_negotiated_tbl,
2543 l_dnz_chr_id_tbl,
2544 l_code_tbl,
2545 l_ste_code_tbl,
2546 l_meaning_tbl
2547 LIMIT 1000;
2548 IF (l_Id_tbl.COUNT < 1) THEN
2549 EXIT;
2550 END IF;
2551 IF (l_Id_tbl.COUNT > 0) THEN
2552 i := l_Id_tbl.FIRST;
2553 LOOP --II
2554 r_active_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2555 r_active_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2556 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2557 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2558 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2559 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2560 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2561 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2562 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2563 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2564 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2565 r_active_line_tbl(x_num).termination_reason := NULL;
2566 r_active_line_tbl(x_num).code := l_code_tbl(i);
2567 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2568 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2569 x_num :=x_num+1;
2570 EXIT WHEN (i = l_Id_tbl.LAST);
2571 i := l_Id_tbl.NEXT(i);
2572 END LOOP; --II
2573 END IF;
2574 Exit when c_active_line_all%NOTFOUND;
2575 END LOOP; --I
2576 IF(r_active_line_tbl.COUNT > 0) Then
2577 i := r_active_line_tbl.FIRST;
2578 LOOP
2579 line_active(r_active_line_tbl(i));
2580 EXIT WHEN (i = r_active_line_tbl.LAST);
2581 i := r_active_line_tbl.NEXT(i);
2582 END LOOP;
2583 END IF;
2584
2585 commit;
2586 c:= 0;
2587 END IF;-- Added for BUG #3967643
2588 ELSE -- p_kid IS NULL
2589
2590 /* FOR r_active_line in c_active_line_k LOOP
2591
2592
2593 r_active_line_rec.contract_number := p_k_num;
2594 r_active_line_rec.contract_number_modifier := p_k_num_mod;
2595 r_active_line_rec.id := r_active_line.id;
2596 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2597 r_active_line_rec.sts_code := r_active_line.sts_code;
2598 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2599 r_active_line_rec.start_date := r_active_line.start_date;
2600 r_active_line_rec.end_date := r_active_line.end_date;
2601 r_active_line_rec.line_number := r_active_line.line_number;
2602 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2603 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2604 r_active_line_rec.termination_reason := NULL;
2605 r_active_line_rec.code := r_active_line.code;
2606 r_active_line_rec.ste_code := r_active_line.ste_code;
2607 r_active_line_rec.meaning := r_active_line.meaning;
2608
2609 line_active(r_active_line_rec);
2610 END LOOP; -- r_line_cursor_AHS */
2611 x_num :=0;
2612 open c_active_line_k;
2613 LOOP --I
2614 FETCH c_active_line_k BULK COLLECT INTO
2615 l_Id_tbl,
2616 l_Object_version_number_tbl,
2617 l_sts_code_tbl,
2618 l_date_terminated_tbl,
2619 l_start_date_tbl,
2620 l_end_date_tbl,
2621 l_line_number_tbl,
2622 l_price_negotiated_tbl,
2623 l_dnz_chr_id_tbl,
2624 l_code_tbl,
2625 l_ste_code_tbl,
2626 l_meaning_tbl
2627 LIMIT 1000;
2628 IF (l_Id_tbl.COUNT < 1) THEN
2629 EXIT;
2630 END IF;
2631 IF (l_Id_tbl.COUNT > 0) THEN
2632 i := l_Id_tbl.FIRST;
2633 LOOP --II
2634 r_active_line_tbl(x_num).contract_number := p_k_num;
2635 r_active_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
2636 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2637 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2638 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2639 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2640 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2641 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2642 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2643 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2644 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2645 r_active_line_tbl(x_num).termination_reason := NULL;
2646 r_active_line_tbl(x_num).code := l_code_tbl(i);
2647 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2648 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2649 x_num :=x_num+1;
2650 EXIT WHEN (i = l_Id_tbl.LAST);
2651 i := l_Id_tbl.NEXT(i);
2652 END LOOP; --II
2653 END IF;
2654 Exit when c_active_line_k%NOTFOUND;
2655 END LOOP; --I
2656 IF(r_active_line_tbl.COUNT > 0) Then
2657 i := r_active_line_tbl.FIRST;
2658 LOOP
2659 line_active(r_active_line_tbl(i));
2660 EXIT WHEN (i = r_active_line_tbl.LAST);
2661 i := r_active_line_tbl.NEXT(i);
2662 END LOOP;
2663 END IF;
2664 END IF;
2665 END IF;
2666
2667 Savepoint H_STATUS;
2668
2669 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2670 l_new_status := null;
2671 l_new_status_m := null;
2672 l_knum_and_mod := null;
2673 l_line_number := null;
2674
2675 delete_table_type;
2676 -------------------------------------------------------------------------
2677 -- BUG 5930684
2678 -- From Active To Signed
2679 IF h_status = 'SIGNED' OR h_status IS NULL THEN
2680 IF p_kid IS NULL THEN
2681 -- Added for BUG #3967643
2682 IF p_from_k IS NOT NULL THEN
2683 -- When only the From-to Contract number range is provided
2684 x_num :=0;
2685 open c_sign_line_all_cntr;
2686 LOOP --I
2687 FETCH c_sign_line_all_cntr BULK COLLECT INTO
2688 l_Contract_number_tbl,
2689 l_Contract_number_modifier_tbl,
2690 l_Id_tbl,
2691 l_Object_version_number_tbl,
2692 l_sts_code_tbl,
2693 l_date_terminated_tbl,
2694 l_start_date_tbl,
2695 l_end_date_tbl,
2696 l_line_number_tbl,
2697 l_price_negotiated_tbl,
2698 l_dnz_chr_id_tbl,
2699 l_code_tbl,
2700 l_ste_code_tbl,
2701 l_meaning_tbl
2702 LIMIT 1000;
2703 IF (l_Id_tbl.COUNT < 1) THEN
2704 EXIT;
2705 END IF;
2706 IF (l_Id_tbl.COUNT > 0) THEN
2707 i := l_Id_tbl.FIRST;
2708 LOOP --II
2709 r_signed_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2710 r_signed_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2711 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2712 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2713 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2714 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2715 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2716 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2717 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2718 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2719 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2720 r_signed_line_tbl(x_num).termination_reason := NULL;
2721 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2722 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2723 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2724 x_num :=x_num+1;
2725 EXIT WHEN (i = l_Id_tbl.LAST);
2726 i := l_Id_tbl.NEXT(i);
2727 END LOOP; --II
2728 END IF;
2729 Exit when c_sign_line_all_cntr%NOTFOUND;
2730 END LOOP; --I
2731 IF(r_signed_line_tbl.COUNT > 0) Then
2732 i := r_signed_line_tbl.FIRST;
2733 LOOP
2734 line_signed(r_signed_line_tbl(i));
2735 EXIT WHEN (i = r_signed_line_tbl.LAST);
2736 i := r_signed_line_tbl.NEXT(i);
2737 END LOOP;
2738 END IF;
2739 commit;
2740 c:= 0;
2741
2742 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
2743 -- When only Category is provided
2744
2745 x_num :=0;
2746 open c_sign_line_all_category;
2747 LOOP --I
2748 FETCH c_sign_line_all_category BULK COLLECT INTO
2749 l_Contract_number_tbl,
2750 l_Contract_number_modifier_tbl,
2751 l_Id_tbl,
2752 l_Object_version_number_tbl,
2753 l_sts_code_tbl,
2754 l_date_terminated_tbl,
2755 l_start_date_tbl,
2756 l_end_date_tbl,
2757 l_line_number_tbl,
2758 l_price_negotiated_tbl,
2759 l_dnz_chr_id_tbl,
2760 l_code_tbl,
2761 l_ste_code_tbl,
2762 l_meaning_tbl
2763 LIMIT 1000;
2764 IF (l_Id_tbl.COUNT < 1) THEN
2765 EXIT;
2766 END IF;
2767 IF (l_Id_tbl.COUNT > 0) THEN
2768 i := l_Id_tbl.FIRST;
2769 LOOP --II
2770 r_signed_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2771 r_signed_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2772 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2773 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2774 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2775 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2776 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2777 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2778 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2779 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2780 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2781 r_signed_line_tbl(x_num).termination_reason := NULL;
2782 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2783 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2784 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2785 x_num :=x_num+1;
2786 EXIT WHEN (i = l_Id_tbl.LAST);
2787 i := l_Id_tbl.NEXT(i);
2788 END LOOP; --II
2789 END IF;
2790 Exit when c_sign_line_all_category%NOTFOUND;
2791 END LOOP; --I
2792 IF(r_signed_line_tbl.COUNT > 0) Then
2793 i := r_signed_line_tbl.FIRST;
2794 LOOP
2795 line_signed(r_signed_line_tbl(i));
2796 EXIT WHEN (i = r_signed_line_tbl.LAST);
2797 i := r_signed_line_tbl.NEXT(i);
2798 END LOOP;
2799 END IF;
2800 commit;
2801 c:= 0;
2802 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
2803 -- When no parameters are provided
2804
2805 x_num :=0;
2806 open c_signed_line_all;
2807 LOOP --I
2808 FETCH c_signed_line_all BULK COLLECT INTO
2809 l_Contract_number_tbl,
2810 l_Contract_number_modifier_tbl,
2811 l_Id_tbl,
2812 l_Object_version_number_tbl,
2813 l_sts_code_tbl,
2814 l_date_terminated_tbl,
2815 l_start_date_tbl,
2816 l_end_date_tbl,
2817 l_line_number_tbl,
2818 l_price_negotiated_tbl,
2819 l_dnz_chr_id_tbl,
2820 l_code_tbl,
2821 l_ste_code_tbl,
2822 l_meaning_tbl
2823 LIMIT 1000;
2824 IF (l_Id_tbl.COUNT < 1) THEN
2825 EXIT;
2826 END IF;
2827 IF (l_Id_tbl.COUNT > 0) THEN
2828 i := l_Id_tbl.FIRST;
2829 LOOP --II
2830 r_signed_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2831 r_signed_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2832 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2833 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2834 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2835 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2836 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2837 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2838 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2839 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2840 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2841 r_signed_line_tbl(x_num).termination_reason := NULL;
2842 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2843 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2844 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2845 x_num :=x_num+1;
2846 EXIT WHEN (i = l_Id_tbl.LAST);
2847 i := l_Id_tbl.NEXT(i);
2848 END LOOP; --II
2849 END IF;
2850 Exit when c_signed_line_all%NOTFOUND;
2851 END LOOP; --I
2852 IF(r_signed_line_tbl.COUNT > 0) Then
2853 i := r_signed_line_tbl.FIRST;
2854 LOOP
2855 line_signed(r_signed_line_tbl(i));
2856 EXIT WHEN (i = r_signed_line_tbl.LAST);
2857 i := r_signed_line_tbl.NEXT(i);
2858 END LOOP;
2859 END IF;
2860
2861 commit;
2862 c:= 0;
2863 END IF;-- Added for BUG #3967643
2864 ELSE -- p_kid IS NULL
2865
2866 x_num :=0;
2867 open c_signed_line_k;
2868 LOOP --I
2869 FETCH c_signed_line_k BULK COLLECT INTO
2870 l_Id_tbl,
2871 l_Object_version_number_tbl,
2872 l_sts_code_tbl,
2873 l_date_terminated_tbl,
2874 l_start_date_tbl,
2875 l_end_date_tbl,
2876 l_line_number_tbl,
2877 l_price_negotiated_tbl,
2878 l_dnz_chr_id_tbl,
2879 l_code_tbl,
2880 l_ste_code_tbl,
2881 l_meaning_tbl
2882 LIMIT 1000;
2883 IF (l_Id_tbl.COUNT < 1) THEN
2884 EXIT;
2885 END IF;
2886 IF (l_Id_tbl.COUNT > 0) THEN
2887 i := l_Id_tbl.FIRST;
2888 LOOP --II
2889 r_signed_line_tbl(x_num).contract_number := p_k_num;
2890 r_signed_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
2891 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2892 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2893 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2894 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2895 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2896 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2897 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2898 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2899 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2900 r_signed_line_tbl(x_num).termination_reason := NULL;
2901 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2902 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2903 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2904 x_num :=x_num+1;
2905 EXIT WHEN (i = l_Id_tbl.LAST);
2906 i := l_Id_tbl.NEXT(i);
2907 END LOOP; --II
2908 END IF;
2909 Exit when c_signed_line_k%NOTFOUND;
2910 END LOOP; --I
2911 IF(r_signed_line_tbl.COUNT > 0) Then
2912 i := r_signed_line_tbl.FIRST;
2913 LOOP
2914 line_signed(r_signed_line_tbl(i));
2915 EXIT WHEN (i = r_signed_line_tbl.LAST);
2916 i := r_signed_line_tbl.NEXT(i);
2917 END LOOP;
2918 END IF;
2919 END IF;
2920 END IF;
2921 --END OF BUG 5930684
2922
2923 delete_table_type;
2924 -------------------------------------------------------------------------
2925
2926 EXCEPTION
2927 WHEN OTHERS THEN
2928 line_message(p_knum_and_mod =>l_knum_and_mod,
2929 p_line_number=>l_line_number,
2930 p_status =>l_new_status,
2931 p_msg_data => x_msg_data,
2932 p_type =>'U');
2933 p_line_errors := p_line_errors +1 ;
2934 rollback to H_STATUS;
2935 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2936 return;
2937
2938 END; -- procedure line_status_change
2939 -- BUG 4285665 --
2940 -- NEW --
2941 -----------------------------------------------------------------
2942 -- End LINE Status change procedure
2943 -----------------------------------------------------------------
2944
2945 -----------------------------------------------------------------
2946 -- End LINE Status change procedure
2947 -----------------------------------------------------------------
2948
2949
2950 -----------------------------------------------------------------
2951 -- Begin Status change procedure
2952 -----------------------------------------------------------------
2953 Procedure header_message(p_knum_and_mod IN VARCHAR2,
2954 p_old_status IN VARCHAR2 DEFAULT NULL,
2955 p_status IN VARCHAR2 DEFAULT NULL,
2956 p_msg_data IN VARCHAR2 DEFAULT NULL,
2957 p_type IN VARCHAR2) IS
2958 BEGIN
2959 if p_type='S' Then
2960 /*
2961 FND_MESSAGE.set_name('OKC','OKC_HDR_STS_CHANGE_SUCCESS');
2962 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_knum_and_mod);
2963 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
2964 FND_MESSAGE.set_token('STATUS', p_status);
2965 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2966 */
2967 NULL;
2968 elsif p_type='E'Then
2969 get_fnd_msg_stack(p_msg_data);
2970 p_hdr_errors := p_hdr_errors +1 ;
2971 FND_MESSAGE.set_name('OKC','OKC_HDR_STS_CHANGE_FAILURE');
2972 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_knum_and_mod);
2973 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
2974 FND_MESSAGE.set_token('STATUS', p_status);
2975 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2976 elsif p_type='U'Then
2977 get_fnd_msg_stack(p_msg_data);
2978 FND_MESSAGE.set_name('OKC',G_UNEXPECTED_ERROR);
2979 FND_MESSAGE.set_token(G_SQLCODE_TOKEN,SQLCODE);
2980 FND_MESSAGE.set_token(G_SQLERRM_TOKEN,SQLERRM);
2981 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2982 FND_MESSAGE.set_name('OKC','OKC_HDR_STS_CHANGE_FAILURE');
2983 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_knum_and_mod);
2984 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
2985 FND_MESSAGE.set_token('STATUS', p_status);
2986 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2987 end if;
2988 end header_message;
2989
2990 PROCEDURE WrapUp IS
2991 BEGIN
2992 ----------------------------------------------------------------------------------------
2993 ---LOG MESSAGES (SUMMARY)
2994 ----------------------------------------------------------------------------------------
2995 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
2996 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
2997 FND_MESSAGE.set_name('OKC','OKC_SUMMARY');
2998 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2999 IF p_hdr_errors > 0 or p_line_errors > 0 then
3000 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3001 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_ERRORS');
3002 FND_MESSAGE.set_token('HEADER_ERROR', p_hdr_errors);
3003 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3004 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3005 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_ERRORS');
3006 FND_MESSAGE.set_token('LINE_ERRORS', p_line_errors);
3007 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3008 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3009 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_ERRORS');
3010 FND_MESSAGE.set_token('HEADER_ERROR', p_hdr_errors);
3011 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3012 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
3013 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_ERRORS');
3014 FND_MESSAGE.set_token('LINE_ERRORS', p_line_errors);
3015 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3016 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'--------------------------------------');
3017 ELSE
3018 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3019 FND_MESSAGE.set_name('OKC','OKC_NO_ERRORS');
3020 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3021 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'--------------------------------------');
3022 FND_MESSAGE.set_name('OKC','OKC_NO_ERRORS');
3023 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3024 END IF;
3025 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3026 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_TOTAL');
3027 FND_MESSAGE.set_token('HEADER_TOTAL', p_hdr_count);
3028 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3029 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_TOTAL');
3030 FND_MESSAGE.set_token('LINE_TOTAL', p_line_count);
3031 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3032 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'--------------------------------------');
3033 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_TOTAL');
3034 FND_MESSAGE.set_token('HEADER_TOTAL', p_hdr_count);
3035 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3036 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_TOTAL');
3037 FND_MESSAGE.set_token('LINE_TOTAL', p_line_count);
3038 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3039
3040 --Comments
3041 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'===============================================================');
3042 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,' End of Status Change Concurrent Program');
3043 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'===============================================================');
3044 END;
3045
3046 PROCEDURE change_status (
3047 ERRBUF OUT NOCOPY VARCHAR2,
3048 RETCODE OUT NOCOPY NUMBER,
3049 p_category IN VARCHAR2 ,
3050 p_from_k IN VARCHAR2 ,
3051 p_to_k IN VARCHAR2 ,
3052 p_from_m IN VARCHAR2 ,
3053 p_to_m IN VARCHAR2 ,
3054 p_debug IN VARCHAR2 ,
3055 p_last_rundate IN VARCHAR2 ) IS
3056 L_K_N_W_M VARCHAR2(240); -- contract number conactinated with contract number modifier.
3057 C number := 0;
3058 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
3059 p_init_msg_list VARCHAR2(200) := okc_api.g_true;
3060 x_msg_count NUMBER := okc_api.g_miss_num;
3061 x_msg_data VARCHAR2(2000) := okc_api.g_miss_char;
3062 l_chr_rec okc_contract_pub.chrv_rec_type;
3063 i_chr_rec okc_contract_pub.chrv_rec_type;
3064 p_error_from_line VARCHAR2(1) := 'N';
3065
3066 -- 'SIGNED' to ACTIVE
3067 CURSOR C_ACTIVE IS
3068 SELECT
3069 hdr.ID,
3070 hdr.OBJECT_VERSION_NUMBER,
3071 hdr.STS_CODE,
3072 hdr.CONTRACT_NUMBER,
3073 hdr.CONTRACT_NUMBER_MODIFIER,
3074 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3075 ' - '||hdr.contract_number_modifier) K_N_W_M,
3076 hdr.DATE_TERMINATED,
3077 hdr.TRN_CODE,
3078 hdr.START_DATE,
3079 hdr.END_DATE,
3080 hdr.SCS_CODE,
3081 hdr.ESTIMATED_AMOUNT,
3082 scs.CLS_CODE,
3083 status.CODE,
3084 status.STE_CODE,
3085 status.meaning
3086 FROM OKC_K_HEADERS_B hdr,
3087 OKC_STATUSES_V status,
3088 OKC_SUBCLASSES_B scs
3089 WHERE hdr.STS_CODE = status.CODE
3090 AND scs.code = hdr.scs_code
3091 and scs.cls_code <> 'OKL'
3092 and ((p_category is NULL) or (scs.CODE = p_category))
3093 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3094 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3095 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3096 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3097 and hdr.STS_CODE <> 'QA_HOLD'
3098 and status.ste_code = 'SIGNED'
3099 AND (hdr.date_terminated IS NULL
3100 or hdr.date_terminated >= trunc(sysdate))
3101 AND hdr.start_date <= trunc(sysdate)+0.99999
3102 AND hdr.start_date >= trunc(l_last_rundate);
3103
3104 --bug 5930684
3105 -- 'ACTIVE' to SIGNED
3106 CURSOR C_SIGNED IS
3107 SELECT
3108 hdr.ID,
3109 hdr.OBJECT_VERSION_NUMBER,
3110 hdr.STS_CODE,
3111 hdr.CONTRACT_NUMBER,
3112 hdr.CONTRACT_NUMBER_MODIFIER,
3113 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3114 ' - '||hdr.contract_number_modifier) K_N_W_M,
3115 hdr.DATE_TERMINATED,
3116 hdr.TRN_CODE,
3117 hdr.START_DATE,
3118 hdr.END_DATE,
3119 hdr.SCS_CODE,
3120 hdr.ESTIMATED_AMOUNT,
3121 scs.CLS_CODE,
3122 status.CODE,
3123 status.STE_CODE,
3124 status.meaning
3125 FROM OKC_K_HEADERS_B hdr,
3126 OKC_STATUSES_V status,
3127 OKC_SUBCLASSES_B scs
3128 WHERE hdr.STS_CODE = status.CODE
3129 AND scs.code = hdr.scs_code
3130 and scs.cls_code <> 'OKL'
3131 and ((p_category is NULL) or (scs.CODE = p_category))
3132 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3133 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3134 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3135 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3136 and hdr.STS_CODE <> 'QA_HOLD'
3137 and status.ste_code = 'ACTIVE'
3138 AND (hdr.date_terminated IS NULL
3139 or hdr.date_terminated >= trunc(sysdate))
3140 AND hdr.start_date >= trunc(sysdate)+0.99999
3141 AND hdr.start_date >= trunc(l_last_rundate);
3142 --end of bug 5930684.
3143
3144 -- 'ACTIVE,HOLD,SIGNED to EXPIRED'
3145 CURSOR C_EXPIRED IS
3146 SELECT /*+ leading(hdr) no_merge(status) use_hash(hdr scs status.stsb status.stst) */
3147 hdr.ID,
3148 hdr.OBJECT_VERSION_NUMBER,
3149 hdr.STS_CODE,
3150 hdr.CONTRACT_NUMBER,
3151 hdr.CONTRACT_NUMBER_MODIFIER,
3152 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3153 ' - '||hdr.contract_number_modifier) K_N_W_M,
3154 hdr.DATE_TERMINATED,
3155 hdr.TRN_CODE,
3156 hdr.START_DATE,
3157 hdr.END_DATE,
3158 hdr.SCS_CODE,
3159 hdr.ESTIMATED_AMOUNT,
3160 scs.CLS_CODE,
3161 status.CODE,
3162 status.STE_CODE,
3163 status.meaning
3164 FROM OKC_K_HEADERS_B hdr,
3165 OKC_STATUSES_V status,
3166 OKC_SUBCLASSES_B scs
3167 WHERE hdr.STS_CODE = status.CODE
3168 AND scs.code = hdr.scs_code
3169 and scs.cls_code <> 'OKL'
3170 and ((p_category is NULL) or (scs.CODE = p_category))
3171 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3172 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3173 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3174 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3175 and hdr.STS_CODE <> 'QA_HOLD'
3176 -- and status.ste_code in ('ACTIVE','SIGNED','HOLD') -- <> 'EXPIRED'
3177 and status.ste_code in ('ACTIVE','SIGNED') -- Bug 4915692 --
3178 AND hdr.end_date >= trunc(l_last_rundate)
3179 --
3180 -- Bug 2672565 - Removed time component and changed from <= to <
3181 --and hdr.end_date <= trunc(sysdate)+0.99999
3182 --
3183 and hdr.end_date < trunc(sysdate)
3184 AND (hdr.date_terminated IS NULL
3185 or hdr.date_terminated >= trunc(sysdate));
3186
3187 -- 'ACTIVE','HOLD','SIGNED' to TERMINATED
3188 CURSOR C_TERMINATED IS
3189 SELECT
3190 hdr.ID,
3191 hdr.OBJECT_VERSION_NUMBER,
3192 hdr.STS_CODE,
3193 hdr.CONTRACT_NUMBER,
3194 hdr.CONTRACT_NUMBER_MODIFIER,
3195 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3196 ' - '||hdr.contract_number_modifier) K_N_W_M,
3197 hdr.DATE_TERMINATED,
3198 hdr.TRN_CODE,
3199 hdr.START_DATE,
3200 hdr.END_DATE,
3201 hdr.SCS_CODE,
3202 hdr.ESTIMATED_AMOUNT,
3203 fnd.meaning TERMINATION_REASON,
3204 scs.CLS_CODE,
3205 status.CODE,
3206 status.STE_CODE,
3207 status.meaning
3208 FROM OKC_K_HEADERS_B hdr,
3209 OKC_STATUSES_V status,
3210 FND_LOOKUPS fnd,
3211 OKC_SUBCLASSES_B scs
3212 WHERE hdr.STS_CODE = status.CODE
3213 AND scs.code = hdr.scs_code
3214 and scs.cls_code <> 'OKL'
3215 and ((p_category is NULL) or (scs.CODE = p_category))
3216 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3217 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3218 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3219 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3220 and hdr.STS_CODE <> 'QA_HOLD'
3221 -- and status.ste_code IN ('ACTIVE','HOLD','SIGNED')
3222 and status.ste_code IN ('ACTIVE','SIGNED') -- Bug 4915692
3223 and hdr.trn_code = fnd.lookup_code
3224 and fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
3225 and hdr.date_terminated >= trunc(l_last_rundate)
3226 and hdr.date_terminated <= trunc(sysdate)+0.99999;
3227
3228 ----------------------------------------------------------------------------------------
3229 --- BEGIN CHANGE STATUS AT HEADER LEVEL ---
3230 ----------------------------------------------------------------------------------------
3231 BEGIN
3232
3233 savepoint H_STATUS;
3234 FND_MSG_PUB.initialize;
3235 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'===============================================================');
3236 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Start of Status Change Concurrent Program');
3237 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'===============================================================');
3238 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Parameters for the Run:');
3239 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Last Run Date: ' || p_last_rundate);
3240 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Category: ' || p_category);
3241 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract Number From: ' || p_from_k ||'*'||p_from_m);
3242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract Number To: ' || p_to_k ||'*'||p_to_m);
3243 --
3244 ERRBUF := NULL;
3245 RETCODE := 0;
3246
3247 -- Bug 5086847 --
3248 IF (p_from_m IS NOT NULL) AND (p_from_k IS NULL) THEN
3249
3250 FND_MESSAGE.set_name('OKC','OKC_ENTER_K_NUMBER');
3251 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3252
3253 RETCODE := 2;
3254 return;
3255 END IF;
3256
3257 IF (p_to_m IS NOT NULL) AND (p_to_k IS NULL) THEN
3258
3259 FND_MESSAGE.set_name('OKC','OKC_ENTER_K_NUMBER');
3260 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3261
3262 RETCODE := 2;
3263 return;
3264 END IF;
3265
3266 -- Bug 5086847 --
3267
3268 T := NVL(to_number(FND_PROFILE.VALUE('OKC_BATCH_SIZE')),1000);
3269 open sts(v_active);
3270 fetch sts into v_active,v_active_m;
3271 if sts%NOTFOUND Then
3272 v_active := 'ACTIVE';
3273 v_active_m := 'Active';
3274 end if;
3275 close sts;
3276
3277 open sts(v_expired);
3278 fetch sts into v_expired,v_expired_m;
3279 if sts%NOTFOUND Then
3280 v_active := 'EXPIRED';
3281 v_active_m := 'Expired';
3282 end if;
3283 close sts;
3284
3285 open sts(v_terminated);
3286 fetch sts into v_terminated,v_terminated_m;
3287 if sts%NOTFOUND Then
3288 v_active := 'TERMINATED';
3289 v_active_m := 'Terminated';
3290 end if;
3291 close sts;
3292
3293 open sts(v_signed);
3294 fetch sts into v_signed,v_signed_m;
3295 if sts%NOTFOUND Then
3296 v_active := 'SIGNED';
3297 v_active_m := 'Signed';
3298 end if;
3299 close sts;
3300
3301 l_last_rundate := nvl(fnd_date.canonical_to_date(p_last_rundate), to_date('01011901','ddmmyyyy')) - 3; -- a three day grace period.
3302
3303 -- from active hold signed to terminated
3304 c := 0;
3305 l_new_status := null;
3306 l_new_status_m := null;
3307 FOR r_terminated in c_terminated LOOP
3308
3309
3310 BEGIN
3311
3312 if (C >= T) then
3313 commit;
3314 c := 0;
3315 end if;
3316 savepoint H_STATUS;
3317 p_hdr_count:= p_hdr_count + 1;
3318 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3319 L_K_N_W_M := r_terminated.K_N_W_M;
3320
3321 l_new_status := null;
3322 l_new_status_m := null;
3323 h_status := null;
3324 h_new_status := v_terminated ;
3325 h_new_status_m := v_terminated_m ;
3326 h_status_type := 'TERMINATED';
3327
3328 l_chr_rec.id := r_terminated.id ;
3329 l_chr_rec.object_version_number := r_terminated.object_version_number ;
3330 l_chr_rec.sts_code := h_new_status ;
3331 l_chr_rec.old_sts_code := r_terminated.sts_code;
3332 l_chr_rec.old_ste_code := r_terminated.ste_code;
3333 l_chr_rec.new_sts_code := h_new_status;
3334 l_chr_rec.new_ste_code := h_status_type;
3335 --
3336 -- End: Added for Status Change Action Assembler Changes 10/19/2000
3337 --
3338 -- lock added not to depend on update implementation
3339 --
3340 okc_contract_pub.lock_contract_header(
3341 p_api_version => 1.0,
3342 p_init_msg_list => p_init_msg_list,
3343 x_return_status => l_return_status,
3344 x_msg_count => x_msg_count,
3345 x_msg_data => x_msg_data,
3346 p_chrv_rec => l_chr_rec);
3347 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3348 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3349 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3350 raise OKC_API.G_EXCEPTION_ERROR;
3351 END IF;
3352 --
3353 --
3354 update_contract_header (
3355 p_api_version => 1.0,
3356 p_init_msg_list => p_init_msg_list,
3357 x_return_status => l_return_status,
3358 x_msg_count => x_msg_count,
3359 x_msg_data => x_msg_data,
3360 p_chrv_rec => l_chr_rec,
3361 x_chrv_rec => i_chr_rec);
3362
3363 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3364 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3365 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3366 raise OKC_API.G_EXCEPTION_ERROR;
3367 END IF;
3368
3369 OKC_K_TERM_ASMBLR_PVT.acn_assemble(
3370 p_api_version => 1.0 ,
3371 p_init_msg_list => p_init_msg_list,
3372 x_return_status => l_return_status,
3373 x_msg_count => x_msg_count,
3374 x_msg_data => x_msg_data,
3375 p_k_class => r_terminated.cls_code,
3376 p_k_id => r_terminated.id,
3377 p_k_number => r_terminated.contract_number,
3378 p_k_nbr_mod => r_terminated.contract_number_modifier,
3379 p_k_subclass => r_terminated.scs_code,
3380 p_estimated_amount => r_terminated.estimated_amount,
3381 P_K_STATUS_CODE => r_terminated.STS_CODE,
3382 p_term_date => r_terminated.date_terminated,
3383 p_term_reason => r_terminated.termination_reason);
3384
3385
3386 --
3387 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3388 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3389 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3390 raise OKC_API.G_EXCEPTION_ERROR;
3391 END IF;
3392 OKC_TIME_RES_PUB.res_time_termnt_k(
3393 P_CHR_ID => r_terminated.id,
3394 P_CLE_ID => NULL,
3395 P_END_DATE => r_terminated.DATE_TERMINATED,
3396 P_API_VERSION => 1.0 ,
3397 p_init_msg_list => p_init_msg_list,
3398 x_return_status => l_return_status
3399 );
3400
3401
3402 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3403 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3404 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3405 raise OKC_API.G_EXCEPTION_ERROR;
3406 END IF;
3407 --
3408 --+
3409 --+ remember header status for lines to follow it, not default
3410 --+
3411
3412 h_status := h_new_status;
3413 h_status_m := h_new_status_m ;
3414
3415 line_status_change (p_kid => r_terminated.id,
3416 p_cls_code => r_terminated.cls_code,
3417 p_scs_code => r_terminated.scs_code,
3418 p_k_num => r_terminated.contract_number,
3419 p_k_num_mod => r_terminated.contract_number_modifier,
3420 p_update_minor_version =>'N',
3421 x_return_status => l_return_status);
3422
3423
3424
3425 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3426 p_error_from_line := 'Y';
3427 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3428 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3429 p_error_from_line := 'Y';
3430 raise OKC_API.G_EXCEPTION_ERROR;
3431 END IF;
3432 header_message(p_knum_and_mod =>L_K_N_W_M,
3433 p_old_status =>r_terminated.meaning,
3434 p_status =>h_new_status_m,
3435 p_type =>'S');
3436 c:= c+1;
3437 EXCEPTION
3438 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3439 header_message(p_knum_and_mod =>L_K_N_W_M,
3440 p_status =>h_new_status_m,
3441 p_old_status =>r_terminated.meaning,
3442 p_msg_data => x_msg_data,
3443 p_type =>'U');
3444 if p_error_from_line <> 'Y' then
3445 p_hdr_errors := p_hdr_errors +1 ;
3446 else
3447 p_error_from_line := 'N';
3448 end if;
3449 rollback to H_STATUS;
3450 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3451 header_message(p_knum_and_mod =>L_K_N_W_M,
3452 p_status =>h_new_status_m,
3453 p_old_status =>r_terminated.meaning,
3454 p_msg_data => x_msg_data,
3455 p_type =>'E');
3456 if p_error_from_line <> 'Y' then
3457 p_hdr_errors := p_hdr_errors +1 ;
3458 else
3459 p_error_from_line := 'N';
3460 end if;
3461 rollback to H_STATUS;
3462 WHEN OTHERS then
3463 header_message(p_knum_and_mod =>L_K_N_W_M,
3464 p_status =>h_new_status_m,
3465 p_old_status =>r_terminated.meaning,
3466 p_msg_data => x_msg_data,
3467 p_type =>'U');
3468 p_hdr_errors := p_hdr_errors +1 ;
3469 rollback to H_STATUS;
3470 END;
3471
3472 END LOOP; -- from active hold signed to terminated
3473 commit;
3474 c:= 0;
3475 savepoint H_STATUS;
3476 ----------------------------------------------------------------------------------------
3477 l_new_status := null;
3478 l_new_status_m := null;
3479
3480 -- From Active, hold, signed to EXPIRED
3481 FOR r_expired in c_expired LOOP
3482
3483
3484 BEGIN
3485 if (C >= T) then
3486 commit;
3487 c := 0;
3488 end if;
3489
3490 savepoint H_STATUS;
3491 p_hdr_count:= p_hdr_count + 1;
3492 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3493 L_K_N_W_M := r_expired.K_N_W_M;
3494 l_new_status := null;
3495 l_new_status_m := null;
3496 h_status := null;
3497 h_new_status := v_expired;
3498 h_new_status_m := v_expired_m;
3499 h_status_type := 'EXPIRED';
3500
3501 l_chr_rec.id := r_expired.id ;
3502 l_chr_rec.object_version_number := r_expired.object_version_number ;
3503 l_chr_rec.sts_code := h_new_status ;
3504 l_chr_rec.old_sts_code := r_expired.sts_code;
3505 l_chr_rec.old_ste_code := r_expired.ste_code;
3506 l_chr_rec.new_sts_code := h_new_status;
3507 l_chr_rec.new_ste_code := h_status_type;
3508 --
3509 -- lock added not to depend on update implementation
3510 --
3511 okc_contract_pub.lock_contract_header(
3512 p_api_version => 1.0,
3513 p_init_msg_list => p_init_msg_list,
3514 x_return_status => l_return_status,
3515 x_msg_count => x_msg_count,
3516 x_msg_data => x_msg_data,
3517 p_chrv_rec => l_chr_rec);
3518 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3519 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3520 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3521 raise OKC_API.G_EXCEPTION_ERROR;
3522 END IF;
3523 --
3524 update_contract_header (
3525 p_api_version => 1.0,
3526 p_init_msg_list => p_init_msg_list,
3527 x_return_status => l_return_status,
3528 x_msg_count => x_msg_count,
3529 x_msg_data => x_msg_data,
3530 p_chrv_rec => l_chr_rec,
3531 x_chrv_rec => i_chr_rec);
3532 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3533 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3534 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3535 raise OKC_API.G_EXCEPTION_ERROR;
3536 END IF;
3537 --+
3538 --+ remember header status for lines to follow it, not default
3539 --+
3540 h_status := h_new_status;
3541 h_status_m := h_new_status_m ;
3542 line_status_change (p_kid => r_expired.id,
3543 p_cls_code => r_expired.cls_code,
3544 p_scs_code => r_expired.scs_code,
3545 p_k_num => r_expired.contract_number,
3546 p_k_num_mod => r_expired.contract_number_modifier,
3547 p_update_minor_version =>'N',
3548 x_return_status => l_return_status);
3549 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3550 p_error_from_line := 'Y';
3551 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3552 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3553 p_error_from_line := 'Y';
3554 raise OKC_API.G_EXCEPTION_ERROR;
3555 END IF;
3556 header_message(p_knum_and_mod =>L_K_N_W_M,
3557 p_status =>h_new_status_m,
3558 p_old_status =>r_expired.meaning,
3559 p_type =>'S');
3560
3561 c := c + 1;
3562 EXCEPTION
3563 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3564 header_message(p_knum_and_mod =>L_K_N_W_M,
3565 p_status =>h_new_status_m,
3566 p_old_status =>r_expired.meaning,
3567 p_msg_data => x_msg_data,
3568 p_type =>'U');
3569 if p_error_from_line <> 'Y' then
3570 p_hdr_errors := p_hdr_errors +1 ;
3571 else
3572 p_error_from_line := 'N';
3573 end if;
3574 rollback to H_STATUS;
3575 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3576 header_message(p_knum_and_mod =>L_K_N_W_M,
3577 p_status =>h_new_status_m,
3578 p_old_status =>r_expired.meaning,
3579 p_msg_data => x_msg_data,
3580 p_type =>'E');
3581 if p_error_from_line <> 'Y' then
3582 p_hdr_errors := p_hdr_errors +1 ;
3583 else
3584 p_error_from_line := 'N';
3585 end if;
3586 rollback to H_STATUS;
3587 WHEN OTHERS then
3588 header_message(p_knum_and_mod =>L_K_N_W_M,
3589 p_status =>h_new_status_m,
3590 p_old_status =>r_expired.meaning,
3591 p_msg_data => x_msg_data,
3592 p_type =>'U');
3593 p_hdr_errors := p_hdr_errors +1 ;
3594 rollback to H_STATUS;
3595 END;
3596 END LOOP; -- From Active, hold, signed to EXPIRED
3597
3598 commit;
3599 c:= 0;
3600 savepoint H_STATUS;
3601 l_new_status := null;
3602 l_new_status_m := null;
3603 ----------------------------------------------------------------------------------------
3604 -- From Signed to Active
3605 FOR r_active in c_active LOOP
3606
3607 BEGIN
3608 if (C >= T) then
3609 commit;
3610 c := 0;
3611 end if;
3612 savepoint H_STATUS;
3613 p_hdr_count:= p_hdr_count + 1;
3614 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3615 L_K_N_W_M := r_active.K_N_W_M;
3616
3617 l_new_status := null;
3618 l_new_status_m := null;
3619 h_status := null;
3620 h_new_status := v_active ;
3621 h_new_status_m := v_active_m ;
3622 h_status_type := 'ACTIVE';
3623
3624 l_chr_rec.id := r_active.id ;
3625 l_chr_rec.object_version_number := r_active.object_version_number ;
3626 l_chr_rec.sts_code := h_new_status ;
3627 l_chr_rec.old_sts_code := r_active.sts_code;
3628 l_chr_rec.old_ste_code := r_active.ste_code;
3629 l_chr_rec.new_sts_code := h_new_status;
3630 l_chr_rec.new_ste_code := h_status_type;
3631 --
3632 okc_contract_pub.lock_contract_header(
3633 p_api_version => 1.0,
3634 p_init_msg_list => p_init_msg_list,
3635 x_return_status => l_return_status,
3636 x_msg_count => x_msg_count,
3637 x_msg_data => x_msg_data,
3638 p_chrv_rec => l_chr_rec);
3639
3640 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3641 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3642 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3643 raise OKC_API.G_EXCEPTION_ERROR;
3644 END IF;
3645 --
3646 update_contract_header (
3647 p_api_version => 1.0,
3648 p_init_msg_list => p_init_msg_list,
3649 x_return_status => l_return_status,
3650 x_msg_count => x_msg_count,
3651 x_msg_data => x_msg_data,
3652 p_chrv_rec => l_chr_rec,
3653 x_chrv_rec => i_chr_rec);
3654
3655 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3656 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3657 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3658 raise OKC_API.G_EXCEPTION_ERROR;
3659 END IF;
3660 --+
3661 --+ remember header status for lines to follow it, not default
3662 --+
3663 h_status := h_new_status;
3664 h_status_m := h_new_status_m ;
3665
3666 line_status_change (p_kid => r_active.id,
3667 p_cls_code => r_active.cls_code,
3668 p_scs_code => r_active.scs_code,
3669 p_k_num => r_active.contract_number,
3670 p_k_num_mod => r_active.contract_number_modifier,
3671 p_update_minor_version =>'N',
3672 x_return_status => l_return_status);
3673
3674 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3675 p_error_from_line := 'Y';
3676 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3677 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3678 p_error_from_line := 'Y';
3679 raise OKC_API.G_EXCEPTION_ERROR;
3680 END IF;
3681 header_message(p_knum_and_mod =>L_K_N_W_M,
3682 p_status =>h_new_status_m,
3683 p_old_status =>r_active.meaning,
3684 p_type =>'S');
3685 c := c+1;
3686 EXCEPTION
3687 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3688 header_message(p_knum_and_mod =>L_K_N_W_M,
3689 p_status =>h_new_status_m,
3690 p_old_status =>r_active.meaning,
3691 p_msg_data => x_msg_data,
3692 p_type =>'U');
3693 if p_error_from_line <> 'Y' then
3694 p_hdr_errors := p_hdr_errors +1 ;
3695 else
3696 p_error_from_line := 'N';
3697 end if;
3698 rollback to H_STATUS;
3699 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3700 header_message(p_knum_and_mod =>L_K_N_W_M,
3701 p_status =>h_new_status_m,
3702 p_old_status =>r_active.meaning,
3703 p_msg_data => x_msg_data,
3704 p_type =>'E');
3705 if p_error_from_line <> 'Y' then
3706 p_hdr_errors := p_hdr_errors +1 ;
3707 else
3708 p_error_from_line := 'N';
3709 end if;
3710 rollback to H_STATUS;
3711 WHEN OTHERS then
3712 header_message(p_knum_and_mod =>L_K_N_W_M,
3713 p_status =>h_new_status_m,
3714 p_old_status =>r_active.meaning,
3715 p_msg_data => x_msg_data,
3716 p_type =>'U');
3717 p_hdr_errors := p_hdr_errors +1 ;
3718 rollback to H_STATUS;
3719 END;
3720 END LOOP; -- From Signed to Active
3721
3722 commit;
3723 c := 0;
3724
3725 savepoint H_STATUS;
3726 l_new_status := null;
3727 l_new_status_m := null;
3728 ----------------------------------------------------------------------------------------
3729 --bug 5930684
3730 -- From Active to Signed
3731
3732 FOR r_signed in c_signed LOOP
3733
3734 BEGIN
3735 if (C >= T) then
3736 commit;
3737 c := 0;
3738 end if;
3739 savepoint H_STATUS;
3740 p_hdr_count:= p_hdr_count + 1;
3741 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3742 L_K_N_W_M := r_signed.K_N_W_M;
3743
3744 l_new_status := null;
3745 l_new_status_m := null;
3746 h_status := null;
3747 h_new_status := v_signed ;
3748 h_new_status_m := v_signed_m ;
3749 h_status_type := 'SIGNED';
3750
3751 l_chr_rec.id := r_signed.id ;
3752 l_chr_rec.object_version_number := r_signed.object_version_number ;
3753 l_chr_rec.sts_code := h_new_status ;
3754 l_chr_rec.old_sts_code := r_signed.sts_code;
3755 l_chr_rec.old_ste_code := r_signed.ste_code;
3756 l_chr_rec.new_sts_code := h_new_status;
3757 l_chr_rec.new_ste_code := h_status_type;
3758 --
3759 okc_contract_pub.lock_contract_header(
3760 p_api_version => 1.0,
3761 p_init_msg_list => p_init_msg_list,
3762 x_return_status => l_return_status,
3763 x_msg_count => x_msg_count,
3764 x_msg_data => x_msg_data,
3765 p_chrv_rec => l_chr_rec);
3766
3767 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3768 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3769 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3770 raise OKC_API.G_EXCEPTION_ERROR;
3771 END IF;
3772 --
3773 update_contract_header (
3774 p_api_version => 1.0,
3775 p_init_msg_list => p_init_msg_list,
3776 x_return_status => l_return_status,
3777 x_msg_count => x_msg_count,
3778 x_msg_data => x_msg_data,
3779 p_chrv_rec => l_chr_rec,
3780 x_chrv_rec => i_chr_rec);
3781
3782 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3783 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3784 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3785 raise OKC_API.G_EXCEPTION_ERROR;
3786 END IF;
3787 --+
3788 --+ remember header status for lines to follow it, not default
3789 --+
3790 h_status := h_new_status;
3791 h_status_m := h_new_status_m ;
3792
3793 line_status_change (p_kid => r_signed.id,
3794 p_cls_code => r_signed.cls_code,
3795 p_scs_code => r_signed.scs_code,
3796 p_k_num => r_signed.contract_number,
3797 p_k_num_mod => r_signed.contract_number_modifier,
3798 p_update_minor_version =>'N',
3799 x_return_status => l_return_status);
3800
3801 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3802 p_error_from_line := 'Y';
3803 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3804 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3805 p_error_from_line := 'Y';
3806 raise OKC_API.G_EXCEPTION_ERROR;
3807 END IF;
3808 header_message(p_knum_and_mod =>L_K_N_W_M,
3809 p_status =>h_new_status_m,
3810 p_old_status =>r_signed.meaning,
3811 p_type =>'S');
3812 c := c+1;
3813 EXCEPTION
3814 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3815 header_message(p_knum_and_mod =>L_K_N_W_M,
3816 p_status =>h_new_status_m,
3817 p_old_status =>r_signed.meaning,
3818 p_msg_data => x_msg_data,
3819 p_type =>'U');
3820 if p_error_from_line <> 'Y' then
3821 p_hdr_errors := p_hdr_errors +1 ;
3822 else
3823 p_error_from_line := 'N';
3824 end if;
3825 rollback to H_STATUS;
3826 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3827 header_message(p_knum_and_mod =>L_K_N_W_M,
3828 p_status =>h_new_status_m,
3829 p_old_status =>r_signed.meaning,
3830 p_msg_data => x_msg_data,
3831 p_type =>'E');
3832 if p_error_from_line <> 'Y' then
3833 p_hdr_errors := p_hdr_errors +1 ;
3834 else
3835 p_error_from_line := 'N';
3836 end if;
3837 rollback to H_STATUS;
3838 WHEN OTHERS then
3839 header_message(p_knum_and_mod =>L_K_N_W_M,
3840 p_status =>h_new_status_m,
3841 p_old_status =>r_signed.meaning,
3842 p_msg_data => x_msg_data,
3843 p_type =>'U');
3844 p_hdr_errors := p_hdr_errors +1 ;
3845 rollback to H_STATUS;
3846 END;
3847 END LOOP; -- From Active to signed
3848
3849 commit;
3850 c := 0;
3851 --- end of bug 5930684
3852
3853
3854 ----------------------------------------------------------------------------------------
3855 --- END CHANGE STATUS AT HEADER LEVEL ---
3856 ----------------------------------------------------------------------------------------
3857 l_new_status := null;
3858 l_new_status_m := null;
3859
3860 -- Perform line status changes indepenant of Contract Header
3861 -- For Terminate, Expire, and Active
3862
3863 h_status := NULL;
3864 h_status_type := NULL;
3865 h_status_m := NULL;
3866
3867 h_new_status := NULL;
3868 h_new_status_m := NULL;
3869 -- line_status_change;
3870 line_status_change (p_kid => NULL
3871 ,p_cls_code => NULL
3872 ,p_scs_code => p_category
3873 ,p_from_k => p_from_k
3874 ,p_to_k => p_to_k
3875 ,p_from_m => p_from_m
3876 ,p_to_m => p_to_m
3877 ,p_k_num => NULL
3878 ,p_k_num_mod => NULL
3879 ,p_update_minor_version =>'Y'
3880 ,x_return_status => l_return_status );
3881 commit;
3882 wrapup;
3883 EXCEPTION
3884 WHEN OTHERS THEN
3885 header_message(p_knum_and_mod =>NULL,
3886 p_status =>NULL,
3887 p_old_status =>NULL,
3888 p_msg_data => x_msg_data,
3889 p_type =>'U');
3890 retcode := 1;
3891 wrapup;
3892 rollback to H_STATUS;
3893 END change_status; --1
3894
3895 END okc_status_change_pvt;