[Home] [Help]
PACKAGE BODY: APPS.OKC_STATUS_CHANGE_PVT
Source
1 package body okc_status_change_pvt as
2 /* $Header: OKCRSTSB.pls 120.12 2011/10/20 12:42:46 spingali 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 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
138
139 BEGIN
140 x_return_status := OKC_API.G_RET_STS_SUCCESS;
141 x_chrv_rec := p_chrv_rec;
142 UPDATE OKC_K_HEADERS_B
143 SET STS_CODE = p_chrv_rec.sts_code,
144 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
145 PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),PROGRAM_ID),
146 REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),REQUEST_ID),
147 PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,SYSDATE),
148 PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),PROGRAM_APPLICATION_ID),
149 LAST_UPDATE_DATE = SYSDATE,
150 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
151 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
152 WHERE ID = p_chrv_rec.id;
153
154 /*cgopinee bugfix for 6882512*/
155 IF (p_chrv_rec.old_ste_code <> p_chrv_rec.new_ste_code) THEN
156
157 OKC_CTC_PVT.update_contact_stecode(p_chr_id => p_chrv_rec.id,
158 x_return_status=>l_return_status);
159
160 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
161 RAISE OKC_API.G_EXCEPTION_ERROR;
162 END IF;
163 END IF;
164
165
166 FND_FILE.PUT_LINE( FND_FILE.LOG,'old_sts_code'||p_chrv_rec.old_sts_code);
167 FND_FILE.PUT_LINE( FND_FILE.LOG,'old_ste_code'||p_chrv_rec.old_ste_code);
168 FND_FILE.PUT_LINE( FND_FILE.LOG,'new_sts_code'||p_chrv_rec.new_sts_code);
169 FND_FILE.PUT_LINE( FND_FILE.LOG,'new_ste_code'||p_chrv_rec.new_ste_code);
170 -- Call action assembler if status is changed
171 If p_chrv_rec.old_sts_code is not null AND
172 p_chrv_rec.new_sts_code is not null AND
173 p_chrv_rec.old_ste_code is not null AND
174 p_chrv_rec.new_ste_code is not null AND
175 (p_chrv_rec.old_sts_code <> p_chrv_rec.new_sts_code OR
176 p_chrv_rec.old_ste_code <> p_chrv_rec.new_ste_code)
177 Then
178 OKC_K_STS_CHG_ASMBLR_PVT.Acn_Assemble(
179 p_api_version => p_api_version,
180 p_init_msg_list => p_init_msg_list,
181 x_return_status => x_return_status,
182 x_msg_count => x_msg_count,
183 x_msg_data => x_msg_data,
184 p_k_id => p_chrv_rec.id,
185 p_k_number => p_chrv_rec.contract_number,
186 p_k_nbr_mod => p_chrv_rec.contract_number_modifier,
187 p_k_cur_sts_code => p_chrv_rec.new_sts_code,
188 p_k_cur_sts_type => p_chrv_rec.new_ste_code,
189 p_k_pre_sts_code => p_chrv_rec.old_sts_code,
190 p_k_pre_sts_type => p_chrv_rec.old_ste_code,
191 p_k_source_system_code => p_chrv_rec.ORIG_SYSTEM_SOURCE_CODE);
192 End If;
193 -- Update minor version
194 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
195 x_return_status := Update_Minor_Version(p_chrv_rec.id);
196
197 End If;
198 exception
199 when OTHERS then
200 -- store SQL error message on message stack
201 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
202 p_msg_name => g_unexpected_error,
203 p_token1 => g_sqlcode_token,
204 p_token1_value => sqlcode,
205 p_token2 => g_sqlerrm_token,
206 p_token2_value => sqlerrm);
207
208 -- notify caller of an UNEXPETED error
209 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
210 END update_contract_header;
211
212 PROCEDURE update_contract_line(
213 p_api_version IN NUMBER,
214 p_init_msg_list IN VARCHAR2 ,
215 p_update_minor_version IN VARCHAR2 ,
216 p_contract_number IN VARCHAR2,
217 p_contract_number_modifier IN VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
222 x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
223
224 l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Line';
225
226 BEGIN
227
228 x_return_status := OKC_API.G_RET_STS_SUCCESS;
229 x_clev_rec := p_clev_rec;
230 UPDATE OKC_K_LINES_B
231 SET STS_CODE = p_clev_rec.sts_code,
232 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
233 PROGRAM_ID = NVL(decode(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),PROGRAM_ID),
234 REQUEST_ID = NVL(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),REQUEST_ID),
235 PROGRAM_UPDATE_DATE = decode(decode(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,SYSDATE),
236 PROGRAM_APPLICATION_ID = NVL(decode(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),PROGRAM_APPLICATION_ID),
237 LAST_UPDATE_DATE = SYSDATE,
238 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
239 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
240 WHERE ID = p_clev_rec.id;
241 -- Call action assembler if status is changed
242 If NVL(p_clev_rec.Call_Action_Asmblr,'Y') = 'Y' AND
243 p_clev_rec.old_sts_code is not null AND
244 p_clev_rec.new_sts_code is not null AND
245 p_clev_rec.old_ste_code is not null AND
246 p_clev_rec.new_ste_code is not null AND
247 (p_clev_rec.old_sts_code <> p_clev_rec.new_sts_code OR
248 p_clev_rec.old_ste_code <> p_clev_rec.new_ste_code)
249 Then
250
251 OKC_KL_STS_CHG_ASMBLR_PVT.Acn_Assemble(
252 p_api_version => p_api_version,
253 p_init_msg_list => p_init_msg_list,
254 x_return_status => x_return_status,
255 x_msg_count => x_msg_count,
256 x_msg_data => x_msg_data,
257 p_k_id => p_clev_rec.dnz_chr_id,
258 p_kl_id => p_clev_rec.id,
259 p_k_number => p_contract_number,
260 p_k_nbr_mod => p_contract_number_modifier,
261 p_kl_number => p_clev_rec.line_number,
262 p_kl_cur_sts_code => p_clev_rec.new_sts_code,
263 p_kl_cur_sts_type => p_clev_rec.new_ste_code,
264 p_kl_pre_sts_code => p_clev_rec.old_sts_code,
265 p_kl_pre_sts_type => p_clev_rec.old_ste_code,
266 p_kl_source_system_code => p_clev_rec.ORIG_SYSTEM_SOURCE_CODE);
267 End If;
268
269 -- Update minor version
270 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
271 If p_update_minor_version ='Y' Then
272 x_return_status := Update_Minor_Version(p_clev_rec.dnz_chr_id);
273
274 End If;
275 End If;
276 exception
277 when OTHERS then
278
279 -- store SQL error message on message stack
280 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
281 p_msg_name => g_unexpected_error,
282 p_token1 => g_sqlcode_token,
283 p_token1_value => sqlcode,
284 p_token2 => g_sqlerrm_token,
285 p_token2_value => sqlerrm);
286
287 -- notify caller of an UNEXPETED error
288 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
289 END update_contract_line;
290 -----------------------------------------------------------------
291 -- Begin LINE Status change procedure
292 -----------------------------------------------------------------
293
294 Procedure line_message(p_knum_and_mod IN VARCHAR2,
295 p_line_number IN VARCHAR2,
296 p_old_status IN VARCHAR2 DEFAULT NULL,
297 p_status IN VARCHAR2 DEFAULT NULL,
298 p_msg_data IN VARCHAR2 DEFAULT NULL,
299 p_type IN VARCHAR2) IS
300 BEGIN
301 if p_type='S' Then
302 /*
303 FND_MESSAGE.set_name('OKC','OKC_LINE_STS_CHANGE_SUCCESS');
304 FND_MESSAGE.set_token('CONTRACT_NUMBER',p_knum_and_mod);
305 FND_MESSAGE.set_token('LINE_NUMBER',p_line_number);
306 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
307 FND_MESSAGE.set_token('STATUS', p_status);
308 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
309 */
310 NULL;
311 elsif p_type='E'Then
312 get_fnd_msg_stack(p_msg_data);
313 FND_MESSAGE.set_name('OKC','OKC_LINE_STS_CHANGE_FAILURE');
314 FND_MESSAGE.set_token('CONTRACT_NUMBER',p_knum_and_mod);
315 FND_MESSAGE.set_token('LINE_NUMBER',p_line_number);
316 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
317 FND_MESSAGE.set_token('STATUS', p_status);
318 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
319 elsif p_type='U'Then
320 get_fnd_msg_stack(p_msg_data);
321 FND_MESSAGE.set_name('OKC',G_UNEXPECTED_ERROR);
322 FND_MESSAGE.set_token(G_SQLCODE_TOKEN,SQLCODE);
323 FND_MESSAGE.set_token(G_SQLERRM_TOKEN,SQLERRM);
324 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
325 FND_MESSAGE.set_name('OKC','OKC_LINE_STS_CHANGE_FAILURE');
326 FND_MESSAGE.set_token('CONTRACT_NUMBER',p_knum_and_mod);
327 FND_MESSAGE.set_token('LINE_NUMBER',p_line_number);
328 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
329 FND_MESSAGE.set_token('STATUS', p_status);
330 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
331 end if;
332 end line_message;
333
334
335 -- NEW --
336 -- BUG 4285665 --
337 -- GCHADHA --
338 -- Added the fix done for the bug 3967643 --
339 Procedure line_status_change (p_kid IN NUMBER DEFAULT NULL,
340 p_cls_code IN okc_subclasses_b.cls_code%TYPE DEFAULT NULL,
341 p_scs_code IN okc_k_headers_b.scs_code%TYPE DEFAULT NULL,
342 p_from_k IN VARCHAR2 DEFAULT NULL,
343 p_to_k IN VARCHAR2 DEFAULT NULL,
344 p_from_m IN VARCHAR2 DEFAULT NULL,
345 p_to_m IN VARCHAR2 DEFAULT NULL,
346 p_k_num IN okc_k_headers_b.contract_number%TYPE DEFAULT NULL,
347 p_k_num_mod IN okc_k_headers_b.contract_number_modifier%TYPE DEFAULT NULL,
348 p_update_minor_version IN VARCHAR2 DEFAULT 'Y',
349 x_return_status OUT NOCOPY VARCHAR2) IS
350 C number := 0;
351
352 l_knum_and_mod VARCHAR2(240) := p_k_num||' '||p_k_num_mod;
353 l_line_number VARCHAR2(150);
354 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
355 p_init_msg_list VARCHAR2(200) := okc_api.g_true;
356 x_msg_count NUMBER := okc_api.g_miss_num;
357 x_msg_data VARCHAR2(2000) := okc_api.g_miss_char;
358
359 l_cle_rec okc_contract_pub.clev_rec_type;
360 x_cle_rec okc_contract_pub.clev_rec_type;
361
362 TYPE line_rec_type IS RECORD (
363 contract_number okc_k_headers_b.contract_number%TYPE,
364 contract_number_modifier okc_k_headers_b.contract_number_modifier%TYPE,
365 ID okc_k_lines_b.id%TYPE,
366 OBJECT_VERSION_NUMBER okc_k_lines_b.object_version_number%TYPE,
367 STS_CODE okc_k_lines_b.sts_code%TYPE,
368 DATE_TERMINATED okc_k_lines_b.date_terminated%TYPE,
369 START_DATE okc_k_lines_b.start_date%TYPE,
370 END_DATE okc_k_lines_b.end_date%TYPE,
371 LINE_NUMBER okc_k_lines_b.line_number%TYPE,
372 PRICE_NEGOTIATED okc_k_lines_b.price_negotiated%TYPE,
373 dnz_chr_id okc_k_lines_b.dnz_chr_id%TYPE,
374 TERMINATION_REASON fnd_lookups.meaning%TYPE,
375 CODE okc_statuses_b.code%TYPE,
376 STE_CODE okc_statuses_b.ste_code%TYPE,
377 meaning okc_statuses_v.meaning%TYPE);
378
379 line_rec line_rec_type;
380 r_terminate_line_rec line_rec_type;
381 r_expired_line_rec line_rec_type;
382 r_active_line_rec line_rec_type;
383 r_signed_line_rec line_rec_type;
384
385 -- GCHADHA --
386 -- 4285665 --
387 TYPE line_Table_Type IS TABLE OF line_rec_type INDEX BY BINARY_INTEGER;
388 r_terminate_line_tbl line_Table_Type;
389 r_expired_line_tbl line_Table_Type;
390 r_active_line_tbl line_Table_Type;
391 r_signed_line_tbl line_Table_Type;
392 Type Num_Tbl_Type is table of NUMBER index by BINARY_INTEGER ;
393 Type Num9_Tbl_Type is table of NUMBER(9,0) index by BINARY_INTEGER ;
394 TYPE VC30_Tbl_Type is TABLE of VARCHAR2(30) index by BINARY_INTEGER ;
395 TYPE VC120_Tbl_Type is TABLE of VARCHAR2(120) index by BINARY_INTEGER ;
396 TYPE VC150_Tbl_Type is TABLE of VARCHAR2(150) index by BINARY_INTEGER ;
397 TYPE VC80_Tbl_Type is TABLE of VARCHAR2(80) index by BINARY_INTEGER ;
398 TYPE Date_Tbl_Type is TABLE of DATE index by BINARY_INTEGER ;
399
400 l_contract_number_tbl VC120_Tbl_Type;
401 l_contract_number_modifier_tbl VC120_Tbl_Type;
402 l_id_tbl Num_Tbl_Type;
403 l_object_version_number_tbl Num9_Tbl_Type;
404 l_sts_code_tbl VC120_Tbl_Type;
405 l_date_terminated_tbl Date_Tbl_Type;
406 l_start_date_tbl Date_Tbl_Type;
407 l_end_date_tbl Date_Tbl_Type;
408 l_line_number_tbl VC150_Tbl_Type;
409 l_price_negotiated_tbl Num_Tbl_Type;
410 l_dnz_chr_id_tbl Num_Tbl_Type;
411 l_termination_reason_tbl VC80_Tbl_Type;
412 l_code_tbl VC30_Tbl_Type;
413 l_ste_code_tbl VC30_Tbl_Type;
414 l_meaning_tbl VC30_Tbl_Type;
415 -- Used as Counters --
416 i NUMBER := 0;
417 x_num NUMBER := 0;
418
419 -- END GCHADHA --
420
421 -- Cursors -- Contract LINE Level
422
423 -- From ACTIVE/SIGNED to Terminated.
424 /* Commented for Bug #3967643. Split cursor into 3 parts based on
425 parameters as suggested by Appsperf Team
426 CURSOR c_terminate_line_all IS
427 SELECT chr.contract_number,
428 chr.contract_number_modifier,
429 line.ID ,
430 line.OBJECT_VERSION_NUMBER,
431 line.STS_CODE,
432 line.DATE_TERMINATED ,
433 line.START_DATE ,
434 line.END_DATE,
435 line.LINE_NUMBER,
436 line.PRICE_NEGOTIATED,
437 line.dnz_chr_id,
438 fnd.meaning TERMINATION_REASON,
439 status.CODE,
440 status.STE_CODE,
441 status.meaning
442 FROM OKC_K_LINES_B line,
443 OKC_STATUSES_V status,
444 fnd_lookups fnd,
445 okc_k_headers_b chr,
446 okc_subclasses_b scs
447 WHERE line.STS_CODE = status.CODE
448 AND line.dnz_chr_id = chr.id
449 AND chr.scs_code = scs.code
450 AND scs.cls_code <> 'OKL'
451 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
452 AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
453 AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
454 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
455 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
456 AND status.ste_code IN ('ACTIVE','HOLD','SIGNED')
457 AND line.date_terminated <= trunc(sysdate) + 0.99999
458 AND LINE.date_terminated >= trunc(l_last_rundate)
459 AND line.trn_code = fnd.lookup_code
460 AND fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON';
461 */
462 -- Bug #3967643. Split cursors into 3 parts as suggested by Appsperf Team
463 -- Hint added as suggested by Appsperf Team
464 -- From ACTIVE/SIGNED to Terminated
465
466 -- When only the From-to Contract number range is provided with optional Contract number modifier range
467 -- and optional Category code
468 CURSOR c_termnt_line_all_cntr IS
469 SELECT
470 CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
471 LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
472 LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
473 FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
474 FROM OKC_K_LINES_B LINE,
475 OKC_STATUSES_TL stst,
476 OKC_STATUSES_B stsb,
477 FND_LOOKUPS FND,
478 OKC_K_HEADERS_B CHR,
479 OKC_SUBCLASSES_B SCS
480 WHERE LINE.STS_CODE = STST.CODE
481 AND LINE.DNZ_CHR_ID = CHR.ID
482 AND stst.code = stsb.code
483 AND STST.LANGUAGE = USERENV('LANG')
484 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
485 AND CHR.CONTRACT_NUMBER >= p_from_k
486 AND CHR.CONTRACT_NUMBER <= p_to_k
487 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
488 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
489 --BUG 4915692 Gchadha
490 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
491 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
492 AND stsb.ste_code IN ('ACTIVE','SIGNED')
493 --END BUG 4915692 Gchadha
494 AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
495 AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
496 AND LINE.TRN_CODE = FND.LOOKUP_CODE
497 AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
498 --AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
499 AND chr.scs_code = scs.code
500 And scs.cls_code <> 'OKL';
501
502 -- When only Category is provided
503 CURSOR c_termnt_line_all_category IS
504 SELECT /*+ leading(stsb, stst, line) index(line OKC_K_LINES_N13) */ ---Modified hint for bug 12976183
505 CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
506 LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
507 LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
508 FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
509 FROM OKC_K_LINES_B LINE,
510 OKC_STATUSES_TL stst,
511 OKC_STATUSES_B stsb,
512 FND_LOOKUPS FND,
513 OKC_K_HEADERS_B CHR,
514 OKC_SUBCLASSES_B SCS
515 WHERE LINE.STS_CODE = STST.CODE
516 AND LINE.DNZ_CHR_ID = CHR.ID
517 AND stst.code = stsb.code
518 AND STST.LANGUAGE = USERENV('LANG')
519 AND chr.scs_code = p_scs_code
520 --BUG 4915692 Gchadha
521 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
522 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
523 AND stsb.ste_code IN ('ACTIVE','SIGNED')
524 --BUG 4915692 Gchadha
525 AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
526 AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
527 AND LINE.TRN_CODE = FND.LOOKUP_CODE
528 AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
529 --AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
530 And chr.scs_code = scs.code
531 And scs.cls_code <> 'OKL';
532
533 -- When no parameters are provided
534 CURSOR c_terminate_line_all IS
535 SELECT
536 CHR.CONTRACT_NUMBER, CHR.CONTRACT_NUMBER_MODIFIER, LINE.ID,
537 LINE.OBJECT_VERSION_NUMBER, LINE.STS_CODE, LINE.DATE_TERMINATED, LINE.START_DATE,
538 LINE.END_DATE, LINE.LINE_NUMBER, LINE.PRICE_NEGOTIATED, LINE.DNZ_CHR_ID,
539 FND.MEANING TERMINATION_REASON, STSB.CODE, STSB.STE_CODE, STST.MEANING
540 FROM OKC_K_LINES_B LINE,
541 OKC_STATUSES_TL stst,
542 OKC_STATUSES_B stsb,
543 FND_LOOKUPS FND,
544 OKC_K_HEADERS_B CHR,
545 OKC_SUBCLASSES_B SCS
546 WHERE LINE.STS_CODE = STST.CODE
547 AND LINE.DNZ_CHR_ID = CHR.ID
548 AND stst.code = stsb.code
549 AND STST.LANGUAGE = USERENV('LANG')
550 -- BUG 4915692 Gchadha
551 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
552 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
553 AND stsb.ste_code IN ('ACTIVE','SIGNED')
554 -- BUG 4915692 Gchadha
555 AND LINE.DATE_TERMINATED <= TRUNC(SYSDATE) + 0.99999
556 AND LINE.DATE_TERMINATED >= TRUNC(l_last_rundate)
557 AND LINE.TRN_CODE = FND.LOOKUP_CODE
558 AND FND.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
559 --AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code); -- new
560 And chr.scs_code = scs.code
561 And scs.cls_code <> 'OKL';
562
563 -- From ACTIVE/SIGNED/HOLD to EXPIRED
564 -- Hint added as per Bug 2563108 (Suggested by Appsperf Team)
565 -- Commented for Bug #3967643. Split cursor into 3 parts based on parameters
566 -- as suggested by Appsperf Team
567 --CURSOR c_expired_line_all IS
568 --SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
569 /* chr.contract_number,
570 chr.contract_number_modifier,
571 line.ID ,
572 line.OBJECT_VERSION_NUMBER,
573 line.STS_CODE,
574 line.DATE_TERMINATED ,
575 line.START_DATE ,
576 line.END_DATE,
577 line.LINE_NUMBER,
578 line.PRICE_NEGOTIATED,
579 line.dnz_chr_id,
580 status.CODE,
581 status.STE_CODE,
582 status.meaning
583 FROM OKC_K_LINES_B line,
584 OKC_STATUSES_V status,
585 OKC_K_HEADERS_B chr,
586 okc_subclasses_b scs
587 WHERE line.STS_CODE = status.CODE
588 AND line.dnz_chr_id = chr.id
589 AND chr.scs_code = scs.code
590 AND scs.cls_code <> 'OKL'
591 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
592 AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
593 AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
594 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
595 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
596 and status.ste_code in ('ACTIVE','SIGNED','HOLD') -- <> 'EXPIRED'
597 --
598 -- Bug 2672565 - Removed time component and changed from <= to <
599 --
600 --AND line.end_date <= trunc(sysdate) + 0.99999
601 AND line.end_date < trunc(sysdate)
602 AND line.end_date >= trunc(l_last_rundate)
603 AND (line.date_terminated IS NULL
604 OR line.date_terminated >= trunc(sysdate)); */
605
606 -- Bug #3967643. Split cursors into 3 parts as suggested by Appsperf Team
607 -- Hint added as suggested by Appsperf Team
608 -- From ACTIVE/SIGNED to EXPIRED
609
610 -- When only the From-to Contract number range is provided with optional Contract number modifier range
611 -- and optional Category code
612 CURSOR c_expr_line_all_cntr IS
613 SELECT
614 chr.contract_number,
615 chr.contract_number_modifier,
616 line.ID ,
617 line.OBJECT_VERSION_NUMBER,
618 line.STS_CODE,
619 line.DATE_TERMINATED ,
620 line.START_DATE ,
621 line.END_DATE,
622 line.LINE_NUMBER,
623 line.PRICE_NEGOTIATED,
624 line.dnz_chr_id,
625 stsb.CODE,
626 stsb.STE_CODE,
627 stst.meaning
628 FROM OKC_K_LINES_B line,
629 OKC_STATUSES_TL stst,
630 OKC_STATUSES_B stsb,
631 OKC_K_HEADERS_B chr
632 -- okc_subclasses_b scs
633 WHERE line.STS_CODE = stst.CODE
634 AND line.dnz_chr_id = chr.id
635 AND stst.code = stsb.code
636 AND STST.LANGUAGE = USERENV('LANG')
637 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
638 AND CHR.CONTRACT_NUMBER >= p_from_k
639 AND CHR.CONTRACT_NUMBER <= p_to_k
640 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
641 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
642 -- BUG 4915692 Gchadha
643 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
644 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
645 AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
646 --BUG 4915692 Gchadha
647 --
648 -- Bug 2672565 - Removed time component and changed from <= to <
649 --
650 --AND line.end_date <= trunc(sysdate) + 0.99999
651 AND line.end_date < trunc(sysdate)
652 AND line.end_date >= trunc(l_last_rundate)
653 AND (line.date_terminated IS NULL
654 OR line.date_terminated >= trunc(sysdate))
655 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
656
657
658 -- When only Category is provided
659 CURSOR c_expr_line_all_category IS
660 SELECT /*+ leading(stsb, stst, line) index(line okc_k_lines_b_n12) */ ---Modified hint for bug 12976183
661 chr.contract_number,
662 chr.contract_number_modifier,
663 line.ID ,
664 line.OBJECT_VERSION_NUMBER,
665 line.STS_CODE,
666 line.DATE_TERMINATED ,
667 line.START_DATE ,
668 line.END_DATE,
669 line.LINE_NUMBER,
670 line.PRICE_NEGOTIATED,
671 line.dnz_chr_id,
672 stsb.CODE,
673 stsb.STE_CODE,
674 stst.meaning
675 FROM OKC_K_LINES_B line,
676 -- OKC_STATUSES_V status,
677 OKC_STATUSES_TL stst,
678 OKC_STATUSES_B stsb,
679 OKC_K_HEADERS_B chr
680 -- okc_subclasses_b scs
681 WHERE line.STS_CODE = stst.CODE
682 AND line.dnz_chr_id = chr.id
683 AND stst.code = stsb.code
684 AND STST.LANGUAGE = USERENV('LANG')
685 AND chr.scs_code = p_scs_code
686 -- Bug 4915692 --
687 -- AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
688 -- AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
689 AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
690 -- Bug 4915692 --
691 --
692 -- Bug 2672565 - Removed time component and changed from <= to <
693 --
694 --AND line.end_date <= trunc(sysdate) + 0.99999
695 AND line.end_date < trunc(sysdate)
696 AND line.end_date >= trunc(l_last_rundate)
697 AND (line.date_terminated IS NULL
698 OR line.date_terminated >= trunc(sysdate))
699 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
700
701 -- When no parameters are is provided
702 CURSOR c_expired_line_all IS
703 SELECT
704 chr.contract_number,
705 chr.contract_number_modifier,
706 line.ID ,
707 line.OBJECT_VERSION_NUMBER,
708 line.STS_CODE,
709 line.DATE_TERMINATED ,
710 line.START_DATE ,
711 line.END_DATE,
712 line.LINE_NUMBER,
713 line.PRICE_NEGOTIATED,
714 line.dnz_chr_id,
715 stsb.CODE,
716 stsb.STE_CODE,
717 stst.meaning
718 FROM OKC_K_LINES_B line,
719 OKC_STATUSES_TL stst,
720 OKC_STATUSES_B stsb,
721 OKC_K_HEADERS_B chr
722 -- okc_subclasses_b scs
723 WHERE line.STS_CODE = stst.CODE
724 AND line.dnz_chr_id = chr.id
725 AND stst.code = stsb.code
726 AND STST.LANGUAGE = USERENV('LANG')
727 -- Bug 4915692 --
728 --AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
729 AND STSB.STE_CODE IN ('ACTIVE','SIGNED')
730
731 -- Bug 4915692 --
732 --
733 -- Bug 2672565 - Removed time component and changed from <= to <
734 --
735 --AND line.end_date <= trunc(sysdate) + 0.99999
736 AND line.end_date < trunc(sysdate)
737 AND line.end_date >= trunc(l_last_rundate)
738 AND (line.date_terminated IS NULL
739 OR line.date_terminated >= trunc(sysdate))
740 AND exists (select 1 from okc_subclasses_b scs where scs.cls_code <> 'OKL' AND chr.scs_code = scs.code);
741
742 -- LINE from SIGNED to ACTIVE
743 /* Commented for Bug #3967643. Split cursor into 3 parts based on
744 parameters as suggested by Appsperf Team
745 CURSOR c_active_line_all IS
746 SELECT chr.contract_number,
747 chr.contract_number_modifier,
748 line.ID ,
749 line.OBJECT_VERSION_NUMBER,
750 line.STS_CODE,
751 line.DATE_TERMINATED ,
752 line.START_DATE ,
753 line.END_DATE,
754 line.LINE_NUMBER,
755 line.PRICE_NEGOTIATED,
756 line.dnz_chr_id,
757 status.CODE,
758 status.STE_CODE,
759 status.meaning
760 FROM OKC_K_LINES_B line,
761 OKC_STATUSES_V status,
762 OKC_STATUSES_B status1,
763 okc_k_headers_b chr,
764 okc_subclasses_b scs
765 WHERE line.STS_CODE = status.CODE
766 AND line.dnz_chr_id = chr.id
767 AND chr.scs_code = scs.code
768 AND scs.cls_code <> 'OKL'
769 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
770 AND ((p_from_k is NULL) or (chr.CONTRACT_NUMBER >= p_from_k ))
771 AND ((p_to_k is NULL) or (chr.CONTRACT_NUMBER <= p_to_k ))
772 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
773 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
774 AND status.ste_code = 'SIGNED'
775 AND chr.STS_CODE = status1.CODE
776 AND status1.ste_code = 'ACTIVE'
777 AND line.start_date >= trunc(l_last_rundate) AND
778 line.start_date <= trunc(sysdate) + 0.99999
779 AND (line.date_terminated IS NULL
780 or line.date_terminated >= trunc(sysdate));
781 */
782 -- Bug #3967643. Split cursors into 3 parts as suggested by Appsperf Team
783 -- Hint added as suggested by Appsperf Team
784 -- LINE from SIGNED to ACTIVE
785
786 -- When only the From-to Contract number range is provided with optional Contract number modifier range
787 -- and optional Category code
788 CURSOR c_actv_line_all_cntr IS
789 SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
790 chr.contract_number,
791 chr.contract_number_modifier,
792 line.ID ,
793 line.OBJECT_VERSION_NUMBER,
794 line.STS_CODE,
795 line.DATE_TERMINATED ,
796 line.START_DATE ,
797 line.END_DATE,
798 line.LINE_NUMBER,
799 line.PRICE_NEGOTIATED,
800 line.dnz_chr_id,
801 status.CODE,
802 status.STE_CODE,
803 status.meaning
804 FROM OKC_K_LINES_B line,
805 OKC_STATUSES_V status,
806 OKC_STATUSES_B status1,
807 okc_k_headers_b chr,
808 okc_subclasses_b scs
809 WHERE line.STS_CODE = status.CODE
810 AND line.dnz_chr_id = chr.id
811 AND chr.scs_code = scs.code
812 AND scs.cls_code <> 'OKL'
813 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
814 AND chr.CONTRACT_NUMBER >= p_from_k
815 AND chr.CONTRACT_NUMBER <= p_to_k
816 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
817 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
818 AND status.ste_code = 'SIGNED'
819 AND chr.STS_CODE = status1.CODE
820 AND status1.ste_code = 'ACTIVE'
821 AND line.start_date >= trunc(l_last_rundate)
822 AND line.start_date <= trunc(sysdate) + 0.99999
823 AND (line.date_terminated IS NULL
824 or line.date_terminated >= trunc(sysdate));
825
826 -- When only Category is provided
827 CURSOR c_actv_line_all_category IS
828 SELECT /*+leading(status, line) index(line okc_k_lines_b_n11) */ ---Modified hint for bug 12976183
829 chr.contract_number,
830 chr.contract_number_modifier,
831 line.ID ,
832 line.OBJECT_VERSION_NUMBER,
833 line.STS_CODE,
834 line.DATE_TERMINATED ,
835 line.START_DATE ,
836 line.END_DATE,
837 line.LINE_NUMBER,
838 line.PRICE_NEGOTIATED,
839 line.dnz_chr_id,
840 status.CODE,
841 status.STE_CODE,
842 status.meaning
843 FROM OKC_K_LINES_B line,
844 OKC_STATUSES_V status,
845 OKC_STATUSES_B status1,
846 okc_k_headers_b chr,
847 okc_subclasses_b scs
848 WHERE line.STS_CODE = status.CODE
849 AND line.dnz_chr_id = chr.id
850 AND chr.scs_code = scs.code
851 AND scs.cls_code <> 'OKL'
852 AND CHR.SCS_CODE = p_scs_code
853 AND status.ste_code = 'SIGNED'
854 AND chr.STS_CODE = status1.CODE
855 AND status1.ste_code = 'ACTIVE'
856 AND line.start_date >= trunc(l_last_rundate)
857 AND line.start_date <= trunc(sysdate) + 0.99999
858 AND (line.date_terminated IS NULL
859 or line.date_terminated >= trunc(sysdate));
860
861 -- When no parameters are provided
862 CURSOR c_active_line_all IS
863 SELECT /*+leading(LINE) USE_NL(LINE CHR SCS FND.LV STATUS.STSB STATUS.STST))*/
864 chr.contract_number,
865 chr.contract_number_modifier,
866 line.ID ,
867 line.OBJECT_VERSION_NUMBER,
868 line.STS_CODE,
869 line.DATE_TERMINATED ,
870 line.START_DATE ,
871 line.END_DATE,
872 line.LINE_NUMBER,
873 line.PRICE_NEGOTIATED,
874 line.dnz_chr_id,
875 status.CODE,
876 status.STE_CODE,
877 status.meaning
878 FROM OKC_K_LINES_B line,
879 OKC_STATUSES_V status,
880 OKC_STATUSES_B status1,
881 okc_k_headers_b chr,
882 okc_subclasses_b scs
883 WHERE line.STS_CODE = status.CODE
884 AND line.dnz_chr_id = chr.id
885 AND chr.scs_code = scs.code
886 AND scs.cls_code <> 'OKL'
887 AND status.ste_code = 'SIGNED'
888 AND chr.STS_CODE = status1.CODE
889 AND status1.ste_code = 'ACTIVE'
890 AND line.start_date >= trunc(l_last_rundate)
891 AND line.start_date <= trunc(sysdate) + 0.99999
892 AND (line.date_terminated IS NULL
893 or line.date_terminated >= trunc(sysdate));
894
895 --bug 5930684
896 -- LINE from ACTIVE to SIGNED
897
898 -- When only the From-to Contract number range is provided with optional Contract number modifier range
899 -- and optional Category code
900 CURSOR c_sign_line_all_cntr IS
901 SELECT /*+leading(CHR) USE_NL(CHR SCS LINE FND.LV STATUS.STSB STATUS.STST))*/
902 chr.contract_number,
903 chr.contract_number_modifier,
904 line.ID ,
905 line.OBJECT_VERSION_NUMBER,
906 line.STS_CODE,
907 line.DATE_TERMINATED ,
908 line.START_DATE ,
909 line.END_DATE,
910 line.LINE_NUMBER,
911 line.PRICE_NEGOTIATED,
912 line.dnz_chr_id,
913 status.CODE,
914 status.STE_CODE,
915 status.meaning
916 FROM OKC_K_LINES_B line,
917 OKC_STATUSES_V status,
918 OKC_STATUSES_B status1,
919 okc_k_headers_b chr,
920 okc_subclasses_b scs
921 WHERE line.STS_CODE = status.CODE
922 AND line.dnz_chr_id = chr.id
923 AND chr.scs_code = scs.code
924 AND scs.cls_code <> 'OKL'
925 AND ((p_scs_code is NULL) or (chr.scs_code = p_scs_code))
926 AND chr.CONTRACT_NUMBER >= p_from_k
927 AND chr.CONTRACT_NUMBER <= p_to_k
928 AND ((p_from_m is NULL) or (chr.CONTRACT_NUMBER_modifier >= p_from_m ))
929 AND ((p_to_m is NULL) or (chr.CONTRACT_NUMBER_modifier <= p_to_m ))
930 AND status.ste_code = 'ACTIVE'
931 AND chr.STS_CODE = status1.CODE
932 AND status1.ste_code = 'SIGNED'
933 AND line.start_date >= trunc(l_last_rundate)
934 AND line.start_date >= trunc(sysdate) + 0.99999
935 AND (line.date_terminated IS NULL
936 or line.date_terminated >= trunc(sysdate));
937
938 -- When only Category is provided
939 CURSOR c_sign_line_all_category IS
940 SELECT /*+leading(SCS) USE_NL(SCS LINE CHR FND.LV STATUS.STSB STATUS.STST))*/
941 chr.contract_number,
942 chr.contract_number_modifier,
943 line.ID ,
944 line.OBJECT_VERSION_NUMBER,
945 line.STS_CODE,
946 line.DATE_TERMINATED ,
947 line.START_DATE ,
948 line.END_DATE,
949 line.LINE_NUMBER,
950 line.PRICE_NEGOTIATED,
951 line.dnz_chr_id,
952 status.CODE,
953 status.STE_CODE,
954 status.meaning
955 FROM OKC_K_LINES_B line,
956 OKC_STATUSES_V status,
957 OKC_STATUSES_B status1,
958 okc_k_headers_b chr,
959 okc_subclasses_b scs
960 WHERE line.STS_CODE = status.CODE
961 AND line.dnz_chr_id = chr.id
962 AND chr.scs_code = scs.code
963 AND scs.cls_code <> 'OKL'
964 AND CHR.SCS_CODE = p_scs_code
965 AND status.ste_code = 'ACTIVE'
966 AND chr.STS_CODE = status1.CODE
967 AND status1.ste_code = 'SIGNED'
968 AND line.start_date >= trunc(l_last_rundate)
969 AND line.start_date >= trunc(sysdate) + 0.99999
970 AND (line.date_terminated IS NULL
971 or line.date_terminated >= trunc(sysdate));
972
973 -- When no parameters are provided
974 CURSOR c_signed_line_all IS
975 SELECT /*+leading(LINE) USE_NL(LINE CHR SCS FND.LV STATUS.STSB STATUS.STST))*/
976 chr.contract_number,
977 chr.contract_number_modifier,
978 line.ID ,
979 line.OBJECT_VERSION_NUMBER,
980 line.STS_CODE,
981 line.DATE_TERMINATED ,
982 line.START_DATE ,
983 line.END_DATE,
984 line.LINE_NUMBER,
985 line.PRICE_NEGOTIATED,
986 line.dnz_chr_id,
987 status.CODE,
988 status.STE_CODE,
989 status.meaning
990 FROM OKC_K_LINES_B line,
991 OKC_STATUSES_V status,
992 OKC_STATUSES_B status1,
993 okc_k_headers_b chr,
994 okc_subclasses_b scs
995 WHERE line.STS_CODE = status.CODE
996 AND line.dnz_chr_id = chr.id
997 AND chr.scs_code = scs.code
998 AND scs.cls_code <> 'OKL'
999 AND status.ste_code = 'ACTIVE'
1000 AND chr.STS_CODE = status1.CODE
1001 AND status1.ste_code = 'SIGNED'
1002 AND line.start_date >= trunc(l_last_rundate)
1003 AND line.start_date >= trunc(sysdate) + 0.99999
1004 AND (line.date_terminated IS NULL
1005 or line.date_terminated >= trunc(sysdate));
1006 -- end of bug 5930684.
1007
1008 -- From ACTIVE/SIGNED to Terminated.
1009 CURSOR c_terminate_line_k IS
1010 SELECT line.ID ,
1011 line.OBJECT_VERSION_NUMBER,
1012 line.STS_CODE,
1013 line.DATE_TERMINATED ,
1014 line.START_DATE ,
1015 line.END_DATE,
1016 line.LINE_NUMBER,
1017 line.PRICE_NEGOTIATED,
1018 line.dnz_chr_id,
1019 fnd.meaning TERMINATION_REASON,
1020 status.CODE,
1021 status.STE_CODE,
1022 status.meaning
1023 FROM OKC_K_LINES_B line,
1024 OKC_STATUSES_V status,
1025 fnd_lookups fnd
1026 WHERE line.STS_CODE = status.CODE
1027 AND line.dnz_chr_id = p_kid
1028 --BUG 4915692 --
1029 --AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
1030 AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
1031 --Bug 4915692 --
1032 AND line.date_terminated <= trunc(sysdate) + 0.99999
1033 AND LINE.date_terminated >= trunc(l_last_rundate)
1034 AND line.trn_code = fnd.lookup_code
1035 AND fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON';
1036
1037 -- From ACTIVE/SIGNED to EXPIRED
1038 CURSOR c_expired_line_k IS
1039 SELECT line.ID ,
1040 line.OBJECT_VERSION_NUMBER,
1041 line.STS_CODE,
1042 line.DATE_TERMINATED ,
1043 line.START_DATE ,
1044 line.END_DATE,
1045 line.LINE_NUMBER,
1046 line.PRICE_NEGOTIATED,
1047 line.dnz_chr_id,
1048 status.CODE,
1049 status.STE_CODE,
1050 status.meaning
1051 FROM OKC_K_LINES_B line,
1052 OKC_STATUSES_V status
1053 WHERE line.STS_CODE = status.CODE
1054 AND line.dnz_chr_id = p_kid
1055 --BUG 4915692 --
1056 --AND STATUS.STE_CODE IN ('ACTIVE','HOLD','SIGNED')
1057 AND STATUS.STE_CODE IN ('ACTIVE','SIGNED')
1058 -- Bug 4915692 --
1059 --
1060 -- Bug 2672565 - Removed time component and changed <= to <
1061 --AND line.end_date <= trunc(sysdate) + 0.99999
1062 --
1063 AND line.end_date < trunc(sysdate)
1064 AND line.end_date >= trunc(l_last_rundate)
1065 AND (line.date_terminated IS NULL
1066 OR line.date_terminated >= trunc(sysdate));
1067
1068
1069 -- LINE from SIGNED to ACTIVE
1070 CURSOR c_active_line_k IS
1071 SELECT line.ID ,
1072 line.OBJECT_VERSION_NUMBER,
1073 line.STS_CODE,
1074 line.DATE_TERMINATED ,
1075 line.START_DATE ,
1076 line.END_DATE,
1077 line.LINE_NUMBER,
1078 line.PRICE_NEGOTIATED,
1079 line.dnz_chr_id,
1080 status.CODE,
1081 status.STE_CODE,
1082 status.meaning
1083 FROM OKC_K_LINES_B line,
1084 OKC_STATUSES_V status
1085 WHERE line.STS_CODE = status.CODE
1086 AND line.dnz_chr_id = p_kid
1087 AND status.ste_code = 'SIGNED'
1088 AND line.start_date >= trunc(l_last_rundate) AND
1089 line.start_date <= trunc(sysdate) + 0.99999
1090 AND (line.date_terminated IS NULL
1091 or line.date_terminated >= trunc(sysdate));
1092
1093 --bug 5930684
1094 -- LINE from ACTIVE to SIGNED
1095 CURSOR c_signed_line_k IS
1096 SELECT line.ID ,
1097 line.OBJECT_VERSION_NUMBER,
1098 line.STS_CODE,
1099 line.DATE_TERMINATED ,
1100 line.START_DATE ,
1101 line.END_DATE,
1102 line.LINE_NUMBER,
1103 line.PRICE_NEGOTIATED,
1104 line.dnz_chr_id,
1105 status.CODE,
1106 status.STE_CODE,
1107 status.meaning
1108 FROM OKC_K_LINES_B line,
1109 OKC_STATUSES_V status
1110 WHERE line.STS_CODE = status.CODE
1111 AND line.dnz_chr_id = p_kid
1112 AND status.ste_code = 'ACTIVE'
1113 AND line.start_date >= trunc(l_last_rundate) AND
1114 line.start_date >= trunc(sysdate) + 0.99999
1115 AND (line.date_terminated IS NULL
1116 or line.date_terminated >= trunc(sysdate));
1117 --end of bug 5930684
1118
1119 PROCEDURE line_terminate(p_term_line_rec IN line_rec_type) IS
1120 BEGIN
1121
1122
1123 if ((C >= T) and p_kid IS NULL) then
1124 commit;
1125 c := 0;
1126 end if;
1127 savepoint H_STATUS;
1128 p_line_count:= p_line_count + 1;
1129 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1130 l_knum_and_mod := p_term_line_rec.contract_number||' '||p_term_line_rec.contract_number_modifier;
1131 l_new_status := null;
1132 l_new_status_m := null;
1133 l_line_number := p_term_line_rec.line_number;
1134 if (h_status_type = 'TERMINATED') then
1135 l_new_status := h_status;
1136 l_new_status_m := h_status_m;
1137 else
1138 l_new_status := v_terminated ;
1139 l_new_status_m := v_terminated_m ;
1140 end if;
1141
1142 l_cle_rec.id := p_term_line_rec.id ;
1143 l_cle_rec.object_version_number := p_term_line_rec.object_version_number ;
1144 l_cle_rec.sts_code := l_new_status;
1145 l_cle_rec.dnz_chr_id := p_term_line_rec.dnz_chr_id;
1146 --
1147 -- Assign values if and only if the change in line status is not resulting
1148 -- from the change in header status
1149 ---- Bug# 1405237
1150 l_cle_rec.call_action_asmblr := 'N';
1151 --
1152 IF h_new_status is null and l_new_status is not null then
1153 l_cle_rec.old_sts_code := p_term_line_rec.sts_code;
1154 l_cle_rec.old_ste_code := p_term_line_rec.ste_code;
1155 l_cle_rec.new_sts_code := l_new_status;
1156 l_cle_rec.new_ste_code := 'TERMINATED';
1157 l_cle_rec.call_action_asmblr := 'Y';
1158 END IF;
1159
1160 --
1161 -- lock added not to depend on update implementation
1162 --
1163
1164
1165 okc_contract_pub.lock_contract_line(
1166 p_api_version => 1.0,
1167 p_init_msg_list => p_init_msg_list,
1168 x_return_status => l_return_status,
1169 x_msg_count => x_msg_count,
1170 x_msg_data => x_msg_data,
1171 p_clev_rec => l_cle_rec);
1172
1173 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1174 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1175 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1176 raise OKC_API.G_EXCEPTION_ERROR;
1177 END IF;
1178
1179 update_contract_line (
1180 p_api_version => 1.0,
1181 p_init_msg_list => p_init_msg_list,
1182 p_contract_number => p_term_line_rec.contract_number,
1183 p_contract_number_modifier => p_term_line_rec.contract_number_modifier,
1184 p_update_minor_version => p_update_minor_version,
1185 x_return_status => l_return_status,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 p_clev_rec => l_cle_rec,
1189 x_clev_rec => x_cle_rec);
1190
1191 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1192 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1193 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1194 raise OKC_API.G_EXCEPTION_ERROR;
1195 END IF;
1196
1197
1198 OKC_KL_TERM_ASMBLR_PVT.acn_assemble(
1199 p_api_version => 1.0 ,
1200 p_init_msg_list => p_init_msg_list,
1201 x_return_status => l_return_status,
1202 x_msg_count => x_msg_count,
1203 x_msg_data => x_msg_data,
1204 p_k_class => p_cls_code,
1205 p_k_id => p_kid,
1206 p_kl_id => p_term_line_rec.id,
1207 p_kl_term_date => p_term_line_rec.date_terminated,
1208 p_kl_term_reason => p_term_line_rec.termination_reason,
1209 p_k_number => p_term_line_rec.contract_number,
1210 p_k_nbr_mod => p_term_line_rec.contract_number_modifier,
1211 p_k_subclass => p_scs_code,
1212 P_KL_STATUS_CODE => p_term_line_rec.STS_CODE,
1213 p_estimated_amount => p_term_line_rec.price_negotiated );
1214 --
1215 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1216 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1217 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1218 raise OKC_API.G_EXCEPTION_ERROR;
1219 END IF;
1220
1221 OKC_TIME_RES_PUB.res_time_termnt_k(
1222 P_CHR_ID => NULL,
1223 P_CLE_ID => p_term_line_rec.id,
1224 P_END_DATE => p_term_line_rec.DATE_TERMINATED,
1225 P_API_VERSION => 1.0 ,
1226 p_init_msg_list => p_init_msg_list,
1227 x_return_status => l_return_status
1228 );
1229 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1230 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1231 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1232 raise OKC_API.G_EXCEPTION_ERROR;
1233 END IF;
1234 --
1235 if p_kid is NULL then
1236 c := c+1;
1237 --
1238
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_type =>'S');
1244 end if;
1245
1246
1247 EXCEPTION
1248 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR 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 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1259 line_message(p_knum_and_mod =>l_knum_and_mod,
1260 p_line_number=>l_line_number,
1261 p_old_status =>p_term_line_rec.meaning,
1262 p_status =>l_new_status,
1263 p_msg_data => x_msg_data,
1264 p_type =>'E');
1265 p_line_errors := p_line_errors +1 ;
1266 x_return_status := OKC_API.G_RET_STS_ERROR;
1267 rollback to H_STATUS;
1268 WHEN OTHERS then
1269 line_message(p_knum_and_mod =>l_knum_and_mod,
1270 p_line_number=>l_line_number,
1271 p_old_status =>p_term_line_rec.meaning,
1272 p_status =>l_new_status,
1273 p_msg_data => x_msg_data,
1274 p_type =>'U');
1275 p_line_errors := p_line_errors +1 ;
1276 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1277 rollback to H_STATUS;
1278 END line_terminate;
1279
1280 PROCEDURE line_expire(p_exp_line_rec IN line_rec_type) IS
1281 BEGIN
1282
1283 if ((C >= T) and p_kid IS NULL) then
1284 commit;
1285 c := 0;
1286 end if;
1287 savepoint H_STATUS;
1288 p_line_count:= p_line_count + 1;
1289 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1290 l_knum_and_mod := p_exp_line_rec.contract_number||' '||p_exp_line_rec.contract_number_modifier;
1291 l_new_status := null;
1292 l_new_status_m := null;
1293
1294 l_line_number := p_exp_line_rec.line_number;
1295 if (h_status_type = 'EXPIRED') then
1296 l_new_status := h_status;
1297 l_new_status_m := h_status_m;
1298 else
1299 l_new_status := v_expired ;
1300 l_new_status_m := v_expired_m ;
1301 end if;
1302
1303 l_cle_rec.id := p_exp_line_rec.id ;
1304 l_cle_rec.object_version_number := p_exp_line_rec.object_version_number ;
1305 l_cle_rec.sts_code := l_new_status;
1306 l_cle_rec.dnz_chr_id := p_exp_line_rec.dnz_chr_id;
1307 --
1308 --
1309 -- Start: Added for Status Change Action Assembler Changes 10/19/2000
1310 --
1311 -- Assign values if and oly if the change in line status is not resulting
1312 -- from the change in header status
1313 --
1314 l_cle_rec.call_action_asmblr := 'N';
1315 IF h_new_status is null and l_new_status is not null then
1316 l_cle_rec.old_sts_code := p_exp_line_rec.sts_code;
1317 l_cle_rec.old_ste_code := p_exp_line_rec.ste_code;
1318 l_cle_rec.new_sts_code := l_new_status;
1319 l_cle_rec.new_ste_code := 'EXPIRED';
1320 l_cle_rec.call_action_asmblr := 'Y';
1321 END IF;
1322
1323 -- lock added not to depend on update implementation
1324 --
1325
1326 okc_contract_pub.lock_contract_line(
1327 p_api_version => 1.0,
1328 p_init_msg_list => p_init_msg_list,
1329 x_return_status => l_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data,
1332 p_clev_rec => l_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
1342 update_contract_line(
1343 p_api_version => 1.0,
1344 p_init_msg_list => p_init_msg_list,
1345 p_update_minor_version => p_update_minor_version,
1346 p_contract_number => p_exp_line_rec.contract_number,
1347 p_contract_number_modifier => p_exp_line_rec.contract_number_modifier,
1348 x_return_status => l_return_status,
1349 x_msg_count => x_msg_count,
1350 x_msg_data => x_msg_data,
1351 p_clev_rec => l_cle_rec,
1352 x_clev_rec => x_cle_rec);
1353
1354
1355 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1356 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1357 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1358 raise OKC_API.G_EXCEPTION_ERROR;
1359 END IF;
1360 --
1361 if p_kid is NULL then
1362 c := c+1;
1363 --
1364
1365
1366 line_message(p_knum_and_mod =>l_knum_and_mod,
1367 p_line_number=>l_line_number,
1368 p_status =>l_new_status,
1369 p_old_status =>p_exp_line_rec.meaning,
1370 p_type =>'S');
1371 end if;
1372
1373 EXCEPTION
1374 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR 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 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1385 line_message(p_knum_and_mod =>l_knum_and_mod,
1386 p_line_number=>l_line_number,
1387 p_status =>l_new_status,
1388 p_old_status =>p_exp_line_rec.meaning,
1389 p_msg_data => x_msg_data,
1390 p_type =>'E');
1391 p_line_errors := p_line_errors +1 ;
1392 x_return_status := OKC_API.G_RET_STS_ERROR;
1393 rollback to H_STATUS;
1394 WHEN OTHERS then
1395 line_message(p_knum_and_mod =>l_knum_and_mod,
1396 p_line_number=>l_line_number,
1397 p_status =>l_new_status,
1398 p_old_status =>p_exp_line_rec.meaning,
1399 p_msg_data => x_msg_data,
1400 p_type =>'U');
1401 p_line_errors := p_line_errors +1 ;
1402 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1403 rollback to H_STATUS;
1404 END line_expire;
1405
1406 PROCEDURE line_active(p_active_line_rec IN line_rec_type) IS
1407 BEGIN
1408
1409
1410 if ((C >= T) and p_kid IS NULL) then
1411 commit;
1412 c := 0;
1413 end if;
1414 savepoint H_STATUS;
1415 p_line_count:= p_line_count + 1;
1416 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1417 l_knum_and_mod := p_active_line_rec.contract_number||' '||p_active_line_rec.contract_number_modifier;
1418 l_new_status := null;
1419 l_new_status_m := null;
1420
1421 l_line_number := p_active_line_rec.line_number;
1422 if (h_status_type = 'ACTIVE') then
1423 l_new_status := h_status;
1424 l_new_status_m := h_status_m;
1425 else
1426 l_new_status := v_active;
1427 l_new_status_m := v_active_m ;
1428 end if;
1429
1430 l_cle_rec.id := p_active_line_rec.id ;
1431 l_cle_rec.object_version_number := p_active_line_rec.object_version_number ;
1432 l_cle_rec.sts_code := l_new_status;
1433 l_cle_rec.dnz_chr_id := p_active_line_rec.dnz_chr_id;
1434 --
1435 --
1436 -- Assign values if and oly if the change in line status is not resulting
1437 -- from the change in header status
1438 --
1439 l_cle_rec.call_action_asmblr := 'N';
1440 --
1441 IF h_new_status is null and l_new_status is not null then
1442 l_cle_rec.old_sts_code := p_active_line_rec.sts_code;
1443 l_cle_rec.old_ste_code := p_active_line_rec.ste_code;
1444 l_cle_rec.new_sts_code := l_new_status;
1445 l_cle_rec.new_ste_code := 'ACTIVE';
1446 l_cle_rec.call_action_asmblr := 'Y';
1447 END IF;
1448
1449 --
1450 -- lock added not to depend on update implementation
1451 --
1452
1453 okc_contract_pub.lock_contract_line(
1454 p_api_version => 1.0,
1455 p_init_msg_list => p_init_msg_list,
1456 x_return_status => l_return_status,
1457 x_msg_count => x_msg_count,
1458 x_msg_data => x_msg_data,
1459 p_clev_rec => l_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
1469 update_contract_line (
1470 p_api_version => 1.0,
1471 p_init_msg_list => p_init_msg_list,
1472 p_update_minor_version => p_update_minor_version,
1473 p_contract_number => p_active_line_rec.contract_number,
1474 p_contract_number_modifier => p_active_line_rec.contract_number_modifier,
1475 x_return_status => l_return_status,
1476 x_msg_count => x_msg_count,
1477 x_msg_data => x_msg_data,
1478 p_clev_rec => l_cle_rec,
1479 x_clev_rec => x_cle_rec);
1480
1481
1482 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1483 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1484 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1485 raise OKC_API.G_EXCEPTION_ERROR;
1486 END IF;
1487 --
1488 if p_kid is NULL then
1489 c := c+1;
1490
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_type =>'S');
1496 end if;
1497
1498
1499 EXCEPTION
1500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR 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 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1511 line_message(p_knum_and_mod =>l_knum_and_mod,
1512 p_line_number=>l_line_number,
1513 p_status =>l_new_status,
1514 p_old_status =>p_active_line_rec.meaning,
1515 p_msg_data => x_msg_data,
1516 p_type =>'E');
1517 p_line_errors := p_line_errors +1 ;
1518 x_return_status := OKC_API.G_RET_STS_ERROR;
1519 rollback to H_STATUS;
1520 WHEN OTHERS then
1521 line_message(p_knum_and_mod =>l_knum_and_mod,
1522 p_line_number=>l_line_number,
1523 p_status =>l_new_status,
1524 p_old_status =>p_active_line_rec.meaning,
1525 p_msg_data => x_msg_data,
1526 p_type =>'U');
1527 p_line_errors := p_line_errors +1 ;
1528 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1529 rollback to H_STATUS;
1530 END line_active;
1531
1532 --bug 5930684
1533 PROCEDURE line_signed(p_signed_line_rec IN line_rec_type) IS
1534 BEGIN
1535
1536
1537 if ((C >= T) and p_kid IS NULL) then
1538 commit;
1539 c := 0;
1540 end if;
1541 savepoint H_STATUS;
1542 p_line_count:= p_line_count + 1;
1543 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1544 l_knum_and_mod := p_signed_line_rec.contract_number||' '||p_signed_line_rec.contract_number_modifier;
1545 l_new_status := null;
1546 l_new_status_m := null;
1547
1548 l_line_number := p_signed_line_rec.line_number;
1549 if (h_status_type = 'SIGNED') then
1550 l_new_status := h_status;
1551 l_new_status_m := h_status_m;
1552 else
1553 l_new_status := v_signed;
1554 l_new_status_m := v_signed_m ;
1555 end if;
1556
1557 l_cle_rec.id := p_signed_line_rec.id ;
1558 l_cle_rec.object_version_number := p_signed_line_rec.object_version_number ;
1559 l_cle_rec.sts_code := l_new_status;
1560 l_cle_rec.dnz_chr_id := p_signed_line_rec.dnz_chr_id;
1561 --
1562 --
1563 -- Assign values if and oly if the change in line status is not resulting
1564 -- from the change in header status
1565 --
1566 l_cle_rec.call_action_asmblr := 'N';
1567 --
1568 IF h_new_status is null and l_new_status is not null then
1569 l_cle_rec.old_sts_code := p_signed_line_rec.sts_code;
1570 l_cle_rec.old_ste_code := p_signed_line_rec.ste_code;
1571 l_cle_rec.new_sts_code := l_new_status;
1572 l_cle_rec.new_ste_code := 'SIGNED';
1573 l_cle_rec.call_action_asmblr := 'Y';
1574 END IF;
1575
1576 --
1577 -- lock added not to depend on update implementation
1578 --
1579
1580 okc_contract_pub.lock_contract_line(
1581 p_api_version => 1.0,
1582 p_init_msg_list => p_init_msg_list,
1583 x_return_status => l_return_status,
1584 x_msg_count => x_msg_count,
1585 x_msg_data => x_msg_data,
1586 p_clev_rec => l_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
1596 update_contract_line (
1597 p_api_version => 1.0,
1598 p_init_msg_list => p_init_msg_list,
1599 p_update_minor_version => p_update_minor_version,
1600 p_contract_number => p_signed_line_rec.contract_number,
1601 p_contract_number_modifier => p_signed_line_rec.contract_number_modifier,
1602 x_return_status => l_return_status,
1603 x_msg_count => x_msg_count,
1604 x_msg_data => x_msg_data,
1605 p_clev_rec => l_cle_rec,
1606 x_clev_rec => x_cle_rec);
1607
1608
1609 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1610 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1611 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1612 raise OKC_API.G_EXCEPTION_ERROR;
1613 END IF;
1614 --
1615 if p_kid is NULL then
1616 c := c+1;
1617
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_type =>'S');
1623 end if;
1624
1625
1626 EXCEPTION
1627 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR 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 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1638 line_message(p_knum_and_mod =>l_knum_and_mod,
1639 p_line_number=>l_line_number,
1640 p_status =>l_new_status,
1641 p_old_status =>p_signed_line_rec.meaning,
1642 p_msg_data => x_msg_data,
1643 p_type =>'E');
1644 p_line_errors := p_line_errors +1 ;
1645 x_return_status := OKC_API.G_RET_STS_ERROR;
1646 rollback to H_STATUS;
1647 WHEN OTHERS then
1648 line_message(p_knum_and_mod =>l_knum_and_mod,
1649 p_line_number=>l_line_number,
1650 p_status =>l_new_status,
1651 p_old_status =>p_signed_line_rec.meaning,
1652 p_msg_data => x_msg_data,
1653 p_type =>'U');
1654 p_line_errors := p_line_errors +1 ;
1655 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1656 rollback to H_STATUS;
1657 END line_signed;
1658 -- end of bug 5930684
1659
1660 -- New procedure Added to Delete the --
1661 -- table type after each iteration --
1662 Procedure delete_table_type IS
1663 BEGIN
1664 l_Contract_number_tbl.delete;
1665 l_Contract_number_modifier_tbl.delete;
1666 l_Id_tbl.delete;
1667 l_Object_version_number_tbl.delete;
1668 l_sts_code_tbl.delete;
1669 l_date_terminated_tbl.delete;
1670 l_start_date_tbl.delete;
1671 l_end_date_tbl.delete;
1672 l_line_number_tbl.delete;
1673 l_price_negotiated_tbl.delete;
1674 l_dnz_chr_id_tbl.delete;
1675 l_termination_reason_tbl.delete;
1676 l_code_tbl.delete;
1677 l_ste_code_tbl.delete;
1678 l_meaning_tbl.delete;
1679 END delete_table_type;
1680
1681
1682 BEGIN
1683
1684
1685 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1686 --added for bug fix 5285247
1687 Savepoint H_STATUS;
1688 l_new_status := null;
1689 l_new_status_m := null;
1690 -- terminate line
1691
1692 IF (h_status = 'TERMINATED') OR (h_status IS NULL) THEN
1693 IF p_kid IS NULL THEN
1694
1695 /* Commented for Bug #3967643.
1696 FOR r_terminate_line in c_terminate_line_all LOOP
1697 r_terminate_line_rec.contract_number := r_terminate_line.contract_number;
1698 r_terminate_line_rec.contract_number_modifier := r_terminate_line.contract_number_modifier;
1699 r_terminate_line_rec.id := r_terminate_line.id;
1700 r_terminate_line_rec.object_version_number := r_terminate_line.object_version_number;
1701 r_terminate_line_rec.sts_code := r_terminate_line.sts_code;
1702 r_terminate_line_rec.date_terminated := r_terminate_line.date_terminated;
1703 r_terminate_line_rec.start_date := r_terminate_line.start_date;
1704 r_terminate_line_rec.end_date := r_terminate_line.end_date;
1705 r_terminate_line_rec.line_number := r_terminate_line.line_number;
1706 r_terminate_line_rec.price_negotiated := r_terminate_line.price_negotiated;
1707 r_terminate_line_rec.dnz_chr_id := r_terminate_line.dnz_chr_id;
1708 r_terminate_line_rec.termination_reason := r_terminate_line.termination_reason;
1709 r_terminate_line_rec.code := r_terminate_line.code;
1710 r_terminate_line_rec.ste_code := r_terminate_line.ste_code;
1711 r_terminate_line_rec.meaning := r_terminate_line.meaning;
1712 line_terminate(r_terminate_line_rec);
1713 END LOOP; -- r_line_cursor_AHS
1714 commit;
1715 c:= 0; */
1716 -- Added for BUG #3967643
1717 IF p_from_k IS NOT NULL THEN
1718
1719 -- When only the From-to Contract number range is provided
1720 x_num :=0;
1721 open c_termnt_line_all_cntr;
1722 LOOP -- I
1723 FETCH c_termnt_line_all_cntr BULK COLLECT INTO
1724 l_Contract_number_tbl,
1725 l_Contract_number_modifier_tbl,
1726 l_Id_tbl,
1727 l_Object_version_number_tbl,
1728 l_sts_code_tbl,
1729 l_date_terminated_tbl,
1730 l_start_date_tbl,
1731 l_end_date_tbl,
1732 l_line_number_tbl,
1733 l_price_negotiated_tbl,
1734 l_dnz_chr_id_tbl,
1735 l_termination_reason_tbl,
1736 l_code_tbl,
1737 l_ste_code_tbl,
1738 l_meaning_tbl
1739 LIMIT 1000;
1740 IF (l_Id_tbl.COUNT < 1) THEN
1741 EXIT;
1742 END IF;
1743 IF (l_Id_tbl.COUNT > 0) THEN
1744
1745 i := l_Id_tbl.FIRST;
1746 LOOP --II
1747 r_terminate_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
1748 r_terminate_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
1749 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1750 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1751 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1752 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1753 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1754 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1755 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1756 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1757 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1758 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1759 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1760 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1761 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1762 -- line_terminate(r_terminate_line_rec);
1763 x_num :=x_num+1;
1764 EXIT WHEN (i = l_Id_tbl.LAST);
1765 i := l_Id_tbl.NEXT(i);
1766 END LOOP; --II
1767
1768 END IF;
1769 Exit when c_termnt_line_all_cntr%NOTFOUND;
1770 END LOOP; --I
1771 IF(r_terminate_line_tbl.COUNT > 0) Then
1772 i := r_terminate_line_tbl.FIRST;
1773 LOOP
1774 line_terminate(r_terminate_line_tbl(i));
1775 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1776 i := r_terminate_line_tbl.NEXT(i);
1777 END LOOP;
1778 END IF;
1779 commit;
1780 c:=0;
1781 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
1782 -- When only Category is provided
1783 x_num :=0;
1784 open c_termnt_line_all_category;
1785 LOOP -- I
1786 FETCH c_termnt_line_all_category BULK COLLECT INTO
1787 l_Contract_number_tbl,
1788 l_Contract_number_modifier_tbl,
1789 l_Id_tbl,
1790 l_Object_version_number_tbl,
1791 l_sts_code_tbl,
1792 l_date_terminated_tbl,
1793 l_start_date_tbl,
1794 l_end_date_tbl,
1795 l_line_number_tbl,
1796 l_price_negotiated_tbl,
1797 l_dnz_chr_id_tbl,
1798 l_termination_reason_tbl,
1799 l_code_tbl,
1800 l_ste_code_tbl,
1801 l_meaning_tbl
1802 LIMIT 1000;
1803 IF (l_Id_tbl.COUNT < 1) THEN
1804 EXIT;
1805 END IF;
1806 IF (l_Id_tbl.COUNT > 0) THEN
1807 i := l_Id_tbl.FIRST;
1808 LOOP -- II
1809 r_terminate_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
1810 r_terminate_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
1811 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1812 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1813 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1814 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1815 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1816 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1817 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1818 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1819 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1820 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1821 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1822 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1823 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1824 -- line_terminate(r_terminate_line_rec);
1825 x_num :=x_num+1;
1826 EXIT WHEN (i = l_Id_tbl.LAST);
1827 i := l_Id_tbl.NEXT(i);
1828 END LOOP; --II
1829 END IF;
1830 Exit when c_termnt_line_all_category%NOTFOUND;
1831 END LOOP; --I
1832 IF(r_terminate_line_tbl.COUNT > 0) Then
1833 i := r_terminate_line_tbl.FIRST;
1834 LOOP
1835 line_terminate(r_terminate_line_tbl(i));
1836 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1837 i := r_terminate_line_tbl.NEXT(i);
1838 END LOOP;
1839 END IF;
1840 commit;
1841 c:=0;
1842 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
1843 -- When no parameters are provided
1844 x_num :=0;
1845 open c_terminate_line_all;
1846 LOOP -- I
1847 FETCH c_terminate_line_all BULK COLLECT INTO
1848 l_Contract_number_tbl,
1849 l_Contract_number_modifier_tbl,
1850 l_Id_tbl,
1851 l_Object_version_number_tbl,
1852 l_sts_code_tbl,
1853 l_date_terminated_tbl,
1854 l_start_date_tbl,
1855 l_end_date_tbl,
1856 l_line_number_tbl,
1857 l_price_negotiated_tbl,
1858 l_dnz_chr_id_tbl,
1859 l_termination_reason_tbl,
1860 l_code_tbl,
1861 l_ste_code_tbl,
1862 l_meaning_tbl
1863 LIMIT 1000;
1864 IF (l_Id_tbl.COUNT < 1) THEN
1865 EXIT;
1866 END IF;
1867 IF (l_Id_tbl.COUNT > 0) THEN
1868 i := l_Id_tbl.FIRST;
1869 LOOP --II
1870 r_terminate_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
1871 r_terminate_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
1872 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1873 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1874 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1875 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1876 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1877 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1878 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1879 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1880 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1881 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1882 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1883 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1884 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1885 -- line_terminate(r_terminate_line_rec);
1886 x_num :=x_num+1;
1887 EXIT WHEN (i = l_Id_tbl.LAST);
1888 i := l_Id_tbl.NEXT(i);
1889 END LOOP; --II
1890 END IF;
1891 Exit when c_terminate_line_all%NOTFOUND;
1892 END LOOP; --I
1893 IF(r_terminate_line_tbl.COUNT > 0) Then
1894 i := r_terminate_line_tbl.FIRST;
1895 LOOP
1896 line_terminate(r_terminate_line_tbl(i));
1897 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1898 i := r_terminate_line_tbl.NEXT(i);
1899 END LOOP;
1900 END IF;
1901 commit;
1902 c:=0;
1903 END IF;-- Added for BUG #3967643
1904 ELSE -- p_kid IS NULL
1905 x_num :=0;
1906 open c_terminate_line_k;
1907 LOOP -- I
1908 FETCH c_terminate_line_k BULK COLLECT INTO
1909 l_Id_tbl,
1910 l_Object_version_number_tbl,
1911 l_sts_code_tbl,
1912 l_date_terminated_tbl,
1913 l_start_date_tbl,
1914 l_end_date_tbl,
1915 l_line_number_tbl,
1916 l_price_negotiated_tbl,
1917 l_dnz_chr_id_tbl,
1918 l_termination_reason_tbl,
1919 l_code_tbl,
1920 l_ste_code_tbl,
1921 l_meaning_tbl
1922 LIMIT 1000;
1923 IF (l_Id_tbl.COUNT < 1) THEN
1924 EXIT;
1925 END IF;
1926 IF (l_Id_tbl.COUNT > 0) THEN
1927 i := l_Id_tbl.FIRST;
1928 LOOP -- II
1929 r_terminate_line_tbl(x_num).contract_number := p_k_num;
1930 r_terminate_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
1931 r_terminate_line_tbl(x_num).id := l_Id_tbl(i);
1932 r_terminate_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
1933 r_terminate_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
1934 r_terminate_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
1935 r_terminate_line_tbl(x_num).start_date := l_start_date_tbl(i);
1936 r_terminate_line_tbl(x_num).end_date := l_end_date_tbl(i);
1937 r_terminate_line_tbl(x_num).line_number := l_line_number_tbl(i);
1938 r_terminate_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
1939 r_terminate_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
1940 r_terminate_line_tbl(x_num).termination_reason := l_termination_reason_tbl(i);
1941 r_terminate_line_tbl(x_num).code := l_code_tbl(i);
1942 r_terminate_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
1943 r_terminate_line_tbl(x_num).meaning := l_meaning_tbl(i);
1944 x_num :=x_num+1;
1945 EXIT WHEN (i = l_Id_tbl.LAST);
1946 i := l_Id_tbl.NEXT(i);
1947 END LOOP; --II
1948 END IF;
1949 Exit when c_terminate_line_k%NOTFOUND;
1950 END LOOP; --I
1951 IF(r_terminate_line_tbl.COUNT > 0) Then
1952 i := r_terminate_line_tbl.FIRST;
1953 LOOP
1954 line_terminate(r_terminate_line_tbl(i));
1955 EXIT WHEN (i = r_terminate_line_tbl.LAST);
1956 i := r_terminate_line_tbl.NEXT(i);
1957 END LOOP;
1958 END IF;
1959 END IF;
1960 END IF; -- End if for termination condition.
1961
1962 l_new_status := null;
1963 l_new_status_m := null;
1964 l_knum_and_mod := null;
1965 l_line_number := null;
1966
1967 delete_table_type;
1968 ---------------------------------------------------------------------------------
1969 --Added for bug 5402421
1970 Savepoint H_STATUS;
1971
1972 l_return_status := OKC_API.G_RET_STS_SUCCESS;
1973 -- From ACTIVE/SIGNED to Expired
1974 IF (h_status = 'EXPIRED') OR (h_status IS NULL) THEN
1975 IF p_kid IS NULL THEN
1976 /* Commented for Bug #3967643.
1977 FOR r_expired_line in c_expired_line_all LOOP
1978 r_expired_line_rec.contract_number := r_expired_line.contract_number;
1979 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
1980 r_expired_line_rec.id := r_expired_line.id;
1981 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
1982 r_expired_line_rec.sts_code := r_expired_line.sts_code;
1983 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
1984 r_expired_line_rec.start_date := r_expired_line.start_date;
1985 r_expired_line_rec.end_date := r_expired_line.end_date;
1986 r_expired_line_rec.line_number := r_expired_line.line_number;
1987 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
1988 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
1989 r_expired_line_rec.termination_reason := NULL;
1990 r_expired_line_rec.code := r_expired_line.code;
1991 r_expired_line_rec.ste_code := r_expired_line.ste_code;
1992 r_expired_line_rec.meaning := r_expired_line.meaning;
1993 line_expire(r_expired_line_rec);
1994 END LOOP; -- r_line_cursor_AHS
1995 commit;
1996 c:= 0;*/
1997 -- Added for BUG #3967643
1998 IF p_from_k IS NOT NULL THEN
1999
2000 -- When only the From-to Contract number range is provided
2001 /* FOR r_expired_line in c_expr_line_all_cntr LOOP
2002
2003
2004 r_expired_line_rec.contract_number := r_expired_line.contract_number;
2005 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
2006 r_expired_line_rec.id := r_expired_line.id;
2007 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2008 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2009 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2010 r_expired_line_rec.start_date := r_expired_line.start_date;
2011 r_expired_line_rec.end_date := r_expired_line.end_date;
2012 r_expired_line_rec.line_number := r_expired_line.line_number;
2013 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2014 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2015 r_expired_line_rec.termination_reason := NULL;
2016 r_expired_line_rec.code := r_expired_line.code;
2017 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2018 r_expired_line_rec.meaning := r_expired_line.meaning;
2019
2020 line_expire(r_expired_line_rec);
2021 END LOOP; -- r_line_cursor_AHS
2022 commit;
2023 c:= 0;*/
2024 x_num :=0;
2025 open c_expr_line_all_cntr;
2026 LOOP -- I
2027 FETCH c_expr_line_all_cntr BULK COLLECT INTO
2028
2029 l_Contract_number_tbl,
2030 l_Contract_number_modifier_tbl,
2031 l_Id_tbl,
2032 l_Object_version_number_tbl,
2033 l_sts_code_tbl,
2034 l_date_terminated_tbl,
2035 l_start_date_tbl,
2036 l_end_date_tbl,
2037 l_line_number_tbl,
2038 l_price_negotiated_tbl,
2039 l_dnz_chr_id_tbl,
2040 l_code_tbl,
2041 l_ste_code_tbl,
2042 l_meaning_tbl
2043 LIMIT 1000;
2044 IF (l_Id_tbl.COUNT < 1) THEN
2045 EXIT;
2046 END IF;
2047 IF (l_Id_tbl.COUNT > 0) THEN
2048 i := l_Id_tbl.FIRST;
2049 LOOP -- II
2050 r_expired_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2051 r_expired_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2052 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2053 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2054 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2055 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2056 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2057 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2058 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2059 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2060 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2061 r_expired_line_tbl(x_num).termination_reason :=NULL;
2062 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2063 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2064 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2065 x_num :=x_num+1;
2066 EXIT WHEN (i = l_Id_tbl.LAST);
2067 i := l_Id_tbl.NEXT(i);
2068 END LOOP; --II
2069 END IF;
2070 Exit when c_expr_line_all_cntr%NOTFOUND;
2071 END LOOP; --I
2072 IF(r_expired_line_tbl.COUNT > 0) Then
2073 i := r_expired_line_tbl.FIRST;
2074 LOOP
2075 line_expire(r_expired_line_tbl(i));
2076 EXIT WHEN (i = r_expired_line_tbl.LAST);
2077 i := r_expired_line_tbl.NEXT(i);
2078 END LOOP;
2079 END IF;
2080 commit;
2081 c:=0;
2082 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
2083
2084 -- When only Category is provided
2085 /*FOR r_expired_line in c_expr_line_all_category LOOP
2086
2087 r_expired_line_rec.contract_number := r_expired_line.contract_number;
2088 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
2089 r_expired_line_rec.id := r_expired_line.id;
2090 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2091 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2092 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2093 r_expired_line_rec.start_date := r_expired_line.start_date;
2094 r_expired_line_rec.end_date := r_expired_line.end_date;
2095 r_expired_line_rec.line_number := r_expired_line.line_number;
2096 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2097 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2098 r_expired_line_rec.termination_reason := NULL;
2099 r_expired_line_rec.code := r_expired_line.code;
2100 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2101 r_expired_line_rec.meaning := r_expired_line.meaning;
2102
2103 line_expire(r_expired_line_rec);
2104
2105 END LOOP; -- r_line_cursor_AHS */
2106 x_num :=0;
2107 open c_expr_line_all_category;
2108 LOOP --I
2109 FETCH c_expr_line_all_category BULK COLLECT INTO
2110 l_Contract_number_tbl,
2111 l_Contract_number_modifier_tbl,
2112 l_Id_tbl,
2113 l_Object_version_number_tbl,
2114 l_sts_code_tbl,
2115 l_date_terminated_tbl,
2116 l_start_date_tbl,
2117 l_end_date_tbl,
2118 l_line_number_tbl,
2119 l_price_negotiated_tbl,
2120 l_dnz_chr_id_tbl,
2121 l_code_tbl,
2122 l_ste_code_tbl,
2123 l_meaning_tbl
2124 LIMIT 1000;
2125 IF (l_Id_tbl.COUNT < 1) THEN
2126 EXIT;
2127 END IF;
2128 IF (l_Id_tbl.COUNT > 0) THEN
2129 i := l_Id_tbl.FIRST;
2130 LOOP --II
2131 r_expired_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2132 r_expired_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2133 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2134 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2135 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2136 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2137 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2138 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2139 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2140 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2141 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2142 r_expired_line_tbl(x_num).termination_reason := NULL;
2143 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2144 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2145 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2146 x_num :=x_num+1;
2147 EXIT WHEN (i = l_Id_tbl.LAST);
2148 i := l_Id_tbl.NEXT(i);
2149 END LOOP; --II
2150 END IF;
2151 Exit when c_expr_line_all_category%NOTFOUND;
2152 END LOOP; --I
2153 IF(r_expired_line_tbl.COUNT > 0) Then
2154 i := r_expired_line_tbl.FIRST;
2155 LOOP
2156 line_expire(r_expired_line_tbl(i));
2157 EXIT WHEN (i = r_expired_line_tbl.LAST);
2158 i := r_expired_line_tbl.NEXT(i);
2159 END LOOP;
2160 END IF;
2161 commit;
2162 c:= 0;
2163 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
2164
2165 -- When no parameters are provided
2166 /* FOR r_expired_line in c_expired_line_all LOOP
2167
2168 r_expired_line_rec.contract_number := r_expired_line.contract_number;
2169 r_expired_line_rec.contract_number_modifier := r_expired_line.contract_number_modifier;
2170 r_expired_line_rec.id := r_expired_line.id;
2171 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2172 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2173 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2174 r_expired_line_rec.start_date := r_expired_line.start_date;
2175 r_expired_line_rec.end_date := r_expired_line.end_date;
2176 r_expired_line_rec.line_number := r_expired_line.line_number;
2177 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2178 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2179 r_expired_line_rec.termination_reason := NULL;
2180 r_expired_line_rec.code := r_expired_line.code;
2181 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2182 r_expired_line_rec.meaning := r_expired_line.meaning;
2183
2184
2185 line_expire(r_expired_line_rec);
2186 END LOOP; -- r_line_cursor_AHS
2187 commit;
2188 c:= 0; */
2189 x_num :=0;
2190 open c_expired_line_all;
2191 LOOP --I
2192 FETCH c_expired_line_all BULK COLLECT INTO
2193 l_Contract_number_tbl,
2194 l_Contract_number_modifier_tbl,
2195 l_Id_tbl,
2196 l_Object_version_number_tbl,
2197 l_sts_code_tbl,
2198 l_date_terminated_tbl,
2199 l_start_date_tbl,
2200 l_end_date_tbl,
2201 l_line_number_tbl,
2202 l_price_negotiated_tbl,
2203 l_dnz_chr_id_tbl,
2204 l_code_tbl,
2205 l_ste_code_tbl,
2206 l_meaning_tbl
2207 LIMIT 1000;
2208 IF (l_Id_tbl.COUNT < 1) THEN
2209 EXIT;
2210 END IF;
2211 IF (l_Id_tbl.COUNT > 0) THEN
2212 i := l_Id_tbl.FIRST;
2213 LOOP --II
2214 r_expired_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2215 r_expired_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2216 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2217 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2218 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2219 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2220 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2221 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2222 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2223 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2224 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2225 r_expired_line_tbl(x_num).termination_reason :=NULL;
2226 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2227 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2228 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2229 x_num :=x_num+1;
2230 EXIT WHEN (i = l_Id_tbl.LAST);
2231 i := l_Id_tbl.NEXT(i);
2232 END LOOP; --II
2233 END IF;
2234 Exit when c_expired_line_all%NOTFOUND;
2235 END LOOP; --I
2236 IF(r_expired_line_tbl.COUNT > 0) Then
2237 i := r_expired_line_tbl.FIRST;
2238 LOOP
2239 line_expire(r_expired_line_tbl(i));
2240 EXIT WHEN (i = r_expired_line_tbl.LAST);
2241 i := r_expired_line_tbl.NEXT(i);
2242 END LOOP;
2243 END IF;
2244 commit;
2245 c:= 0;
2246
2247 END IF;-- Added for BUG #3967643
2248 ELSE -- p_kid IS NULL
2249
2250 /* FOR r_expired_line in c_expired_line_k LOOP
2251
2252
2253 r_expired_line_rec.contract_number := p_k_num;
2254 r_expired_line_rec.contract_number_modifier := p_k_num_mod;
2255 r_expired_line_rec.id := r_expired_line.id;
2256 r_expired_line_rec.object_version_number := r_expired_line.object_version_number;
2257 r_expired_line_rec.sts_code := r_expired_line.sts_code;
2258 r_expired_line_rec.date_terminated := r_expired_line.date_terminated;
2259 r_expired_line_rec.start_date := r_expired_line.start_date;
2260 r_expired_line_rec.end_date := r_expired_line.end_date;
2261 r_expired_line_rec.line_number := r_expired_line.line_number;
2262 r_expired_line_rec.price_negotiated := r_expired_line.price_negotiated;
2263 r_expired_line_rec.dnz_chr_id := r_expired_line.dnz_chr_id;
2264 r_expired_line_rec.termination_reason := NULL;
2265 r_expired_line_rec.code := r_expired_line.code;
2266 r_expired_line_rec.ste_code := r_expired_line.ste_code;
2267 r_expired_line_rec.meaning := r_expired_line.meaning;
2268
2269 line_expire(r_expired_line_rec);
2270 END LOOP; -- r_line_cursor_AHS */
2271 x_num :=0;
2272 open c_expired_line_k;
2273 LOOP -- I
2274 FETCH c_expired_line_k BULK COLLECT INTO
2275 l_Id_tbl,
2276 l_Object_version_number_tbl,
2277 l_sts_code_tbl,
2278 l_date_terminated_tbl,
2279 l_start_date_tbl,
2280 l_end_date_tbl,
2281 l_line_number_tbl,
2282 l_price_negotiated_tbl,
2283 l_dnz_chr_id_tbl,
2284 l_code_tbl,
2285 l_ste_code_tbl,
2286 l_meaning_tbl
2287 LIMIT 1000;
2288 IF (l_Id_tbl.COUNT < 1) THEN
2289 EXIT;
2290 END IF;
2291 IF (l_Id_tbl.COUNT > 0) THEN
2292 i := l_Id_tbl.FIRST;
2293 LOOP --II
2294 r_expired_line_tbl(x_num).contract_number := p_k_num;
2295 r_expired_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
2296 r_expired_line_tbl(x_num).id := l_Id_tbl(i);
2297 r_expired_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2298 r_expired_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2299 r_expired_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2300 r_expired_line_tbl(x_num).start_date := l_start_date_tbl(i);
2301 r_expired_line_tbl(x_num).end_date := l_end_date_tbl(i);
2302 r_expired_line_tbl(x_num).line_number := l_line_number_tbl(i);
2303 r_expired_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2304 r_expired_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2305 r_expired_line_tbl(x_num).termination_reason := NULL;
2306 r_expired_line_tbl(x_num).code := l_code_tbl(i);
2307 r_expired_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2308 r_expired_line_tbl(x_num).meaning := l_meaning_tbl(i);
2309 x_num :=x_num+1;
2310 EXIT WHEN (i = l_Id_tbl.LAST);
2311 i := l_Id_tbl.NEXT(i);
2312 END LOOP; --II
2313 END IF;
2314 Exit when c_expired_line_k%NOTFOUND;
2315 END LOOP; --I
2316 IF(r_expired_line_tbl.COUNT > 0) Then
2317 i := r_expired_line_tbl.FIRST;
2318 LOOP
2319 line_expire(r_expired_line_tbl(i));
2320 EXIT WHEN (i = r_expired_line_tbl.LAST);
2321 i := r_expired_line_tbl.NEXT(i);
2322 END LOOP;
2323 END IF;
2324 END IF;
2325 END IF; --
2326 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2327 l_new_status := null;
2328 l_new_status_m := null;
2329 l_knum_and_mod := null;
2330 l_line_number := null;
2331
2332 delete_table_type;
2333
2334 --added for bug fix 5402421
2335 Savepoint H_STATUS;
2336
2337 -- From Signed to Active
2338 IF h_status = 'ACTIVE' OR h_status IS NULL THEN
2339 IF p_kid IS NULL THEN
2340 /* Commented for Bug #3967643.
2341 FOR r_active_line in c_active_line_all LOOP
2342 r_active_line_rec.contract_number := r_active_line.contract_number;
2343 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2344 r_active_line_rec.id := r_active_line.id;
2345 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2346 r_active_line_rec.sts_code := r_active_line.sts_code;
2347 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2348 r_active_line_rec.start_date := r_active_line.start_date;
2349 r_active_line_rec.end_date := r_active_line.end_date;
2350 r_active_line_rec.line_number := r_active_line.line_number;
2351 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2352 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2353 r_active_line_rec.termination_reason := NULL;
2354 r_active_line_rec.code := r_active_line.code;
2355 r_active_line_rec.ste_code := r_active_line.ste_code;
2356 r_active_line_rec.meaning := r_active_line.meaning;
2357 line_active(r_active_line_rec);
2358 END LOOP; -- r_line_cursor_AHS
2359 commit;
2360 c:= 0;*/
2361 -- Added for BUG #3967643
2362 IF p_from_k IS NOT NULL THEN
2363 -- When only the From-to Contract number range is provided
2364 /* FOR r_active_line in c_actv_line_all_cntr LOOP
2365 r_active_line_rec.contract_number := r_active_line.contract_number;
2366 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2367 r_active_line_rec.id := r_active_line.id;
2368 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2369 r_active_line_rec.sts_code := r_active_line.sts_code;
2370 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2371 r_active_line_rec.start_date := r_active_line.start_date;
2372 r_active_line_rec.end_date := r_active_line.end_date;
2373 r_active_line_rec.line_number := r_active_line.line_number;
2374 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2375 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2376 r_active_line_rec.termination_reason := NULL;
2377 r_active_line_rec.code := r_active_line.code;
2378 r_active_line_rec.ste_code := r_active_line.ste_code;
2379 r_active_line_rec.meaning := r_active_line.meaning;
2380
2381
2382 line_active(r_active_line_rec);
2383 END LOOP; -- r_line_cursor_AHS
2384 commit;
2385 c:= 0; */
2386 x_num :=0;
2387 open c_actv_line_all_cntr;
2388 LOOP --I
2389 FETCH c_actv_line_all_cntr BULK COLLECT INTO
2390 l_Contract_number_tbl,
2391 l_Contract_number_modifier_tbl,
2392 l_Id_tbl,
2393 l_Object_version_number_tbl,
2394 l_sts_code_tbl,
2395 l_date_terminated_tbl,
2396 l_start_date_tbl,
2397 l_end_date_tbl,
2398 l_line_number_tbl,
2399 l_price_negotiated_tbl,
2400 l_dnz_chr_id_tbl,
2401 l_code_tbl,
2402 l_ste_code_tbl,
2403 l_meaning_tbl
2404 LIMIT 1000;
2405 IF (l_Id_tbl.COUNT < 1) THEN
2406 EXIT;
2407 END IF;
2408 IF (l_Id_tbl.COUNT > 0) THEN
2409 i := l_Id_tbl.FIRST;
2410 LOOP --II
2411 r_active_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2412 r_active_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2413 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2414 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2415 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2416 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2417 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2418 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2419 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2420 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2421 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2422 r_active_line_tbl(x_num).termination_reason := NULL;
2423 r_active_line_tbl(x_num).code := l_code_tbl(i);
2424 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2425 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2426 x_num :=x_num+1;
2427 EXIT WHEN (i = l_Id_tbl.LAST);
2428 i := l_Id_tbl.NEXT(i);
2429 END LOOP; --II
2430 END IF;
2431 Exit when c_actv_line_all_cntr%NOTFOUND;
2432 END LOOP; --I
2433 IF(r_active_line_tbl.COUNT > 0) Then
2434 i := r_active_line_tbl.FIRST;
2435 LOOP
2436 line_active(r_active_line_tbl(i));
2437 EXIT WHEN (i = r_active_line_tbl.LAST);
2438 i := r_active_line_tbl.NEXT(i);
2439 END LOOP;
2440 END IF;
2441 commit;
2442 c:= 0;
2443
2444 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
2445 -- When only Category is provided
2446 /* FOR r_active_line in c_actv_line_all_category LOOP
2447
2448
2449 r_active_line_rec.contract_number := r_active_line.contract_number;
2450 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2451 r_active_line_rec.id := r_active_line.id;
2452 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2453 r_active_line_rec.sts_code := r_active_line.sts_code;
2454 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2455 r_active_line_rec.start_date := r_active_line.start_date;
2456 r_active_line_rec.end_date := r_active_line.end_date;
2457 r_active_line_rec.line_number := r_active_line.line_number;
2458 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2459 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2460 r_active_line_rec.termination_reason := NULL;
2461 r_active_line_rec.code := r_active_line.code;
2462 r_active_line_rec.ste_code := r_active_line.ste_code;
2463 r_active_line_rec.meaning := r_active_line.meaning;
2464
2465 line_active(r_active_line_rec);
2466 END LOOP; -- r_line_cursor_AHS
2467 commit;
2468 c:= 0;*/
2469 x_num :=0;
2470 open c_actv_line_all_category;
2471 LOOP --I
2472 FETCH c_actv_line_all_category BULK COLLECT INTO
2473 l_Contract_number_tbl,
2474 l_Contract_number_modifier_tbl,
2475 l_Id_tbl,
2476 l_Object_version_number_tbl,
2477 l_sts_code_tbl,
2478 l_date_terminated_tbl,
2479 l_start_date_tbl,
2480 l_end_date_tbl,
2481 l_line_number_tbl,
2482 l_price_negotiated_tbl,
2483 l_dnz_chr_id_tbl,
2484 l_code_tbl,
2485 l_ste_code_tbl,
2486 l_meaning_tbl
2487 LIMIT 1000;
2488 IF (l_Id_tbl.COUNT < 1) THEN
2489 EXIT;
2490 END IF;
2491 IF (l_Id_tbl.COUNT > 0) THEN
2492 i := l_Id_tbl.FIRST;
2493 LOOP --II
2494 r_active_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2495 r_active_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2496 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2497 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2498 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2499 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2500 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2501 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2502 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2503 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2504 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2505 r_active_line_tbl(x_num).termination_reason := NULL;
2506 r_active_line_tbl(x_num).code := l_code_tbl(i);
2507 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2508 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2509 x_num :=x_num+1;
2510 EXIT WHEN (i = l_Id_tbl.LAST);
2511 i := l_Id_tbl.NEXT(i);
2512 END LOOP; --II
2513 END IF;
2514 Exit when c_actv_line_all_category%NOTFOUND;
2515 END LOOP; --I
2516 IF(r_active_line_tbl.COUNT > 0) Then
2517 i := r_active_line_tbl.FIRST;
2518 LOOP
2519 line_active(r_active_line_tbl(i));
2520 EXIT WHEN (i = r_active_line_tbl.LAST);
2521 i := r_active_line_tbl.NEXT(i);
2522 END LOOP;
2523 END IF;
2524 commit;
2525 c:= 0;
2526 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
2527 -- When no parameters are provided
2528 /* FOR r_active_line in c_active_line_all LOOP
2529
2530
2531 r_active_line_rec.contract_number := r_active_line.contract_number;
2532 r_active_line_rec.contract_number_modifier := r_active_line.contract_number_modifier;
2533 r_active_line_rec.id := r_active_line.id;
2534 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2535 r_active_line_rec.sts_code := r_active_line.sts_code;
2536 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2537 r_active_line_rec.start_date := r_active_line.start_date;
2538 r_active_line_rec.end_date := r_active_line.end_date;
2539 r_active_line_rec.line_number := r_active_line.line_number;
2540 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2541 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2542 r_active_line_rec.termination_reason := NULL;
2543 r_active_line_rec.code := r_active_line.code;
2544 r_active_line_rec.ste_code := r_active_line.ste_code;
2545 r_active_line_rec.meaning := r_active_line.meaning;
2546
2547 line_active(r_active_line_rec);
2548 END LOOP; -- r_line_cursor_AHS */
2549 x_num :=0;
2550 open c_active_line_all;
2551 LOOP --I
2552 FETCH c_active_line_all BULK COLLECT INTO
2553 l_Contract_number_tbl,
2554 l_Contract_number_modifier_tbl,
2555 l_Id_tbl,
2556 l_Object_version_number_tbl,
2557 l_sts_code_tbl,
2558 l_date_terminated_tbl,
2559 l_start_date_tbl,
2560 l_end_date_tbl,
2561 l_line_number_tbl,
2562 l_price_negotiated_tbl,
2563 l_dnz_chr_id_tbl,
2564 l_code_tbl,
2565 l_ste_code_tbl,
2566 l_meaning_tbl
2567 LIMIT 1000;
2568 IF (l_Id_tbl.COUNT < 1) THEN
2569 EXIT;
2570 END IF;
2571 IF (l_Id_tbl.COUNT > 0) THEN
2572 i := l_Id_tbl.FIRST;
2573 LOOP --II
2574 r_active_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2575 r_active_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2576 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2577 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2578 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2579 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2580 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2581 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2582 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2583 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2584 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2585 r_active_line_tbl(x_num).termination_reason := NULL;
2586 r_active_line_tbl(x_num).code := l_code_tbl(i);
2587 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2588 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2589 x_num :=x_num+1;
2590 EXIT WHEN (i = l_Id_tbl.LAST);
2591 i := l_Id_tbl.NEXT(i);
2592 END LOOP; --II
2593 END IF;
2594 Exit when c_active_line_all%NOTFOUND;
2595 END LOOP; --I
2596 IF(r_active_line_tbl.COUNT > 0) Then
2597 i := r_active_line_tbl.FIRST;
2598 LOOP
2599 line_active(r_active_line_tbl(i));
2600 EXIT WHEN (i = r_active_line_tbl.LAST);
2601 i := r_active_line_tbl.NEXT(i);
2602 END LOOP;
2603 END IF;
2604
2605 commit;
2606 c:= 0;
2607 END IF;-- Added for BUG #3967643
2608 ELSE -- p_kid IS NULL
2609
2610 /* FOR r_active_line in c_active_line_k LOOP
2611
2612
2613 r_active_line_rec.contract_number := p_k_num;
2614 r_active_line_rec.contract_number_modifier := p_k_num_mod;
2615 r_active_line_rec.id := r_active_line.id;
2616 r_active_line_rec.object_version_number := r_active_line.object_version_number;
2617 r_active_line_rec.sts_code := r_active_line.sts_code;
2618 r_active_line_rec.date_terminated := r_active_line.date_terminated;
2619 r_active_line_rec.start_date := r_active_line.start_date;
2620 r_active_line_rec.end_date := r_active_line.end_date;
2621 r_active_line_rec.line_number := r_active_line.line_number;
2622 r_active_line_rec.price_negotiated := r_active_line.price_negotiated;
2623 r_active_line_rec.dnz_chr_id := r_active_line.dnz_chr_id;
2624 r_active_line_rec.termination_reason := NULL;
2625 r_active_line_rec.code := r_active_line.code;
2626 r_active_line_rec.ste_code := r_active_line.ste_code;
2627 r_active_line_rec.meaning := r_active_line.meaning;
2628
2629 line_active(r_active_line_rec);
2630 END LOOP; -- r_line_cursor_AHS */
2631 x_num :=0;
2632 open c_active_line_k;
2633 LOOP --I
2634 FETCH c_active_line_k BULK COLLECT INTO
2635 l_Id_tbl,
2636 l_Object_version_number_tbl,
2637 l_sts_code_tbl,
2638 l_date_terminated_tbl,
2639 l_start_date_tbl,
2640 l_end_date_tbl,
2641 l_line_number_tbl,
2642 l_price_negotiated_tbl,
2643 l_dnz_chr_id_tbl,
2644 l_code_tbl,
2645 l_ste_code_tbl,
2646 l_meaning_tbl
2647 LIMIT 1000;
2648 IF (l_Id_tbl.COUNT < 1) THEN
2649 EXIT;
2650 END IF;
2651 IF (l_Id_tbl.COUNT > 0) THEN
2652 i := l_Id_tbl.FIRST;
2653 LOOP --II
2654 r_active_line_tbl(x_num).contract_number := p_k_num;
2655 r_active_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
2656 r_active_line_tbl(x_num).id := l_Id_tbl(i);
2657 r_active_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2658 r_active_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2659 r_active_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2660 r_active_line_tbl(x_num).start_date := l_start_date_tbl(i);
2661 r_active_line_tbl(x_num).end_date := l_end_date_tbl(i);
2662 r_active_line_tbl(x_num).line_number := l_line_number_tbl(i);
2663 r_active_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2664 r_active_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2665 r_active_line_tbl(x_num).termination_reason := NULL;
2666 r_active_line_tbl(x_num).code := l_code_tbl(i);
2667 r_active_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2668 r_active_line_tbl(x_num).meaning := l_meaning_tbl(i);
2669 x_num :=x_num+1;
2670 EXIT WHEN (i = l_Id_tbl.LAST);
2671 i := l_Id_tbl.NEXT(i);
2672 END LOOP; --II
2673 END IF;
2674 Exit when c_active_line_k%NOTFOUND;
2675 END LOOP; --I
2676 IF(r_active_line_tbl.COUNT > 0) Then
2677 i := r_active_line_tbl.FIRST;
2678 LOOP
2679 line_active(r_active_line_tbl(i));
2680 EXIT WHEN (i = r_active_line_tbl.LAST);
2681 i := r_active_line_tbl.NEXT(i);
2682 END LOOP;
2683 END IF;
2684 END IF;
2685 END IF;
2686
2687 Savepoint H_STATUS;
2688
2689 l_return_status := OKC_API.G_RET_STS_SUCCESS;
2690 l_new_status := null;
2691 l_new_status_m := null;
2692 l_knum_and_mod := null;
2693 l_line_number := null;
2694
2695 delete_table_type;
2696 -------------------------------------------------------------------------
2697 -- BUG 5930684
2698 -- From Active To Signed
2699 IF h_status = 'SIGNED' OR h_status IS NULL THEN
2700 IF p_kid IS NULL THEN
2701 -- Added for BUG #3967643
2702 IF p_from_k IS NOT NULL THEN
2703 -- When only the From-to Contract number range is provided
2704 x_num :=0;
2705 open c_sign_line_all_cntr;
2706 LOOP --I
2707 FETCH c_sign_line_all_cntr BULK COLLECT INTO
2708 l_Contract_number_tbl,
2709 l_Contract_number_modifier_tbl,
2710 l_Id_tbl,
2711 l_Object_version_number_tbl,
2712 l_sts_code_tbl,
2713 l_date_terminated_tbl,
2714 l_start_date_tbl,
2715 l_end_date_tbl,
2716 l_line_number_tbl,
2717 l_price_negotiated_tbl,
2718 l_dnz_chr_id_tbl,
2719 l_code_tbl,
2720 l_ste_code_tbl,
2721 l_meaning_tbl
2722 LIMIT 1000;
2723 IF (l_Id_tbl.COUNT < 1) THEN
2724 EXIT;
2725 END IF;
2726 IF (l_Id_tbl.COUNT > 0) THEN
2727 i := l_Id_tbl.FIRST;
2728 LOOP --II
2729 r_signed_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2730 r_signed_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2731 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2732 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2733 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2734 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2735 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2736 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2737 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2738 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2739 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2740 r_signed_line_tbl(x_num).termination_reason := NULL;
2741 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2742 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2743 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2744 x_num :=x_num+1;
2745 EXIT WHEN (i = l_Id_tbl.LAST);
2746 i := l_Id_tbl.NEXT(i);
2747 END LOOP; --II
2748 END IF;
2749 Exit when c_sign_line_all_cntr%NOTFOUND;
2750 END LOOP; --I
2751 IF(r_signed_line_tbl.COUNT > 0) Then
2752 i := r_signed_line_tbl.FIRST;
2753 LOOP
2754 line_signed(r_signed_line_tbl(i));
2755 EXIT WHEN (i = r_signed_line_tbl.LAST);
2756 i := r_signed_line_tbl.NEXT(i);
2757 END LOOP;
2758 END IF;
2759 commit;
2760 c:= 0;
2761
2762 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NOT NULL) THEN
2763 -- When only Category is provided
2764
2765 x_num :=0;
2766 open c_sign_line_all_category;
2767 LOOP --I
2768 FETCH c_sign_line_all_category BULK COLLECT INTO
2769 l_Contract_number_tbl,
2770 l_Contract_number_modifier_tbl,
2771 l_Id_tbl,
2772 l_Object_version_number_tbl,
2773 l_sts_code_tbl,
2774 l_date_terminated_tbl,
2775 l_start_date_tbl,
2776 l_end_date_tbl,
2777 l_line_number_tbl,
2778 l_price_negotiated_tbl,
2779 l_dnz_chr_id_tbl,
2780 l_code_tbl,
2781 l_ste_code_tbl,
2782 l_meaning_tbl
2783 LIMIT 1000;
2784 IF (l_Id_tbl.COUNT < 1) THEN
2785 EXIT;
2786 END IF;
2787 IF (l_Id_tbl.COUNT > 0) THEN
2788 i := l_Id_tbl.FIRST;
2789 LOOP --II
2790 r_signed_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2791 r_signed_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2792 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2793 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2794 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2795 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2796 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2797 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2798 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2799 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2800 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2801 r_signed_line_tbl(x_num).termination_reason := NULL;
2802 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2803 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2804 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2805 x_num :=x_num+1;
2806 EXIT WHEN (i = l_Id_tbl.LAST);
2807 i := l_Id_tbl.NEXT(i);
2808 END LOOP; --II
2809 END IF;
2810 Exit when c_sign_line_all_category%NOTFOUND;
2811 END LOOP; --I
2812 IF(r_signed_line_tbl.COUNT > 0) Then
2813 i := r_signed_line_tbl.FIRST;
2814 LOOP
2815 line_signed(r_signed_line_tbl(i));
2816 EXIT WHEN (i = r_signed_line_tbl.LAST);
2817 i := r_signed_line_tbl.NEXT(i);
2818 END LOOP;
2819 END IF;
2820 commit;
2821 c:= 0;
2822 ELSIF (p_from_k IS NULL) AND (p_scs_code IS NULL) THEN
2823 -- When no parameters are provided
2824
2825 x_num :=0;
2826 open c_signed_line_all;
2827 LOOP --I
2828 FETCH c_signed_line_all BULK COLLECT INTO
2829 l_Contract_number_tbl,
2830 l_Contract_number_modifier_tbl,
2831 l_Id_tbl,
2832 l_Object_version_number_tbl,
2833 l_sts_code_tbl,
2834 l_date_terminated_tbl,
2835 l_start_date_tbl,
2836 l_end_date_tbl,
2837 l_line_number_tbl,
2838 l_price_negotiated_tbl,
2839 l_dnz_chr_id_tbl,
2840 l_code_tbl,
2841 l_ste_code_tbl,
2842 l_meaning_tbl
2843 LIMIT 1000;
2844 IF (l_Id_tbl.COUNT < 1) THEN
2845 EXIT;
2846 END IF;
2847 IF (l_Id_tbl.COUNT > 0) THEN
2848 i := l_Id_tbl.FIRST;
2849 LOOP --II
2850 r_signed_line_tbl(x_num).contract_number := l_Contract_number_tbl(i);
2851 r_signed_line_tbl(x_num).contract_number_modifier := l_Contract_number_modifier_tbl(i);
2852 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2853 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2854 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2855 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2856 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2857 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2858 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2859 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2860 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2861 r_signed_line_tbl(x_num).termination_reason := NULL;
2862 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2863 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2864 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2865 x_num :=x_num+1;
2866 EXIT WHEN (i = l_Id_tbl.LAST);
2867 i := l_Id_tbl.NEXT(i);
2868 END LOOP; --II
2869 END IF;
2870 Exit when c_signed_line_all%NOTFOUND;
2871 END LOOP; --I
2872 IF(r_signed_line_tbl.COUNT > 0) Then
2873 i := r_signed_line_tbl.FIRST;
2874 LOOP
2875 line_signed(r_signed_line_tbl(i));
2876 EXIT WHEN (i = r_signed_line_tbl.LAST);
2877 i := r_signed_line_tbl.NEXT(i);
2878 END LOOP;
2879 END IF;
2880
2881 commit;
2882 c:= 0;
2883 END IF;-- Added for BUG #3967643
2884 ELSE -- p_kid IS NULL
2885
2886 x_num :=0;
2887 open c_signed_line_k;
2888 LOOP --I
2889 FETCH c_signed_line_k BULK COLLECT INTO
2890 l_Id_tbl,
2891 l_Object_version_number_tbl,
2892 l_sts_code_tbl,
2893 l_date_terminated_tbl,
2894 l_start_date_tbl,
2895 l_end_date_tbl,
2896 l_line_number_tbl,
2897 l_price_negotiated_tbl,
2898 l_dnz_chr_id_tbl,
2899 l_code_tbl,
2900 l_ste_code_tbl,
2901 l_meaning_tbl
2902 LIMIT 1000;
2903 IF (l_Id_tbl.COUNT < 1) THEN
2904 EXIT;
2905 END IF;
2906 IF (l_Id_tbl.COUNT > 0) THEN
2907 i := l_Id_tbl.FIRST;
2908 LOOP --II
2909 r_signed_line_tbl(x_num).contract_number := p_k_num;
2910 r_signed_line_tbl(x_num).contract_number_modifier := p_k_num_mod;
2911 r_signed_line_tbl(x_num).id := l_Id_tbl(i);
2912 r_signed_line_tbl(x_num).object_version_number := l_Object_version_number_tbl(i);
2913 r_signed_line_tbl(x_num).sts_code := l_sts_code_tbl(i);
2914 r_signed_line_tbl(x_num).date_terminated := l_date_terminated_tbl(i);
2915 r_signed_line_tbl(x_num).start_date := l_start_date_tbl(i);
2916 r_signed_line_tbl(x_num).end_date := l_end_date_tbl(i);
2917 r_signed_line_tbl(x_num).line_number := l_line_number_tbl(i);
2918 r_signed_line_tbl(x_num).price_negotiated := l_price_negotiated_tbl(i);
2919 r_signed_line_tbl(x_num).dnz_chr_id := l_dnz_chr_id_tbl(i);
2920 r_signed_line_tbl(x_num).termination_reason := NULL;
2921 r_signed_line_tbl(x_num).code := l_code_tbl(i);
2922 r_signed_line_tbl(x_num).ste_code := l_ste_code_tbl(i);
2923 r_signed_line_tbl(x_num).meaning := l_meaning_tbl(i);
2924 x_num :=x_num+1;
2925 EXIT WHEN (i = l_Id_tbl.LAST);
2926 i := l_Id_tbl.NEXT(i);
2927 END LOOP; --II
2928 END IF;
2929 Exit when c_signed_line_k%NOTFOUND;
2930 END LOOP; --I
2931 IF(r_signed_line_tbl.COUNT > 0) Then
2932 i := r_signed_line_tbl.FIRST;
2933 LOOP
2934 line_signed(r_signed_line_tbl(i));
2935 EXIT WHEN (i = r_signed_line_tbl.LAST);
2936 i := r_signed_line_tbl.NEXT(i);
2937 END LOOP;
2938 END IF;
2939 END IF;
2940 END IF;
2941 --END OF BUG 5930684
2942
2943 delete_table_type;
2944 -------------------------------------------------------------------------
2945
2946 EXCEPTION
2947 WHEN OTHERS THEN
2948 line_message(p_knum_and_mod =>l_knum_and_mod,
2949 p_line_number=>l_line_number,
2950 p_status =>l_new_status,
2951 p_msg_data => x_msg_data,
2952 p_type =>'U');
2953 p_line_errors := p_line_errors +1 ;
2954 rollback to H_STATUS;
2955 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2956 return;
2957
2958 END; -- procedure line_status_change
2959 -- BUG 4285665 --
2960 -- NEW --
2961 -----------------------------------------------------------------
2962 -- End LINE Status change procedure
2963 -----------------------------------------------------------------
2964
2965 -----------------------------------------------------------------
2966 -- End LINE Status change procedure
2967 -----------------------------------------------------------------
2968
2969
2970 -----------------------------------------------------------------
2971 -- Begin Status change procedure
2972 -----------------------------------------------------------------
2973 Procedure header_message(p_knum_and_mod IN VARCHAR2,
2974 p_old_status IN VARCHAR2 DEFAULT NULL,
2975 p_status IN VARCHAR2 DEFAULT NULL,
2976 p_msg_data IN VARCHAR2 DEFAULT NULL,
2977 p_type IN VARCHAR2) IS
2978 BEGIN
2979 if p_type='S' Then
2980 /*
2981 FND_MESSAGE.set_name('OKC','OKC_HDR_STS_CHANGE_SUCCESS');
2982 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_knum_and_mod);
2983 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
2984 FND_MESSAGE.set_token('STATUS', p_status);
2985 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2986 */
2987 NULL;
2988 elsif p_type='E'Then
2989 get_fnd_msg_stack(p_msg_data);
2990 p_hdr_errors := p_hdr_errors +1 ;
2991 FND_MESSAGE.set_name('OKC','OKC_HDR_STS_CHANGE_FAILURE');
2992 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_knum_and_mod);
2993 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
2994 FND_MESSAGE.set_token('STATUS', p_status);
2995 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
2996 elsif p_type='U'Then
2997 get_fnd_msg_stack(p_msg_data);
2998 FND_MESSAGE.set_name('OKC',G_UNEXPECTED_ERROR);
2999 FND_MESSAGE.set_token(G_SQLCODE_TOKEN,SQLCODE);
3000 FND_MESSAGE.set_token(G_SQLERRM_TOKEN,SQLERRM);
3001 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3002 FND_MESSAGE.set_name('OKC','OKC_HDR_STS_CHANGE_FAILURE');
3003 FND_MESSAGE.set_token('CONTRACT_NUMBER', p_knum_and_mod);
3004 FND_MESSAGE.set_token('OLD_STATUS', p_old_status);
3005 FND_MESSAGE.set_token('STATUS', p_status);
3006 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3007 end if;
3008 end header_message;
3009
3010 PROCEDURE WrapUp IS
3011 BEGIN
3012 ----------------------------------------------------------------------------------------
3013 ---LOG MESSAGES (SUMMARY)
3014 ----------------------------------------------------------------------------------------
3015 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
3016 FND_FILE.PUT_LINE( FND_FILE.LOG,' ');
3017 FND_MESSAGE.set_name('OKC','OKC_SUMMARY');
3018 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3019 IF p_hdr_errors > 0 or p_line_errors > 0 then
3020 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3021 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_ERRORS');
3022 FND_MESSAGE.set_token('HEADER_ERROR', p_hdr_errors);
3023 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3024 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
3025 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_ERRORS');
3026 FND_MESSAGE.set_token('LINE_ERRORS', p_line_errors);
3027 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3028 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3029 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_ERRORS');
3030 FND_MESSAGE.set_token('HEADER_ERROR', p_hdr_errors);
3031 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3032 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
3033 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_ERRORS');
3034 FND_MESSAGE.set_token('LINE_ERRORS', p_line_errors);
3035 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3036 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'--------------------------------------');
3037 ELSE
3038 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3039 FND_MESSAGE.set_name('OKC','OKC_NO_ERRORS');
3040 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3041 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'--------------------------------------');
3042 FND_MESSAGE.set_name('OKC','OKC_NO_ERRORS');
3043 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3044 END IF;
3045 FND_FILE.PUT_LINE( FND_FILE.LOG,'--------------------------------------');
3046 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_TOTAL');
3047 FND_MESSAGE.set_token('HEADER_TOTAL', p_hdr_count);
3048 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3049 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_TOTAL');
3050 FND_MESSAGE.set_token('LINE_TOTAL', p_line_count);
3051 FND_FILE.PUT_LINE( FND_FILE.LOG,FND_MESSAGE.GET);
3052 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'--------------------------------------');
3053 FND_MESSAGE.set_name('OKC','OKC_HDR_NUM_TOTAL');
3054 FND_MESSAGE.set_token('HEADER_TOTAL', p_hdr_count);
3055 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3056 FND_MESSAGE.set_name('OKC','OKC_LINE_NUM_TOTAL');
3057 FND_MESSAGE.set_token('LINE_TOTAL', p_line_count);
3058 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3059
3060 --Comments
3061 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'===============================================================');
3062 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,' End of Status Change Concurrent Program');
3063 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,'===============================================================');
3064 END;
3065
3066 PROCEDURE change_status (
3067 ERRBUF OUT NOCOPY VARCHAR2,
3068 RETCODE OUT NOCOPY NUMBER,
3069 p_category IN VARCHAR2 ,
3070 p_from_k IN VARCHAR2 ,
3071 p_to_k IN VARCHAR2 ,
3072 p_from_m IN VARCHAR2 ,
3073 p_to_m IN VARCHAR2 ,
3074 p_debug IN VARCHAR2 ,
3075 p_last_rundate IN VARCHAR2 ) IS
3076 L_K_N_W_M VARCHAR2(240); -- contract number conactinated with contract number modifier.
3077 C number := 0;
3078 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
3079 p_init_msg_list VARCHAR2(200) := okc_api.g_true;
3080 x_msg_count NUMBER := okc_api.g_miss_num;
3081 x_msg_data VARCHAR2(2000) := okc_api.g_miss_char;
3082 l_chr_rec okc_contract_pub.chrv_rec_type;
3083 i_chr_rec okc_contract_pub.chrv_rec_type;
3084 p_error_from_line VARCHAR2(1) := 'N';
3085
3086 /*commented for bug6475371 and split the cursors into 4 parts*/
3087 -- 'SIGNED' to ACTIVE
3088 --CURSOR C_ACTIVE IS
3089 --SELECT
3090 /* hdr.ID,
3091 hdr.OBJECT_VERSION_NUMBER,
3092 hdr.STS_CODE,
3093 hdr.CONTRACT_NUMBER,
3094 hdr.CONTRACT_NUMBER_MODIFIER,
3095 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3096 ' - '||hdr.contract_number_modifier) K_N_W_M,
3097 hdr.DATE_TERMINATED,
3098 hdr.TRN_CODE,
3099 hdr.START_DATE,
3100 hdr.END_DATE,
3101 hdr.SCS_CODE,
3102 hdr.ESTIMATED_AMOUNT,
3103 scs.CLS_CODE,
3104 status.CODE,
3105 status.STE_CODE,
3106 status.meaning
3107 FROM OKC_K_HEADERS_B hdr,
3108 OKC_STATUSES_V status,
3109 OKC_SUBCLASSES_B scs
3110 WHERE hdr.STS_CODE = status.CODE
3111 AND scs.code = hdr.scs_code
3112 and scs.cls_code <> 'OKL'
3113 and ((p_category is NULL) or (scs.CODE = p_category))
3114 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3115 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3116 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3117 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3118 and hdr.STS_CODE <> 'QA_HOLD'
3119 and status.ste_code = 'SIGNED'
3120 AND (hdr.date_terminated IS NULL
3121 or hdr.date_terminated >= trunc(sysdate))
3122 AND hdr.start_date <= trunc(sysdate)+0.99999
3123 AND hdr.start_date >= trunc(l_last_rundate);
3124 */
3125
3126 -- 'SIGNED' to ACTIVE
3127 -- when from-to contract range with optional category and contract modifier is provided
3128 CURSOR c_actv_hdr_all_cntr IS
3129 SELECT hdr.ID,
3130 hdr.OBJECT_VERSION_NUMBER,
3131 hdr.STS_CODE,
3132 hdr.CONTRACT_NUMBER,
3133 hdr.CONTRACT_NUMBER_MODIFIER,
3134 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3135 ' - '||hdr.contract_number_modifier) K_N_W_M,
3136 hdr.DATE_TERMINATED,
3137 hdr.TRN_CODE,
3138 hdr.START_DATE,
3139 hdr.END_DATE,
3140 hdr.SCS_CODE,
3141 hdr.ESTIMATED_AMOUNT,
3142 scs.CLS_CODE,
3143 status.CODE,
3144 status.STE_CODE,
3145 status.meaning
3146 FROM OKC_K_HEADERS_B hdr,
3147 OKC_STATUSES_V status,
3148 OKC_SUBCLASSES_B scs
3149 WHERE hdr.STS_CODE = status.CODE
3150 AND scs.code = hdr.scs_code
3151 and scs.cls_code <> 'OKL'
3152 and ((p_category is NULL) or (scs.CODE = p_category))
3153 and hdr.CONTRACT_NUMBER >= p_from_k
3154 and hdr.CONTRACT_NUMBER <= p_to_k
3155 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3156 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3157 and hdr.STS_CODE <> 'QA_HOLD'
3158 and status.ste_code = 'SIGNED'
3159 AND (hdr.date_terminated IS NULL
3160 or hdr.date_terminated >= trunc(sysdate))
3161 AND hdr.start_date <= trunc(sysdate)+0.99999
3162 AND hdr.start_date >= trunc(l_last_rundate);
3163
3164 -- 'SIGNED' to ACTIVE
3165 -- when only from-to contract range with all other options as null
3166 CURSOR c_actv_hdr_only_contract IS
3167 SELECT hdr.ID,
3168 hdr.OBJECT_VERSION_NUMBER,
3169 hdr.STS_CODE,
3170 hdr.CONTRACT_NUMBER,
3171 hdr.CONTRACT_NUMBER_MODIFIER,
3172 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3173 ' - '||hdr.contract_number_modifier) K_N_W_M,
3174 hdr.DATE_TERMINATED,
3175 hdr.TRN_CODE,
3176 hdr.START_DATE,
3177 hdr.END_DATE,
3178 hdr.SCS_CODE,
3179 hdr.ESTIMATED_AMOUNT,
3180 scs.CLS_CODE,
3181 status.CODE,
3182 status.STE_CODE,
3183 status.meaning
3184 FROM OKC_K_HEADERS_B hdr,
3185 OKC_STATUSES_V status,
3186 OKC_SUBCLASSES_B scs
3187 WHERE hdr.STS_CODE = status.CODE
3188 AND scs.code = hdr.scs_code
3189 and scs.cls_code <> 'OKL'
3190 and hdr.CONTRACT_NUMBER >= p_from_k
3191 and hdr.CONTRACT_NUMBER <= p_to_k
3192 and hdr.STS_CODE <> 'QA_HOLD'
3193 and status.ste_code = 'SIGNED'
3194 AND (hdr.date_terminated IS NULL or hdr.date_terminated >= trunc(sysdate))
3195 AND hdr.start_date <= trunc(sysdate)+0.99999
3196 AND hdr.start_date >= trunc(l_last_rundate);
3197
3198
3199 -- 'SIGNED' to ACTIVE
3200 -- when only category is provided
3201 CURSOR c_actv_hdr_only_category IS
3202 SELECT /*+ leading( status, hdr, scs ) index(hdr okc_k_headers_all_b_n12) */ ---Added hint for bug 12976183
3203 hdr.ID,
3204 hdr.OBJECT_VERSION_NUMBER,
3205 hdr.STS_CODE,
3206 hdr.CONTRACT_NUMBER,
3207 hdr.CONTRACT_NUMBER_MODIFIER,
3208 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3209 ' - '||hdr.contract_number_modifier) K_N_W_M,
3210 hdr.DATE_TERMINATED,
3211 hdr.TRN_CODE,
3212 hdr.START_DATE,
3213 hdr.END_DATE,
3214 hdr.SCS_CODE,
3215 hdr.ESTIMATED_AMOUNT,
3216 scs.CLS_CODE,
3217 status.CODE,
3218 status.STE_CODE,
3219 status.meaning
3220 FROM OKC_K_HEADERS_B hdr,
3221 OKC_STATUSES_V status,
3222 OKC_SUBCLASSES_B scs
3223 WHERE hdr.STS_CODE = status.CODE
3224 AND scs.code = hdr.scs_code
3225 and scs.cls_code <> 'OKL'
3226 and scs.CODE = p_category
3227 and hdr.STS_CODE <> 'QA_HOLD'
3228 and status.ste_code = 'SIGNED'
3229 AND (hdr.date_terminated IS NULL
3230 or hdr.date_terminated >= trunc(sysdate))
3231 AND hdr.start_date <= trunc(sysdate)+0.99999
3232 AND hdr.start_date >= trunc(l_last_rundate);
3233
3234 -- 'SIGNED' to ACTIVE
3235 -- when no parameter is provided
3236 CURSOR c_active_hdr_all IS
3237 SELECT hdr.ID,
3238 hdr.OBJECT_VERSION_NUMBER,
3239 hdr.STS_CODE,
3240 hdr.CONTRACT_NUMBER,
3241 hdr.CONTRACT_NUMBER_MODIFIER,
3242 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3243 ' - '||hdr.contract_number_modifier) K_N_W_M,
3244 hdr.DATE_TERMINATED,
3245 hdr.TRN_CODE,
3246 hdr.START_DATE,
3247 hdr.END_DATE,
3248 hdr.SCS_CODE,
3249 hdr.ESTIMATED_AMOUNT,
3250 scs.CLS_CODE,
3251 status.CODE,
3252 status.STE_CODE,
3253 status.meaning
3254 FROM OKC_K_HEADERS_B hdr,
3255 OKC_STATUSES_V status,
3256 OKC_SUBCLASSES_B scs
3257 WHERE hdr.STS_CODE = status.CODE
3258 AND scs.code = hdr.scs_code
3259 and scs.cls_code <> 'OKL'
3260 and hdr.STS_CODE <> 'QA_HOLD'
3261 and status.ste_code = 'SIGNED'
3262 AND (hdr.date_terminated IS NULL or hdr.date_terminated >= trunc(sysdate))
3263 AND hdr.start_date <= trunc(sysdate)+0.99999
3264 AND hdr.start_date >= trunc(l_last_rundate);
3265
3266 /*commented for bug6475371 and split the cursor into 4 parts*/
3267 --bug 5930684
3268 -- 'ACTIVE' to SIGNED
3269 -- CURSOR C_SIGNED IS
3270 -- SELECT
3271 /* hdr.ID,
3272 hdr.OBJECT_VERSION_NUMBER,
3273 hdr.STS_CODE,
3274 hdr.CONTRACT_NUMBER,
3275 hdr.CONTRACT_NUMBER_MODIFIER,
3276 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3277 ' - '||hdr.contract_number_modifier) K_N_W_M,
3278 hdr.DATE_TERMINATED,
3279 hdr.TRN_CODE,
3280 hdr.START_DATE,
3281 hdr.END_DATE,
3282 hdr.SCS_CODE,
3283 hdr.ESTIMATED_AMOUNT,
3284 scs.CLS_CODE,
3285 status.CODE,
3286 status.STE_CODE,
3287 status.meaning
3288 FROM OKC_K_HEADERS_B hdr,
3289 OKC_STATUSES_V status,
3290 OKC_SUBCLASSES_B scs
3291 WHERE hdr.STS_CODE = status.CODE
3292 AND scs.code = hdr.scs_code
3293 and scs.cls_code <> 'OKL'
3294 and ((p_category is NULL) or (scs.CODE = p_category))
3295 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3296 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3297 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3298 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3299 and hdr.STS_CODE <> 'QA_HOLD'
3300 and status.ste_code = 'ACTIVE'
3301 AND (hdr.date_terminated IS NULL
3302 or hdr.date_terminated >= trunc(sysdate))
3303 AND hdr.start_date >= trunc(sysdate)+0.99999
3304 AND hdr.start_date >= trunc(l_last_rundate);
3305 */
3306 --end of bug 5930684.
3307
3308 -- 'ACTIVE' to SIGNED
3309 -- when from-to contract range with optional category and contract modifier is provided
3310 CURSOR C_SIG_HDR_ALL_CNTR IS
3311 SELECT
3312 hdr.ID,
3313 hdr.OBJECT_VERSION_NUMBER,
3314 hdr.STS_CODE,
3315 hdr.CONTRACT_NUMBER,
3316 hdr.CONTRACT_NUMBER_MODIFIER,
3317 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3318 ' - '||hdr.contract_number_modifier) K_N_W_M,
3319 hdr.DATE_TERMINATED,
3320 hdr.TRN_CODE,
3321 hdr.START_DATE,
3322 hdr.END_DATE,
3323 hdr.SCS_CODE,
3324 hdr.ESTIMATED_AMOUNT,
3325 scs.CLS_CODE,
3326 status.CODE,
3327 status.STE_CODE,
3328 status.meaning
3329 FROM OKC_K_HEADERS_B hdr,
3330 OKC_STATUSES_V status,
3331 OKC_SUBCLASSES_B scs
3332 WHERE hdr.STS_CODE = status.CODE
3333 AND scs.code = hdr.scs_code
3334 and scs.cls_code <> 'OKL'
3335 and ((p_category is NULL) or (scs.CODE = p_category))
3336 and hdr.CONTRACT_NUMBER >= p_from_k
3337 and hdr.CONTRACT_NUMBER <= p_to_k
3338 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3339 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3340 and hdr.STS_CODE <> 'QA_HOLD'
3341 and status.ste_code = 'ACTIVE'
3342 AND (hdr.date_terminated IS NULL
3343 or hdr.date_terminated >= trunc(sysdate))
3344 AND hdr.start_date >= trunc(sysdate)+0.99999
3345 AND hdr.start_date >= trunc(l_last_rundate);
3346
3347 -- 'ACTIVE' to SIGNED
3348 -- WHEN ONLY FROM-TO CONTRACT RANGE IS PROVIDED
3349 CURSOR C_SIG_HDR_ONLY_CONTRACT IS
3350 SELECT
3351 hdr.ID,
3352 hdr.OBJECT_VERSION_NUMBER,
3353 hdr.STS_CODE,
3354 hdr.CONTRACT_NUMBER,
3355 hdr.CONTRACT_NUMBER_MODIFIER,
3356 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3357 ' - '||hdr.contract_number_modifier) K_N_W_M,
3358 hdr.DATE_TERMINATED,
3359 hdr.TRN_CODE,
3360 hdr.START_DATE,
3361 hdr.END_DATE,
3362 hdr.SCS_CODE,
3363 hdr.ESTIMATED_AMOUNT,
3364 scs.CLS_CODE,
3365 status.CODE,
3366 status.STE_CODE,
3367 status.meaning
3368 FROM OKC_K_HEADERS_B hdr,
3369 OKC_STATUSES_V status,
3370 OKC_SUBCLASSES_B scs
3371 WHERE hdr.STS_CODE = status.CODE
3372 AND scs.code = hdr.scs_code
3373 and scs.cls_code <> 'OKL'
3374 and hdr.CONTRACT_NUMBER >= p_from_k
3375 and hdr.CONTRACT_NUMBER <= p_to_k
3376 and hdr.STS_CODE <> 'QA_HOLD'
3377 and status.ste_code = 'ACTIVE'
3378 AND (hdr.date_terminated IS NULL
3379 or hdr.date_terminated >= trunc(sysdate))
3380 AND hdr.start_date >= trunc(sysdate)+0.99999
3381 AND hdr.start_date >= trunc(l_last_rundate);
3382
3383 -- 'ACTIVE' to SIGNED
3384 -- WHEN ONLY CATEGORY IS PROVIDED
3385 CURSOR C_SIG_HDR_ONLY_CATEGORY IS
3386 SELECT
3387 hdr.ID,
3388 hdr.OBJECT_VERSION_NUMBER,
3389 hdr.STS_CODE,
3390 hdr.CONTRACT_NUMBER,
3391 hdr.CONTRACT_NUMBER_MODIFIER,
3392 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3393 ' - '||hdr.contract_number_modifier) K_N_W_M,
3394 hdr.DATE_TERMINATED,
3395 hdr.TRN_CODE,
3396 hdr.START_DATE,
3397 hdr.END_DATE,
3398 hdr.SCS_CODE,
3399 hdr.ESTIMATED_AMOUNT,
3400 scs.CLS_CODE,
3401 status.CODE,
3402 status.STE_CODE,
3403 status.meaning
3404 FROM OKC_K_HEADERS_B hdr,
3405 OKC_STATUSES_V status,
3406 OKC_SUBCLASSES_B scs
3407 WHERE hdr.STS_CODE = status.CODE
3408 AND scs.code = hdr.scs_code
3409 and scs.cls_code <> 'OKL'
3410 and scs.CODE = p_category
3411 and hdr.STS_CODE <> 'QA_HOLD'
3412 and status.ste_code = 'ACTIVE'
3413 AND (hdr.date_terminated IS NULL
3414 or hdr.date_terminated >= trunc(sysdate))
3415 AND hdr.start_date >= trunc(sysdate)+0.99999
3416 AND hdr.start_date >= trunc(l_last_rundate);
3417
3418 -- 'ACTIVE' to SIGNED
3419 --when no parameter is provided
3420 CURSOR C_SIGNED_HDR_ALL IS
3421 SELECT
3422 hdr.ID,
3423 hdr.OBJECT_VERSION_NUMBER,
3424 hdr.STS_CODE,
3425 hdr.CONTRACT_NUMBER,
3426 hdr.CONTRACT_NUMBER_MODIFIER,
3427 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3428 ' - '||hdr.contract_number_modifier) K_N_W_M,
3429 hdr.DATE_TERMINATED,
3430 hdr.TRN_CODE,
3431 hdr.START_DATE,
3432 hdr.END_DATE,
3433 hdr.SCS_CODE,
3434 hdr.ESTIMATED_AMOUNT,
3435 scs.CLS_CODE,
3436 status.CODE,
3437 status.STE_CODE,
3438 status.meaning
3439 FROM OKC_K_HEADERS_B hdr,
3440 OKC_STATUSES_V status,
3441 OKC_SUBCLASSES_B scs
3442 WHERE hdr.STS_CODE = status.CODE
3443 AND scs.code = hdr.scs_code
3444 and scs.cls_code <> 'OKL'
3445 and hdr.STS_CODE <> 'QA_HOLD'
3446 and status.ste_code = 'ACTIVE'
3447 AND (hdr.date_terminated IS NULL
3448 or hdr.date_terminated >= trunc(sysdate))
3449 AND hdr.start_date >= trunc(sysdate)+0.99999
3450 AND hdr.start_date >= trunc(l_last_rundate);
3451
3452
3453 /*commented for bug6475371 and split the cursor into 4 parts*/
3454 -- 'ACTIVE,HOLD,SIGNED to EXPIRED'
3455 -- CURSOR C_EXPIRED IS
3456 -- SELECT /*+ leading(hdr) no_merge(status) use_hash(hdr scs status.stsb status.stst) */
3457 /* hdr.ID,
3458 hdr.OBJECT_VERSION_NUMBER,
3459 hdr.STS_CODE,
3460 hdr.CONTRACT_NUMBER,
3461 hdr.CONTRACT_NUMBER_MODIFIER,
3462 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3463 ' - '||hdr.contract_number_modifier) K_N_W_M,
3464 hdr.DATE_TERMINATED,
3465 hdr.TRN_CODE,
3466 hdr.START_DATE,
3467 hdr.END_DATE,
3468 hdr.SCS_CODE,
3469 hdr.ESTIMATED_AMOUNT,
3470 scs.CLS_CODE,
3471 status.CODE,
3472 status.STE_CODE,
3473 status.meaning
3474 FROM OKC_K_HEADERS_B hdr,
3475 OKC_STATUSES_V status,
3476 OKC_SUBCLASSES_B scs
3477 WHERE hdr.STS_CODE = status.CODE
3478 AND scs.code = hdr.scs_code
3479 and scs.cls_code <> 'OKL'
3480 and ((p_category is NULL) or (scs.CODE = p_category))
3481 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3482 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3483 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3484 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3485 and hdr.STS_CODE <> 'QA_HOLD'
3486 -- and status.ste_code in ('ACTIVE','SIGNED','HOLD') -- <> 'EXPIRED'
3487 and status.ste_code in ('ACTIVE','SIGNED') -- Bug 4915692 --
3488 AND hdr.end_date >= trunc(l_last_rundate)
3489 --
3490 -- Bug 2672565 - Removed time component and changed from <= to <
3491 --and hdr.end_date <= trunc(sysdate)+0.99999
3492 --
3493 and hdr.end_date < trunc(sysdate)
3494 AND (hdr.date_terminated IS NULL
3495 or hdr.date_terminated >= trunc(sysdate));
3496 */
3497
3498 -- 'ACTIVE,SIGNED to EXPIRED'
3499 -- when from-to contract range with optional category and contract modifier is provided
3500 CURSOR C_EXPIRED_HDR_ALL_CNTR IS
3501 SELECT /*+ leading(hdr) no_merge(status) use_hash(hdr scs status.stsb status.stst) */
3502 hdr.ID,
3503 hdr.OBJECT_VERSION_NUMBER,
3504 hdr.STS_CODE,
3505 hdr.CONTRACT_NUMBER,
3506 hdr.CONTRACT_NUMBER_MODIFIER,
3507 hdr.CONTRACT_NUMBER ||
3508 decode(hdr.contract_number_modifier,
3509 NULL,
3510 NULL,
3511 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3512 hdr.DATE_TERMINATED,
3513 hdr.TRN_CODE,
3514 hdr.START_DATE,
3515 hdr.END_DATE,
3516 hdr.SCS_CODE,
3517 hdr.ESTIMATED_AMOUNT,
3518 scs.CLS_CODE,
3519 status.CODE,
3520 status.STE_CODE,
3521 status.meaning
3522 FROM OKC_K_HEADERS_B hdr, OKC_STATUSES_V status, OKC_SUBCLASSES_B scs
3523 WHERE hdr.STS_CODE = status.CODE
3524 AND scs.code = hdr.scs_code
3525 AND scs.cls_code <> 'OKL'
3526 AND ((p_category IS NULL) OR (scs.CODE = p_category))
3527 AND hdr.CONTRACT_NUMBER >= p_from_k
3528 AND hdr.CONTRACT_NUMBER <= p_to_k
3529 AND ((p_from_m IS NULL) OR (hdr.CONTRACT_NUMBER_modifier >= p_from_m))
3530 AND ((p_to_m IS NULL) OR (hdr.CONTRACT_NUMBER_modifier <= p_to_m))
3531 AND hdr.STS_CODE <> 'QA_HOLD'
3532 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3533 AND hdr.end_date >= trunc(l_last_rundate)
3534 AND hdr.end_date < trunc(SYSDATE)
3535 AND (hdr.date_terminated IS NULL OR
3536 hdr.date_terminated >= trunc(SYSDATE));
3537
3538
3539
3540 -- 'ACTIVE,SIGNED to EXPIRED'
3541 -- WHEN ONLY FROM-TO CONTRACT RANGE IS PROVIDED
3542 CURSOR C_EXP_HDR_ONLY_CONTRACT IS
3543 SELECT /*+ leading(hdr) no_merge(status) use_hash(hdr scs status.stsb status.stst) */
3544 hdr.ID,
3545 hdr.OBJECT_VERSION_NUMBER,
3546 hdr.STS_CODE,
3547 hdr.CONTRACT_NUMBER,
3548 hdr.CONTRACT_NUMBER_MODIFIER,
3549 hdr.CONTRACT_NUMBER ||
3550 decode(hdr.contract_number_modifier,
3551 NULL,
3552 NULL,
3553 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3554 hdr.DATE_TERMINATED,
3555 hdr.TRN_CODE,
3556 hdr.START_DATE,
3557 hdr.END_DATE,
3558 hdr.SCS_CODE,
3559 hdr.ESTIMATED_AMOUNT,
3560 scs.CLS_CODE,
3561 status.CODE,
3562 status.STE_CODE,
3563 status.meaning
3564 FROM OKC_K_HEADERS_B hdr, OKC_STATUSES_V status, OKC_SUBCLASSES_B scs
3565 WHERE hdr.STS_CODE = status.CODE
3566 AND scs.code = hdr.scs_code
3567 AND scs.cls_code <> 'OKL'
3568 AND hdr.CONTRACT_NUMBER >= p_from_k
3569 AND hdr.CONTRACT_NUMBER <= p_to_k
3570 AND hdr.STS_CODE <> 'QA_HOLD'
3571 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3572 AND hdr.end_date >= trunc(l_last_rundate)
3573 AND hdr.end_date < trunc(SYSDATE)
3574 AND (hdr.date_terminated IS NULL OR
3575 hdr.date_terminated >= trunc(SYSDATE));
3576
3577
3578 -- 'ACTIVE,SIGNED to EXPIRED'
3579 -- WHEN ONLY CATEGORY IS PROVIDED
3580 CURSOR C_EXP_HDR_ONLY_CATEGORY IS
3581 SELECT /*+ leading( status, hdr, scs ) index(hdr okc_k_headers_all_b_n13) */ -----Modified hint for bug 12976183
3582 hdr.ID,
3583 hdr.OBJECT_VERSION_NUMBER,
3584 hdr.STS_CODE,
3585 hdr.CONTRACT_NUMBER,
3586 hdr.CONTRACT_NUMBER_MODIFIER,
3587 hdr.CONTRACT_NUMBER ||
3588 decode(hdr.contract_number_modifier,
3589 NULL,
3590 NULL,
3591 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3592 hdr.DATE_TERMINATED,
3593 hdr.TRN_CODE,
3594 hdr.START_DATE,
3595 hdr.END_DATE,
3596 hdr.SCS_CODE,
3597 hdr.ESTIMATED_AMOUNT,
3598 scs.CLS_CODE,
3599 status.CODE,
3600 status.STE_CODE,
3601 status.meaning
3602 FROM OKC_K_HEADERS_B hdr, OKC_STATUSES_V status, OKC_SUBCLASSES_B scs
3603 WHERE hdr.STS_CODE = status.CODE
3604 AND scs.code = hdr.scs_code
3605 AND scs.cls_code <> 'OKL'
3606 AND scs.CODE = p_category
3607 AND hdr.STS_CODE <> 'QA_HOLD'
3608 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3609 AND hdr.end_date >= trunc(l_last_rundate)
3610 AND hdr.end_date < trunc(SYSDATE)
3611 AND (hdr.date_terminated IS NULL OR
3612 hdr.date_terminated >= trunc(SYSDATE));
3613
3614
3615 -- 'ACTIVE,SIGNED to EXPIRED'
3616 --when no parameter is provided
3617 CURSOR C_EXPIRED_HDR_ALL IS
3618 SELECT /*+ leading(hdr) no_merge(status) use_hash(hdr scs status.stsb status.stst) */
3619 hdr.ID,
3620 hdr.OBJECT_VERSION_NUMBER,
3621 hdr.STS_CODE,
3622 hdr.CONTRACT_NUMBER,
3623 hdr.CONTRACT_NUMBER_MODIFIER,
3624 hdr.CONTRACT_NUMBER ||
3625 decode(hdr.contract_number_modifier,
3626 NULL,
3627 NULL,
3628 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3629 hdr.DATE_TERMINATED,
3630 hdr.TRN_CODE,
3631 hdr.START_DATE,
3632 hdr.END_DATE,
3633 hdr.SCS_CODE,
3634 hdr.ESTIMATED_AMOUNT,
3635 scs.CLS_CODE,
3636 status.CODE,
3637 status.STE_CODE,
3638 status.meaning
3639 FROM OKC_K_HEADERS_B hdr, OKC_STATUSES_V status, OKC_SUBCLASSES_B scs
3640 WHERE hdr.STS_CODE = status.CODE
3641 AND scs.code = hdr.scs_code
3642 AND scs.cls_code <> 'OKL'
3643 AND hdr.STS_CODE <> 'QA_HOLD'
3644 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3645 AND hdr.end_date >= trunc(l_last_rundate)
3646 AND hdr.end_date < trunc(SYSDATE)
3647 AND (hdr.date_terminated IS NULL OR
3648 hdr.date_terminated >= trunc(SYSDATE));
3649
3650
3651
3652 /*commented for bug6475371 and split cursor into 4 parts*/
3653 -- 'ACTIVE','HOLD','SIGNED' to TERMINATED
3654 -- CURSOR C_TERMINATED IS
3655 -- SELECT
3656 /* hdr.ID,
3657 hdr.OBJECT_VERSION_NUMBER,
3658 hdr.STS_CODE,
3659 hdr.CONTRACT_NUMBER,
3660 hdr.CONTRACT_NUMBER_MODIFIER,
3661 hdr.CONTRACT_NUMBER||decode(hdr.contract_number_modifier,NULL,NULL,
3662 ' - '||hdr.contract_number_modifier) K_N_W_M,
3663 hdr.DATE_TERMINATED,
3664 hdr.TRN_CODE,
3665 hdr.START_DATE,
3666 hdr.END_DATE,
3667 hdr.SCS_CODE,
3668 hdr.ESTIMATED_AMOUNT,
3669 fnd.meaning TERMINATION_REASON,
3670 scs.CLS_CODE,
3671 status.CODE,
3672 status.STE_CODE,
3673 status.meaning
3674 FROM OKC_K_HEADERS_B hdr,
3675 OKC_STATUSES_V status,
3676 FND_LOOKUPS fnd,
3677 OKC_SUBCLASSES_B scs
3678 WHERE hdr.STS_CODE = status.CODE
3679 AND scs.code = hdr.scs_code
3680 and scs.cls_code <> 'OKL'
3681 and ((p_category is NULL) or (scs.CODE = p_category))
3682 and ((p_from_k is NULL) or (hdr.CONTRACT_NUMBER >= p_from_k ))
3683 and ((p_to_k is NULL) or (hdr.CONTRACT_NUMBER <= p_to_k ))
3684 and ((p_from_m is NULL) or (hdr.CONTRACT_NUMBER_modifier >= p_from_m ))
3685 and ((p_to_m is NULL) or (hdr.CONTRACT_NUMBER_modifier <= p_to_m ))
3686 and hdr.STS_CODE <> 'QA_HOLD'
3687 -- and status.ste_code IN ('ACTIVE','HOLD','SIGNED')
3688 and status.ste_code IN ('ACTIVE','SIGNED') -- Bug 4915692
3689 and hdr.trn_code = fnd.lookup_code
3690 and fnd.LOOKUP_TYPE= 'OKC_TERMINATION_REASON'
3691 and hdr.date_terminated >= trunc(l_last_rundate)
3692 and hdr.date_terminated <= trunc(sysdate)+0.99999;
3693 */
3694
3695 -- 'ACTIVE','HOLD','SIGNED' to TERMINATED
3696 --when from-to contract range is provided with optional contract modifier and category
3697 CURSOR C_TERMN_HDR_ALL_CNTR IS
3698 SELECT hdr.ID,
3699 hdr.OBJECT_VERSION_NUMBER,
3700 hdr.STS_CODE,
3701 hdr.CONTRACT_NUMBER,
3702 hdr.CONTRACT_NUMBER_MODIFIER,
3703 hdr.CONTRACT_NUMBER ||
3704 decode(hdr.contract_number_modifier,
3705 NULL,
3706 NULL,
3707 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3708 hdr.DATE_TERMINATED,
3709 hdr.TRN_CODE,
3710 hdr.START_DATE,
3711 hdr.END_DATE,
3712 hdr.SCS_CODE,
3713 hdr.ESTIMATED_AMOUNT,
3714 fnd.meaning TERMINATION_REASON,
3715 scs.CLS_CODE,
3716 status.CODE,
3717 status.STE_CODE,
3718 status.meaning
3719 FROM OKC_K_HEADERS_B hdr,
3720 OKC_STATUSES_V status,
3721 FND_LOOKUPS fnd,
3722 OKC_SUBCLASSES_B scs
3723 WHERE hdr.STS_CODE = status.CODE
3724 AND scs.code = hdr.scs_code
3725 AND scs.cls_code <> 'OKL'
3726 AND ((p_category IS NULL) OR (scs.CODE = p_category))
3727 AND hdr.CONTRACT_NUMBER >= p_from_k
3728 AND hdr.CONTRACT_NUMBER <= p_to_k
3729 AND ((p_from_m IS NULL) OR (hdr.CONTRACT_NUMBER_modifier >= p_from_m))
3730 AND ((p_to_m IS NULL) OR (hdr.CONTRACT_NUMBER_modifier <= p_to_m))
3731 AND hdr.STS_CODE <> 'QA_HOLD'
3732 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3733 AND hdr.trn_code = fnd.lookup_code
3734 AND fnd.LOOKUP_TYPE = 'OKC_TERMINATION_REASON'
3735 AND hdr.date_terminated >= trunc(l_last_rundate)
3736 AND hdr.date_terminated <= trunc(SYSDATE) + 0.99999;
3737
3738 -- 'ACTIVE','HOLD','SIGNED' to TERMINATED
3739 --WHEN ONLY FROM-TO CONTRACT RANGE IS PROVIDED
3740 CURSOR C_TERMN_HDR_ONLY_CONTRACT IS
3741 SELECT hdr.ID,
3742 hdr.OBJECT_VERSION_NUMBER,
3743 hdr.STS_CODE,
3744 hdr.CONTRACT_NUMBER,
3745 hdr.CONTRACT_NUMBER_MODIFIER,
3746 hdr.CONTRACT_NUMBER ||
3747 decode(hdr.contract_number_modifier,
3748 NULL,
3749 NULL,
3750 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3751 hdr.DATE_TERMINATED,
3752 hdr.TRN_CODE,
3753 hdr.START_DATE,
3754 hdr.END_DATE,
3755 hdr.SCS_CODE,
3756 hdr.ESTIMATED_AMOUNT,
3757 fnd.meaning TERMINATION_REASON,
3758 scs.CLS_CODE,
3759 status.CODE,
3760 status.STE_CODE,
3761 status.meaning
3762 FROM OKC_K_HEADERS_B hdr,
3763 OKC_STATUSES_V status,
3764 FND_LOOKUPS fnd,
3765 OKC_SUBCLASSES_B scs
3766 WHERE hdr.STS_CODE = status.CODE
3767 AND scs.code = hdr.scs_code
3768 AND scs.cls_code <> 'OKL'
3769 AND hdr.CONTRACT_NUMBER >= p_from_k
3770 AND hdr.CONTRACT_NUMBER <= p_to_k
3771 AND hdr.STS_CODE <> 'QA_HOLD'
3772 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3773 AND hdr.trn_code = fnd.lookup_code
3774 AND fnd.LOOKUP_TYPE = 'OKC_TERMINATION_REASON'
3775 AND hdr.date_terminated >= trunc(l_last_rundate)
3776 AND hdr.date_terminated <= trunc(SYSDATE) + 0.99999;
3777
3778
3779 -- 'ACTIVE','HOLD','SIGNED' to TERMINATED
3780 --when only category is provided
3781 CURSOR C_TERMN_HDR_ONLY_CATEGORY IS
3782 SELECT hdr.ID,
3783 hdr.OBJECT_VERSION_NUMBER,
3784 hdr.STS_CODE,
3785 hdr.CONTRACT_NUMBER,
3786 hdr.CONTRACT_NUMBER_MODIFIER,
3787 hdr.CONTRACT_NUMBER ||
3788 decode(hdr.contract_number_modifier,
3789 NULL,
3790 NULL,
3791 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3792 hdr.DATE_TERMINATED,
3793 hdr.TRN_CODE,
3794 hdr.START_DATE,
3795 hdr.END_DATE,
3796 hdr.SCS_CODE,
3797 hdr.ESTIMATED_AMOUNT,
3798 fnd.meaning TERMINATION_REASON,
3799 scs.CLS_CODE,
3800 status.CODE,
3801 status.STE_CODE,
3802 status.meaning
3803 FROM OKC_K_HEADERS_B hdr,
3804 OKC_STATUSES_V status,
3805 FND_LOOKUPS fnd,
3806 OKC_SUBCLASSES_B scs
3807 WHERE hdr.STS_CODE = status.CODE
3808 AND scs.code = hdr.scs_code
3809 AND scs.cls_code <> 'OKL'
3810 AND scs.CODE = p_category
3811 AND hdr.STS_CODE <> 'QA_HOLD'
3812 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3813 AND hdr.trn_code = fnd.lookup_code
3814 AND fnd.LOOKUP_TYPE = 'OKC_TERMINATION_REASON'
3815 AND hdr.date_terminated >= trunc(l_last_rundate)
3816 AND hdr.date_terminated <= trunc(SYSDATE) + 0.99999;
3817
3818 -- 'ACTIVE','HOLD','SIGNED' to TERMINATED
3819 --when no parameter is provided
3820 CURSOR C_TERMN_HDR_ALL IS
3821 SELECT hdr.ID,
3822 hdr.OBJECT_VERSION_NUMBER,
3823 hdr.STS_CODE,
3824 hdr.CONTRACT_NUMBER,
3825 hdr.CONTRACT_NUMBER_MODIFIER,
3826 hdr.CONTRACT_NUMBER ||
3827 decode(hdr.contract_number_modifier,
3828 NULL,
3829 NULL,
3830 ' - ' || hdr.contract_number_modifier) K_N_W_M,
3831 hdr.DATE_TERMINATED,
3832 hdr.TRN_CODE,
3833 hdr.START_DATE,
3834 hdr.END_DATE,
3835 hdr.SCS_CODE,
3836 hdr.ESTIMATED_AMOUNT,
3837 fnd.meaning TERMINATION_REASON,
3838 scs.CLS_CODE,
3839 status.CODE,
3840 status.STE_CODE,
3841 status.meaning
3842 FROM OKC_K_HEADERS_B hdr,
3843 OKC_STATUSES_V status,
3844 FND_LOOKUPS fnd,
3845 OKC_SUBCLASSES_B scs
3846 WHERE hdr.STS_CODE = status.CODE
3847 AND scs.code = hdr.scs_code
3848 AND scs.cls_code <> 'OKL'
3849 AND hdr.STS_CODE <> 'QA_HOLD'
3850 AND status.ste_code IN ('ACTIVE', 'SIGNED')
3851 AND hdr.trn_code = fnd.lookup_code
3852 AND fnd.LOOKUP_TYPE = 'OKC_TERMINATION_REASON'
3853 AND hdr.date_terminated >= trunc(l_last_rundate)
3854 AND hdr.date_terminated <= trunc(SYSDATE) + 0.99999;
3855
3856 ----------------------------------------------------------------------------------------
3857 --- BEGIN CHANGE STATUS AT HEADER LEVEL ---
3858 ----------------------------------------------------------------------------------------
3859 BEGIN
3860
3861 savepoint H_STATUS;
3862 FND_MSG_PUB.initialize;
3863 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'===============================================================');
3864 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Start of Status Change Concurrent Program');
3865 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'===============================================================');
3866 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Parameters for the Run:');
3867 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Last Run Date: ' || p_last_rundate);
3868 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Category: ' || p_category);
3869 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract Number From: ' || p_from_k ||'*'||p_from_m);
3870 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Contract Number To: ' || p_to_k ||'*'||p_to_m);
3871 --
3872 ERRBUF := NULL;
3873 RETCODE := 0;
3874
3875 -- Bug 5086847 --
3876 IF (p_from_m IS NOT NULL) AND (p_from_k IS NULL) THEN
3877
3878 FND_MESSAGE.set_name('OKC','OKC_ENTER_K_NUMBER');
3879 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3880
3881 RETCODE := 2;
3882 return;
3883 END IF;
3884
3885 IF (p_to_m IS NOT NULL) AND (p_to_k IS NULL) THEN
3886
3887 FND_MESSAGE.set_name('OKC','OKC_ENTER_K_NUMBER');
3888 FND_FILE.PUT_LINE( FND_FILE.OUTPUT,FND_MESSAGE.GET);
3889
3890 RETCODE := 2;
3891 return;
3892 END IF;
3893
3894 -- Bug 5086847 --
3895
3896 T := NVL(to_number(FND_PROFILE.VALUE('OKC_BATCH_SIZE')),1000);
3897 open sts(v_active);
3898 fetch sts into v_active,v_active_m;
3899 if sts%NOTFOUND Then
3900 v_active := 'ACTIVE';
3901 v_active_m := 'Active';
3902 end if;
3903 close sts;
3904
3905 open sts(v_expired);
3906 fetch sts into v_expired,v_expired_m;
3907 if sts%NOTFOUND Then
3908 v_active := 'EXPIRED';
3909 v_active_m := 'Expired';
3910 end if;
3911 close sts;
3912
3913 open sts(v_terminated);
3914 fetch sts into v_terminated,v_terminated_m;
3915 if sts%NOTFOUND Then
3916 v_active := 'TERMINATED';
3917 v_active_m := 'Terminated';
3918 end if;
3919 close sts;
3920
3921 open sts(v_signed);
3922 fetch sts into v_signed,v_signed_m;
3923 if sts%NOTFOUND Then
3924 v_active := 'SIGNED';
3925 v_active_m := 'Signed';
3926 end if;
3927 close sts;
3928
3929 l_last_rundate := nvl(fnd_date.canonical_to_date(p_last_rundate), to_date('01011901','ddmmyyyy')) - 3; -- a three day grace period.
3930
3931 -- from active hold signed to terminated
3932
3933 /*MODIFIED FOR THE BUG6475371*/
3934 --WHEN FROM-TO CONTRACT NUMBER RANGE WITH OPTIONAL CATEGORY
3935 --AND CONTRACT MODIFIER IS PROVIDED
3936 IF (p_from_k IS NOT NULL) or (p_to_k IS NOT NULL) THEN
3937 IF (p_category is NOT NULL) or (p_from_m is not null or p_to_m is not null) THEN
3938 c := 0;
3939 l_new_status := null;
3940 l_new_status_m := null;
3941
3942 FND_FILE.PUT_LINE(FND_FILE.LOG,'when from-to contract number range with optional category and contract modifier is provided');
3943
3944 FOR r_terminated in c_termn_hdr_all_cntr LOOP
3945
3946
3947 BEGIN
3948 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to terminated' || C);
3949 if (C >= T) then
3950 commit;
3951 c := 0;
3952 end if;
3953 savepoint H_STATUS;
3954 p_hdr_count:= p_hdr_count + 1;
3955 l_return_status := OKC_API.G_RET_STS_SUCCESS;
3956 L_K_N_W_M := r_terminated.K_N_W_M;
3957
3958 l_new_status := null;
3959 l_new_status_m := null;
3960 h_status := null;
3961 h_new_status := v_terminated ;
3962 h_new_status_m := v_terminated_m ;
3963 h_status_type := 'TERMINATED';
3964
3965 l_chr_rec.id := r_terminated.id ;
3966 l_chr_rec.object_version_number := r_terminated.object_version_number ;
3967 l_chr_rec.sts_code := h_new_status ;
3968 l_chr_rec.old_sts_code := r_terminated.sts_code;
3969 l_chr_rec.old_ste_code := r_terminated.ste_code;
3970 l_chr_rec.new_sts_code := h_new_status;
3971 l_chr_rec.new_ste_code := h_status_type;
3972 --
3973 -- End: Added for Status Change Action Assembler Changes 10/19/2000
3974 --
3975 -- lock added not to depend on update implementation
3976 --
3977 okc_contract_pub.lock_contract_header(
3978 p_api_version => 1.0,
3979 p_init_msg_list => p_init_msg_list,
3980 x_return_status => l_return_status,
3981 x_msg_count => x_msg_count,
3982 x_msg_data => x_msg_data,
3983 p_chrv_rec => l_chr_rec);
3984 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3985 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3986 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3987 raise OKC_API.G_EXCEPTION_ERROR;
3988 END IF;
3989 --
3990 --
3991 update_contract_header (
3992 p_api_version => 1.0,
3993 p_init_msg_list => p_init_msg_list,
3994 x_return_status => l_return_status,
3995 x_msg_count => x_msg_count,
3996 x_msg_data => x_msg_data,
3997 p_chrv_rec => l_chr_rec,
3998 x_chrv_rec => i_chr_rec);
3999
4000 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4001 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4002 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4003 raise OKC_API.G_EXCEPTION_ERROR;
4004 END IF;
4005
4006 OKC_K_TERM_ASMBLR_PVT.acn_assemble(
4007 p_api_version => 1.0 ,
4008 p_init_msg_list => p_init_msg_list,
4009 x_return_status => l_return_status,
4010 x_msg_count => x_msg_count,
4011 x_msg_data => x_msg_data,
4012 p_k_class => r_terminated.cls_code,
4013 p_k_id => r_terminated.id,
4014 p_k_number => r_terminated.contract_number,
4015 p_k_nbr_mod => r_terminated.contract_number_modifier,
4016 p_k_subclass => r_terminated.scs_code,
4017 p_estimated_amount => r_terminated.estimated_amount,
4018 P_K_STATUS_CODE => r_terminated.STS_CODE,
4019 p_term_date => r_terminated.date_terminated,
4020 p_term_reason => r_terminated.termination_reason);
4021
4022
4023 --
4024 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4025 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4026 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4027 raise OKC_API.G_EXCEPTION_ERROR;
4028 END IF;
4029 OKC_TIME_RES_PUB.res_time_termnt_k(
4030 P_CHR_ID => r_terminated.id,
4031 P_CLE_ID => NULL,
4032 P_END_DATE => r_terminated.DATE_TERMINATED,
4033 P_API_VERSION => 1.0 ,
4034 p_init_msg_list => p_init_msg_list,
4035 x_return_status => l_return_status
4036 );
4037
4038
4039 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4040 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4041 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4042 raise OKC_API.G_EXCEPTION_ERROR;
4043 END IF;
4044 --
4045 --+
4046 --+ remember header status for lines to follow it, not default
4047 --+
4048
4049 h_status := h_new_status;
4050 h_status_m := h_new_status_m ;
4051
4052 line_status_change (p_kid => r_terminated.id,
4053 p_cls_code => r_terminated.cls_code,
4054 p_scs_code => r_terminated.scs_code,
4055 p_k_num => r_terminated.contract_number,
4056 p_k_num_mod => r_terminated.contract_number_modifier,
4057 p_update_minor_version =>'N',
4058 x_return_status => l_return_status);
4059
4060
4061
4062 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4063 p_error_from_line := 'Y';
4064 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4065 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4066 p_error_from_line := 'Y';
4067 raise OKC_API.G_EXCEPTION_ERROR;
4068 END IF;
4069 header_message(p_knum_and_mod =>L_K_N_W_M,
4070 p_old_status =>r_terminated.meaning,
4071 p_status =>h_new_status_m,
4072 p_type =>'S');
4073 c:= c+1;
4074 EXCEPTION
4075 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4076 header_message(p_knum_and_mod =>L_K_N_W_M,
4077 p_status =>h_new_status_m,
4078 p_old_status =>r_terminated.meaning,
4079 p_msg_data => x_msg_data,
4080 p_type =>'U');
4081 if p_error_from_line <> 'Y' then
4082 p_hdr_errors := p_hdr_errors +1 ;
4083 else
4084 p_error_from_line := 'N';
4085 end if;
4086 rollback to H_STATUS;
4087 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4088 header_message(p_knum_and_mod =>L_K_N_W_M,
4089 p_status =>h_new_status_m,
4090 p_old_status =>r_terminated.meaning,
4091 p_msg_data => x_msg_data,
4092 p_type =>'E');
4093 if p_error_from_line <> 'Y' then
4094 p_hdr_errors := p_hdr_errors +1 ;
4095 else
4096 p_error_from_line := 'N';
4097 end if;
4098 rollback to H_STATUS;
4099 WHEN OTHERS then
4100 header_message(p_knum_and_mod =>L_K_N_W_M,
4101 p_status =>h_new_status_m,
4102 p_old_status =>r_terminated.meaning,
4103 p_msg_data => x_msg_data,
4104 p_type =>'U');
4105 p_hdr_errors := p_hdr_errors +1 ;
4106 rollback to H_STATUS;
4107 END;
4108
4109 END LOOP; -- from active hold signed to terminated
4110 commit;
4111 c:= 0;
4112 savepoint H_STATUS;
4113 ----------------------------------------------------------------------------------------
4114 l_new_status := null;
4115 l_new_status_m := null;
4116
4117 -- From Active, hold, signed to EXPIRED
4118 FOR r_expired in c_expired_hdr_all_cntr LOOP
4119
4120
4121 BEGIN
4122 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to expired' || C);
4123 if (C >= T) then
4124 commit;
4125 c := 0;
4126 end if;
4127
4128 savepoint H_STATUS;
4129 p_hdr_count:= p_hdr_count + 1;
4130 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4131 L_K_N_W_M := r_expired.K_N_W_M;
4132 l_new_status := null;
4133 l_new_status_m := null;
4134 h_status := null;
4135 h_new_status := v_expired;
4136 h_new_status_m := v_expired_m;
4137 h_status_type := 'EXPIRED';
4138
4139 l_chr_rec.id := r_expired.id ;
4140 l_chr_rec.object_version_number := r_expired.object_version_number ;
4141 l_chr_rec.sts_code := h_new_status ;
4142 l_chr_rec.old_sts_code := r_expired.sts_code;
4143 l_chr_rec.old_ste_code := r_expired.ste_code;
4144 l_chr_rec.new_sts_code := h_new_status;
4145 l_chr_rec.new_ste_code := h_status_type;
4146 --
4147 -- lock added not to depend on update implementation
4148 --
4149 okc_contract_pub.lock_contract_header(
4150 p_api_version => 1.0,
4151 p_init_msg_list => p_init_msg_list,
4152 x_return_status => l_return_status,
4153 x_msg_count => x_msg_count,
4154 x_msg_data => x_msg_data,
4155 p_chrv_rec => l_chr_rec);
4156 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4157 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4158 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4159 raise OKC_API.G_EXCEPTION_ERROR;
4160 END IF;
4161 --
4162 update_contract_header (
4163 p_api_version => 1.0,
4164 p_init_msg_list => p_init_msg_list,
4165 x_return_status => l_return_status,
4166 x_msg_count => x_msg_count,
4167 x_msg_data => x_msg_data,
4168 p_chrv_rec => l_chr_rec,
4169 x_chrv_rec => i_chr_rec);
4170 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4171 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4172 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4173 raise OKC_API.G_EXCEPTION_ERROR;
4174 END IF;
4175 --+
4176 --+ remember header status for lines to follow it, not default
4177 --+
4178 h_status := h_new_status;
4179 h_status_m := h_new_status_m ;
4180 line_status_change (p_kid => r_expired.id,
4181 p_cls_code => r_expired.cls_code,
4182 p_scs_code => r_expired.scs_code,
4183 p_k_num => r_expired.contract_number,
4184 p_k_num_mod => r_expired.contract_number_modifier,
4185 p_update_minor_version =>'N',
4186 x_return_status => l_return_status);
4187 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4188 p_error_from_line := 'Y';
4189 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4190 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4191 p_error_from_line := 'Y';
4192 raise OKC_API.G_EXCEPTION_ERROR;
4193 END IF;
4194 header_message(p_knum_and_mod =>L_K_N_W_M,
4195 p_status =>h_new_status_m,
4196 p_old_status =>r_expired.meaning,
4197 p_type =>'S');
4198
4199 c := c + 1;
4200 EXCEPTION
4201 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4202 header_message(p_knum_and_mod =>L_K_N_W_M,
4203 p_status =>h_new_status_m,
4204 p_old_status =>r_expired.meaning,
4205 p_msg_data => x_msg_data,
4206 p_type =>'U');
4207 if p_error_from_line <> 'Y' then
4208 p_hdr_errors := p_hdr_errors +1 ;
4209 else
4210 p_error_from_line := 'N';
4211 end if;
4212 rollback to H_STATUS;
4213 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4214 header_message(p_knum_and_mod =>L_K_N_W_M,
4215 p_status =>h_new_status_m,
4216 p_old_status =>r_expired.meaning,
4217 p_msg_data => x_msg_data,
4218 p_type =>'E');
4219 if p_error_from_line <> 'Y' then
4220 p_hdr_errors := p_hdr_errors +1 ;
4221 else
4222 p_error_from_line := 'N';
4223 end if;
4224 rollback to H_STATUS;
4225 WHEN OTHERS then
4226 header_message(p_knum_and_mod =>L_K_N_W_M,
4227 p_status =>h_new_status_m,
4228 p_old_status =>r_expired.meaning,
4229 p_msg_data => x_msg_data,
4230 p_type =>'U');
4231 p_hdr_errors := p_hdr_errors +1 ;
4232 rollback to H_STATUS;
4233 END;
4234 END LOOP; -- From Active, hold, signed to EXPIRED
4235
4236 commit;
4237 c:= 0;
4238 savepoint H_STATUS;
4239 l_new_status := null;
4240 l_new_status_m := null;
4241 ----------------------------------------------------------------------------------------
4242 -- From Signed to Active
4243 FOR r_active in c_actv_hdr_all_cntr LOOP
4244
4245 BEGIN
4246 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from signed to active' || C);
4247 if (C >= T) then
4248 commit;
4249 c := 0;
4250 end if;
4251 savepoint H_STATUS;
4252 p_hdr_count:= p_hdr_count + 1;
4253 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4254 L_K_N_W_M := r_active.K_N_W_M;
4255
4256 l_new_status := null;
4257 l_new_status_m := null;
4258 h_status := null;
4259 h_new_status := v_active ;
4260 h_new_status_m := v_active_m ;
4261 h_status_type := 'ACTIVE';
4262
4263 l_chr_rec.id := r_active.id ;
4264 l_chr_rec.object_version_number := r_active.object_version_number ;
4265 l_chr_rec.sts_code := h_new_status ;
4266 l_chr_rec.old_sts_code := r_active.sts_code;
4267 l_chr_rec.old_ste_code := r_active.ste_code;
4268 l_chr_rec.new_sts_code := h_new_status;
4269 l_chr_rec.new_ste_code := h_status_type;
4270 --
4271 okc_contract_pub.lock_contract_header(
4272 p_api_version => 1.0,
4273 p_init_msg_list => p_init_msg_list,
4274 x_return_status => l_return_status,
4275 x_msg_count => x_msg_count,
4276 x_msg_data => x_msg_data,
4277 p_chrv_rec => l_chr_rec);
4278
4279 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4280 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4281 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4282 raise OKC_API.G_EXCEPTION_ERROR;
4283 END IF;
4284 --
4285 update_contract_header (
4286 p_api_version => 1.0,
4287 p_init_msg_list => p_init_msg_list,
4288 x_return_status => l_return_status,
4289 x_msg_count => x_msg_count,
4290 x_msg_data => x_msg_data,
4291 p_chrv_rec => l_chr_rec,
4292 x_chrv_rec => i_chr_rec);
4293
4294 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4295 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4296 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4297 raise OKC_API.G_EXCEPTION_ERROR;
4298 END IF;
4299 --+
4300 --+ remember header status for lines to follow it, not default
4301 --+
4302 h_status := h_new_status;
4303 h_status_m := h_new_status_m ;
4304
4305 line_status_change (p_kid => r_active.id,
4306 p_cls_code => r_active.cls_code,
4307 p_scs_code => r_active.scs_code,
4308 p_k_num => r_active.contract_number,
4309 p_k_num_mod => r_active.contract_number_modifier,
4310 p_update_minor_version =>'N',
4311 x_return_status => l_return_status);
4312
4313 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4314 p_error_from_line := 'Y';
4315 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4316 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4317 p_error_from_line := 'Y';
4318 raise OKC_API.G_EXCEPTION_ERROR;
4319 END IF;
4320 header_message(p_knum_and_mod =>L_K_N_W_M,
4321 p_status =>h_new_status_m,
4322 p_old_status =>r_active.meaning,
4323 p_type =>'S');
4324 c := c+1;
4325 EXCEPTION
4326 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4327 header_message(p_knum_and_mod =>L_K_N_W_M,
4328 p_status =>h_new_status_m,
4329 p_old_status =>r_active.meaning,
4330 p_msg_data => x_msg_data,
4331 p_type =>'U');
4332 if p_error_from_line <> 'Y' then
4333 p_hdr_errors := p_hdr_errors +1 ;
4334 else
4335 p_error_from_line := 'N';
4336 end if;
4337 rollback to H_STATUS;
4338 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4339 header_message(p_knum_and_mod =>L_K_N_W_M,
4340 p_status =>h_new_status_m,
4341 p_old_status =>r_active.meaning,
4342 p_msg_data => x_msg_data,
4343 p_type =>'E');
4344 if p_error_from_line <> 'Y' then
4345 p_hdr_errors := p_hdr_errors +1 ;
4346 else
4347 p_error_from_line := 'N';
4348 end if;
4349 rollback to H_STATUS;
4350 WHEN OTHERS then
4351 header_message(p_knum_and_mod =>L_K_N_W_M,
4352 p_status =>h_new_status_m,
4353 p_old_status =>r_active.meaning,
4354 p_msg_data => x_msg_data,
4355 p_type =>'U');
4356 p_hdr_errors := p_hdr_errors +1 ;
4357 rollback to H_STATUS;
4358 END;
4359 END LOOP; -- From Signed to Active
4360
4361 commit;
4362 c := 0;
4363
4364 savepoint H_STATUS;
4365 l_new_status := null;
4366 l_new_status_m := null;
4367 ----------------------------------------------------------------------------------------
4368 --bug 5930684
4369 -- From Active to Signed
4370
4371 FOR r_signed in c_sig_hdr_all_cntr LOOP
4372
4373 BEGIN
4374 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active to signed ' || C);
4375 if (C >= T) then
4376 commit;
4377 c := 0;
4378 end if;
4379 savepoint H_STATUS;
4380 p_hdr_count:= p_hdr_count + 1;
4381 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4382 L_K_N_W_M := r_signed.K_N_W_M;
4383
4384 l_new_status := null;
4385 l_new_status_m := null;
4386 h_status := null;
4387 h_new_status := v_signed ;
4388 h_new_status_m := v_signed_m ;
4389 h_status_type := 'SIGNED';
4390
4391 l_chr_rec.id := r_signed.id ;
4392 l_chr_rec.object_version_number := r_signed.object_version_number ;
4393 l_chr_rec.sts_code := h_new_status ;
4394 l_chr_rec.old_sts_code := r_signed.sts_code;
4395 l_chr_rec.old_ste_code := r_signed.ste_code;
4396 l_chr_rec.new_sts_code := h_new_status;
4397 l_chr_rec.new_ste_code := h_status_type;
4398 --
4399 okc_contract_pub.lock_contract_header(
4400 p_api_version => 1.0,
4401 p_init_msg_list => p_init_msg_list,
4402 x_return_status => l_return_status,
4403 x_msg_count => x_msg_count,
4404 x_msg_data => x_msg_data,
4405 p_chrv_rec => l_chr_rec);
4406
4407 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4408 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4409 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4410 raise OKC_API.G_EXCEPTION_ERROR;
4411 END IF;
4412 --
4413 update_contract_header (
4414 p_api_version => 1.0,
4415 p_init_msg_list => p_init_msg_list,
4416 x_return_status => l_return_status,
4417 x_msg_count => x_msg_count,
4418 x_msg_data => x_msg_data,
4419 p_chrv_rec => l_chr_rec,
4420 x_chrv_rec => i_chr_rec);
4421
4422 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4423 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4424 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4425 raise OKC_API.G_EXCEPTION_ERROR;
4426 END IF;
4427 --+
4428 --+ remember header status for lines to follow it, not default
4429 --+
4430 h_status := h_new_status;
4431 h_status_m := h_new_status_m ;
4432
4433 line_status_change (p_kid => r_signed.id,
4434 p_cls_code => r_signed.cls_code,
4435 p_scs_code => r_signed.scs_code,
4436 p_k_num => r_signed.contract_number,
4437 p_k_num_mod => r_signed.contract_number_modifier,
4438 p_update_minor_version =>'N',
4439 x_return_status => l_return_status);
4440
4441 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4442 p_error_from_line := 'Y';
4443 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4444 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4445 p_error_from_line := 'Y';
4446 raise OKC_API.G_EXCEPTION_ERROR;
4447 END IF;
4448 header_message(p_knum_and_mod =>L_K_N_W_M,
4449 p_status =>h_new_status_m,
4450 p_old_status =>r_signed.meaning,
4451 p_type =>'S');
4452 c := c+1;
4453 EXCEPTION
4454 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4455 header_message(p_knum_and_mod =>L_K_N_W_M,
4456 p_status =>h_new_status_m,
4457 p_old_status =>r_signed.meaning,
4458 p_msg_data => x_msg_data,
4459 p_type =>'U');
4460 if p_error_from_line <> 'Y' then
4461 p_hdr_errors := p_hdr_errors +1 ;
4462 else
4463 p_error_from_line := 'N';
4464 end if;
4465 rollback to H_STATUS;
4466 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4467 header_message(p_knum_and_mod =>L_K_N_W_M,
4468 p_status =>h_new_status_m,
4469 p_old_status =>r_signed.meaning,
4470 p_msg_data => x_msg_data,
4471 p_type =>'E');
4472 if p_error_from_line <> 'Y' then
4473 p_hdr_errors := p_hdr_errors +1 ;
4474 else
4475 p_error_from_line := 'N';
4476 end if;
4477 rollback to H_STATUS;
4478 WHEN OTHERS then
4479 header_message(p_knum_and_mod =>L_K_N_W_M,
4480 p_status =>h_new_status_m,
4481 p_old_status =>r_signed.meaning,
4482 p_msg_data => x_msg_data,
4483 p_type =>'U');
4484 p_hdr_errors := p_hdr_errors +1 ;
4485 rollback to H_STATUS;
4486 END;
4487 END LOOP; -- From Active to signed
4488
4489 commit;
4490 c := 0;
4491 --- end of bug 5930684
4492 ELSE
4493 /*when only contract from-to range is provided*/
4494 /*changes made for bug6475371*/
4495 -- from active, signed to terminated
4496 c := 0;
4497 l_new_status := null;
4498 l_new_status_m := null;
4499
4500 FND_FILE.PUT_LINE(FND_FILE.LOG,'when only contract from-to range is provided as input');
4501
4502 FOR r_terminated in c_termn_hdr_only_contract LOOP
4503
4504
4505 BEGIN
4506 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to terminated ' || C);
4507 if (C >= T) then
4508 commit;
4509 c := 0;
4510 end if;
4511 savepoint H_STATUS;
4512 p_hdr_count:= p_hdr_count + 1;
4513 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4514 L_K_N_W_M := r_terminated.K_N_W_M;
4515
4516 l_new_status := null;
4517 l_new_status_m := null;
4518 h_status := null;
4519 h_new_status := v_terminated ;
4520 h_new_status_m := v_terminated_m ;
4521 h_status_type := 'TERMINATED';
4522
4523 l_chr_rec.id := r_terminated.id ;
4524 l_chr_rec.object_version_number := r_terminated.object_version_number ;
4525 l_chr_rec.sts_code := h_new_status ;
4526 l_chr_rec.old_sts_code := r_terminated.sts_code;
4527 l_chr_rec.old_ste_code := r_terminated.ste_code;
4528 l_chr_rec.new_sts_code := h_new_status;
4529 l_chr_rec.new_ste_code := h_status_type;
4530 --
4531 -- End: Added for Status Change Action Assembler Changes 10/19/2000
4532 --
4533 -- lock added not to depend on update implementation
4534 --
4535 okc_contract_pub.lock_contract_header(
4536 p_api_version => 1.0,
4537 p_init_msg_list => p_init_msg_list,
4538 x_return_status => l_return_status,
4539 x_msg_count => x_msg_count,
4540 x_msg_data => x_msg_data,
4541 p_chrv_rec => l_chr_rec);
4542 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4543 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4544 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4545 raise OKC_API.G_EXCEPTION_ERROR;
4546 END IF;
4547 --
4548 --
4549 update_contract_header (
4550 p_api_version => 1.0,
4551 p_init_msg_list => p_init_msg_list,
4552 x_return_status => l_return_status,
4553 x_msg_count => x_msg_count,
4554 x_msg_data => x_msg_data,
4555 p_chrv_rec => l_chr_rec,
4556 x_chrv_rec => i_chr_rec);
4557 FND_FILE.PUT_LINE(FND_FILE.LOG,'update_contract_header ' || l_return_status);
4558
4559 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4560 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4561 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4562 raise OKC_API.G_EXCEPTION_ERROR;
4563 END IF;
4564
4565 OKC_K_TERM_ASMBLR_PVT.acn_assemble(
4566 p_api_version => 1.0 ,
4567 p_init_msg_list => p_init_msg_list,
4568 x_return_status => l_return_status,
4569 x_msg_count => x_msg_count,
4570 x_msg_data => x_msg_data,
4571 p_k_class => r_terminated.cls_code,
4572 p_k_id => r_terminated.id,
4573 p_k_number => r_terminated.contract_number,
4574 p_k_nbr_mod => r_terminated.contract_number_modifier,
4575 p_k_subclass => r_terminated.scs_code,
4576 p_estimated_amount => r_terminated.estimated_amount,
4577 P_K_STATUS_CODE => r_terminated.STS_CODE,
4578 p_term_date => r_terminated.date_terminated,
4579 p_term_reason => r_terminated.termination_reason);
4580 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKC_K_TERM_ASMBLR_PVT ' || l_return_status);
4581
4582 --
4583 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4584 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4585 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4586 raise OKC_API.G_EXCEPTION_ERROR;
4587 END IF;
4588 OKC_TIME_RES_PUB.res_time_termnt_k(
4589 P_CHR_ID => r_terminated.id,
4590 P_CLE_ID => NULL,
4591 P_END_DATE => r_terminated.DATE_TERMINATED,
4592 P_API_VERSION => 1.0 ,
4593 p_init_msg_list => p_init_msg_list,
4594 x_return_status => l_return_status
4595 );
4596
4597 FND_FILE.PUT_LINE(FND_FILE.LOG,'res_time_termnt_k ' || l_return_status);
4598
4599 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4600 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4601 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4602 raise OKC_API.G_EXCEPTION_ERROR;
4603 END IF;
4604 --
4605 --+
4606 --+ remember header status for lines to follow it, not default
4607 --+
4608
4609 h_status := h_new_status;
4610 h_status_m := h_new_status_m ;
4611
4612 line_status_change (p_kid => r_terminated.id,
4613 p_cls_code => r_terminated.cls_code,
4614 p_scs_code => r_terminated.scs_code,
4615 p_k_num => r_terminated.contract_number,
4616 p_k_num_mod => r_terminated.contract_number_modifier,
4617 p_update_minor_version =>'N',
4618 x_return_status => l_return_status);
4619 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_status_change ' || l_return_status);
4620
4621
4622 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4623 p_error_from_line := 'Y';
4624 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4625 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4626 p_error_from_line := 'Y';
4627 raise OKC_API.G_EXCEPTION_ERROR;
4628 END IF;
4629 header_message(p_knum_and_mod =>L_K_N_W_M,
4630 p_old_status =>r_terminated.meaning,
4631 p_status =>h_new_status_m,
4632 p_type =>'S');
4633 c:= c+1;
4634 EXCEPTION
4635 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4636 header_message(p_knum_and_mod =>L_K_N_W_M,
4637 p_status =>h_new_status_m,
4638 p_old_status =>r_terminated.meaning,
4639 p_msg_data => x_msg_data,
4640 p_type =>'U');
4641 if p_error_from_line <> 'Y' then
4642 p_hdr_errors := p_hdr_errors +1 ;
4643 else
4644 p_error_from_line := 'N';
4645 end if;
4646 rollback to H_STATUS;
4647 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4648 header_message(p_knum_and_mod =>L_K_N_W_M,
4649 p_status =>h_new_status_m,
4650 p_old_status =>r_terminated.meaning,
4651 p_msg_data => x_msg_data,
4652 p_type =>'E');
4653 if p_error_from_line <> 'Y' then
4654 p_hdr_errors := p_hdr_errors +1 ;
4655 else
4656 p_error_from_line := 'N';
4657 end if;
4658 rollback to H_STATUS;
4659 WHEN OTHERS then
4660 header_message(p_knum_and_mod =>L_K_N_W_M,
4661 p_status =>h_new_status_m,
4662 p_old_status =>r_terminated.meaning,
4663 p_msg_data => x_msg_data,
4664 p_type =>'U');
4665 p_hdr_errors := p_hdr_errors +1 ;
4666 rollback to H_STATUS;
4667 END;
4668
4669 END LOOP; -- from active hold signed to terminated
4670 commit;
4671 c:= 0;
4672 savepoint H_STATUS;
4673 ----------------------------------------------------------------------------------------
4674 l_new_status := null;
4675 l_new_status_m := null;
4676
4677 -- From Active, signed to EXPIRED
4678 FOR r_expired in c_exp_hdr_only_contract LOOP
4679
4680
4681 BEGIN
4682 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to expired' || C);
4683 if (C >= T) then
4684 commit;
4685 c := 0;
4686 end if;
4687
4688 savepoint H_STATUS;
4689 p_hdr_count:= p_hdr_count + 1;
4690 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4691 L_K_N_W_M := r_expired.K_N_W_M;
4692 l_new_status := null;
4693 l_new_status_m := null;
4694 h_status := null;
4695 h_new_status := v_expired;
4696 h_new_status_m := v_expired_m;
4697 h_status_type := 'EXPIRED';
4698
4699 l_chr_rec.id := r_expired.id ;
4700 l_chr_rec.object_version_number := r_expired.object_version_number ;
4701 l_chr_rec.sts_code := h_new_status ;
4702 l_chr_rec.old_sts_code := r_expired.sts_code;
4703 l_chr_rec.old_ste_code := r_expired.ste_code;
4704 l_chr_rec.new_sts_code := h_new_status;
4705 l_chr_rec.new_ste_code := h_status_type;
4706 --
4707 -- lock added not to depend on update implementation
4708 --
4709 okc_contract_pub.lock_contract_header(
4710 p_api_version => 1.0,
4711 p_init_msg_list => p_init_msg_list,
4712 x_return_status => l_return_status,
4713 x_msg_count => x_msg_count,
4714 x_msg_data => x_msg_data,
4715 p_chrv_rec => l_chr_rec);
4716 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4717 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4718 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4719 raise OKC_API.G_EXCEPTION_ERROR;
4720 END IF;
4721 --
4722 update_contract_header (
4723 p_api_version => 1.0,
4724 p_init_msg_list => p_init_msg_list,
4725 x_return_status => l_return_status,
4726 x_msg_count => x_msg_count,
4727 x_msg_data => x_msg_data,
4728 p_chrv_rec => l_chr_rec,
4729 x_chrv_rec => i_chr_rec);
4730 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4731 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4732 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4733 raise OKC_API.G_EXCEPTION_ERROR;
4734 END IF;
4735 --+
4736 --+ remember header status for lines to follow it, not default
4737 --+
4738 h_status := h_new_status;
4739 h_status_m := h_new_status_m ;
4740 line_status_change (p_kid => r_expired.id,
4741 p_cls_code => r_expired.cls_code,
4742 p_scs_code => r_expired.scs_code,
4743 p_k_num => r_expired.contract_number,
4744 p_k_num_mod => r_expired.contract_number_modifier,
4745 p_update_minor_version =>'N',
4746 x_return_status => l_return_status);
4747 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4748 p_error_from_line := 'Y';
4749 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4750 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4751 p_error_from_line := 'Y';
4752 raise OKC_API.G_EXCEPTION_ERROR;
4753 END IF;
4754 header_message(p_knum_and_mod =>L_K_N_W_M,
4755 p_status =>h_new_status_m,
4756 p_old_status =>r_expired.meaning,
4757 p_type =>'S');
4758
4759 c := c + 1;
4760 EXCEPTION
4761 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4762 header_message(p_knum_and_mod =>L_K_N_W_M,
4763 p_status =>h_new_status_m,
4764 p_old_status =>r_expired.meaning,
4765 p_msg_data => x_msg_data,
4766 p_type =>'U');
4767 if p_error_from_line <> 'Y' then
4768 p_hdr_errors := p_hdr_errors +1 ;
4769 else
4770 p_error_from_line := 'N';
4771 end if;
4772 rollback to H_STATUS;
4773 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4774 header_message(p_knum_and_mod =>L_K_N_W_M,
4775 p_status =>h_new_status_m,
4776 p_old_status =>r_expired.meaning,
4777 p_msg_data => x_msg_data,
4778 p_type =>'E');
4779 if p_error_from_line <> 'Y' then
4780 p_hdr_errors := p_hdr_errors +1 ;
4781 else
4782 p_error_from_line := 'N';
4783 end if;
4784 rollback to H_STATUS;
4785 WHEN OTHERS then
4786 header_message(p_knum_and_mod =>L_K_N_W_M,
4787 p_status =>h_new_status_m,
4788 p_old_status =>r_expired.meaning,
4789 p_msg_data => x_msg_data,
4790 p_type =>'U');
4791 p_hdr_errors := p_hdr_errors +1 ;
4792 rollback to H_STATUS;
4793 END;
4794 END LOOP; -- From Active, hold, signed to EXPIRED
4795
4796 commit;
4797 c:= 0;
4798 savepoint H_STATUS;
4799 l_new_status := null;
4800 l_new_status_m := null;
4801 ----------------------------------------------------------------------------------------
4802 -- From Signed to Active
4803 FOR r_active in c_actv_hdr_only_contract LOOP
4804
4805 BEGIN
4806 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from signed to active ' || C);
4807 if (C >= T) then
4808 commit;
4809 c := 0;
4810 end if;
4811 savepoint H_STATUS;
4812 p_hdr_count:= p_hdr_count + 1;
4813 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4814 L_K_N_W_M := r_active.K_N_W_M;
4815
4816 l_new_status := null;
4817 l_new_status_m := null;
4818 h_status := null;
4819 h_new_status := v_active ;
4820 h_new_status_m := v_active_m ;
4821 h_status_type := 'ACTIVE';
4822
4823 l_chr_rec.id := r_active.id ;
4824 l_chr_rec.object_version_number := r_active.object_version_number ;
4825 l_chr_rec.sts_code := h_new_status ;
4826 l_chr_rec.old_sts_code := r_active.sts_code;
4827 l_chr_rec.old_ste_code := r_active.ste_code;
4828 l_chr_rec.new_sts_code := h_new_status;
4829 l_chr_rec.new_ste_code := h_status_type;
4830 --
4831 okc_contract_pub.lock_contract_header(
4832 p_api_version => 1.0,
4833 p_init_msg_list => p_init_msg_list,
4834 x_return_status => l_return_status,
4835 x_msg_count => x_msg_count,
4836 x_msg_data => x_msg_data,
4837 p_chrv_rec => l_chr_rec);
4838
4839 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4840 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4841 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4842 raise OKC_API.G_EXCEPTION_ERROR;
4843 END IF;
4844 --
4845 update_contract_header (
4846 p_api_version => 1.0,
4847 p_init_msg_list => p_init_msg_list,
4848 x_return_status => l_return_status,
4849 x_msg_count => x_msg_count,
4850 x_msg_data => x_msg_data,
4851 p_chrv_rec => l_chr_rec,
4852 x_chrv_rec => i_chr_rec);
4853
4854 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4855 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4856 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4857 raise OKC_API.G_EXCEPTION_ERROR;
4858 END IF;
4859 --+
4860 --+ remember header status for lines to follow it, not default
4861 --+
4862 h_status := h_new_status;
4863 h_status_m := h_new_status_m ;
4864
4865 line_status_change (p_kid => r_active.id,
4866 p_cls_code => r_active.cls_code,
4867 p_scs_code => r_active.scs_code,
4868 p_k_num => r_active.contract_number,
4869 p_k_num_mod => r_active.contract_number_modifier,
4870 p_update_minor_version =>'N',
4871 x_return_status => l_return_status);
4872
4873 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4874 p_error_from_line := 'Y';
4875 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4876 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4877 p_error_from_line := 'Y';
4878 raise OKC_API.G_EXCEPTION_ERROR;
4879 END IF;
4880 header_message(p_knum_and_mod =>L_K_N_W_M,
4881 p_status =>h_new_status_m,
4882 p_old_status =>r_active.meaning,
4883 p_type =>'S');
4884 c := c+1;
4885 EXCEPTION
4886 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4887 header_message(p_knum_and_mod =>L_K_N_W_M,
4888 p_status =>h_new_status_m,
4889 p_old_status =>r_active.meaning,
4890 p_msg_data => x_msg_data,
4891 p_type =>'U');
4892 if p_error_from_line <> 'Y' then
4893 p_hdr_errors := p_hdr_errors +1 ;
4894 else
4895 p_error_from_line := 'N';
4896 end if;
4897 rollback to H_STATUS;
4898 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4899 header_message(p_knum_and_mod =>L_K_N_W_M,
4900 p_status =>h_new_status_m,
4901 p_old_status =>r_active.meaning,
4902 p_msg_data => x_msg_data,
4903 p_type =>'E');
4904 if p_error_from_line <> 'Y' then
4905 p_hdr_errors := p_hdr_errors +1 ;
4906 else
4907 p_error_from_line := 'N';
4908 end if;
4909 rollback to H_STATUS;
4910 WHEN OTHERS then
4911 header_message(p_knum_and_mod =>L_K_N_W_M,
4912 p_status =>h_new_status_m,
4913 p_old_status =>r_active.meaning,
4914 p_msg_data => x_msg_data,
4915 p_type =>'U');
4916 p_hdr_errors := p_hdr_errors +1 ;
4917 rollback to H_STATUS;
4918 END;
4919 END LOOP; -- From Signed to Active
4920
4921 commit;
4922 c := 0;
4923 savepoint H_STATUS;
4924 l_new_status := null;
4925 l_new_status_m := null;
4926 ----------------------------------------------------------------------------------------
4927 --bug 5866484
4928 -- From Active to Signed
4929
4930 FOR r_signed in c_sig_hdr_only_contract LOOP
4931
4932 BEGIN
4933 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active to signed ' || C);
4934 if (C >= T) then
4935 commit;
4936 c := 0;
4937 end if;
4938 savepoint H_STATUS;
4939 p_hdr_count:= p_hdr_count + 1;
4940 l_return_status := OKC_API.G_RET_STS_SUCCESS;
4941 L_K_N_W_M := r_signed.K_N_W_M;
4942
4943 l_new_status := null;
4944 l_new_status_m := null;
4945 h_status := null;
4946 h_new_status := v_signed ;
4947 h_new_status_m := v_signed_m ;
4948 h_status_type := 'SIGNED';
4949
4950 l_chr_rec.id := r_signed.id ;
4951 l_chr_rec.object_version_number := r_signed.object_version_number ;
4952 l_chr_rec.sts_code := h_new_status ;
4953 l_chr_rec.old_sts_code := r_signed.sts_code;
4954 l_chr_rec.old_ste_code := r_signed.ste_code;
4955 l_chr_rec.new_sts_code := h_new_status;
4956 l_chr_rec.new_ste_code := h_status_type;
4957 --
4958 okc_contract_pub.lock_contract_header(
4959 p_api_version => 1.0,
4960 p_init_msg_list => p_init_msg_list,
4961 x_return_status => l_return_status,
4962 x_msg_count => x_msg_count,
4963 x_msg_data => x_msg_data,
4964 p_chrv_rec => l_chr_rec);
4965
4966 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4967 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4968 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4969 raise OKC_API.G_EXCEPTION_ERROR;
4970 END IF;
4971 --
4972 update_contract_header (
4973 p_api_version => 1.0,
4974 p_init_msg_list => p_init_msg_list,
4975 x_return_status => l_return_status,
4976 x_msg_count => x_msg_count,
4977 x_msg_data => x_msg_data,
4978 p_chrv_rec => l_chr_rec,
4979 x_chrv_rec => i_chr_rec);
4980
4981 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4982 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4983 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4984 raise OKC_API.G_EXCEPTION_ERROR;
4985 END IF;
4986 --+
4987 --+ remember header status for lines to follow it, not default
4988 --+
4989 h_status := h_new_status;
4990 h_status_m := h_new_status_m ;
4991
4992 line_status_change (p_kid => r_signed.id,
4993 p_cls_code => r_signed.cls_code,
4994 p_scs_code => r_signed.scs_code,
4995 p_k_num => r_signed.contract_number,
4996 p_k_num_mod => r_signed.contract_number_modifier,
4997 p_update_minor_version =>'N',
4998 x_return_status => l_return_status);
4999
5000 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5001 p_error_from_line := 'Y';
5002 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5003 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5004 p_error_from_line := 'Y';
5005 raise OKC_API.G_EXCEPTION_ERROR;
5006 END IF;
5007 header_message(p_knum_and_mod =>L_K_N_W_M,
5008 p_status =>h_new_status_m,
5009 p_old_status =>r_signed.meaning,
5010 p_type =>'S');
5011 c := c+1;
5012 EXCEPTION
5013 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5014 header_message(p_knum_and_mod =>L_K_N_W_M,
5015 p_status =>h_new_status_m,
5016 p_old_status =>r_signed.meaning,
5017 p_msg_data => x_msg_data,
5018 p_type =>'U');
5019 if p_error_from_line <> 'Y' then
5020 p_hdr_errors := p_hdr_errors +1 ;
5021 else
5022 p_error_from_line := 'N';
5023 end if;
5024 rollback to H_STATUS;
5025 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5026 header_message(p_knum_and_mod =>L_K_N_W_M,
5027 p_status =>h_new_status_m,
5028 p_old_status =>r_signed.meaning,
5029 p_msg_data => x_msg_data,
5030 p_type =>'E');
5031 if p_error_from_line <> 'Y' then
5032 p_hdr_errors := p_hdr_errors +1 ;
5033 else
5034 p_error_from_line := 'N';
5035 end if;
5036 rollback to H_STATUS;
5037 WHEN OTHERS then
5038 header_message(p_knum_and_mod =>L_K_N_W_M,
5039 p_status =>h_new_status_m,
5040 p_old_status =>r_signed.meaning,
5041 p_msg_data => x_msg_data,
5042 p_type =>'U');
5043 p_hdr_errors := p_hdr_errors +1 ;
5044 rollback to H_STATUS;
5045 END;
5046 END LOOP; -- From Active to Signed
5047
5048 commit;
5049 c := 0;
5050 END IF;
5051
5052 ELSIF (p_from_k is null) AND (p_category is not null) THEN
5053 /*WHEN ONLY CATEGORY IS PROVIDED*/
5054 /*changes made for bug6475371*/
5055
5056 -- from active signed to terminated
5057 c := 0;
5058 l_new_status := null;
5059 l_new_status_m := null;
5060
5061 FND_FILE.PUT_LINE(FND_FILE.LOG,'Category is provided as input ' || p_category);
5062
5063 FOR r_terminated in c_termn_hdr_only_category LOOP
5064
5065
5066 BEGIN
5067 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to terminated' || C);
5068 if (C >= T) then
5069 commit;
5070 c := 0;
5071 end if;
5072 savepoint H_STATUS;
5073 p_hdr_count:= p_hdr_count + 1;
5074 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5075 L_K_N_W_M := r_terminated.K_N_W_M;
5076
5077 l_new_status := null;
5078 l_new_status_m := null;
5079 h_status := null;
5080 h_new_status := v_terminated ;
5081 h_new_status_m := v_terminated_m ;
5082 h_status_type := 'TERMINATED';
5083
5084 l_chr_rec.id := r_terminated.id ;
5085 l_chr_rec.object_version_number := r_terminated.object_version_number ;
5086 l_chr_rec.sts_code := h_new_status ;
5087 l_chr_rec.old_sts_code := r_terminated.sts_code;
5088 l_chr_rec.old_ste_code := r_terminated.ste_code;
5089 l_chr_rec.new_sts_code := h_new_status;
5090 l_chr_rec.new_ste_code := h_status_type;
5091 --
5092 -- End: Added for Status Change Action Assembler Changes 10/19/2000
5093 --
5094 -- lock added not to depend on update implementation
5095 --
5096 okc_contract_pub.lock_contract_header(
5097 p_api_version => 1.0,
5098 p_init_msg_list => p_init_msg_list,
5099 x_return_status => l_return_status,
5100 x_msg_count => x_msg_count,
5101 x_msg_data => x_msg_data,
5102 p_chrv_rec => l_chr_rec);
5103 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5104 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5105 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5106 raise OKC_API.G_EXCEPTION_ERROR;
5107 END IF;
5108 --
5109 --
5110 update_contract_header (
5111 p_api_version => 1.0,
5112 p_init_msg_list => p_init_msg_list,
5113 x_return_status => l_return_status,
5114 x_msg_count => x_msg_count,
5115 x_msg_data => x_msg_data,
5116 p_chrv_rec => l_chr_rec,
5117 x_chrv_rec => i_chr_rec);
5118 FND_FILE.PUT_LINE(FND_FILE.LOG,'update_contract_header ' || l_return_status);
5119
5120 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5121 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5122 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5123 raise OKC_API.G_EXCEPTION_ERROR;
5124 END IF;
5125
5126 OKC_K_TERM_ASMBLR_PVT.acn_assemble(
5127 p_api_version => 1.0 ,
5128 p_init_msg_list => p_init_msg_list,
5129 x_return_status => l_return_status,
5130 x_msg_count => x_msg_count,
5131 x_msg_data => x_msg_data,
5132 p_k_class => r_terminated.cls_code,
5133 p_k_id => r_terminated.id,
5134 p_k_number => r_terminated.contract_number,
5135 p_k_nbr_mod => r_terminated.contract_number_modifier,
5136 p_k_subclass => r_terminated.scs_code,
5137 p_estimated_amount => r_terminated.estimated_amount,
5138 P_K_STATUS_CODE => r_terminated.STS_CODE,
5139 p_term_date => r_terminated.date_terminated,
5140 p_term_reason => r_terminated.termination_reason);
5141 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKC_K_TERM_ASMBLR_PVT ' || l_return_status);
5142
5143 --
5144 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5145 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5146 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5147 raise OKC_API.G_EXCEPTION_ERROR;
5148 END IF;
5149 OKC_TIME_RES_PUB.res_time_termnt_k(
5150 P_CHR_ID => r_terminated.id,
5151 P_CLE_ID => NULL,
5152 P_END_DATE => r_terminated.DATE_TERMINATED,
5153 P_API_VERSION => 1.0 ,
5154 p_init_msg_list => p_init_msg_list,
5155 x_return_status => l_return_status
5156 );
5157
5158 FND_FILE.PUT_LINE(FND_FILE.LOG,'res_time_termnt_k ' || l_return_status);
5159
5160 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5161 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5162 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5163 raise OKC_API.G_EXCEPTION_ERROR;
5164 END IF;
5165 --
5166 --+
5167 --+ remember header status for lines to follow it, not default
5168 --+
5169
5170 h_status := h_new_status;
5171 h_status_m := h_new_status_m ;
5172
5173 line_status_change (p_kid => r_terminated.id,
5174 p_cls_code => r_terminated.cls_code,
5175 p_scs_code => r_terminated.scs_code,
5176 p_k_num => r_terminated.contract_number,
5177 p_k_num_mod => r_terminated.contract_number_modifier,
5178 p_update_minor_version =>'N',
5179 x_return_status => l_return_status);
5180 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_status_change ' || l_return_status);
5181
5182
5183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5184 p_error_from_line := 'Y';
5185 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5186 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5187 p_error_from_line := 'Y';
5188 raise OKC_API.G_EXCEPTION_ERROR;
5189 END IF;
5190 header_message(p_knum_and_mod =>L_K_N_W_M,
5191 p_old_status =>r_terminated.meaning,
5192 p_status =>h_new_status_m,
5193 p_type =>'S');
5194 c:= c+1;
5195 EXCEPTION
5196 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5197 header_message(p_knum_and_mod =>L_K_N_W_M,
5198 p_status =>h_new_status_m,
5199 p_old_status =>r_terminated.meaning,
5200 p_msg_data => x_msg_data,
5201 p_type =>'U');
5202 if p_error_from_line <> 'Y' then
5203 p_hdr_errors := p_hdr_errors +1 ;
5204 else
5205 p_error_from_line := 'N';
5206 end if;
5207 rollback to H_STATUS;
5208 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5209 header_message(p_knum_and_mod =>L_K_N_W_M,
5210 p_status =>h_new_status_m,
5211 p_old_status =>r_terminated.meaning,
5212 p_msg_data => x_msg_data,
5213 p_type =>'E');
5214 if p_error_from_line <> 'Y' then
5215 p_hdr_errors := p_hdr_errors +1 ;
5216 else
5217 p_error_from_line := 'N';
5218 end if;
5219 rollback to H_STATUS;
5220 WHEN OTHERS then
5221 header_message(p_knum_and_mod =>L_K_N_W_M,
5222 p_status =>h_new_status_m,
5223 p_old_status =>r_terminated.meaning,
5224 p_msg_data => x_msg_data,
5225 p_type =>'U');
5226 p_hdr_errors := p_hdr_errors +1 ;
5227 rollback to H_STATUS;
5228 END;
5229
5230 END LOOP; -- from active signed to terminated
5231 commit;
5232 c:= 0;
5233 savepoint H_STATUS;
5234 ----------------------------------------------------------------------------------------
5235 l_new_status := null;
5236 l_new_status_m := null;
5237
5238 -- From Active, signed to EXPIRED
5239 FOR r_expired in c_exp_hdr_only_category LOOP
5240
5241
5242 BEGIN
5243 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to expired' || C);
5244 if (C >= T) then
5245 commit;
5246 c := 0;
5247 end if;
5248
5249 savepoint H_STATUS;
5250 p_hdr_count:= p_hdr_count + 1;
5251 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5252 L_K_N_W_M := r_expired.K_N_W_M;
5253 l_new_status := null;
5254 l_new_status_m := null;
5255 h_status := null;
5256 h_new_status := v_expired;
5257 h_new_status_m := v_expired_m;
5258 h_status_type := 'EXPIRED';
5259
5260 l_chr_rec.id := r_expired.id ;
5261 l_chr_rec.object_version_number := r_expired.object_version_number ;
5262 l_chr_rec.sts_code := h_new_status ;
5263 l_chr_rec.old_sts_code := r_expired.sts_code;
5264 l_chr_rec.old_ste_code := r_expired.ste_code;
5265 l_chr_rec.new_sts_code := h_new_status;
5266 l_chr_rec.new_ste_code := h_status_type;
5267 --
5268 -- lock added not to depend on update implementation
5269 --
5270 okc_contract_pub.lock_contract_header(
5271 p_api_version => 1.0,
5272 p_init_msg_list => p_init_msg_list,
5273 x_return_status => l_return_status,
5274 x_msg_count => x_msg_count,
5275 x_msg_data => x_msg_data,
5276 p_chrv_rec => l_chr_rec);
5277 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5278 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5279 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5280 raise OKC_API.G_EXCEPTION_ERROR;
5281 END IF;
5282 --
5283 update_contract_header (
5284 p_api_version => 1.0,
5285 p_init_msg_list => p_init_msg_list,
5286 x_return_status => l_return_status,
5287 x_msg_count => x_msg_count,
5288 x_msg_data => x_msg_data,
5289 p_chrv_rec => l_chr_rec,
5290 x_chrv_rec => i_chr_rec);
5291 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5292 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5293 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5294 raise OKC_API.G_EXCEPTION_ERROR;
5295 END IF;
5296 --+
5297 --+ remember header status for lines to follow it, not default
5298 --+
5299 h_status := h_new_status;
5300 h_status_m := h_new_status_m ;
5301 line_status_change (p_kid => r_expired.id,
5302 p_cls_code => r_expired.cls_code,
5303 p_scs_code => r_expired.scs_code,
5304 p_k_num => r_expired.contract_number,
5305 p_k_num_mod => r_expired.contract_number_modifier,
5306 p_update_minor_version =>'N',
5307 x_return_status => l_return_status);
5308 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5309 p_error_from_line := 'Y';
5310 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5311 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5312 p_error_from_line := 'Y';
5313 raise OKC_API.G_EXCEPTION_ERROR;
5314 END IF;
5315 header_message(p_knum_and_mod =>L_K_N_W_M,
5316 p_status =>h_new_status_m,
5317 p_old_status =>r_expired.meaning,
5318 p_type =>'S');
5319
5320 c := c + 1;
5321 EXCEPTION
5322 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5323 header_message(p_knum_and_mod =>L_K_N_W_M,
5324 p_status =>h_new_status_m,
5325 p_old_status =>r_expired.meaning,
5326 p_msg_data => x_msg_data,
5327 p_type =>'U');
5328 if p_error_from_line <> 'Y' then
5329 p_hdr_errors := p_hdr_errors +1 ;
5330 else
5331 p_error_from_line := 'N';
5332 end if;
5333 rollback to H_STATUS;
5334 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5335 header_message(p_knum_and_mod =>L_K_N_W_M,
5336 p_status =>h_new_status_m,
5337 p_old_status =>r_expired.meaning,
5338 p_msg_data => x_msg_data,
5339 p_type =>'E');
5340 if p_error_from_line <> 'Y' then
5341 p_hdr_errors := p_hdr_errors +1 ;
5342 else
5343 p_error_from_line := 'N';
5344 end if;
5345 rollback to H_STATUS;
5346 WHEN OTHERS then
5347 header_message(p_knum_and_mod =>L_K_N_W_M,
5348 p_status =>h_new_status_m,
5349 p_old_status =>r_expired.meaning,
5350 p_msg_data => x_msg_data,
5351 p_type =>'U');
5352 p_hdr_errors := p_hdr_errors +1 ;
5353 rollback to H_STATUS;
5354 END;
5355 END LOOP; -- From Active, signed to EXPIRED
5356
5357 commit;
5358 c:= 0;
5359 savepoint H_STATUS;
5360 l_new_status := null;
5361 l_new_status_m := null;
5362 ----------------------------------------------------------------------------------------
5363 -- From Signed to Active
5364 FOR r_active in c_actv_hdr_only_category LOOP
5365
5366 BEGIN
5367 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from signed to active ' || C);
5368 if (C >= T) then
5369 commit;
5370 c := 0;
5371 end if;
5372 savepoint H_STATUS;
5373 p_hdr_count:= p_hdr_count + 1;
5374 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5375 L_K_N_W_M := r_active.K_N_W_M;
5376
5377 l_new_status := null;
5378 l_new_status_m := null;
5379 h_status := null;
5380 h_new_status := v_active ;
5381 h_new_status_m := v_active_m ;
5382 h_status_type := 'ACTIVE';
5383
5384 l_chr_rec.id := r_active.id ;
5385 l_chr_rec.object_version_number := r_active.object_version_number ;
5386 l_chr_rec.sts_code := h_new_status ;
5387 l_chr_rec.old_sts_code := r_active.sts_code;
5388 l_chr_rec.old_ste_code := r_active.ste_code;
5389 l_chr_rec.new_sts_code := h_new_status;
5390 l_chr_rec.new_ste_code := h_status_type;
5391 --
5392 okc_contract_pub.lock_contract_header(
5393 p_api_version => 1.0,
5394 p_init_msg_list => p_init_msg_list,
5395 x_return_status => l_return_status,
5396 x_msg_count => x_msg_count,
5397 x_msg_data => x_msg_data,
5398 p_chrv_rec => l_chr_rec);
5399
5400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5401 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5403 raise OKC_API.G_EXCEPTION_ERROR;
5404 END IF;
5405 --
5406 update_contract_header (
5407 p_api_version => 1.0,
5408 p_init_msg_list => p_init_msg_list,
5409 x_return_status => l_return_status,
5410 x_msg_count => x_msg_count,
5411 x_msg_data => x_msg_data,
5412 p_chrv_rec => l_chr_rec,
5413 x_chrv_rec => i_chr_rec);
5414
5415 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5416 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5417 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5418 raise OKC_API.G_EXCEPTION_ERROR;
5419 END IF;
5420 --+
5421 --+ remember header status for lines to follow it, not default
5422 --+
5423 h_status := h_new_status;
5424 h_status_m := h_new_status_m ;
5425
5426 line_status_change (p_kid => r_active.id,
5427 p_cls_code => r_active.cls_code,
5428 p_scs_code => r_active.scs_code,
5429 p_k_num => r_active.contract_number,
5430 p_k_num_mod => r_active.contract_number_modifier,
5431 p_update_minor_version =>'N',
5432 x_return_status => l_return_status);
5433
5434 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5435 p_error_from_line := 'Y';
5436 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5437 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5438 p_error_from_line := 'Y';
5439 raise OKC_API.G_EXCEPTION_ERROR;
5440 END IF;
5441 header_message(p_knum_and_mod =>L_K_N_W_M,
5442 p_status =>h_new_status_m,
5443 p_old_status =>r_active.meaning,
5444 p_type =>'S');
5445 c := c+1;
5446 EXCEPTION
5447 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5448 header_message(p_knum_and_mod =>L_K_N_W_M,
5449 p_status =>h_new_status_m,
5450 p_old_status =>r_active.meaning,
5451 p_msg_data => x_msg_data,
5452 p_type =>'U');
5453 if p_error_from_line <> 'Y' then
5454 p_hdr_errors := p_hdr_errors +1 ;
5455 else
5456 p_error_from_line := 'N';
5457 end if;
5458 rollback to H_STATUS;
5459 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5460 header_message(p_knum_and_mod =>L_K_N_W_M,
5461 p_status =>h_new_status_m,
5462 p_old_status =>r_active.meaning,
5463 p_msg_data => x_msg_data,
5464 p_type =>'E');
5465 if p_error_from_line <> 'Y' then
5466 p_hdr_errors := p_hdr_errors +1 ;
5467 else
5468 p_error_from_line := 'N';
5469 end if;
5470 rollback to H_STATUS;
5471 WHEN OTHERS then
5472 header_message(p_knum_and_mod =>L_K_N_W_M,
5473 p_status =>h_new_status_m,
5474 p_old_status =>r_active.meaning,
5475 p_msg_data => x_msg_data,
5476 p_type =>'U');
5477 p_hdr_errors := p_hdr_errors +1 ;
5478 rollback to H_STATUS;
5479 END;
5480 END LOOP; -- From Signed to Active
5481
5482 commit;
5483 c := 0;
5484 savepoint H_STATUS;
5485 l_new_status := null;
5486 l_new_status_m := null;
5487 ----------------------------------------------------------------------------------------
5488 --bug 5866484
5489 -- From Active to Signed
5490
5491 FOR r_signed in c_sig_hdr_only_category LOOP
5492
5493 BEGIN
5494 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active to signed ' || C);
5495 if (C >= T) then
5496 commit;
5497 c := 0;
5498 end if;
5499 savepoint H_STATUS;
5500 p_hdr_count:= p_hdr_count + 1;
5501 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5502 L_K_N_W_M := r_signed.K_N_W_M;
5503
5504 l_new_status := null;
5505 l_new_status_m := null;
5506 h_status := null;
5507 h_new_status := v_signed ;
5508 h_new_status_m := v_signed_m ;
5509 h_status_type := 'SIGNED';
5510
5511 l_chr_rec.id := r_signed.id ;
5512 l_chr_rec.object_version_number := r_signed.object_version_number ;
5513 l_chr_rec.sts_code := h_new_status ;
5514 l_chr_rec.old_sts_code := r_signed.sts_code;
5515 l_chr_rec.old_ste_code := r_signed.ste_code;
5516 l_chr_rec.new_sts_code := h_new_status;
5517 l_chr_rec.new_ste_code := h_status_type;
5518 --
5519 okc_contract_pub.lock_contract_header(
5520 p_api_version => 1.0,
5521 p_init_msg_list => p_init_msg_list,
5522 x_return_status => l_return_status,
5523 x_msg_count => x_msg_count,
5524 x_msg_data => x_msg_data,
5525 p_chrv_rec => l_chr_rec);
5526
5527 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5528 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5529 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5530 raise OKC_API.G_EXCEPTION_ERROR;
5531 END IF;
5532 --
5533 update_contract_header (
5534 p_api_version => 1.0,
5535 p_init_msg_list => p_init_msg_list,
5536 x_return_status => l_return_status,
5537 x_msg_count => x_msg_count,
5538 x_msg_data => x_msg_data,
5539 p_chrv_rec => l_chr_rec,
5540 x_chrv_rec => i_chr_rec);
5541
5542 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5543 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5544 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5545 raise OKC_API.G_EXCEPTION_ERROR;
5546 END IF;
5547 --+
5548 --+ remember header status for lines to follow it, not default
5549 --+
5550 h_status := h_new_status;
5551 h_status_m := h_new_status_m ;
5552
5553 line_status_change (p_kid => r_signed.id,
5554 p_cls_code => r_signed.cls_code,
5555 p_scs_code => r_signed.scs_code,
5556 p_k_num => r_signed.contract_number,
5557 p_k_num_mod => r_signed.contract_number_modifier,
5558 p_update_minor_version =>'N',
5559 x_return_status => l_return_status);
5560
5561 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5562 p_error_from_line := 'Y';
5563 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5564 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5565 p_error_from_line := 'Y';
5566 raise OKC_API.G_EXCEPTION_ERROR;
5567 END IF;
5568 header_message(p_knum_and_mod =>L_K_N_W_M,
5569 p_status =>h_new_status_m,
5570 p_old_status =>r_signed.meaning,
5571 p_type =>'S');
5572 c := c+1;
5573 EXCEPTION
5574 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5575 header_message(p_knum_and_mod =>L_K_N_W_M,
5576 p_status =>h_new_status_m,
5577 p_old_status =>r_signed.meaning,
5578 p_msg_data => x_msg_data,
5579 p_type =>'U');
5580 if p_error_from_line <> 'Y' then
5581 p_hdr_errors := p_hdr_errors +1 ;
5582 else
5583 p_error_from_line := 'N';
5584 end if;
5585 rollback to H_STATUS;
5586 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5587 header_message(p_knum_and_mod =>L_K_N_W_M,
5588 p_status =>h_new_status_m,
5589 p_old_status =>r_signed.meaning,
5590 p_msg_data => x_msg_data,
5591 p_type =>'E');
5592 if p_error_from_line <> 'Y' then
5593 p_hdr_errors := p_hdr_errors +1 ;
5594 else
5595 p_error_from_line := 'N';
5596 end if;
5597 rollback to H_STATUS;
5598 WHEN OTHERS then
5599 header_message(p_knum_and_mod =>L_K_N_W_M,
5600 p_status =>h_new_status_m,
5601 p_old_status =>r_signed.meaning,
5602 p_msg_data => x_msg_data,
5603 p_type =>'U');
5604 p_hdr_errors := p_hdr_errors +1 ;
5605 rollback to H_STATUS;
5606 END;
5607 END LOOP; -- From Active to Signed
5608
5609 commit;
5610 c := 0;
5611
5612 ELSIF (p_from_k is null) AND (p_category is null) THEN
5613 /*WHEN NO PARAMETER IS PROVIDED*/
5614 /*changes made for bug6475371*/
5615
5616 -- from active signed to terminated
5617 c := 0;
5618 l_new_status := null;
5619 l_new_status_m := null;
5620
5621 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent request submitted with last_run_date as '||p_last_rundate);
5622
5623 FOR r_terminated in c_termn_hdr_all LOOP
5624
5625
5626 BEGIN
5627 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to terminated' || C);
5628 if (C >= T) then
5629 commit;
5630 c := 0;
5631 end if;
5632 savepoint H_STATUS;
5633 p_hdr_count:= p_hdr_count + 1;
5634 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5635 L_K_N_W_M := r_terminated.K_N_W_M;
5636
5637 l_new_status := null;
5638 l_new_status_m := null;
5639 h_status := null;
5640 h_new_status := v_terminated ;
5641 h_new_status_m := v_terminated_m ;
5642 h_status_type := 'TERMINATED';
5643
5644 l_chr_rec.id := r_terminated.id ;
5645 l_chr_rec.object_version_number := r_terminated.object_version_number ;
5646 l_chr_rec.sts_code := h_new_status ;
5647 l_chr_rec.old_sts_code := r_terminated.sts_code;
5648 l_chr_rec.old_ste_code := r_terminated.ste_code;
5649 l_chr_rec.new_sts_code := h_new_status;
5650 l_chr_rec.new_ste_code := h_status_type;
5651 --
5652 -- End: Added for Status Change Action Assembler Changes 10/19/2000
5653 --
5654 -- lock added not to depend on update implementation
5655 --
5656 okc_contract_pub.lock_contract_header(
5657 p_api_version => 1.0,
5658 p_init_msg_list => p_init_msg_list,
5659 x_return_status => l_return_status,
5660 x_msg_count => x_msg_count,
5661 x_msg_data => x_msg_data,
5662 p_chrv_rec => l_chr_rec);
5663 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5664 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5665 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5666 raise OKC_API.G_EXCEPTION_ERROR;
5667 END IF;
5668 --
5669 --
5670 update_contract_header (
5671 p_api_version => 1.0,
5672 p_init_msg_list => p_init_msg_list,
5673 x_return_status => l_return_status,
5674 x_msg_count => x_msg_count,
5675 x_msg_data => x_msg_data,
5676 p_chrv_rec => l_chr_rec,
5677 x_chrv_rec => i_chr_rec);
5678 FND_FILE.PUT_LINE(FND_FILE.LOG,'update_contract_header ' || l_return_status);
5679
5680 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5681 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5682 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5683 raise OKC_API.G_EXCEPTION_ERROR;
5684 END IF;
5685
5686 OKC_K_TERM_ASMBLR_PVT.acn_assemble(
5687 p_api_version => 1.0 ,
5688 p_init_msg_list => p_init_msg_list,
5689 x_return_status => l_return_status,
5690 x_msg_count => x_msg_count,
5691 x_msg_data => x_msg_data,
5692 p_k_class => r_terminated.cls_code,
5693 p_k_id => r_terminated.id,
5694 p_k_number => r_terminated.contract_number,
5695 p_k_nbr_mod => r_terminated.contract_number_modifier,
5696 p_k_subclass => r_terminated.scs_code,
5697 p_estimated_amount => r_terminated.estimated_amount,
5698 P_K_STATUS_CODE => r_terminated.STS_CODE,
5699 p_term_date => r_terminated.date_terminated,
5700 p_term_reason => r_terminated.termination_reason);
5701 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKC_K_TERM_ASMBLR_PVT ' || l_return_status);
5702
5703 --
5704 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5705 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5706 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5707 raise OKC_API.G_EXCEPTION_ERROR;
5708 END IF;
5709 OKC_TIME_RES_PUB.res_time_termnt_k(
5710 P_CHR_ID => r_terminated.id,
5711 P_CLE_ID => NULL,
5712 P_END_DATE => r_terminated.DATE_TERMINATED,
5713 P_API_VERSION => 1.0 ,
5714 p_init_msg_list => p_init_msg_list,
5715 x_return_status => l_return_status
5716 );
5717
5718 FND_FILE.PUT_LINE(FND_FILE.LOG,'res_time_termnt_k ' || l_return_status);
5719
5720 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5721 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5722 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5723 raise OKC_API.G_EXCEPTION_ERROR;
5724 END IF;
5725 --
5726 --+
5727 --+ remember header status for lines to follow it, not default
5728 --+
5729
5730 h_status := h_new_status;
5731 h_status_m := h_new_status_m ;
5732
5733 line_status_change (p_kid => r_terminated.id,
5734 p_cls_code => r_terminated.cls_code,
5735 p_scs_code => r_terminated.scs_code,
5736 p_k_num => r_terminated.contract_number,
5737 p_k_num_mod => r_terminated.contract_number_modifier,
5738 p_update_minor_version =>'N',
5739 x_return_status => l_return_status);
5740 FND_FILE.PUT_LINE(FND_FILE.LOG,'line_status_change ' || l_return_status);
5741
5742
5743 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5744 p_error_from_line := 'Y';
5745 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5746 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5747 p_error_from_line := 'Y';
5748 raise OKC_API.G_EXCEPTION_ERROR;
5749 END IF;
5750 header_message(p_knum_and_mod =>L_K_N_W_M,
5751 p_old_status =>r_terminated.meaning,
5752 p_status =>h_new_status_m,
5753 p_type =>'S');
5754 c:= c+1;
5755 EXCEPTION
5756 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5757 header_message(p_knum_and_mod =>L_K_N_W_M,
5758 p_status =>h_new_status_m,
5759 p_old_status =>r_terminated.meaning,
5760 p_msg_data => x_msg_data,
5761 p_type =>'U');
5762 if p_error_from_line <> 'Y' then
5763 p_hdr_errors := p_hdr_errors +1 ;
5764 else
5765 p_error_from_line := 'N';
5766 end if;
5767 rollback to H_STATUS;
5768 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5769 header_message(p_knum_and_mod =>L_K_N_W_M,
5770 p_status =>h_new_status_m,
5771 p_old_status =>r_terminated.meaning,
5772 p_msg_data => x_msg_data,
5773 p_type =>'E');
5774 if p_error_from_line <> 'Y' then
5775 p_hdr_errors := p_hdr_errors +1 ;
5776 else
5777 p_error_from_line := 'N';
5778 end if;
5779 rollback to H_STATUS;
5780 WHEN OTHERS then
5781 header_message(p_knum_and_mod =>L_K_N_W_M,
5782 p_status =>h_new_status_m,
5783 p_old_status =>r_terminated.meaning,
5784 p_msg_data => x_msg_data,
5785 p_type =>'U');
5786 p_hdr_errors := p_hdr_errors +1 ;
5787 rollback to H_STATUS;
5788 END;
5789
5790 END LOOP; -- from active signed to terminated
5791 commit;
5792 c:= 0;
5793 savepoint H_STATUS;
5794 ----------------------------------------------------------------------------------------
5795 l_new_status := null;
5796 l_new_status_m := null;
5797
5798 -- From Active, signed to EXPIRED
5799 FOR r_expired in c_expired_hdr_all LOOP
5800
5801
5802 BEGIN
5803 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active, signed to expired' || C);
5804 if (C >= T) then
5805 commit;
5806 c := 0;
5807 end if;
5808
5809 savepoint H_STATUS;
5810 p_hdr_count:= p_hdr_count + 1;
5811 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5812 L_K_N_W_M := r_expired.K_N_W_M;
5813 l_new_status := null;
5814 l_new_status_m := null;
5815 h_status := null;
5816 h_new_status := v_expired;
5817 h_new_status_m := v_expired_m;
5818 h_status_type := 'EXPIRED';
5819
5820 l_chr_rec.id := r_expired.id ;
5821 l_chr_rec.object_version_number := r_expired.object_version_number ;
5822 l_chr_rec.sts_code := h_new_status ;
5823 l_chr_rec.old_sts_code := r_expired.sts_code;
5824 l_chr_rec.old_ste_code := r_expired.ste_code;
5825 l_chr_rec.new_sts_code := h_new_status;
5826 l_chr_rec.new_ste_code := h_status_type;
5827 --
5828 -- lock added not to depend on update implementation
5829 --
5830 okc_contract_pub.lock_contract_header(
5831 p_api_version => 1.0,
5832 p_init_msg_list => p_init_msg_list,
5833 x_return_status => l_return_status,
5834 x_msg_count => x_msg_count,
5835 x_msg_data => x_msg_data,
5836 p_chrv_rec => l_chr_rec);
5837 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5838 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5839 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5840 raise OKC_API.G_EXCEPTION_ERROR;
5841 END IF;
5842 --
5843 update_contract_header (
5844 p_api_version => 1.0,
5845 p_init_msg_list => p_init_msg_list,
5846 x_return_status => l_return_status,
5847 x_msg_count => x_msg_count,
5848 x_msg_data => x_msg_data,
5849 p_chrv_rec => l_chr_rec,
5850 x_chrv_rec => i_chr_rec);
5851 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5852 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5853 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5854 raise OKC_API.G_EXCEPTION_ERROR;
5855 END IF;
5856 --+
5857 --+ remember header status for lines to follow it, not default
5858 --+
5859 h_status := h_new_status;
5860 h_status_m := h_new_status_m ;
5861 line_status_change (p_kid => r_expired.id,
5862 p_cls_code => r_expired.cls_code,
5863 p_scs_code => r_expired.scs_code,
5864 p_k_num => r_expired.contract_number,
5865 p_k_num_mod => r_expired.contract_number_modifier,
5866 p_update_minor_version =>'N',
5867 x_return_status => l_return_status);
5868 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5869 p_error_from_line := 'Y';
5870 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5871 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5872 p_error_from_line := 'Y';
5873 raise OKC_API.G_EXCEPTION_ERROR;
5874 END IF;
5875 header_message(p_knum_and_mod =>L_K_N_W_M,
5876 p_status =>h_new_status_m,
5877 p_old_status =>r_expired.meaning,
5878 p_type =>'S');
5879
5880 c := c + 1;
5881 EXCEPTION
5882 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5883 header_message(p_knum_and_mod =>L_K_N_W_M,
5884 p_status =>h_new_status_m,
5885 p_old_status =>r_expired.meaning,
5886 p_msg_data => x_msg_data,
5887 p_type =>'U');
5888 if p_error_from_line <> 'Y' then
5889 p_hdr_errors := p_hdr_errors +1 ;
5890 else
5891 p_error_from_line := 'N';
5892 end if;
5893 rollback to H_STATUS;
5894 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5895 header_message(p_knum_and_mod =>L_K_N_W_M,
5896 p_status =>h_new_status_m,
5897 p_old_status =>r_expired.meaning,
5898 p_msg_data => x_msg_data,
5899 p_type =>'E');
5900 if p_error_from_line <> 'Y' then
5901 p_hdr_errors := p_hdr_errors +1 ;
5902 else
5903 p_error_from_line := 'N';
5904 end if;
5905 rollback to H_STATUS;
5906 WHEN OTHERS then
5907 header_message(p_knum_and_mod =>L_K_N_W_M,
5908 p_status =>h_new_status_m,
5909 p_old_status =>r_expired.meaning,
5910 p_msg_data => x_msg_data,
5911 p_type =>'U');
5912 p_hdr_errors := p_hdr_errors +1 ;
5913 rollback to H_STATUS;
5914 END;
5915 END LOOP; -- From Active, hold, signed to EXPIRED
5916
5917 commit;
5918 c:= 0;
5919 savepoint H_STATUS;
5920 l_new_status := null;
5921 l_new_status_m := null;
5922 ----------------------------------------------------------------------------------------
5923 -- From Signed to Active
5924 FOR r_active in c_active_hdr_all LOOP
5925
5926 BEGIN
5927 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from signed to active ' || C);
5928 if (C >= T) then
5929 commit;
5930 c := 0;
5931 end if;
5932 savepoint H_STATUS;
5933 p_hdr_count:= p_hdr_count + 1;
5934 l_return_status := OKC_API.G_RET_STS_SUCCESS;
5935 L_K_N_W_M := r_active.K_N_W_M;
5936
5937 l_new_status := null;
5938 l_new_status_m := null;
5939 h_status := null;
5940 h_new_status := v_active ;
5941 h_new_status_m := v_active_m ;
5942 h_status_type := 'ACTIVE';
5943
5944 l_chr_rec.id := r_active.id ;
5945 l_chr_rec.object_version_number := r_active.object_version_number ;
5946 l_chr_rec.sts_code := h_new_status ;
5947 l_chr_rec.old_sts_code := r_active.sts_code;
5948 l_chr_rec.old_ste_code := r_active.ste_code;
5949 l_chr_rec.new_sts_code := h_new_status;
5950 l_chr_rec.new_ste_code := h_status_type;
5951 --
5952 okc_contract_pub.lock_contract_header(
5953 p_api_version => 1.0,
5954 p_init_msg_list => p_init_msg_list,
5955 x_return_status => l_return_status,
5956 x_msg_count => x_msg_count,
5957 x_msg_data => x_msg_data,
5958 p_chrv_rec => l_chr_rec);
5959
5960 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5961 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5962 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5963 raise OKC_API.G_EXCEPTION_ERROR;
5964 END IF;
5965 --
5966 update_contract_header (
5967 p_api_version => 1.0,
5968 p_init_msg_list => p_init_msg_list,
5969 x_return_status => l_return_status,
5970 x_msg_count => x_msg_count,
5971 x_msg_data => x_msg_data,
5972 p_chrv_rec => l_chr_rec,
5973 x_chrv_rec => i_chr_rec);
5974
5975 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5976 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5977 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5978 raise OKC_API.G_EXCEPTION_ERROR;
5979 END IF;
5980 --+
5981 --+ remember header status for lines to follow it, not default
5982 --+
5983 h_status := h_new_status;
5984 h_status_m := h_new_status_m ;
5985
5986 line_status_change (p_kid => r_active.id,
5987 p_cls_code => r_active.cls_code,
5988 p_scs_code => r_active.scs_code,
5989 p_k_num => r_active.contract_number,
5990 p_k_num_mod => r_active.contract_number_modifier,
5991 p_update_minor_version =>'N',
5992 x_return_status => l_return_status);
5993
5994 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5995 p_error_from_line := 'Y';
5996 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5997 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5998 p_error_from_line := 'Y';
5999 raise OKC_API.G_EXCEPTION_ERROR;
6000 END IF;
6001 header_message(p_knum_and_mod =>L_K_N_W_M,
6002 p_status =>h_new_status_m,
6003 p_old_status =>r_active.meaning,
6004 p_type =>'S');
6005 c := c+1;
6006 EXCEPTION
6007 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6008 header_message(p_knum_and_mod =>L_K_N_W_M,
6009 p_status =>h_new_status_m,
6010 p_old_status =>r_active.meaning,
6011 p_msg_data => x_msg_data,
6012 p_type =>'U');
6013 if p_error_from_line <> 'Y' then
6014 p_hdr_errors := p_hdr_errors +1 ;
6015 else
6016 p_error_from_line := 'N';
6017 end if;
6018 rollback to H_STATUS;
6019 WHEN OKC_API.G_EXCEPTION_ERROR THEN
6020 header_message(p_knum_and_mod =>L_K_N_W_M,
6021 p_status =>h_new_status_m,
6022 p_old_status =>r_active.meaning,
6023 p_msg_data => x_msg_data,
6024 p_type =>'E');
6025 if p_error_from_line <> 'Y' then
6026 p_hdr_errors := p_hdr_errors +1 ;
6027 else
6028 p_error_from_line := 'N';
6029 end if;
6030 rollback to H_STATUS;
6031 WHEN OTHERS then
6032 header_message(p_knum_and_mod =>L_K_N_W_M,
6033 p_status =>h_new_status_m,
6034 p_old_status =>r_active.meaning,
6035 p_msg_data => x_msg_data,
6036 p_type =>'U');
6037 p_hdr_errors := p_hdr_errors +1 ;
6038 rollback to H_STATUS;
6039 END;
6040 END LOOP; -- From Signed to Active
6041
6042 commit;
6043 c := 0;
6044 savepoint H_STATUS;
6045 l_new_status := null;
6046 l_new_status_m := null;
6047 ----------------------------------------------------------------------------------------
6048 --bug 5866484
6049 -- From Active to Signed
6050
6051 FOR r_signed in c_signed_hdr_all LOOP
6052
6053 BEGIN
6054 FND_FILE.PUT_LINE(FND_FILE.LOG,'C from active to signed ' || C);
6055 if (C >= T) then
6056 commit;
6057 c := 0;
6058 end if;
6059 savepoint H_STATUS;
6060 p_hdr_count:= p_hdr_count + 1;
6061 l_return_status := OKC_API.G_RET_STS_SUCCESS;
6062 L_K_N_W_M := r_signed.K_N_W_M;
6063
6064 l_new_status := null;
6065 l_new_status_m := null;
6066 h_status := null;
6067 h_new_status := v_signed ;
6068 h_new_status_m := v_signed_m ;
6069 h_status_type := 'SIGNED';
6070
6071 l_chr_rec.id := r_signed.id ;
6072 l_chr_rec.object_version_number := r_signed.object_version_number ;
6073 l_chr_rec.sts_code := h_new_status ;
6074 l_chr_rec.old_sts_code := r_signed.sts_code;
6075 l_chr_rec.old_ste_code := r_signed.ste_code;
6076 l_chr_rec.new_sts_code := h_new_status;
6077 l_chr_rec.new_ste_code := h_status_type;
6078 --
6079 okc_contract_pub.lock_contract_header(
6080 p_api_version => 1.0,
6081 p_init_msg_list => p_init_msg_list,
6082 x_return_status => l_return_status,
6083 x_msg_count => x_msg_count,
6084 x_msg_data => x_msg_data,
6085 p_chrv_rec => l_chr_rec);
6086
6087 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
6088 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
6089 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
6090 raise OKC_API.G_EXCEPTION_ERROR;
6091 END IF;
6092 --
6093 update_contract_header (
6094 p_api_version => 1.0,
6095 p_init_msg_list => p_init_msg_list,
6096 x_return_status => l_return_status,
6097 x_msg_count => x_msg_count,
6098 x_msg_data => x_msg_data,
6099 p_chrv_rec => l_chr_rec,
6100 x_chrv_rec => i_chr_rec);
6101
6102 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
6103 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
6104 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
6105 raise OKC_API.G_EXCEPTION_ERROR;
6106 END IF;
6107 --+
6108 --+ remember header status for lines to follow it, not default
6109 --+
6110 h_status := h_new_status;
6111 h_status_m := h_new_status_m ;
6112
6113 line_status_change (p_kid => r_signed.id,
6114 p_cls_code => r_signed.cls_code,
6115 p_scs_code => r_signed.scs_code,
6116 p_k_num => r_signed.contract_number,
6117 p_k_num_mod => r_signed.contract_number_modifier,
6118 p_update_minor_version =>'N',
6119 x_return_status => l_return_status);
6120
6121 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
6122 p_error_from_line := 'Y';
6123 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
6124 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
6125 p_error_from_line := 'Y';
6126 raise OKC_API.G_EXCEPTION_ERROR;
6127 END IF;
6128 header_message(p_knum_and_mod =>L_K_N_W_M,
6129 p_status =>h_new_status_m,
6130 p_old_status =>r_signed.meaning,
6131 p_type =>'S');
6132 c := c+1;
6133 EXCEPTION
6134 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
6135 header_message(p_knum_and_mod =>L_K_N_W_M,
6136 p_status =>h_new_status_m,
6137 p_old_status =>r_signed.meaning,
6138 p_msg_data => x_msg_data,
6139 p_type =>'U');
6140 if p_error_from_line <> 'Y' then
6141 p_hdr_errors := p_hdr_errors +1 ;
6142 else
6143 p_error_from_line := 'N';
6144 end if;
6145 rollback to H_STATUS;
6146 WHEN OKC_API.G_EXCEPTION_ERROR THEN
6147 header_message(p_knum_and_mod =>L_K_N_W_M,
6148 p_status =>h_new_status_m,
6149 p_old_status =>r_signed.meaning,
6150 p_msg_data => x_msg_data,
6151 p_type =>'E');
6152 if p_error_from_line <> 'Y' then
6153 p_hdr_errors := p_hdr_errors +1 ;
6154 else
6155 p_error_from_line := 'N';
6156 end if;
6157 rollback to H_STATUS;
6158 WHEN OTHERS then
6159 header_message(p_knum_and_mod =>L_K_N_W_M,
6160 p_status =>h_new_status_m,
6161 p_old_status =>r_signed.meaning,
6162 p_msg_data => x_msg_data,
6163 p_type =>'U');
6164 p_hdr_errors := p_hdr_errors +1 ;
6165 rollback to H_STATUS;
6166 END;
6167 END LOOP; -- From Active to Signed
6168
6169 commit;
6170 c := 0;
6171 END IF;
6172
6173 ----------------------------------------------------------------------------------------
6174 --- END CHANGE STATUS AT HEADER LEVEL ---
6175 ----------------------------------------------------------------------------------------
6176 l_new_status := null;
6177 l_new_status_m := null;
6178
6179 -- Perform line status changes indepenant of Contract Header
6180 -- For Terminate, Expire, and Active
6181
6182 h_status := NULL;
6183 h_status_type := NULL;
6184 h_status_m := NULL;
6185
6186 h_new_status := NULL;
6187 h_new_status_m := NULL;
6188 -- line_status_change;
6189 line_status_change (p_kid => NULL
6190 ,p_cls_code => NULL
6191 ,p_scs_code => p_category
6192 ,p_from_k => p_from_k
6193 ,p_to_k => p_to_k
6194 ,p_from_m => p_from_m
6195 ,p_to_m => p_to_m
6196 ,p_k_num => NULL
6197 ,p_k_num_mod => NULL
6198 ,p_update_minor_version =>'Y'
6199 ,x_return_status => l_return_status );
6200 commit;
6201 wrapup;
6202 EXCEPTION
6203 WHEN OTHERS THEN
6204 header_message(p_knum_and_mod =>NULL,
6205 p_status =>NULL,
6206 p_old_status =>NULL,
6207 p_msg_data => x_msg_data,
6208 p_type =>'U');
6209 retcode := 1;
6210 wrapup;
6211 rollback to H_STATUS;
6212 END change_status; --1
6213
6214 END okc_status_change_pvt;