[Home] [Help]
PACKAGE BODY: APPS.OKL_ACTIVATE_IB_PVT
Source
1 package body OKL_ACTIVATE_IB_PVT AS
2 /* $Header: OKLRAIBB.pls 120.12 2008/02/08 19:27:14 rkuttiya noship $ */
3 --------------------------------------------------------------------------------
4 --GLOBAL VARIABLES
5 --------------------------------------------------------------------------------
6 G_TRX_LINE_TYPE_BOOK Varchar2(30) := 'CFA';
7 G_TRX_HDR_TYPE_BOOK Varchar2(30) := 'CFA';
8 G_TRX_LINE_TYPE_REBOOK Varchar2(30) := 'CRB';
9 G_TRX_HDR_TYPE_REBOOK Varchar2(30) := 'CRB';
10 G_TSU_CODE_ENTERED Varchar2(30) := 'ENTERED';
11 G_ITM_INST_PARTY Varchar2(30) := 'LESSEE';
12 G_CONTRACT_INTENT Varchar2(1) := 'S';
13 G_PARTY_SRC_TABLE Varchar2(30) := 'HZ_PARTIES';
14 G_PARTY_RELATIONSHIP Varchar2(30) := 'OWNER';
15 G_IB_LINE_LTY_CODE Varchar2(30) := 'INST_ITEM';
16 G_IB_LINE_LTY_ID NUMBER := 45;
17 G_MODEL_LINE_LTY_CODE Varchar2(30) := 'ITEM';
18 G_MODEL_LINE_LTY_ID NUMBER := 34;
19 G_APPROVED_STS_CODE VARCHAR2(100) := 'APPROVED';
20 G_LEASE_SCS_CODE VARCHAR2(30) := 'LEASE';
21 G_MFG_SERIAL_NUMBER_FLAG Varchar2(1) := 'N';
22 G_LOC_TYPE_CODE Varchar2(30) := 'HZ_LOCATIONS';
23 G_INSTALL_LOC_TYPE_CODE Varchar2(30) := 'HZ_PARTY_SITES';
24 G_UOM_CODE Varchar2(10) := 'Ea';
25 G_IB_BKNG_TXN_TYPE Varchar2(30) := 'OKL_BOOK';
26 G_CUST_ACCT_RULE Varchar2(30) := 'CAN';
27 G_CUST_ACCT_RULE_GROUP Varchar2(30) := 'LACAN';
28 G_IB_LINE_SRC_CODE Varchar2(30) := 'OKX_IB_ITEM';
29 G_TSU_CODE_PROCESSED Varchar2(30) := 'PROCESSED';
30 -- gboomina Bug 5362977 - Added - Start
31 G_INST_LINE_LTY_ID NUMBER := 43;
32 -- gboomina Bug 5362977 - End
33
34 --------------------------------------------------------------------------------
35 --GLOBAL MESSAGE CONSTANTS
36 --------------------------------------------------------------------------------
37 G_PARTY_NOT_FOUND Varchar2(200) := 'OKL_LLA_PARTY_NOT_FOUND';
38 G_ROLE_CODE_TOKEN Varchar2(30) := 'RLE_CODE';
39 G_IB_TXN_TYPE_NOT_FOUND Varchar2(200) := 'OKL_LLA_IB_TXN_TYPE_NOT_FOUND';
40 G_TXN_TYPE_TOKEN Varchar2(30) := 'TXN_TYPE';
41 G_CONTRACT_NOT_FOUND VARCHAR2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
42 G_CONTRACT_ID_TOKEN VARCHAR2(200) := 'CONTRACT_ID';
43 G_CONTRACT_NOT_APPROVED VARCHAR2(200) := 'OKL_LLA_CONTRACT_NOT_APPROVED';
44 G_CONTRACT_NOT_LEASE VARCHAR2(200) := 'OKL_LLA_CONTRACT_NOT_LEASE';
45 G_IB_TRX_REC_NOT_FOUND VARCHAR2(200) := 'OKL_LLA_IB_TRX_REC_NOT_FOUND';
46 G_IB_LINE_ID_TOKEN VARCHAR2(100) := 'IB_LINE_ID';
47 G_INV_MSTR_ORG_NOT_FOUND VARCHAR2(200) := 'OKL_LLA_INV_MSTR_ORG_NOT_FOUND';
48 G_CUST_ACCOUNT_FOUND VARCHAR2(200) := 'OKL_LLA_CUST_ACCT_NOT_FOUND';
49 G_STS_UPDATE_TRX_MISSING VARCHAR2(200) := 'OKL_LLA_STS_UPDATE_TRX_MISSING';
50 G_TAS_ID_TOKEN VARCHAR2(100) := 'TAS_ID';
51 G_TRX_ALREADY_PROCESSED VARCHAR2(200) := 'OKL_LLA_TRX_ALREADY_PROCESSED';
52 G_INSTALL_LOC_NOT_FOUND VARCHAR2(200) := 'OKL_LLA_INSTALL_LOC_NOT_FOUND';
53 G_INST_SITE_USE_TOKEN VARCHAR2(100) := 'SITE_USE_ID';
54 G_MODEL_LINE_ITEM_NOT_FOUND VARCHAR2(200) := 'OKL_LLA_MDL_LN_ITEM_NOT_FOUND';
55 G_MODEL_ITEM_NOT_TRACKABLE VARCHAR2(200) := 'OKL_LLA_ITEM_NOT_TRACKABLE';
56 G_ITEM_NAME_TOKEN VARCHAR2(200) := 'ITEM_NAME';
57 G_INV_ITEM_NOT_FOUND VARCHAR2(200) := 'OKL_LLA_INV_ITEM_NOT_FOUND';
58 G_INV_ITEM_ID_TOKEN VARCHAR2(200) := 'INV_ITEM_ID';
59 G_INV_ORG_ID_TOKEN VARCHAR2(200) := 'INV_ORG_ID';
60 G_BULK_BATCH_SIZE CONSTANT NUMBER := 10000;
61
62 PROCEDURE qc is
63 begin
64 null;
65 end qc;
66
67 PROCEDURE api_copy is
68 begin
69 null;
70 end api_copy;
71 ---------------------------------------------------------------------------
72 -- FUNCTION get_rec for: OKC_K_ITEMS_V
73 ---------------------------------------------------------------------------
74 FUNCTION get_cimv_rec (p_cle_id IN NUMBER,
75 x_no_data_found OUT NOCOPY BOOLEAN
76 ) RETURN cimv_rec_type IS
77 CURSOR okc_cimv_csr (p_cle_id IN NUMBER) IS
78 SELECT
79 cim.ID,
80 cim.OBJECT_VERSION_NUMBER,
81 cim.CLE_ID,
82 cim.CHR_ID,
83 cim.CLE_ID_FOR,
84 cim.DNZ_CHR_ID,
85 cim.OBJECT1_ID1,
86 cim.OBJECT1_ID2,
87 cim.JTOT_OBJECT1_CODE,
88 cim.UOM_CODE,
89 cim.EXCEPTION_YN,
90 cim.NUMBER_OF_ITEMS,
91 cim.UPG_ORIG_SYSTEM_REF,
92 cim.UPG_ORIG_SYSTEM_REF_ID,
93 cim.PRICED_ITEM_YN,
94 cim.CREATED_BY,
95 cim.CREATION_DATE,
96 cim.LAST_UPDATED_BY,
97 cim.LAST_UPDATE_DATE,
98 cim.LAST_UPDATE_LOGIN
99 FROM Okc_K_Items_V cim
100 where cle_id = p_cle_id;
101
102 l_cimv_rec cimv_rec_type;
103 BEGIN
104 x_no_data_found := TRUE;
105 -- Get current database values
106 OPEN okc_cimv_csr (p_cle_id);
107 FETCH okc_cimv_csr INTO
108 l_cimv_rec.ID,
109 l_cimv_rec.OBJECT_VERSION_NUMBER,
110 l_cimv_rec.CLE_ID,
111 l_cimv_rec.CHR_ID,
112 l_cimv_rec.CLE_ID_FOR,
113 l_cimv_rec.DNZ_CHR_ID,
114 l_cimv_rec.OBJECT1_ID1,
115 l_cimv_rec.OBJECT1_ID2,
116 l_cimv_rec.JTOT_OBJECT1_CODE,
117 l_cimv_rec.UOM_CODE,
118 l_cimv_rec.EXCEPTION_YN,
119 l_cimv_rec.NUMBER_OF_ITEMS,
120 l_cimv_rec.UPG_ORIG_SYSTEM_REF,
121 l_cimv_rec.UPG_ORIG_SYSTEM_REF_ID,
122 l_cimv_rec.PRICED_ITEM_YN,
123 l_cimv_rec.CREATED_BY,
124 l_cimv_rec.CREATION_DATE,
125 l_cimv_rec.LAST_UPDATED_BY,
126 l_cimv_rec.LAST_UPDATE_DATE,
127 l_cimv_rec.LAST_UPDATE_LOGIN;
128 x_no_data_found := okc_cimv_csr%NOTFOUND;
129 CLOSE okc_cimv_csr;
130 RETURN(l_cimv_rec);
131 END get_cimv_rec;
132 --------------------------------------------------------------------------------
133 --Function get_iipv_rec for getting the ib internal transaction record for the line
134 --------------------------------------------------------------------------------
135 FUNCTION get_iipv_rec (
136 p_kle_id IN NUMBER,
137 p_trx_type IN VARCHAR2,
138 x_no_data_found OUT NOCOPY BOOLEAN
139 ) RETURN iipv_rec_type IS
140 CURSOR okl_iipv_csr (p_kle_id IN NUMBER) IS
141 SELECT ID,
142 OBJECT_VERSION_NUMBER,
143 TAS_ID,
144 TAL_ID,
145 KLE_ID,
146 TAL_TYPE,
147 LINE_NUMBER,
148 INSTANCE_NUMBER_IB,
149 OBJECT_ID1_NEW,
150 OBJECT_ID2_NEW,
151 JTOT_OBJECT_CODE_NEW,
152 OBJECT_ID1_OLD,
153 OBJECT_ID2_OLD,
154 JTOT_OBJECT_CODE_OLD,
155 INVENTORY_ORG_ID,
156 SERIAL_NUMBER,
157 MFG_SERIAL_NUMBER_YN,
158 INVENTORY_ITEM_ID,
159 INV_MASTER_ORG_ID,
160 ATTRIBUTE_CATEGORY,
161 ATTRIBUTE1,
162 ATTRIBUTE2,
163 ATTRIBUTE3,
164 ATTRIBUTE4,
165 ATTRIBUTE5,
166 ATTRIBUTE6,
167 ATTRIBUTE7,
168 ATTRIBUTE8,
169 ATTRIBUTE9,
170 ATTRIBUTE10,
171 ATTRIBUTE11,
172 ATTRIBUTE12,
173 ATTRIBUTE13,
174 ATTRIBUTE14,
175 ATTRIBUTE15,
176 CREATED_BY,
177 CREATION_DATE,
178 LAST_UPDATED_BY,
179 LAST_UPDATE_DATE,
180 LAST_UPDATE_LOGIN
181 FROM OKL_TXL_ITM_INSTS iti
182 WHERE iti.kle_id = p_kle_id
183 and iti.tal_type = p_trx_type
184 and exists (select '1' from OKL_TRX_ASSETS
185 where OKL_TRX_ASSETS.TAS_TYPE = p_trx_type
186 and OKL_TRX_ASSETS.TSU_CODE = G_TSU_CODE_ENTERED
187 and OKL_TRX_ASSETS.ID = iti.tas_id);
188
189 l_iipv_rec iipv_rec_type;
190 BEGIN
191 x_no_data_found := TRUE;
192 -- Get current database values
193 OPEN okl_iipv_csr (p_kle_id);
194 FETCH okl_iipv_csr INTO
195 l_iipv_rec.ID,
196 l_iipv_rec.OBJECT_VERSION_NUMBER,
197 l_iipv_rec.TAS_ID,
198 l_iipv_rec.TAL_ID,
199 l_iipv_rec.KLE_ID,
200 l_iipv_rec.TAL_TYPE,
201 l_iipv_rec.LINE_NUMBER,
202 l_iipv_rec.INSTANCE_NUMBER_IB,
203 l_iipv_rec.OBJECT_ID1_NEW,
204 l_iipv_rec.OBJECT_ID2_NEW,
205 l_iipv_rec.JTOT_OBJECT_CODE_NEW,
206 l_iipv_rec.OBJECT_ID1_OLD,
207 l_iipv_rec.OBJECT_ID2_OLD,
208 l_iipv_rec.JTOT_OBJECT_CODE_OLD,
209 l_iipv_rec.INVENTORY_ORG_ID,
210 l_iipv_rec.SERIAL_NUMBER,
211 l_iipv_rec.MFG_SERIAL_NUMBER_YN,
212 l_iipv_rec.INVENTORY_ITEM_ID,
213 l_iipv_rec.INV_MASTER_ORG_ID,
214 l_iipv_rec.ATTRIBUTE_CATEGORY,
215 l_iipv_rec.ATTRIBUTE1,
216 l_iipv_rec.ATTRIBUTE2,
217 l_iipv_rec.ATTRIBUTE3,
218 l_iipv_rec.ATTRIBUTE4,
219 l_iipv_rec.ATTRIBUTE5,
220 l_iipv_rec.ATTRIBUTE6,
221 l_iipv_rec.ATTRIBUTE7,
222 l_iipv_rec.ATTRIBUTE8,
223 l_iipv_rec.ATTRIBUTE9,
224 l_iipv_rec.ATTRIBUTE10,
225 l_iipv_rec.ATTRIBUTE11,
226 l_iipv_rec.ATTRIBUTE12,
227 l_iipv_rec.ATTRIBUTE13,
228 l_iipv_rec.ATTRIBUTE14,
229 l_iipv_rec.ATTRIBUTE15,
230 l_iipv_rec.CREATED_BY,
231 l_iipv_rec.CREATION_DATE,
232 l_iipv_rec.LAST_UPDATED_BY,
233 l_iipv_rec.LAST_UPDATE_DATE,
234 l_iipv_rec.LAST_UPDATE_LOGIN;
235 x_no_data_found := okl_iipv_csr%NOTFOUND;
236 CLOSE okl_iipv_csr;
237 RETURN(l_iipv_rec);
238 END get_iipv_rec;
239 ------------------------------------------------------------------------------
240 --Start of comments
241 --
242 --Procedure Name : update_trx_status
243 --Purpose : Update transaction status - used internally
244 --Modification History :
245 --20-Feb-2001 avsingh Created
246 ------------------------------------------------------------------------------
247 PROCEDURE update_trx_status(p_api_version IN NUMBER,
248 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_tas_id IN NUMBER,
253 p_tsu_code IN VARCHAR2) IS
254 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
255 l_api_name CONSTANT VARCHAR2(30) := 'update_trx_status';
256 l_api_version CONSTANT NUMBER := 1.0;
257
258 l_thpv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
259 l_thpv_rec_out OKL_TRX_ASSETS_PUB.thpv_rec_type;
260 --cursor to check existing tsu code
261 CURSOR tsu_code_csr (p_tas_id IN NUMBER) is
262 SELECT tsu_code
263 FROM OKL_TRX_ASSETS
264 WHERE id = p_tas_id;
265
266 l_tsu_code OKL_TRX_ASSETS.TSU_CODE%TYPE;
267 BEGIN
268 --call start activity to set savepoint
269 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
270 p_init_msg_list,
271 '_PVT',
272 x_return_status);
273 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
275 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
276 RAISE OKL_API.G_EXCEPTION_ERROR;
277 END IF;
278 --check if tsu code has already been updated to processed
279 OPEN tsu_code_csr(p_tas_id => p_tas_id);
280 FETCH tsu_code_csr into l_tsu_code;
281 If tsu_code_csr%NOTFOUND Then
282 --internal error unable to find trransaction record while trying to update status
283 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
284 p_msg_name => G_STS_UPDATE_TRX_MISSING,
285 p_token1 => G_TAS_ID_TOKEN,
286 p_token1_value => p_tas_id
287 );
288 Raise OKL_API.G_EXCEPTION_ERROR;
289 Else
290 If l_tsu_code = p_tsu_code Then
291 --transaction already processed by another user
292 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
293 p_msg_name => G_TRX_ALREADY_PROCESSED
294 );
295 Raise OKL_API.G_EXCEPTION_ERROR;
296 Else
297 l_thpv_rec.id := p_tas_id;
298 l_thpv_rec.tsu_code := p_tsu_code;
299 OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
300 p_api_version => p_api_version,
301 p_init_msg_list => p_init_msg_list,
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data,
305 p_thpv_rec => l_thpv_rec,
306 x_thpv_rec => l_thpv_rec_out);
307 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
308 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
309 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
310 RAISE OKL_API.G_EXCEPTION_ERROR;
311 END IF;
312 End If;
313 End If;
314 CLOSE tsu_code_csr;
315 --Call end Activity
316 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
317 Exception
318 When OKL_API.G_EXCEPTION_ERROR Then
319 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
320 (
321 l_api_name,
322 G_PKG_NAME,
323 'OKL_API.G_RET_STS_ERROR',
324 x_msg_count,
325 x_msg_data,
326 '_PVT'
327 );
328 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
329 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
330 (
331 l_api_name,
332 G_PKG_NAME,
333 'OKL_API.G_RET_STS_UNEXP_ERROR',
334 x_msg_count,
335 x_msg_data,
336 '_PVT'
337 );
338 WHEN OTHERS THEN
339 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
340 (
341 l_api_name,
342 G_PKG_NAME,
343 'OTHERS',
344 x_msg_count,
345 x_msg_data,
346 '_PVT'
347 );
348 END update_trx_status;
349 ------------------------------------------------------------------------------
350 --Start of comments
351 --
352 --Procedure Name : get_party_rec
353 --Purpose : Gets Party records for IB interface
354 --Modification History :
355 --15-Jun-2001 avsingh Created
356 --Notes : Takes chr_id as input and tries to get the party role
357 -- for that contract for party role = 'LESSEE'
358 -- Assuming that LESSEE will be the owner of the IB instance
359 --End of Comments
360 ------------------------------------------------------------------------------
361 PROCEDURE get_party_rec
362 (p_api_version IN NUMBER,
363 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2,
367 p_chrv_id IN NUMBER,
368 x_party_tbl OUT NOCOPY party_tbl_type) is
369
370 l_party_tab OKL_JTOT_EXTRACT.party_tab_type;
371 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
372 l_api_name CONSTANT VARCHAR2(30) := 'GET_PARTY_REC';
373 l_api_version CONSTANT NUMBER := 1.0;
374
375 l_index number;
376 l_party_id number;
377
378 begin
379 --call start activity to set savepoint
380 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
381 p_init_msg_list,
382 '_PVT',
383 x_return_status);
384 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
386 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
387 RAISE OKL_API.G_EXCEPTION_ERROR;
388 END IF;
389 /*
390 --get Party
391 OKL_JTOT_EXTRACT.Get_Party(p_api_version => p_api_version,
392 p_init_msg_list => p_init_msg_list,
393 x_return_status => x_return_status,
394 x_msg_count => x_msg_count,
395 x_msg_data => x_msg_data,
396 p_chr_id => p_chrv_id,
397 p_cle_id => null,
398 p_role_code => G_ITM_INST_PARTY,
399 p_intent => G_CONTRACT_INTENT,
400 x_party_tab => l_party_tab);
401
402 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
403 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
404 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
405 RAISE OKL_API.G_EXCEPTION_ERROR;
406 END IF;
407
408 for l_index in 1..l_party_tab.LAST
409 Loop
410 x_party_tbl(l_index).party_id := l_party_tab(l_index).id1;
411 x_party_tbl(l_index).party_source_table := G_PARTY_SRC_TABLE;
412 x_party_tbl(l_index).relationship_type_code := G_PARTY_RELATIONSHIP;
413 x_party_tbl(l_index).contact_flag := 'N';
414 --dbms_output.put_line('party_id' || to_char(l_index)||'-'||to_char(x_party_tbl(l_index).party_id));
415 End Loop;
416
417 If (l_index = 0) Then
418 --no owner party record found
419 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
420 p_msg_name => G_PARTY_NOT_FOUND,
421 p_token1 => G_ROLE_CODE_TOKEN,
422 p_token1_value => G_ITM_INST_PARTY
423 );
424 Raise OKL_API.G_EXCEPTION_ERROR;
425 End If;
426 */
427 Begin
428 SELECT P.PARTY_ID
429 INTO l_party_id
430 FROM HZ_PARTIES P, OKC_K_PARTY_ROLES_B OKPRV
431 WHERE OKPRV.chr_id = p_chrv_id
432 AND OKPRV.rle_code = 'LESSEE'
433 AND OKPRV.jtot_object1_code = 'OKX_PARTY'
434 AND p.PARTY_ID = OKPRV.object1_id1
435 AND p.party_type in ('PERSON', 'ORGANIZATION');
436
437 Exception
438 When Others then
439 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
440 p_msg_name => G_PARTY_NOT_FOUND,
441 p_token1 => G_ROLE_CODE_TOKEN,
442 p_token1_value => G_ITM_INST_PARTY
443 );
444 Raise OKL_API.G_EXCEPTION_ERROR;
445 End;
446
447 x_party_tbl(1).party_id := l_party_id;
448 x_party_tbl(1).party_source_table := G_PARTY_SRC_TABLE;
449 x_party_tbl(1).relationship_type_code := G_PARTY_RELATIONSHIP;
450 x_party_tbl(1).contact_flag := 'N';
451
452 --Call end Activity
453 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
454 Exception
455 When OKL_API.G_EXCEPTION_ERROR Then
456 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
457 (
458 l_api_name,
459 G_PKG_NAME,
460 'OKL_API.G_RET_STS_ERROR',
461 x_msg_count,
462 x_msg_data,
463 '_PVT'
464 );
465 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
466 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
467 (
468 l_api_name,
469 G_PKG_NAME,
470 'OKL_API.G_RET_STS_UNEXP_ERROR',
471 x_msg_count,
472 x_msg_data,
473 '_PVT'
474 );
475 WHEN OTHERS THEN
476 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
477 (
478 l_api_name,
479 G_PKG_NAME,
480 'OTHERS',
481 x_msg_count,
482 x_msg_data,
483 '_PVT'
484 );
485 end get_party_rec;
486 ------------------------------------------------------------------------------
487 --Start of comments
488 --
489 --Procedure Name : get_trx_rec
490 --Purpose : Gets source transaction record for IB interface
491 --Modification History :
492 --15-Jun-2001 ashish.singh Created
493 --Notes : Assigns values to transaction_type_id and source_line_ref_id
494 --End of Comments
495 ------------------------------------------------------------------------------
496 PROCEDURE get_trx_rec
497 (p_api_version IN NUMBER,
498 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
499 x_return_status OUT NOCOPY VARCHAR2,
500 x_msg_count OUT NOCOPY NUMBER,
501 x_msg_data OUT NOCOPY VARCHAR2,
502 p_cle_id IN NUMBER,
503 p_transaction_type IN VARCHAR2,
504 x_trx_rec OUT NOCOPY trx_rec_type) is
505
506 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
507 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRX_REC';
508 l_api_version CONSTANT NUMBER := 1.0;
509
510 --Following cursor assumes that a transaction type called
511 --'OKL_BOOK' will be seeded in IB
512 Cursor okl_trx_type_csr(p_transaction_type IN VARCHAR2)is
513 select transaction_type_id
514 from CSI_TXN_TYPES
515 where source_transaction_type = p_transaction_type;
516 l_trx_type_id NUMBER;
517 Begin
518 x_return_status := OKL_API.G_RET_STS_SUCCESS;
519 open okl_trx_type_csr(p_transaction_type);
520 Fetch okl_trx_type_csr
521 into l_trx_type_id;
522 If okl_trx_type_csr%NotFound Then
523 --OKL LINE ACTIVATION not seeded as a source transaction in IB
524 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
525 p_msg_name => G_IB_TXN_TYPE_NOT_FOUND,
526 p_token1 => G_TXN_TYPE_TOKEN,
527 p_token1_value => p_transaction_type
528 );
529 Raise OKL_API.G_EXCEPTION_ERROR;
530 End If;
531 close okl_trx_type_csr;
532 --dbms_output.put_line('Found trx type id '||to_char(l_trx_type_id));
533 --Assign transaction Type id to seeded value in cs_lookups
534 x_trx_rec.transaction_type_id := l_trx_type_id;
535 --Assign Source Line Ref id to contract line id of IB instance line
536 x_trx_rec.source_line_ref_id := p_cle_id;
537 x_trx_rec.transaction_date := sysdate;
538 --confirm whether this has to be sysdate or creation date on line
539 x_trx_rec.source_transaction_date := sysdate;
540 Exception
541 When OKL_API.G_EXCEPTION_ERROR Then
542 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
543 (
544 l_api_name,
545 G_PKG_NAME,
546 'OKL_API.G_RET_STS_ERROR',
547 x_msg_count,
548 x_msg_data,
549 '_PVT'
550 );
551 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
552 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
553 (
554 l_api_name,
555 G_PKG_NAME,
556 'OKL_API.G_RET_STS_UNEXP_ERROR',
557 x_msg_count,
558 x_msg_data,
559 '_PVT'
560 );
561 WHEN OTHERS THEN
562 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
563 (
564 l_api_name,
565 G_PKG_NAME,
566 'OTHERS',
567 x_msg_count,
568 x_msg_data,
569 '_PVT'
570 );
571 END get_trx_rec;
572 --------------------------------------------------------------------------------
573 --Start of comments
574 --
575 --API Name : initialize_instance_rec
576 --Purpose : Private procedure to initialize instance_rec to default values
577 --End of comments
578 --------------------------------------------------------------------------------
579 PROCEDURE Initialize_instance_rec(x_instance_rec OUT NOCOPY inst_rec_type) IS
580 Begin
581 x_instance_rec.INSTANCE_ID := FND_API.G_MISS_NUM;
582 x_instance_rec.INSTANCE_NUMBER := FND_API.G_MISS_CHAR;
583 x_instance_rec.EXTERNAL_REFERENCE := FND_API.G_MISS_CHAR;
584 x_instance_rec.INVENTORY_ITEM_ID := FND_API.G_MISS_NUM;
585 x_instance_rec.VLD_ORGANIZATION_ID := FND_API.G_MISS_NUM;
586 x_instance_rec.INVENTORY_REVISION := FND_API.G_MISS_CHAR;
587 x_instance_rec.INV_MASTER_ORGANIZATION_ID := FND_API.G_MISS_NUM;
588 x_instance_rec.SERIAL_NUMBER := FND_API.G_MISS_CHAR;
589 x_instance_rec.MFG_SERIAL_NUMBER_FLAG := FND_API.G_MISS_CHAR;
590 x_instance_rec.LOT_NUMBER := FND_API.G_MISS_CHAR;
591 x_instance_rec.QUANTITY := FND_API.G_MISS_NUM;
592 x_instance_rec.UNIT_OF_MEASURE := FND_API.G_MISS_CHAR;
593 x_instance_rec.ACCOUNTING_CLASS_CODE := FND_API.G_MISS_CHAR;
594 x_instance_rec.INSTANCE_CONDITION_ID := FND_API.G_MISS_NUM;
595 x_instance_rec.INSTANCE_STATUS_ID := FND_API.G_MISS_NUM;
596 x_instance_rec.CUSTOMER_VIEW_FLAG := FND_API.G_MISS_CHAR;
597 x_instance_rec.MERCHANT_VIEW_FLAG := FND_API.G_MISS_CHAR;
598 x_instance_rec.SELLABLE_FLAG := FND_API.G_MISS_CHAR;
599 x_instance_rec.SYSTEM_ID := FND_API.G_MISS_NUM;
600 x_instance_rec.INSTANCE_TYPE_CODE := FND_API.G_MISS_CHAR;
601 x_instance_rec.ACTIVE_START_DATE := FND_API.G_MISS_DATE;
602 x_instance_rec.ACTIVE_END_DATE := FND_API.G_MISS_DATE;
603 x_instance_rec.LOCATION_TYPE_CODE := FND_API.G_MISS_CHAR;
604 x_instance_rec.LOCATION_ID := FND_API.G_MISS_NUM;
605 x_instance_rec.INV_ORGANIZATION_ID := FND_API.G_MISS_NUM;
606 x_instance_rec.INV_SUBINVENTORY_NAME := FND_API.G_MISS_CHAR;
607 x_instance_rec.INV_LOCATOR_ID := FND_API.G_MISS_NUM;
608 x_instance_rec.PA_PROJECT_ID := FND_API.G_MISS_NUM;
609 x_instance_rec.PA_PROJECT_TASK_ID := FND_API.G_MISS_NUM;
610 x_instance_rec.IN_TRANSIT_ORDER_LINE_ID := FND_API.G_MISS_NUM;
611 x_instance_rec.WIP_JOB_ID := FND_API.G_MISS_NUM;
612 x_instance_rec.PO_ORDER_LINE_ID := FND_API.G_MISS_NUM;
613 x_instance_rec.LAST_OE_ORDER_LINE_ID := FND_API.G_MISS_NUM;
614 x_instance_rec.LAST_OE_RMA_LINE_ID := FND_API.G_MISS_NUM;
615 x_instance_rec.LAST_PO_PO_LINE_ID := FND_API.G_MISS_NUM;
616 x_instance_rec.LAST_OE_PO_NUMBER := FND_API.G_MISS_CHAR;
617 x_instance_rec.LAST_WIP_JOB_ID := FND_API.G_MISS_NUM;
618 x_instance_rec.LAST_PA_PROJECT_ID := FND_API.G_MISS_NUM;
619 x_instance_rec.LAST_PA_TASK_ID := FND_API.G_MISS_NUM;
620 x_instance_rec.LAST_OE_AGREEMENT_ID := FND_API.G_MISS_NUM;
621 x_instance_rec.INSTALL_DATE := FND_API.G_MISS_DATE;
622 x_instance_rec.MANUALLY_CREATED_FLAG := FND_API.G_MISS_CHAR;
623 x_instance_rec.RETURN_BY_DATE := FND_API.G_MISS_DATE;
624 x_instance_rec.ACTUAL_RETURN_DATE := FND_API.G_MISS_DATE;
625 x_instance_rec.CREATION_COMPLETE_FLAG := FND_API.G_MISS_CHAR;
626 x_instance_rec.COMPLETENESS_FLAG := FND_API.G_MISS_CHAR;
627 x_instance_rec.VERSION_LABEL := FND_API.G_MISS_CHAR;
628 x_instance_rec.VERSION_LABEL_DESCRIPTION := FND_API.G_MISS_CHAR;
629 x_instance_rec.CONTEXT := FND_API.G_MISS_CHAR;
630 x_instance_rec.ATTRIBUTE1 := FND_API.G_MISS_CHAR;
631 x_instance_rec.ATTRIBUTE2 := FND_API.G_MISS_CHAR;
632 x_instance_rec.ATTRIBUTE3 := FND_API.G_MISS_CHAR;
633 x_instance_rec.ATTRIBUTE4 := FND_API.G_MISS_CHAR;
634 x_instance_rec.ATTRIBUTE5 := FND_API.G_MISS_CHAR;
635 x_instance_rec.ATTRIBUTE6 := FND_API.G_MISS_CHAR;
636 x_instance_rec.ATTRIBUTE7 := FND_API.G_MISS_CHAR;
637 x_instance_rec.ATTRIBUTE8 := FND_API.G_MISS_CHAR;
638 x_instance_rec.ATTRIBUTE9 := FND_API.G_MISS_CHAR;
639 x_instance_rec.ATTRIBUTE10 := FND_API.G_MISS_CHAR;
640 x_instance_rec.ATTRIBUTE11 := FND_API.G_MISS_CHAR;
641 x_instance_rec.ATTRIBUTE12 := FND_API.G_MISS_CHAR;
642 x_instance_rec.ATTRIBUTE13 := FND_API.G_MISS_CHAR;
643 x_instance_rec.ATTRIBUTE14 := FND_API.G_MISS_CHAR;
644 x_instance_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
645 x_instance_rec.OBJECT_VERSION_NUMBER := FND_API.G_MISS_NUM;
646 x_instance_rec.LAST_TXN_LINE_DETAIL_ID := FND_API.G_MISS_NUM;
647 x_instance_rec.INSTALL_LOCATION_TYPE_CODE := FND_API.G_MISS_CHAR;
648 x_instance_rec.INSTALL_LOCATION_ID := FND_API.G_MISS_NUM;
649 x_instance_rec.INSTANCE_USAGE_CODE := FND_API.G_MISS_CHAR;
650 x_instance_rec.CHECK_FOR_INSTANCE_EXPIRY := FND_API.G_TRUE;
651 End Initialize_instance_rec;
652 --------------------------------------------------------------------------------
653 --Start of comments
654 --
655 --API Name : initialize_txn_rec
656 --Purpose : Private procedure to initialize transaction_rec to default values
657 --End of comments
658 --------------------------------------------------------------------------------
659 PROCEDURE Initialize_txn_rec(x_txn_rec OUT NOCOPY trx_rec_type) IS
660 BEGIN
661 x_txn_rec.TRANSACTION_ID := FND_API.G_MISS_NUM;
662 x_txn_rec.TRANSACTION_DATE := FND_API.G_MISS_DATE;
663 x_txn_rec.SOURCE_TRANSACTION_DATE := FND_API.G_MISS_DATE;
664 x_txn_rec.TRANSACTION_TYPE_ID := FND_API.G_MISS_NUM;
665 x_txn_rec.TXN_SUB_TYPE_ID := FND_API.G_MISS_NUM;
666 x_txn_rec.SOURCE_GROUP_REF_ID := FND_API.G_MISS_NUM;
667 x_txn_rec.SOURCE_GROUP_REF := NULL;
668 x_txn_rec.SOURCE_HEADER_REF_ID := FND_API.G_MISS_NUM;
669 x_txn_rec.SOURCE_HEADER_REF := NULL;
670 x_txn_rec.SOURCE_LINE_REF_ID := FND_API.G_MISS_NUM;
671 x_txn_rec.SOURCE_LINE_REF := NULL;
672 x_txn_rec.SOURCE_DIST_REF_ID1 := FND_API.G_MISS_NUM;
673 x_txn_rec.SOURCE_DIST_REF_ID2 := FND_API.G_MISS_NUM;
674 x_txn_rec.INV_MATERIAL_TRANSACTION_ID := FND_API.G_MISS_NUM;
675 x_txn_rec.TRANSACTION_QUANTITY := FND_API.G_MISS_NUM;
676 x_txn_rec.TRANSACTION_UOM_CODE := FND_API.G_MISS_CHAR;
677 x_txn_rec.TRANSACTED_BY := FND_API.G_MISS_NUM;
678 x_txn_rec.TRANSACTION_STATUS_CODE := FND_API.G_MISS_CHAR;
679 x_txn_rec.TRANSACTION_ACTION_CODE := FND_API.G_MISS_CHAR;
680 x_txn_rec.MESSAGE_ID := FND_API.G_MISS_NUM;
681 x_txn_rec.CONTEXT := FND_API.G_MISS_CHAR;
682 x_txn_rec.ATTRIBUTE1 := FND_API.G_MISS_CHAR;
683 x_txn_rec.ATTRIBUTE2 := FND_API.G_MISS_CHAR;
684 x_txn_rec.ATTRIBUTE3 := FND_API.G_MISS_CHAR;
685 x_txn_rec.ATTRIBUTE4 := FND_API.G_MISS_CHAR;
686 x_txn_rec.ATTRIBUTE5 := FND_API.G_MISS_CHAR;
687 x_txn_rec.ATTRIBUTE6 := FND_API.G_MISS_CHAR;
688 x_txn_rec.ATTRIBUTE7 := FND_API.G_MISS_CHAR;
689 x_txn_rec.ATTRIBUTE8 := FND_API.G_MISS_CHAR;
690 x_txn_rec.ATTRIBUTE9 := FND_API.G_MISS_CHAR;
691 x_txn_rec.ATTRIBUTE10 := FND_API.G_MISS_CHAR;
692 x_txn_rec.ATTRIBUTE11 := FND_API.G_MISS_CHAR;
693 x_txn_rec.ATTRIBUTE12 := FND_API.G_MISS_CHAR;
694 x_txn_rec.ATTRIBUTE13 := FND_API.G_MISS_CHAR;
695 x_txn_rec.ATTRIBUTE14 := FND_API.G_MISS_CHAR;
696 x_txn_rec.ATTRIBUTE15 := FND_API.G_MISS_CHAR;
697 x_txn_rec.OBJECT_VERSION_NUMBER := FND_API.G_MISS_NUM;
698 x_txn_rec.SPLIT_REASON_CODE := FND_API.G_MISS_CHAR;
699 END Initialize_txn_rec;
700 --------------------------------------------------------------------------------
701 --Start of comments
702 --
703 --API Name : initialize_Account_tbl
704 --Purpose : Private procedure to initialize party_account table to defaultvalues
705 --End of comments
706 --------------------------------------------------------------------------------
707 PROCEDURE Initialize_Account_Tbl(x_account_tbl OUT NOCOPY party_account_tbl_type) IS
708 i Number;
709 BEGIN
710 If nvl(x_account_tbl.LAST,0) > 0 Then
711 FOR i in 1..x_account_tbl.LAST LOOP
712 x_account_tbl(i).ip_account_id := FND_API.G_MISS_NUM;
713 x_account_tbl(i).parent_tbl_index := FND_API.G_MISS_NUM;
714 x_account_tbl(i).instance_party_id := FND_API.G_MISS_NUM;
715 x_account_tbl(i).party_account_id := FND_API.G_MISS_NUM;
716 x_account_tbl(i).relationship_type_code := FND_API.G_MISS_CHAR;
717 x_account_tbl(i).bill_to_address := FND_API.G_MISS_NUM;
718 x_account_tbl(i).ship_to_address := FND_API.G_MISS_NUM;
719 x_account_tbl(i).active_start_date := FND_API.G_MISS_DATE;
720 x_account_tbl(i).active_end_date := FND_API.G_MISS_DATE;
721 x_account_tbl(i).context := FND_API.G_MISS_CHAR;
722 x_account_tbl(i).attribute1 := FND_API.G_MISS_CHAR;
723 x_account_tbl(i).attribute2 := FND_API.G_MISS_CHAR;
724 x_account_tbl(i).attribute3 := FND_API.G_MISS_CHAR;
725 x_account_tbl(i).attribute4 := FND_API.G_MISS_CHAR;
726 x_account_tbl(i).attribute5 := FND_API.G_MISS_CHAR;
727 x_account_tbl(i).attribute6 := FND_API.G_MISS_CHAR;
728 x_account_tbl(i).attribute7 := FND_API.G_MISS_CHAR;
729 x_account_tbl(i).attribute8 := FND_API.G_MISS_CHAR;
730 x_account_tbl(i).attribute9 := FND_API.G_MISS_CHAR;
731 x_account_tbl(i).attribute10 := FND_API.G_MISS_CHAR;
732 x_account_tbl(i).attribute11 := FND_API.G_MISS_CHAR;
733 x_account_tbl(i).attribute12 := FND_API.G_MISS_CHAR;
734 x_account_tbl(i).attribute13 := FND_API.G_MISS_CHAR;
735 x_account_tbl(i).attribute14 := FND_API.G_MISS_CHAR;
736 x_account_tbl(i).attribute15 := FND_API.G_MISS_CHAR;
737 x_account_tbl(i).object_version_number := FND_API.G_MISS_NUM ;
738 x_account_tbl(i).call_contracts := FND_API.G_TRUE;
739 x_account_tbl(i).vld_organization_id := FND_API.G_MISS_NUM;
740 END LOOP;
741 End If;
742 END Initialize_account_tbl;
743 --------------------------------------------------------------------------------
744 --Start of comments
745 --
746 --API Name : initialize_party_tbl
747 --Purpose : Private procedure to initialize party table to defaultvalues
748 --End of comments
749 --------------------------------------------------------------------------------
750 PROCEDURE initialize_party_tbl(p_in IN party_tbl_type,
751 x_out OUT NOCOPY party_tbl_type) IS
752 i NUMBER;
753 BEGIN
754 If nvl(p_in.LAST,0) > 0 Then
755 FOR i in 1..p_in.LAST LOOP
756 x_out(i).instance_party_id := FND_API.G_MISS_NUM;
757 x_out(i).instance_id := FND_API.G_MISS_NUM;
758 x_out(i).party_source_table := p_in(i).party_source_table;
759 x_out(i).party_id := p_in(i).party_id;
760 x_out(i).relationship_type_code := p_in(i).relationship_type_code;
761 x_out(i).contact_flag := p_in(i).contact_flag;
762 x_out(i).contact_ip_id := FND_API.G_MISS_NUM;
763 x_out(i).active_start_date := FND_API.G_MISS_DATE;
764 x_out(i).active_end_date := FND_API.G_MISS_DATE;
765 x_out(i).context := FND_API.G_MISS_CHAR;
766 x_out(i).attribute1 := FND_API.G_MISS_CHAR;
767 x_out(i).attribute2 := FND_API.G_MISS_CHAR;
768 x_out(i).attribute3 := FND_API.G_MISS_CHAR;
769 x_out(i).attribute4 := FND_API.G_MISS_CHAR;
770 x_out(i).attribute5 := FND_API.G_MISS_CHAR;
771 x_out(i).attribute6 := FND_API.G_MISS_CHAR;
772 x_out(i).attribute7 := FND_API.G_MISS_CHAR;
773 x_out(i).attribute8 := FND_API.G_MISS_CHAR;
774 x_out(i).attribute9 := FND_API.G_MISS_CHAR;
775 x_out(i).attribute10 := FND_API.G_MISS_CHAR;
776 x_out(i).attribute11 := FND_API.G_MISS_CHAR;
777 x_out(i).attribute12 := FND_API.G_MISS_CHAR;
778 x_out(i).attribute13 := FND_API.G_MISS_CHAR;
779 x_out(i).attribute14 := FND_API.G_MISS_CHAR;
780 x_out(i).attribute15 := FND_API.G_MISS_CHAR;
781 x_out(i).object_version_number := FND_API.G_MISS_NUM;
782 x_out(i).primary_flag := FND_API.G_MISS_CHAR;
783 x_out(i).preferred_flag := FND_API.G_MISS_CHAR;
784 END LOOP;
785 End If;
786 END Initialize_Party_tbl;
787 ------------------------------------------------------------------------------
788 --Start of comments
789 --
790 --API Name : Process_IB_Line_1
791 --Purpose : Local API called from Activate_IB_Instance API
792 -- Does processing contract header level processing
793 -- validations , which are pre-req for calling IB
794 -- create item instance API.
795 -- Logic taken out of Activate_IB_Instance to
796 -- make modular
797 --Modification History :
798 --01-May-2002 avsingh Created
799 --End of Comments
800 ------------------------------------------------------------------------------
801 Procedure Process_IB_Line_1(p_api_version IN NUMBER,
802 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
803 x_return_status OUT NOCOPY VARCHAR2,
804 x_msg_count OUT NOCOPY NUMBER,
805 x_msg_data OUT NOCOPY VARCHAR2,
806 p_chrv_id IN Number,
807 p_inst_cle_id IN NUMBER,
808 p_ib_cle_id IN NUMBER,
809 x_party_tbl OUT NOCOPY party_tbl_type,
810 x_party_account OUT NOCOPY NUMBER,
811 x_inv_mstr_org_id OUT NOCOPY NUMBER,
812 x_model_line_qty OUT NOCOPY NUMBER,
813 --bug#2845959
814 x_primary_uom_code OUT NOCOPY VARCHAR2,
815 --bug# 3222804
816 x_inv_org_id OUT NOCOPY NUMBER
817 ) is
818
819 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
820 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_IB_LINE_1';
821 l_api_version CONSTANT NUMBER := 1.0;
822
823 l_party_tbl party_tbl_type;
824
825 --cursor to fetch ib master org id
826 CURSOR mstr_org_csr (p_chr_id IN NUMBER) is
827 SELECT MP.master_organization_id
828 FROM MTL_PARAMETERS MP,
829 OKC_K_HEADERS_B CHR
830 WHERE MP.organization_id = CHR.inv_organization_id
831 AND CHR.id = p_chr_id;
832
833 l_inv_mstr_org_id NUMBER;
834
835 l_inst_cle_id NUMBER;
836
837 CURSOR get_qty_csr (p_inst_line_id IN NUMBER) is
838 SELECT cim.number_of_items,
839 cim.object1_id1,
840 cim.object1_id2
841 FROM OKC_K_ITEMS CIM,
842 OKC_K_LINES_B MDL,
843 OKC_LINE_STYLES_B MDL_LSE,
844 OKC_K_LINES_B INST
845 WHERE CIM.CLE_ID = MDL.ID
846 AND MDL.CLE_ID = INST.CLE_ID
847 AND MDL.LSE_ID = MDL_LSE.ID
848 AND MDL_LSE.LTY_CODE = G_MODEL_LINE_LTY_CODE
849 AND INST.ID = p_inst_line_id;
850
851 l_model_line_qty NUMBER;
852 l_mdl_line_inv_item_id Varchar2(40);
853 l_mdl_line_inv_org_id Varchar2(200);
854
855 --cursor to check trackable flag and if inv item on model line is valid
856 CURSOR chk_track_flag_csr(p_inv_item_id IN NUMBER,
857 p_inv_org_id IN NUMBER) IS
858 SELECT nvl(comms_nl_trackable_flag,'N'),
859 segment1,
860 description,
861 --Bug#2845959
862 primary_uom_code
863 FROM MTL_SYSTEM_ITEMS
864 WHERE inventory_item_id = p_inv_item_id
865 AND organization_id = p_inv_org_id;
866
867 l_track_flag VARCHAR2(1);
868 l_item_name MTL_SYSTEM_ITEMS.SEGMENT1%TYPE;
869 l_item_description MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE;
870 --Bug#2845959
871 l_primary_uom_code MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE;
872
873 /*-Bug# 3124577 : 11.5.10:Rule Migration----------------------------------------------
874 --cursor to fetch customer account
875 CURSOR party_account_csr(p_chrv_id IN NUMBER) is
876 SELECT to_number(rulv.object1_id1)
877 FROM OKC_RULES_V rulv
878 WHERE rulv.rule_information_category = G_CUST_ACCT_RULE
879 AND rulv.dnz_chr_id = p_chrv_id
880 AND exists (select '1'
881 from OKC_RULE_GROUPS_V rgpv
882 where rgpv.chr_id = p_chrv_id
883 and rgpv.rgd_code = G_CUST_ACCT_RULE_GROUP
884 and rgpv.id = rulv.rgp_id);
885
886 l_party_account NUMBER;
887 ------------------------------------11.5.10:Rule Migration----*/
888 CURSOR party_account_csr(p_chrv_id IN NUMBER) is
889 SELECT chrb.cust_acct_id
890 FROM OKC_K_HEADERS_B chrb
891 WHERE chrb.id = p_chrv_id;
892
893 l_party_account NUMBER;
894
895
896 Begin
897 --call start activity to set savepoint
898 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
899 p_init_msg_list,
900 '_PVT',
901 x_return_status);
902 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
903 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
904 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
905 RAISE OKL_API.G_EXCEPTION_ERROR;
906 END IF;
907
908 l_inst_cle_id := p_inst_cle_id;
909 --initialize party tbl
910 get_party_rec(p_api_version => p_api_version,
911 p_init_msg_list => p_init_msg_list,
912 x_return_status => x_return_status,
913 x_msg_count => x_msg_count,
914 x_msg_data => x_msg_data,
915 p_chrv_id => p_chrv_id,
916 x_party_tbl => l_party_tbl);
917
918 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
919 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
920 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
921 RAISE OKL_API.G_EXCEPTION_ERROR;
922 END IF;
923
924 --get inventory master org id
925 l_inv_mstr_org_id := null;
926 OPEN mstr_org_csr (p_chr_id => p_chrv_id);
927 FETCH mstr_org_csr into
928 l_inv_mstr_org_id;
929 IF mstr_org_csr%NOTFOUND Then
930 --raise error for unable to find inv mstr org
931 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
932 p_msg_name => G_INV_MSTR_ORG_NOT_FOUND,
933 p_token1 => G_CONTRACT_ID_TOKEN,
934 p_token1_value => to_char(p_chrv_id)
935 );
936 RAISE OKL_API.G_EXCEPTION_ERROR;
937 --l_inv_mstr_org_id := 204;
938 ELSE
939 Null;
940 END IF;
941 CLOSE mstr_org_csr;
942
943 --get model line quantity
944 l_model_line_qty := 0;
945 OPEN get_qty_csr (p_inst_line_id => l_inst_cle_id );
946 FETCH get_qty_csr into
947 l_model_line_qty,
948 l_mdl_line_inv_item_id,
949 l_mdl_line_inv_org_id;
950 IF get_qty_csr%NOTFOUND Then
951 l_model_line_qty := 1;
952 ELSE
953 Null;
954 END IF;
955 CLOSE get_qty_csr;
956
957 --check for trackable flag for inventory item
958 --This is an IB prereq.
959 l_track_flag := 'N';
960 l_item_name := Null;
961 If l_mdl_line_inv_item_id is not null and l_mdl_line_inv_org_id is not null Then
962 Open chk_track_flag_csr(p_inv_item_id => to_number(l_mdl_line_inv_item_id),
963 p_inv_org_id => to_number(l_mdl_line_inv_org_id));
964 Fetch chk_track_flag_csr into l_track_flag,
965 l_item_name,
966 l_item_description,
967 --bug#2845959
968 l_primary_uom_code;
969 If chk_track_flag_csr%NOTFOUND Then
970 --raise error for trackable flag is 'N'
971 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
972 p_msg_name => G_INV_ITEM_NOT_FOUND,
973 p_token1 => G_INV_ITEM_ID_TOKEN,
974 p_token1_value => l_mdl_line_inv_item_id,
975 p_token2 => G_INV_ORG_ID_TOKEN,
976 p_token2_value => l_mdl_line_inv_org_id
977 );
978 RAISE OKL_API.G_EXCEPTION_ERROR;
979 End If;
980 Close chk_track_flag_csr;
981 If l_track_flag <> 'Y' Then
982 --raise error for trackable flag is 'N'
983 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
984 p_msg_name => G_MODEL_ITEM_NOT_TRACKABLE,
985 p_token1 => G_ITEM_NAME_TOKEN,
986 --p_token1_value => l_item_name
987 --Bug#2372065
988 p_token1_value => l_item_description
989 );
990 RAISE OKL_API.G_EXCEPTION_ERROR;
991 Elsif l_track_flag = 'Y' Then
992 --everything is fine here
993 Null;
994 End If;
995 Elsif l_mdl_line_inv_item_id is null OR l_mdl_line_inv_org_id is null Then
996 --raise error for trackable flag is 'N'
997 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
998 p_msg_name => G_MODEL_LINE_ITEM_NOT_FOUND
999 );
1000 RAISE OKL_API.G_EXCEPTION_ERROR;
1001 End If;
1002
1003 --get party accoutnt id
1004 l_party_account := null;
1005 OPEN party_account_csr(p_chrv_id => p_chrv_id);
1006 FETCH party_account_csr into
1007 l_party_account;
1008 IF party_account_csr%NOTFOUND Then
1009 --raise error for unable to find inv mstr org
1010 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1011 p_msg_name => G_CUST_ACCOUNT_FOUND,
1012 p_token1 => G_CONTRACT_ID_TOKEN,
1013 p_token1_value => to_char(p_chrv_id)
1014 );
1015 RAISE OKL_API.G_EXCEPTION_ERROR;
1016 ELSE
1017 NULL;
1018 END IF;
1019 CLOSE party_account_csr;
1020
1021 x_party_tbl := l_party_tbl;
1022 x_party_account := l_party_account;
1023 x_inv_mstr_org_id := l_inv_mstr_org_id;
1024 x_model_line_qty := l_model_line_qty;
1025 --bug#2845959
1026 x_primary_uom_code := l_primary_uom_code;
1027
1028 --bug#3222804
1029 x_inv_org_id := to_number(l_mdl_line_inv_org_id);
1030
1031 --Call end Activity
1032 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1033 EXCEPTION
1034 When OKL_API.G_EXCEPTION_ERROR Then
1035 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1036 (
1037 l_api_name,
1038 G_PKG_NAME,
1039 'OKL_API.G_RET_STS_ERROR',
1040 x_msg_count,
1041 x_msg_data,
1042 '_PVT'
1043 );
1044 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1045 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1046 (
1047 l_api_name,
1048 G_PKG_NAME,
1049 'OKL_API.G_RET_STS_UNEXP_ERROR',
1050 x_msg_count,
1051 x_msg_data,
1052 '_PVT'
1053 );
1054 WHEN OTHERS THEN
1055 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1056 (
1057 l_api_name,
1058 G_PKG_NAME,
1059 'OTHERS',
1060 x_msg_count,
1061 x_msg_data,
1062 '_PVT'
1063 );
1064 End Process_IB_Line_1;
1065 ------------------------------------------------------------------------------
1066 --Start of comments
1067 --
1068 --API Name : Process_IB_Line_2
1069 --Purpose : Local API called from Activate_IB_Instance API
1070 -- Does processing for each ib instance line
1071 -- and calls IB create_item_instance API
1072 -- Logic taken out of Activate_IB_Instance to
1073 -- make modular
1074 --Modification History :
1075 --01-May-2002 avsingh Created
1076 --End of Comments
1077 ------------------------------------------------------------------------------
1078 Procedure Process_IB_Line_2(p_api_version IN NUMBER,
1079 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1080 x_return_status OUT NOCOPY VARCHAR2,
1081 x_msg_count OUT NOCOPY NUMBER,
1082 x_msg_data OUT NOCOPY VARCHAR2,
1083 p_chrv_id IN NUMBER,
1084 p_inst_cle_id IN NUMBER,
1085 p_ib_cle_id IN NUMBER,
1086 p_party_tbl IN party_tbl_type,
1087 p_party_account IN NUMBER,
1088 p_inv_mstr_org_id IN NUMBER,
1089 p_model_line_qty IN NUMBER,
1090 --bug# 2845959
1091 p_uom_code IN VARCHAR2,
1092 p_trx_type IN VARCHAR2,
1093 --Bug# 3222804
1094 p_inv_org_id IN NUMBER,
1095 --Bug# 5207066
1096 p_rbk_ib_cle_id IN NUMBER DEFAULT NULL,
1097 x_cimv_rec OUT NOCOPY cimv_rec_type) is
1098
1099 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1100 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_IB_LINE_2';
1101 l_api_version CONSTANT NUMBER := 1.0;
1102
1103 l_no_data_found_cimv BOOLEAN;
1104 l_no_data_found_iipv BOOLEAN;
1105 l_cimv_rec cimv_rec_type;
1106 l_cimv_rec_out cimv_rec_type;
1107 l_iipv_rec iipv_rec_type;
1108 l_ib_cle_id NUMBER;
1109
1110 l_party_account NUMBER;
1111 l_inv_mstr_org_id NUMBER;
1112 l_model_line_qty NUMBER;
1113
1114 l_instance_rec inst_rec_type;
1115 l_ext_attrib_values_tbl ext_attrib_tbl_type;
1116 l_party_tbl party_tbl_type;
1117 l_party_tbl_in party_tbl_type;
1118 l_account_tbl party_account_tbl_type;
1119 l_pricing_attrib_tbl pricing_attribs_tbl_type;
1120 l_org_assignments_tbl org_units_tbl_type;
1121 l_asset_assignment_tbl instance_asset_tbl_type;
1122 l_txn_rec trx_rec_type;
1123
1124 --cursor to fetch party location id
1125 CURSOR instance_loc_csr (p_site_use_id1 IN VARCHAR2,
1126 p_site_use_id2 IN VARCHAR2) is
1127 SELECT location_id,
1128 party_site_id
1129 FROM OKX_PARTY_SITE_USES_V
1130 WHERE id1 = p_site_use_id1
1131 AND id2 = p_site_use_id2;
1132
1133 l_location_id NUMBER;
1134 l_party_site_id NUMBER;
1135
1136 --Bug# 3222804 :
1137 l_inv_org_id NUMBER;
1138
1139 Begin
1140 --call start activity to set savepoint
1141 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
1142 p_init_msg_list,
1143 '_PVT',
1144 x_return_status);
1145 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1146 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1147 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1148 RAISE OKL_API.G_EXCEPTION_ERROR;
1149 END IF;
1150
1151 l_ib_cle_id := p_ib_cle_id;
1152 l_party_account := p_party_account;
1153 l_inv_mstr_org_id := p_inv_mstr_org_id;
1154 l_model_line_qty := p_model_line_qty;
1155 l_party_tbl := p_party_tbl;
1156 --Bug# 3222804 :
1157 l_inv_org_id := p_inv_org_id;
1158
1159
1160 --initialize l_cimv_rec here
1161 l_cimv_rec := get_cimv_rec(l_ib_cle_id,l_no_data_found_cimv);
1162 If (l_cimv_rec.jtot_object1_code is not null) and (l_cimv_rec.object1_id1) is not null Then
1163 --ib instance is already plugged in (do nothing)
1164 x_cimv_rec := l_cimv_rec;
1165 Elsif (l_no_data_found_cimv) OR (l_cimv_rec.jtot_object1_code is null OR l_cimv_rec.object1_id1 is null) Then
1166 -- Call get_iipv_rec
1167 --Bug# 5207066
1168 l_iipv_rec := get_iipv_rec(p_kle_id => NVL(p_rbk_ib_cle_id,l_ib_cle_id), p_trx_type => p_trx_type, x_no_data_found => l_no_data_found_iipv);
1169 --dbms_output.put_line('after fetch iipv rec '||to_char(l_iipv_rec.id));
1170 If l_no_data_found_iipv Then
1171 --dbms_output.put_line('no ib creation transaction records ...!');
1172 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1173 p_msg_name => G_IB_TRX_REC_NOT_FOUND,
1174 p_token1 => G_IB_LINE_ID_TOKEN,
1175 p_token1_value => to_char(NVL(p_rbk_ib_cle_id,l_ib_cle_id))
1176 );
1177 RAISE OKL_API.G_EXCEPTION_ERROR;
1178 Else
1179 --initialize instance rec
1180 --dbms_output.put_line('before initialize instance rec');
1181 Initialize_instance_rec(l_instance_rec);
1182 l_instance_rec.inventory_item_id := l_iipv_rec.inventory_item_id;
1183 l_instance_rec.inv_master_organization_id := l_inv_mstr_org_id;
1184 --Bug# 3222804 :
1185 l_instance_rec.vld_organization_id := p_inv_org_id;
1186 --do not require to give inv_org_id
1187 --l_instance_rec.inv_organization_id := 204;
1188 --l_instance_rec.mfg_serial_number_flag := l_iipv_rec.mfg_serial_number_yn;
1189 l_instance_rec.mfg_serial_number_flag := G_MFG_SERIAL_NUMBER_FLAG;
1190 --l_instance_rec.location_id := to_number(l_iipv_rec.object_id1_new);
1191 --get instance location id
1192 l_location_id := null;
1193 l_party_site_id := null;
1194 OPEN instance_loc_csr (p_site_use_id1 => l_iipv_rec.object_id1_new,
1195 p_site_use_id2 => l_iipv_rec.object_id2_new);
1196 FETCH instance_loc_csr into
1197 l_location_id,
1198 l_party_site_id;
1199 --dbms_output.put_line('Location '||to_char(l_location_id));
1200 IF instance_loc_csr%NOTFOUND Then
1201 --dbms_output.put_line('party site use records not found ...!');
1202 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1203 p_msg_name => G_INSTALL_LOC_NOT_FOUND,
1204 p_token1 => G_INST_SITE_USE_TOKEN,
1205 p_token1_value => l_iipv_rec.object_id1_new
1206 );
1207 RAISE OKL_API.G_EXCEPTION_ERROR;
1208 ELSE
1209 l_instance_rec.location_id := l_location_id;
1210 l_instance_rec.INSTALL_LOCATION_ID := l_party_site_id;
1211 End If;
1212 CLOSE instance_loc_csr;
1213
1214 --l_instance_rec.location_id := 929;
1215 l_instance_rec.serial_number := l_iipv_rec.serial_number;
1216 l_instance_rec.location_type_code := G_LOC_TYPE_CODE;
1217 --l_instance_rec.active_start_date := sysdate-30; --not mandatory
1218 --l_instance_rec.instance_type_code := '10203'; --not mandatory
1219 If l_iipv_rec.serial_number is not null Then
1220 l_instance_rec.quantity := 1;
1221 Elsif l_iipv_rec.serial_number is null Then
1222 l_instance_rec.quantity :=l_model_line_qty;
1223 End If;
1224 --bug# 2845959:
1225 --l_instance_rec.unit_of_measure := G_UOM_CODE;
1226 l_instance_rec.unit_of_measure := p_uom_code;
1227 l_instance_rec.INSTALL_LOCATION_TYPE_CODE := G_INSTALL_LOC_TYPE_CODE;
1228 --l_instance_rec.INSTALL_LOCATION_ID := to_number(l_iipv_rec.object_id1_new);
1229
1230 --get transaction line record
1231 --initialize txn rec
1232 --dbms_output.put_line('before initialize txn rec');
1233 initialize_txn_rec(l_txn_rec);
1234 --Call get_trx_rec
1235 get_trx_rec(p_api_version => p_api_version,
1236 p_init_msg_list => p_init_msg_list,
1237 x_return_status => x_return_status,
1238 x_msg_count => x_msg_count,
1239 x_msg_data => x_msg_data,
1240 p_cle_id => l_ib_cle_id,
1241 p_transaction_type => G_IB_BKNG_TXN_TYPE,
1242 x_trx_rec => l_txn_rec);
1243 --dbms_output.put_line('after initialize txn rec '|| x_return_status);
1244 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1245 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1246 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1247 RAISE OKL_API.G_EXCEPTION_ERROR;
1248 END IF;
1249
1250 --initialize other parameter records to default
1251 --dbms_output.put_line('before initialize account tbl '||to_char(l_party_tbl(1).party_id));
1252 initialize_account_tbl(l_account_tbl);
1253 l_account_tbl(1).instance_party_id := l_party_tbl(1).party_id;
1254 l_account_tbl(1).party_account_id := l_party_account;
1255 l_account_tbl(1).relationship_type_code := G_PARTY_RELATIONSHIP;
1256 --l_account_tbl(1).active_start_date := sysdate;
1257 l_account_tbl(1).parent_tbl_index := 1;
1258
1259 --initialize party tbl
1260 --dbms_output.put_line('before initialize party tbl');
1261 initialize_party_tbl(p_in => l_party_tbl,
1262 x_out => l_party_tbl_in);
1263
1264 --Following code taken care of in initializations :
1265 --l_party_tbl(1).instance_party_id := FND_API.G_MISS_NUM;
1266 --l_party_tbl(1).instance_id := FND_API.G_MISS_NUM;
1267 --
1268 --l_account_tbl(1).ip_account_id := FND_API.G_MISS_NUM;
1269 --
1270 --l_txn_rec.transaction_id := FND_API.G_MISS_NUM;
1271
1272 --call create item instance
1273 --dbms_output.put_line('before calling create item instance');
1274 csi_item_instance_pub.create_item_instance(p_api_version => p_api_version,
1275 p_commit => fnd_api.g_false,
1276 p_init_msg_list => p_init_msg_list,
1277 p_instance_rec => l_instance_rec,
1278 p_validation_level => fnd_api.g_valid_level_full,
1279 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1280 p_party_tbl => l_party_tbl_in,
1281 p_account_tbl => l_account_tbl,
1282 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1283 p_org_assignments_tbl => l_org_assignments_tbl,
1284 p_asset_assignment_tbl => l_asset_assignment_tbl,
1285 p_txn_rec => l_txn_rec,
1286 x_return_status => x_return_status,
1287 x_msg_count => x_msg_count,
1288 x_msg_data => x_msg_data);
1289
1290 --dbms_output.put_line('status '||x_return_status);
1291 --dbms_output.put_line('instance_id '||to_char(l_instance_rec.instance_id));
1292
1293 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1294 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1295 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1296 RAISE OKL_API.G_EXCEPTION_ERROR;
1297 END IF;
1298
1299 --update line source (okc_k_items)
1300 If l_no_data_found_cimv then
1301 l_cimv_rec.cle_id := l_ib_cle_id;
1302 l_cimv_rec.dnz_chr_id := p_chrv_id;
1303 l_cimv_rec.object1_id1 := l_instance_rec.instance_id;
1304 l_cimv_rec.object1_id2 := '#';
1305 l_cimv_rec.jtot_object1_code := G_IB_LINE_SRC_CODE;
1306 l_cimv_rec.exception_yn := 'N';
1307
1308 okl_okc_migration_pvt.create_contract_item
1309 (p_api_version => p_api_version,
1310 p_init_msg_list => p_init_msg_list,
1311 x_return_status => x_return_status,
1312 x_msg_count => x_msg_count,
1313 x_msg_data => x_msg_data,
1314 p_cimv_rec => l_cimv_rec,
1315 x_cimv_rec => l_cimv_rec_out);
1316 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1317 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1318 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1319 RAISE OKL_API.G_EXCEPTION_ERROR;
1320 END IF;
1321 Else
1322 l_cimv_rec.object1_id1 := l_instance_rec.instance_id;
1323 l_cimv_rec.object1_id2 := '#';
1324 l_cimv_rec.jtot_object1_code := G_IB_LINE_SRC_CODE;
1325
1326 okl_okc_migration_pvt.update_contract_item
1327 (p_api_version => p_api_version,
1328 p_init_msg_list => p_init_msg_list,
1329 x_return_status => x_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data,
1332 p_cimv_rec => l_cimv_rec,
1333 x_cimv_rec => l_cimv_rec_out);
1334 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1335 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1336 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1337 RAISE OKL_API.G_EXCEPTION_ERROR;
1338 END IF;
1339 x_cimv_rec := l_cimv_rec_out;
1340 End If;
1341 -- gboomina Bug 5362977 - Start
1342 -- Only update transaction status if a new Asset is being added
1343 -- and the transaction being updated is on the original contract
1344 IF p_rbk_ib_cle_id IS NULL THEN
1345 -- gboomina Bug 5362977 - End
1346 --update transaction status
1347 update_trx_status(p_api_version => p_api_version,
1348 p_init_msg_list => p_init_msg_list,
1349 x_return_status => x_return_status,
1350 x_msg_count => x_msg_count,
1351 x_msg_data => x_msg_data,
1352 p_tas_id => l_iipv_rec.tas_id,
1353 p_tsu_code => G_TSU_CODE_PROCESSED);
1354 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1356 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1357 RAISE OKL_API.G_EXCEPTION_ERROR;
1358 END IF;
1359 -- gboomina Bug 5362977 - Start
1360 END IF;
1361 -- gboomina Bug 5362977 - End
1362 End If;--get iipv rec
1363 End If;--cimv_rec
1364 --Call end Activity
1365 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1366 EXCEPTION
1367 When OKL_API.G_EXCEPTION_ERROR Then
1368 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1369 (
1370 l_api_name,
1371 G_PKG_NAME,
1372 'OKL_API.G_RET_STS_ERROR',
1373 x_msg_count,
1374 x_msg_data,
1375 '_PVT'
1376 );
1377 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1378 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1379 (
1380 l_api_name,
1381 G_PKG_NAME,
1382 'OKL_API.G_RET_STS_UNEXP_ERROR',
1383 x_msg_count,
1384 x_msg_data,
1385 '_PVT'
1386 );
1387 WHEN OTHERS THEN
1388 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1389 (
1390 l_api_name,
1391 G_PKG_NAME,
1392 'OTHERS',
1393 x_msg_count,
1394 x_msg_data,
1395 '_PVT'
1396 );
1397 End Process_IB_Line_2;
1398 ------------------------------------------------------------------------------
1399 --Start of comments
1400 --
1401 --API Name : Process_IB_Line
1402 --Purpose : Local API called from Activate_IB_Instance API
1403 -- Does processing for each ib instance line
1404 -- and calls IB create_item_instance API.
1405 -- API created by merging Process_IB_Line1 and
1406 -- Process_IB_Line2
1407 --Modification History :
1408 --15-Mar-2004 rseela Created
1409 --End of Comments
1410 ------------------------------------------------------------------------------
1411
1412 Procedure Process_IB_Line(p_api_version IN NUMBER,
1413 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1414 x_return_status OUT NOCOPY VARCHAR2,
1415 x_msg_count OUT NOCOPY NUMBER,
1416 x_msg_data OUT NOCOPY VARCHAR2,
1417 p_chrv_id IN NUMBER,
1418 p_start_date IN DATE,
1419 p_inst_cle_id IN NUMBER,
1420 p_ib_cle_id IN NUMBER,
1421 p_party_tbl IN party_tbl_type,
1422 p_party_account IN NUMBER,
1423 p_inv_mstr_org_id IN NUMBER,
1424 p_model_line_qty IN NUMBER,
1425 --bug# 2845959
1426 p_uom_code IN VARCHAR2,
1427 p_trx_type IN VARCHAR2,
1428 --Bug# 3222804
1429 p_inv_org_id IN NUMBER,
1430 x_cimv_rec OUT NOCOPY cimv_rec_type) is
1431
1432 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1433 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_IB_LINE_2';
1434 l_api_version CONSTANT NUMBER := 1.0;
1435
1436 l_no_data_found_cimv BOOLEAN;
1437 l_no_data_found_iipv BOOLEAN;
1438 l_cimv_rec cimv_rec_type;
1439 l_cimv_rec_out cimv_rec_type;
1440 l_iipv_rec iipv_rec_type;
1441 l_ib_cle_id NUMBER;
1442 l_inst_cle_id NUMBER;
1443
1444 l_party_account NUMBER;
1445 l_inv_mstr_org_id NUMBER;
1446 l_model_line_qty NUMBER;
1447 l_mdl_line_inv_item_id Varchar2(40);
1448 l_mdl_line_inv_org_id Varchar2(200);
1449
1450
1451 l_instance_rec inst_rec_type;
1452 l_ext_attrib_values_tbl ext_attrib_tbl_type;
1453 l_party_tbl party_tbl_type;
1454 l_party_tbl_in party_tbl_type;
1455 l_account_tbl party_account_tbl_type;
1456 l_pricing_attrib_tbl pricing_attribs_tbl_type;
1457 l_org_assignments_tbl org_units_tbl_type;
1458 l_asset_assignment_tbl instance_asset_tbl_type;
1459 l_txn_rec trx_rec_type;
1460
1461 l_track_flag VARCHAR2(1);
1462 l_item_name MTL_SYSTEM_ITEMS.SEGMENT1%TYPE;
1463 l_item_description MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE;
1464 --Bug#2845959
1465 l_primary_uom_code MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE;
1466
1467
1468 CURSOR get_qty_csr (p_inst_line_id IN NUMBER) is
1469 SELECT cim.number_of_items,
1470 cim.object1_id1,
1471 cim.object1_id2
1472 FROM OKC_K_ITEMS CIM,
1473 OKC_K_LINES_B MDL,
1474 OKC_K_LINES_B INST
1475 WHERE CIM.CLE_ID = MDL.ID
1476 AND MDL.CLE_ID = INST.CLE_ID
1477 AND MDL.LSE_ID = G_MODEL_LINE_LTY_ID
1478 AND INST.ID = p_inst_line_id;
1479
1480 --cursor to check trackable flag and if inv item on model line is valid
1481 CURSOR chk_track_flag_csr(p_inv_item_id IN NUMBER,
1482 p_inv_org_id IN NUMBER) IS
1483 SELECT nvl(comms_nl_trackable_flag,'N'),
1484 segment1,
1485 description,
1486 --Bug#2845959
1487 primary_uom_code
1488 FROM MTL_SYSTEM_ITEMS
1489 WHERE inventory_item_id = p_inv_item_id
1490 AND organization_id = p_inv_org_id;
1491
1492 --cursor to fetch party location id
1493 CURSOR instance_loc_csr (p_site_use_id1 IN VARCHAR2) is
1494 SELECT HPS.location_id,
1495 HPS.party_site_id
1496 FROM HZ_PARTY_SITE_USES HPSU, HZ_PARTY_SITES HPS
1497 WHERE HPS.party_site_id = HPSU.party_site_id
1498 AND HPSU.party_site_use_id = p_site_use_id1;
1499
1500 --rkuttiya added for IB Link User story 22-jan-2008 sprint 7
1501 -- verify existence of the serial number
1502 CURSOR c_serial_no_exists(p_serial_number IN VARCHAR2,
1503 p_inv_item_id IN NUMBER,
1504 p_inv_mstr_org_id IN NUMBER,
1505 p_khr_start_date IN DATE) IS
1506 SELECT INSTANCE_ID
1507 FROM CSI_ITEM_INSTANCES CSI
1508 WHERE SERIAL_NUMBER = p_serial_number
1509 AND INVENTORY_ITEM_ID = p_inv_item_id
1510 AND INV_MASTER_ORGANIZATION_ID = p_inv_mstr_org_id
1511 AND INSTANCE_STATUS_ID IN (SELECT INSTANCE_STATUS_ID
1512 FROM CSI_INSTANCE_STATUSES
1513 WHERE TERMINATED_FLAG = 'N')
1514 AND NVL(ACTIVE_END_DATE,(p_khr_start_date+1)) > p_khr_start_date
1515 AND ROWNUM = 1
1516 AND NOT EXISTS
1517 (SELECT CLE.DNZ_CHR_ID
1518 FROM OKC_K_LINES_B CLE,
1519 OKC_LINE_STYLES_B CLS,
1520 OKC_K_ITEMS CIM,
1521 OKX_INSTALL_ITEMS_V CIX,
1522 OKL_K_HEADERS KHR
1523 WHERE CLE.LSE_ID = CLS.ID
1524 AND CLE.DNZ_CHR_ID = KHR.ID
1525 AND CLS.LTY_CODE = 'INST_ITEM'
1526 AND CLE.ID = CIM.CLE_ID
1527 AND CIM.OBJECT1_ID1 = CIX.ID1
1528 AND CIM.OBJECT1_ID2 = CIX.ID2
1529 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1530 AND CIX.INSTANCE_ID = CSI.INSTANCE_ID);
1531
1532 CURSOR c_check_usage(p_contract_id IN NUMBER) IS
1533 SELECT '!'
1534 FROM okc_k_headers_b CHR
1535 WHERE chr.id = p_contract_id
1536 AND EXISTS (SELECT '1'
1537 FROM okc_line_styles_b lse,
1538 okc_k_lines_b cle
1539 WHERE cle.sts_code = 'APPROVED'
1540 AND lse.id = cle.lse_id
1541 AND lse.lty_code = 'USAGE'
1542 AND cle.dnz_chr_id = chr.id);
1543
1544
1545 l_usage_khr VARCHAR2(1) DEFAULT '?';
1546
1547 l_serial_number VARCHAR2(30);
1548 l_inv_item_id NUMBER;
1549 l_instance_id NUMBER;
1550
1551 l_location_id NUMBER;
1552 l_party_site_id NUMBER;
1553
1554 --Bug# 3222804 :
1555 l_inv_org_id NUMBER;
1556
1557 Begin
1558 --call start activity to set savepoint
1559 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
1560 p_init_msg_list,
1561 '_PVT',
1562 x_return_status);
1563 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1564 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1565 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1566 RAISE OKL_API.G_EXCEPTION_ERROR;
1567 END IF;
1568
1569 l_inst_cle_id := p_inst_cle_id;
1570
1571 --get model line quantity
1572 l_model_line_qty := 0;
1573 OPEN get_qty_csr (p_inst_line_id => l_inst_cle_id );
1574 FETCH get_qty_csr into
1575 l_model_line_qty,
1576 l_mdl_line_inv_item_id,
1577 l_mdl_line_inv_org_id;
1578 IF get_qty_csr%NOTFOUND Then
1579 l_model_line_qty := 1;
1580 ELSE
1581 Null;
1582 END IF;
1583 CLOSE get_qty_csr;
1584
1585 --check for trackable flag for inventory item
1586 --This is an IB prereq.
1587 l_track_flag := 'N';
1588 l_item_name := Null;
1589 If l_mdl_line_inv_item_id is not null and l_mdl_line_inv_org_id is not null Then
1590 Open chk_track_flag_csr(p_inv_item_id => to_number(l_mdl_line_inv_item_id),
1591 p_inv_org_id => to_number(l_mdl_line_inv_org_id));
1592 Fetch chk_track_flag_csr into l_track_flag,
1593 l_item_name,
1594 l_item_description,
1595 --bug#2845959
1596 l_primary_uom_code;
1597 If chk_track_flag_csr%NOTFOUND Then
1598 --raise error for trackable flag is 'N'
1599 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1600 p_msg_name => G_INV_ITEM_NOT_FOUND,
1601 p_token1 => G_INV_ITEM_ID_TOKEN,
1602 p_token1_value => l_mdl_line_inv_item_id,
1603 p_token2 => G_INV_ORG_ID_TOKEN,
1604 p_token2_value => l_mdl_line_inv_org_id
1605 );
1606 RAISE OKL_API.G_EXCEPTION_ERROR;
1607 End If;
1608 Close chk_track_flag_csr;
1609 If l_track_flag <> 'Y' Then
1610 --raise error for trackable flag is 'N'
1611 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1612 p_msg_name => G_MODEL_ITEM_NOT_TRACKABLE,
1613 p_token1 => G_ITEM_NAME_TOKEN,
1614 --p_token1_value => l_item_name
1615 --Bug#2372065
1616 p_token1_value => l_item_description
1617 );
1618 RAISE OKL_API.G_EXCEPTION_ERROR;
1619 Elsif l_track_flag = 'Y' Then
1620 --everything is fine here
1621 Null;
1622 End If;
1623 Elsif l_mdl_line_inv_item_id is null OR l_mdl_line_inv_org_id is null Then
1624 --raise error for trackable flag is 'N'
1625 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1626 p_msg_name => G_MODEL_LINE_ITEM_NOT_FOUND
1627 );
1628 RAISE OKL_API.G_EXCEPTION_ERROR;
1629 End If;
1630
1631 l_ib_cle_id := p_ib_cle_id;
1632 l_party_account := p_party_account;
1633 l_inv_mstr_org_id := p_inv_mstr_org_id;
1634 l_party_tbl := p_party_tbl;
1635 --Bug# 3222804 :
1636 l_inv_org_id := to_number(l_mdl_line_inv_org_id);
1637
1638
1639 --initialize l_cimv_rec here
1640 l_cimv_rec := get_cimv_rec(l_ib_cle_id,l_no_data_found_cimv);
1641 If (l_cimv_rec.jtot_object1_code is not null) and (l_cimv_rec.object1_id1) is not null Then
1642 --ib instance is already plugged in (do nothing)
1643 x_cimv_rec := l_cimv_rec;
1644 Elsif (l_no_data_found_cimv) OR (l_cimv_rec.jtot_object1_code is null OR l_cimv_rec.object1_id1 is null) Then
1645 -- Call get_iipv_rec
1646 l_iipv_rec := get_iipv_rec(p_kle_id => l_ib_cle_id, p_trx_type => p_trx_type, x_no_data_found => l_no_data_found_iipv);
1647 --dbms_output.put_line('after fetch iipv rec '||to_char(l_iipv_rec.id));
1648 If l_no_data_found_iipv Then
1649 --dbms_output.put_line('no ib creation transaction records ...!');
1650 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1651 p_msg_name => G_IB_TRX_REC_NOT_FOUND,
1652 p_token1 => G_IB_LINE_ID_TOKEN,
1653 p_token1_value => to_char(l_ib_cle_id)
1654 );
1655 RAISE OKL_API.G_EXCEPTION_ERROR;
1656 Else
1657 --rkuttiya added for IB Link user story 22-jan-08
1658 -- validate the existence of a serial number instance created by
1659 -- applications. If matching serial number inventory item combination
1660 -- exists, then link the contract to that IB item instance
1661 -- instead of creating a new one
1662 -- This linking of existing IB instance is not currently opened for
1663 -- contracts with usage lines
1664
1665 -- check if there is usage line on the contract
1666 OPEN c_check_usage(p_chrv_id);
1667 FETCH c_check_usage INTO l_usage_khr;
1668 CLOSE c_check_usage;
1669
1670 -- okl_debug_pub.logmessage('Usage check '||l_usage_khr);
1671 IF l_usage_khr <> '!' THEN
1672 l_serial_number := l_iipv_rec.serial_number;
1673 l_inv_item_id := l_iipv_rec.inventory_item_id;
1674
1675 OPEN
1676 c_serial_no_exists(l_serial_number,l_inv_item_id,p_inv_mstr_org_id,p_start_date);
1677 FETCH c_serial_no_exists INTO l_instance_id;
1678 CLOSE c_serial_no_exists;
1679 END IF;
1680
1681 -- okl_debug_pub.logmessage('serial number '|| l_serial_number);
1682 -- okl_debug_pub.logmessage('inventory item id '||l_inv_item_id);
1683 -- okl_debug_pub.logmessage('instance id ' ||l_instance_id);
1684
1685 --If IB instance already exists do not create a new instance else
1686 -- create
1687 IF l_instance_id IS NOT NULL THEN
1688 l_instance_rec.instance_id := l_instance_id;
1689 --okl_debug_pub.logmessage('linking IB instance');
1690 ELSIF (l_usage_khr = '!') OR (l_instance_id IS NULL) THEN
1691 --okl_debug_pub.logmessage('creating IB instance');
1692
1693 --initialize instance rec
1694 --dbms_output.put_line('before initialize instance rec');
1695 Initialize_instance_rec(l_instance_rec);
1696 l_instance_rec.inventory_item_id := l_iipv_rec.inventory_item_id;
1697 l_instance_rec.inv_master_organization_id := l_inv_mstr_org_id;
1698 --Bug# 3222804 :
1699 l_instance_rec.vld_organization_id := l_inv_org_id;
1700 --do not require to give inv_org_id
1701 --l_instance_rec.inv_organization_id := 204;
1702 --l_instance_rec.mfg_serial_number_flag := l_iipv_rec.mfg_serial_number_yn;
1703 l_instance_rec.mfg_serial_number_flag := G_MFG_SERIAL_NUMBER_FLAG;
1704 --l_instance_rec.location_id := to_number(l_iipv_rec.object_id1_new);
1705 --get instance location id
1706 l_location_id := null;
1707 l_party_site_id := null;
1708 OPEN instance_loc_csr (p_site_use_id1 => l_iipv_rec.object_id1_new);
1709 FETCH instance_loc_csr into
1710 l_location_id,
1711 l_party_site_id;
1712 --dbms_output.put_line('Location '||to_char(l_location_id));
1713 IF instance_loc_csr%NOTFOUND Then
1714 --dbms_output.put_line('party site use records not found ...!');
1715 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1716 p_msg_name => G_INSTALL_LOC_NOT_FOUND,
1717 p_token1 => G_INST_SITE_USE_TOKEN,
1718 p_token1_value => l_iipv_rec.object_id1_new
1719 );
1720 RAISE OKL_API.G_EXCEPTION_ERROR;
1721 ELSE
1722 l_instance_rec.location_id := l_location_id;
1723 l_instance_rec.INSTALL_LOCATION_ID := l_party_site_id;
1724 End If;
1725 CLOSE instance_loc_csr;
1726
1727 --l_instance_rec.location_id := 929;
1728 l_instance_rec.serial_number := l_iipv_rec.serial_number;
1729 l_instance_rec.location_type_code := G_LOC_TYPE_CODE;
1730 --l_instance_rec.active_start_date := sysdate-30; --not mandatory
1731 --l_instance_rec.instance_type_code := '10203'; --not mandatory
1732 If l_iipv_rec.serial_number is not null Then
1733 l_instance_rec.quantity := 1;
1734 Elsif l_iipv_rec.serial_number is null Then
1735 l_instance_rec.quantity :=l_model_line_qty;
1736 End If;
1737 --bug# 2845959:
1738 --l_instance_rec.unit_of_measure := G_UOM_CODE;
1739 l_instance_rec.unit_of_measure := l_primary_uom_code;
1740 l_instance_rec.INSTALL_LOCATION_TYPE_CODE := G_INSTALL_LOC_TYPE_CODE;
1741 --l_instance_rec.INSTALL_LOCATION_ID := to_number(l_iipv_rec.object_id1_new);
1742
1743 --get transaction line record
1744 --initialize txn rec
1745 --dbms_output.put_line('before initialize txn rec');
1746 initialize_txn_rec(l_txn_rec);
1747 --Call get_trx_rec
1748 get_trx_rec(p_api_version => p_api_version,
1749 p_init_msg_list => p_init_msg_list,
1750 x_return_status => x_return_status,
1751 x_msg_count => x_msg_count,
1752 x_msg_data => x_msg_data,
1753 p_cle_id => l_ib_cle_id,
1754 p_transaction_type => G_IB_BKNG_TXN_TYPE,
1755 x_trx_rec => l_txn_rec);
1756 --dbms_output.put_line('after initialize txn rec '|| x_return_status);
1757 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1758 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1759 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1760 RAISE OKL_API.G_EXCEPTION_ERROR;
1761 END IF;
1762
1763 --initialize other parameter records to default
1764 --dbms_output.put_line('before initialize account tbl '||to_char(l_party_tbl(1).party_id));
1765 initialize_account_tbl(l_account_tbl);
1766 l_account_tbl(1).instance_party_id := l_party_tbl(1).party_id;
1767 l_account_tbl(1).party_account_id := l_party_account;
1768 l_account_tbl(1).relationship_type_code := G_PARTY_RELATIONSHIP;
1769 --l_account_tbl(1).active_start_date := sysdate;
1770 l_account_tbl(1).parent_tbl_index := 1;
1771
1772 --initialize party tbl
1773 --dbms_output.put_line('before initialize party tbl');
1774 initialize_party_tbl(p_in => l_party_tbl,
1775 x_out => l_party_tbl_in);
1776
1777 --Following code taken care of in initializations :
1778 --l_party_tbl(1).instance_party_id := FND_API.G_MISS_NUM;
1779 --l_party_tbl(1).instance_id := FND_API.G_MISS_NUM;
1780 --
1781 --l_account_tbl(1).ip_account_id := FND_API.G_MISS_NUM;
1782 --
1783 --l_txn_rec.transaction_id := FND_API.G_MISS_NUM;
1784
1785 --call create item instance
1786 --dbms_output.put_line('before calling create item instance');
1787
1788 csi_item_instance_pub.create_item_instance(p_api_version => p_api_version,
1789 p_commit => fnd_api.g_false,
1790 p_init_msg_list => p_init_msg_list,
1791 p_instance_rec => l_instance_rec,
1792 p_validation_level => fnd_api.g_valid_level_full,
1793 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1794 p_party_tbl => l_party_tbl_in,
1795 p_account_tbl => l_account_tbl,
1796 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1797 p_org_assignments_tbl => l_org_assignments_tbl,
1798 p_asset_assignment_tbl => l_asset_assignment_tbl,
1799 p_txn_rec => l_txn_rec,
1800 x_return_status => x_return_status,
1801 x_msg_count => x_msg_count,
1802 x_msg_data => x_msg_data);
1803
1804 --dbms_output.put_line('status '||x_return_status);
1805 --dbms_output.put_line('instance_id '||to_char(l_instance_rec.instance_id));
1806
1807 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1808 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1809 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1810 RAISE OKL_API.G_EXCEPTION_ERROR;
1811 END IF;
1812 END IF;--check for existing serial no instance
1813
1814 --update line source (okc_k_items)
1815 If l_no_data_found_cimv then
1816 l_cimv_rec.cle_id := l_ib_cle_id;
1817 l_cimv_rec.dnz_chr_id := p_chrv_id;
1818 l_cimv_rec.object1_id1 := l_instance_rec.instance_id;
1819 l_cimv_rec.object1_id2 := '#';
1820 l_cimv_rec.jtot_object1_code := G_IB_LINE_SRC_CODE;
1821 l_cimv_rec.exception_yn := 'N';
1822 okl_okc_migration_pvt.create_contract_item
1823 (p_api_version => p_api_version,
1824 p_init_msg_list => p_init_msg_list,
1825 x_return_status => x_return_status,
1826 x_msg_count => x_msg_count,
1827 x_msg_data => x_msg_data,
1828 p_cimv_rec => l_cimv_rec,
1829 x_cimv_rec => l_cimv_rec_out);
1830 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1831 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1832 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1833 RAISE OKL_API.G_EXCEPTION_ERROR;
1834 END IF;
1835 Else
1836 l_cimv_rec.object1_id1 := l_instance_rec.instance_id;
1837 l_cimv_rec.object1_id2 := '#';
1838 l_cimv_rec.jtot_object1_code := G_IB_LINE_SRC_CODE;
1839 okl_okc_migration_pvt.update_contract_item
1840 (p_api_version => p_api_version,
1841 p_init_msg_list => p_init_msg_list,
1842 x_return_status => x_return_status,
1843 x_msg_count => x_msg_count,
1844 x_msg_data => x_msg_data,
1845 p_cimv_rec => l_cimv_rec,
1846 x_cimv_rec => l_cimv_rec_out);
1847 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1848 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1849 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1850 RAISE OKL_API.G_EXCEPTION_ERROR;
1851 END IF;
1852 x_cimv_rec := l_cimv_rec_out;
1853 End If;
1854 --update transaction status
1855 update_trx_status(p_api_version => p_api_version,
1856 p_init_msg_list => p_init_msg_list,
1857 x_return_status => x_return_status,
1858 x_msg_count => x_msg_count,
1859 x_msg_data => x_msg_data,
1860 p_tas_id => l_iipv_rec.tas_id,
1861 p_tsu_code => G_TSU_CODE_PROCESSED);
1862 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1863 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1864 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1865 RAISE OKL_API.G_EXCEPTION_ERROR;
1866 END IF;
1867 End If;--get iipv rec
1868 End If;--cimv_rec
1869 --Call end Activity
1870 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1871 EXCEPTION
1872 When OKL_API.G_EXCEPTION_ERROR Then
1873 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1874 (
1875 l_api_name,
1876 G_PKG_NAME,
1877 'OKL_API.G_RET_STS_ERROR',
1878 x_msg_count,
1879 x_msg_data,
1880 '_PVT'
1881 );
1882 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1883 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1884 (
1885 l_api_name,
1886 G_PKG_NAME,
1887 'OKL_API.G_RET_STS_UNEXP_ERROR',
1888 x_msg_count,
1889 x_msg_data,
1890 '_PVT'
1891 );
1892 WHEN OTHERS THEN
1893 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1894 (
1895 l_api_name,
1896 G_PKG_NAME,
1897 'OTHERS',
1898 x_msg_count,
1899 x_msg_data,
1900 '_PVT'
1901 );
1902 End Process_IB_Line;
1903
1904
1905 --------------------------------------------------------------------------------
1906 --Start of comments
1907 --
1908 --API Name : ACTIVATE_IB_INSTANCE
1909 --Purpose : Calls IB API to create an item instance in IB
1910 -- Selects ib instance to create given a top line
1911 -- and line style codes for instance line and ib
1912 -- line.
1913 --Modification History :
1914 --15-Jun-2001 avsingh Created
1915 --Notes : Assigns values to transaction_type_id and source_line_ref_id
1916 --End of Comments
1917 --------------------------------------------------------------------------------
1918 Procedure ACTIVATE_IB_INSTANCE(p_api_version IN NUMBER,
1919 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1920 x_return_status OUT NOCOPY VARCHAR2,
1921 x_msg_count OUT NOCOPY NUMBER,
1922 x_msg_data OUT NOCOPY VARCHAR2,
1923 p_chrv_id IN NUMBER,
1924 p_call_mode IN VARCHAR2,
1925 x_cimv_tbl OUT NOCOPY cimv_tbl_type) is
1926
1927 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1928 l_api_name CONSTANT VARCHAR2(30) := 'ACTIVATE_IB_INSTANCE';
1929 l_api_version CONSTANT NUMBER := 1.0;
1930 l_trx_type VARCHAR2(30) := G_TRX_LINE_TYPE_BOOK;
1931
1932 --cursor to verify the subclass code
1933 --contract has to be a 'LEASE' subclass contract to qualify for FA_ADDITION
1934 Cursor chk_subclass_csr(p_chrv_id IN NUMBER) is
1935 SELECT SCS_CODE,
1936 STS_CODE,
1937 INV_ORGANIZATION_ID,
1938 START_DATE -- rkuttiya added for bug # 6795295
1939 From OKC_K_HEADERS_B
1940 WHERE ID = P_CHRV_ID;
1941
1942 l_scs_code OKC_K_HEADERS_B.SCS_CODE%TYPE;
1943 l_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
1944 l_inv_org_id OKC_K_HEADERS_B.INV_ORGANIZATION_ID%TYPE;
1945
1946 --cursor to get ib line
1947 Cursor ib_line_csr(p_chrv_id IN Number) is
1948 SELECT cle.id,
1949 cle.cle_id
1950 from okc_k_lines_b cle,
1951 okc_statuses_b sts -- 4698117
1952 where cle.lse_id = G_IB_LINE_LTY_ID
1953 and cle.dnz_chr_id = p_chrv_id
1954 and cle.sts_code = sts.code
1955 and sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED');
1956 --and sts_code not in ('AMENDED', 'BANKRUPTCY_HOLD', 'EXPIRED', 'LITIGATION_HOLD','REVERSED', 'TERMINATED', 'TERMINATION_HOLD');
1957
1958 --cursor to fetch ib master org id
1959 CURSOR mstr_org_csr (p_chr_id IN NUMBER) is
1960 SELECT MP.master_organization_id
1961 FROM MTL_PARAMETERS MP,
1962 OKC_K_HEADERS_B CHR
1963 WHERE MP.organization_id = CHR.inv_organization_id
1964 AND CHR.id = p_chr_id;
1965
1966 CURSOR party_account_csr(p_chrv_id IN NUMBER) is
1967 SELECT chrb.cust_acct_id
1968 FROM OKC_K_HEADERS_B chrb
1969 WHERE chrb.id = p_chrv_id;
1970
1971
1972 l_inv_mstr_org_id NUMBER;
1973 l_party_account NUMBER;
1974 l_inst_cle_id NUMBER;
1975 l_ib_cle_id NUMBER;
1976 l_ib_line_count NUMBER;
1977 l_cimv_rec cimv_rec_type;
1978 l_cimv_tbl cimv_tbl_type;
1979 l_ib_line_lty_code VARCHAR2(200) := G_IB_LINE_LTY_CODE;
1980 l_party_tbl party_tbl_type;
1981 l_model_line_qty NUMBER;
1982 --bug# 2845959
1983 l_primary_uom_code mtl_system_items.primary_uom_code%TYPE;
1984 --rkuttiya added for bug #6795295
1985 l_start_date DATE;
1986
1987 TYPE ib_cle_id_tbl is table of okc_k_lines_b.id%TYPE INDEX BY BINARY_INTEGER;
1988 l_ib_cle_id_tbl ib_cle_id_tbl;
1989
1990 TYPE inst_cle_id_tbl is table of okc_k_lines_b.cle_id%TYPE INDEX BY BINARY_INTEGER;
1991 l_inst_cle_id_tbl inst_cle_id_tbl;
1992
1993 TYPE ib_inst_rec_type IS RECORD (
1994 ib_cle_id OKC_K_LINES_B.id%TYPE ,
1995 inst_cle_id OKC_K_LINES_B.cle_id %TYPE);
1996
1997 TYPE ib_inst_tbl_type IS TABLE OF ib_inst_rec_type INDEX BY BINARY_INTEGER;
1998 l_ib_inst_tbl ib_inst_tbl_type;
1999 l_counter NUMBER;
2000 l_loop_index NUMBER;
2001
2002
2003 Begin
2004 --call start activity to set savepoint
2005 l_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
2006 p_init_msg_list,
2007 '_PVT',
2008 x_return_status);
2009 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2010 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2011 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2012 RAISE OKL_API.G_EXCEPTION_ERROR;
2013 END IF;
2014 Open chk_subclass_csr(p_chrv_id);
2015 Fetch chk_subclass_csr into
2016 l_scs_code,
2017 l_sts_code,
2018 l_inv_org_id,
2019 l_start_date;
2020 If chk_subclass_csr%NOTFOUND Then
2021 --dbms_output.put_line('Contract Not Found ....!');
2022 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2023 p_msg_name => G_CONTRACT_NOT_FOUND,
2024 p_token1 => G_CONTRACT_ID_TOKEN,
2025 p_token1_value => to_char(p_chrv_id)
2026 );
2027 RAISE OKL_API.G_EXCEPTION_ERROR;
2028 --Handle error appropriately
2029 ElsIf upper(l_sts_code) <> G_APPROVED_STS_CODE Then
2030 --dbms_output.put_line('Contract has not been approved...!');
2031 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2032 p_msg_name => G_CONTRACT_NOT_APPROVED
2033 );
2034 RAISE OKL_API.G_EXCEPTION_ERROR;
2035 --raise appropriate exception
2036 ElsIf l_scs_code <> G_LEASE_SCS_CODE and upper(l_sts_code) = G_APPROVED_STS_CODE Then
2037 --dbms_output.put_line('Contract is not a lease contract...');
2038 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2039 p_msg_name => G_CONTRACT_NOT_LEASE
2040 );
2041 RAISE OKL_API.G_EXCEPTION_ERROR;
2042 ElsIf l_scs_code = G_LEASE_SCS_CODE and upper(l_sts_code) = G_APPROVED_STS_CODE Then
2043 --get inventory master org id
2044 l_inv_mstr_org_id := null;
2045 OPEN mstr_org_csr (p_chr_id => p_chrv_id);
2046 FETCH mstr_org_csr into
2047 l_inv_mstr_org_id;
2048 IF mstr_org_csr%NOTFOUND Then
2049 --raise error for unable to find inv mstr org
2050 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2051 p_msg_name => G_INV_MSTR_ORG_NOT_FOUND,
2052 p_token1 => G_CONTRACT_ID_TOKEN,
2053 p_token1_value => to_char(p_chrv_id)
2054 );
2055 RAISE OKL_API.G_EXCEPTION_ERROR;
2056 --l_inv_mstr_org_id := 204;
2057 ELSE
2058 Null;
2059 END IF;
2060 CLOSE mstr_org_csr;
2061 --get party accoutnt id
2062 l_party_account := null;
2063 OPEN party_account_csr(p_chrv_id => p_chrv_id);
2064 FETCH party_account_csr into
2065 l_party_account;
2066 IF party_account_csr%NOTFOUND Then
2067 --raise error for unable to find inv mstr org
2068 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2069 p_msg_name => G_CUST_ACCOUNT_FOUND,
2070 p_token1 => G_CONTRACT_ID_TOKEN,
2071 p_token1_value => to_char(p_chrv_id)
2072 );
2073 RAISE OKL_API.G_EXCEPTION_ERROR;
2074 ELSE
2075 NULL;
2076 END IF;
2077 CLOSE party_account_csr;
2078 --initialize party tbl
2079 get_party_rec(p_api_version => p_api_version,
2080 p_init_msg_list => p_init_msg_list,
2081 x_return_status => x_return_status,
2082 x_msg_count => x_msg_count,
2083 x_msg_data => x_msg_data,
2084 p_chrv_id => p_chrv_id,
2085 x_party_tbl => l_party_tbl);
2086
2087 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2088 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2089 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2090 RAISE OKL_API.G_EXCEPTION_ERROR;
2091 END IF;
2092
2093 l_counter := 1;
2094 l_ib_line_count := 1;
2095
2096 -- get the transaction records
2097 Open ib_line_csr(p_chrv_id => p_chrv_id);
2098 Loop
2099 l_ib_cle_id_tbl.delete;
2100 l_inst_cle_id_tbl.delete;
2101
2102 Fetch ib_line_csr BULK COLLECT
2103 into l_ib_cle_id_tbl, l_inst_cle_id_tbl
2104 LIMIT G_BULK_BATCH_SIZE;
2105
2106 if (l_ib_cle_id_tbl.COUNT > 0) then
2107 for i in l_ib_cle_id_tbl.FIRST .. l_ib_cle_id_tbl.LAST LOOP
2108 l_ib_inst_tbl(l_counter).ib_cle_id := l_ib_cle_id_tbl(i);
2109 l_ib_inst_tbl(l_counter).inst_cle_id := l_inst_cle_id_tbl(i);
2110 l_counter := l_counter + 1;
2111 End Loop;
2112 end if;
2113 Exit When ib_line_csr%NotFound;
2114 End Loop;
2115 CLOSE ib_line_csr;
2116
2117 IF (l_ib_inst_tbl.COUNT > 0) THEN
2118
2119 l_loop_index := l_ib_inst_tbl.FIRST;
2120 LOOP
2121 l_ib_cle_id := l_ib_inst_tbl(l_loop_index).ib_cle_id;
2122 l_inst_cle_id := l_ib_inst_tbl(l_loop_index).inst_cle_id;
2123
2124
2125 --Bug Fix# 2781900 : This processing will have to be done for each IB line
2126 --If l_ib_line_count = 1 Then
2127 --do contract level one time processing
2128 Process_IB_Line(p_api_version => p_api_version,
2129 p_init_msg_list => p_init_msg_list,
2130 x_return_status => x_return_status,
2131 x_msg_count => x_msg_count,
2132 x_msg_data => x_msg_data,
2133 p_chrv_id => p_chrv_id,
2134 p_start_date => l_start_date,
2135 p_inst_cle_id => l_inst_cle_id,
2136 p_ib_cle_id => l_ib_cle_id,
2137 p_party_tbl => l_party_tbl,
2138 p_party_account => l_party_account,
2139 p_inv_mstr_org_id => l_inv_mstr_org_id,
2140 p_model_line_qty => l_model_line_qty,
2141 --bug#2845959
2142 p_uom_code => l_primary_uom_code,
2143 p_trx_type => l_trx_type,
2144 --bug#3222804
2145 p_inv_org_id => l_inv_org_id,
2146 x_cimv_rec => l_cimv_rec);
2147
2148 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
2149 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2150 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2151 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2152 RAISE OKL_API.G_EXCEPTION_ERROR;
2153 END IF;
2154 l_cimv_tbl(l_ib_line_count) := l_cimv_rec;
2155 l_ib_line_count := l_ib_line_count+1;
2156
2157 EXIT WHEN l_loop_index = l_ib_inst_tbl.LAST;
2158 l_loop_index := l_ib_inst_tbl.NEXT(l_loop_index);
2159
2160 End Loop; -- ib line csr
2161 End IF;
2162
2163 End If;
2164 Close chk_subclass_csr;
2165 x_cimv_tbl := l_cimv_tbl;
2166 --Call end Activity
2167 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2168 EXCEPTION
2169 When OKL_API.G_EXCEPTION_ERROR Then
2170 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2171 (
2172 l_api_name,
2173 G_PKG_NAME,
2174 'OKL_API.G_RET_STS_ERROR',
2175 x_msg_count,
2176 x_msg_data,
2177 '_PVT'
2178 );
2179 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2180 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2181 (
2182 l_api_name,
2183 G_PKG_NAME,
2184 'OKL_API.G_RET_STS_UNEXP_ERROR',
2185 x_msg_count,
2186 x_msg_data,
2187 '_PVT'
2188 );
2189 WHEN OTHERS THEN
2190 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2191 (
2192 l_api_name,
2193 G_PKG_NAME,
2194 'OTHERS',
2195 x_msg_count,
2196 x_msg_data,
2197 '_PVT'
2198 );
2199 END ACTIVATE_IB_INSTANCE;
2200 ------------------------------------------------------------------------------
2201 --Start of comments
2202 --
2203 --API Name : ACTIVATE_RBK_IB_INST
2204 --Purpose : Calls IB API to create an item instance in IB
2205 -- Selects ib instance to create given a top line
2206 -- for a new line created during rebook
2207 --Modification History :
2208 --01-May-2002 avsingh Created
2209 --Notes : Assigns values to transaction_type_id and source_line_ref_id
2210 --End of Comments
2211 ------------------------------------------------------------------------------
2212 Procedure ACTIVATE_RBK_IB_INST(p_api_version IN NUMBER,
2213 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2214 x_return_status OUT NOCOPY VARCHAR2,
2215 x_msg_count OUT NOCOPY NUMBER,
2216 x_msg_data OUT NOCOPY VARCHAR2,
2217 p_fin_ast_cle_id IN NUMBER,
2218 x_cimv_tbl OUT NOCOPY cimv_tbl_type) is
2219
2220 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2221 l_api_name CONSTANT VARCHAR2(30) := 'ACTIVATE_RBK_IB_INST';
2222 l_api_version CONSTANT NUMBER := 1.0;
2223 l_trx_type VARCHAR2(30) := G_TRX_LINE_TYPE_REBOOK;
2224
2225 --cursor to fetch active IB lines under the given top line
2226 Cursor ib_line_csr(p_fin_ast_cle_id IN Number, p_lty_code IN VARCHAR2) is
2227 SELECT cle.id,
2228 cle.cle_id,
2229 cle.dnz_chr_id
2230 from okc_k_lines_b cle,
2231 okc_line_styles_b lse,
2232 okc_k_lines_b inst_cle,
2233 okc_line_styles_b inst_cle_lse
2234 where lse.id = cle.lse_id
2235 and lse.lty_code = p_lty_code
2236 and cle.cle_id = inst_cle.id
2237 and cle.dnz_chr_id = inst_cle.dnz_chr_id
2238 and inst_cle_lse.id = inst_cle.lse_id
2239 and inst_cle_lse.lty_code = 'FREE_FORM2'
2240 and inst_cle.cle_id = p_fin_ast_cle_id
2241 AND not exists (select '1'
2242 from OKC_STATUSES_B sts
2243 Where sts.code = cle.sts_code
2244 --Bug#2522268
2245 --And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED'))
2246 And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED'))
2247 AND not exists (select '1'
2248 from OKC_STATUSES_B sts2
2249 Where sts2.code = inst_cle.sts_code
2250 --Bug#2522268
2251 --And sts2.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED'));
2252 And sts2.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED'));
2253
2254 l_ib_cle_id OKC_K_LINES_B.ID%TYPE;
2255 l_inst_cle_id OKC_K_LINES_B.ID%TYPE;
2256 l_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE;
2257
2258 l_ib_line_lty_code VARCHAR2(200) := G_IB_LINE_LTY_CODE;
2259 l_ib_line_count NUMBER default 0;
2260 l_cimv_tbl cimv_tbl_type;
2261
2262 l_party_tbl party_tbl_type;
2263 l_party_account NUMBER;
2264 l_inv_mstr_org_id NUMBER;
2265 l_cimv_rec cimv_rec_type;
2266 l_model_line_qty NUMBER;
2267 --bug# 2845959
2268 l_primary_uom_code mtl_system_items.primary_uom_code%TYPE;
2269 --bug# 3222804
2270 l_inv_org_id NUMBER;
2271
2272
2273 Begin
2274 --call start activity to set savepoint
2275 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
2276 p_init_msg_list,
2277 '_PVT',
2278 x_return_status);
2279 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2280 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2281 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2282 RAISE OKL_API.G_EXCEPTION_ERROR;
2283 END IF;
2284
2285 -- Open cursor to get IB lines
2286 Open ib_line_csr(p_fin_ast_cle_id => p_fin_ast_cle_id , p_lty_code => l_ib_line_lty_code);
2287 Loop
2288 Fetch ib_line_csr into l_ib_cle_id, l_inst_cle_id, l_chr_id;
2289 Exit when ib_line_csr%NOTFOUND;
2290 l_ib_line_count := ib_line_csr%RowCount;
2291 --Fixed as part of Bug# 3222804
2292 --If l_ib_line_count = 1 Then
2293 --do contract level one time processing
2294 Process_IB_Line_1(p_api_version => p_api_version,
2295 p_init_msg_list => p_init_msg_list,
2296 x_return_status => x_return_status,
2297 x_msg_count => x_msg_count,
2298 x_msg_data => x_msg_data,
2299 p_chrv_id => l_chr_id,
2300 p_inst_cle_id => l_inst_cle_id,
2301 p_ib_cle_id => l_ib_cle_id,
2302 x_party_tbl => l_party_tbl,
2303 x_party_account => l_party_account,
2304 x_inv_mstr_org_id => l_inv_mstr_org_id,
2305 x_model_line_qty => l_model_line_qty,
2306 --bug#2845959
2307 x_primary_uom_code => l_primary_uom_code,
2308 --bug# 3222804
2309 x_inv_org_id => l_inv_org_id);
2310
2311 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
2312 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2313 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2314 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2315 RAISE OKL_API.G_EXCEPTION_ERROR;
2316 END IF;
2317 --End If;
2318 --process for each ib line (this calls ib create item instance api)
2319 Process_IB_Line_2(p_api_version => p_api_version,
2320 p_init_msg_list => p_init_msg_list,
2321 x_return_status => x_return_status,
2322 x_msg_count => x_msg_count,
2323 x_msg_data => x_msg_data,
2324 p_chrv_id => l_chr_id,
2325 p_inst_cle_id => l_inst_cle_id,
2326 p_ib_cle_id => l_ib_cle_id,
2327 p_party_tbl => l_party_tbl,
2328 p_party_account => l_party_account,
2329 p_inv_mstr_org_id => l_inv_mstr_org_id,
2330 p_model_line_qty => l_model_line_qty,
2331 --bug#2845959
2332 p_uom_code => l_primary_uom_code,
2333 p_trx_type => l_trx_type,
2334 --bug#3222804
2335 p_inv_org_id => l_inv_org_id,
2336 x_cimv_rec => l_cimv_rec);
2337 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
2338 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2339 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2340 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2341 RAISE OKL_API.G_EXCEPTION_ERROR;
2342 END IF;
2343 l_cimv_tbl(l_ib_line_count) := l_cimv_rec;
2344 End Loop;
2345 Close ib_line_csr;
2346 x_cimv_tbl := l_cimv_tbl;
2347 --Call end Activity
2348 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2349 EXCEPTION
2350 When OKL_API.G_EXCEPTION_ERROR Then
2351 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2352 (
2353 l_api_name,
2354 G_PKG_NAME,
2355 'OKL_API.G_RET_STS_ERROR',
2356 x_msg_count,
2357 x_msg_data,
2358 '_PVT'
2359 );
2360 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2361 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2362 (
2363 l_api_name,
2364 G_PKG_NAME,
2365 'OKL_API.G_RET_STS_UNEXP_ERROR',
2366 x_msg_count,
2367 x_msg_data,
2368 '_PVT'
2369 );
2370 WHEN OTHERS THEN
2371 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2372 (
2373 l_api_name,
2374 G_PKG_NAME,
2375 'OTHERS',
2376 x_msg_count,
2377 x_msg_data,
2378 '_PVT'
2379 );
2380 END ACTIVATE_RBK_IB_INST;
2381 --Bug# 3533936 :
2382 --------------------------------------------------------------------------------
2383 --Start of Comments
2384 --Procedure Name : RELEASE_IB_INSTANCE (Activate code branch for release)
2385 --Description : Will be called from activate contract and make re-lease updates
2386 -- in IB
2387 --History :
2388 -- 29-Mar-2004 ashish.singh Created
2389 -- Notes :
2390 -- IN Parameters -
2391 -- p_rel_chr_id - contract id of released contract
2392 --
2393 --End of Comments
2394 --------------------------------------------------------------------------------
2395 PROCEDURE RELEASE_IB_INSTANCE
2396 (p_api_version IN NUMBER,
2397 p_init_msg_list IN VARCHAR2,
2398 x_return_status OUT NOCOPY VARCHAR2,
2399 x_msg_count OUT NOCOPY NUMBER,
2400 x_msg_data OUT NOCOPY VARCHAR2,
2401 p_rel_chr_id IN NUMBER
2402 ) IS
2403
2404 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
2405 l_api_name CONSTANT varchar2(30) := 'RELEASE_IB_INSTANCE';
2406 l_api_version CONSTANT NUMBER := 1.0;
2407
2408 ------------------------------------------------
2409 --Cursor to get the k header info
2410 ------------------------------------------------
2411 CURSOR l_hdr_csr (p_rel_chr_id IN NUMBER) IS
2412 SELECT khr.deal_type,
2413 chr.id,
2414 chr.sts_code,
2415 chr.orig_system_id1,
2416 khr.pdt_id,
2417 chr.start_date,
2418 cplb.object1_id1,
2419 chr.cust_acct_id,
2420 chr.scs_code
2421 FROM OKC_K_PARTY_ROLES_B cplb,
2422 OKC_RULES_B rul,
2423 OKL_K_HEADERS khr,
2424 OKC_K_HEADERS_B chr
2425 WHERE cplb.chr_id = chr.id
2426 AND cplb.dnz_chr_id = chr.id
2427 AND cplb.rle_code = 'LESSEE'
2428 AND rul.dnz_chr_id = chr.id
2429 AND rul.rule_information_category = 'LARLES'
2430 AND rul.dnz_chr_id = khr.id --added as part of performance tuning by dkagrawa
2431 AND khr.id = chr.id
2432 AND chr.id = p_rel_chr_id
2433 AND nvl(rul.Rule_information1,'N') = 'Y';
2434
2435 l_hdr_rec l_hdr_csr%ROWTYPE;
2436
2437 ---------------------------------------------------------------------------
2438 --Cursor to find out the instance id and its location on the re-lease asset
2439 --Contract
2440 --------------------------------------------------------------------------
2441 Cursor l_ib_line_csr (p_rel_chr_id in Number) is
2442 Select cim_ib.object1_id1,
2443 iti.object_id1_new,
2444 trx.id tas_id,
2445 cleb_ib.id cleb_ib_id
2446 From okc_k_items cim_ib,
2447 okc_k_lines_b cleb_ib,
2448 okc_line_styles_b lseb_ib,
2449 okc_statuses_b stsb,
2450 okl_txl_itm_insts iti,
2451 okl_trx_assets trx,
2452 okl_trx_types_tl ttyt
2453 where iti.kle_id = cleb_ib.id
2454 and iti.tas_id = trx.id
2455 and trx.tas_type = 'CRL'
2456 and trx.tsu_code = 'ENTERED'
2457 and trx.try_id = ttyt.id
2458 and ttyt.language = userenv('LANG')
2459 and ttyt.name = 'Internal Asset Creation'
2460 and cim_ib.cle_id = cleb_ib.id
2461 and cim_ib.dnz_chr_id = cleb_ib.dnz_chr_id
2462 and cleb_ib.dnz_chr_id = p_rel_chr_id
2463 and lseb_ib.id = cleb_ib.lse_id
2464 and lseb_ib.lty_code = 'INST_ITEM'
2465 and cleb_ib.sts_code = stsb.code
2466 and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
2467
2468 l_ib_line_rec l_ib_line_csr%ROWTYPE;
2469
2470
2471 ----------------------------------------------------------------------------
2472 --Cursor to get owner party and account and location info from install base
2473 ---------------------------------------------------------------------------
2474 Cursor l_csi_csr(p_instance_id in number) is
2475 Select *
2476 from csi_item_instances
2477 where instance_id = p_instance_id;
2478
2479 l_csi_rec l_csi_csr%ROWTYPE;
2480
2481 --------------------------------------------------------------------------
2482 --Cursor to get party site id
2483 -------------------------------------------------------------------------
2484 Cursor l_party_site_csr (p_site_use_id in number) is
2485 Select hps.location_id,
2486 hpsu.party_site_id
2487 from hz_party_sites hps,
2488 hz_party_site_uses hpsu
2489 where hps.party_site_id = hpsu.party_site_id
2490 and hpsu.party_site_use_id = p_site_use_id;
2491
2492 l_party_site_rec l_party_site_csr%ROWTYPE;
2493
2494 --cursor to get owner party rec
2495 cursor l_csi_owner_csr(p_instance_id in number) is
2496 select *
2497 from csi_i_parties
2498 where instance_id = p_instance_id
2499 and relationship_type_code = 'OWNER'
2500 and active_end_date is null;
2501
2502 l_csi_owner_rec l_csi_owner_csr%ROWTYPE;
2503
2504
2505 l_instance_rec csi_datastructures_pub.instance_rec;
2506 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2507 l_party_tbl csi_datastructures_pub.party_tbl;
2508 l_party_account_tbl csi_datastructures_pub.party_account_tbl;
2509 l_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
2510 l_organization_units_tbl csi_datastructures_pub.organization_units_tbl;
2511 l_instance_Asset_tbl csi_datastructures_pub.instance_asset_tbl;
2512 l_transaction_rec csi_datastructures_pub.transaction_rec;
2513 l_id_tbl csi_datastructures_pub.id_tbl;
2514
2515 l_update_required varchar2(1) default 'N';
2516 l_count NUMBER default 0;
2517
2518 --instance query recs
2519 l_instance_query_rec CSI_DATASTRUCTURES_PUB.instance_query_rec;
2520 l_party_query_rec CSI_DATASTRUCTURES_PUB.party_query_rec;
2521 l_account_query_rec CSI_DATASTRUCTURES_PUB.party_account_query_rec;
2522 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.instance_header_tbl;
2523 l_instance_header_rec CSI_DATASTRUCTURES_PUB.instance_header_rec;
2524
2525 begin
2526
2527 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2528 --call start activity to set savepoint
2529 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
2530 p_init_msg_list,
2531 '_PVT',
2532 x_return_status);
2533 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2534 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2535 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2536 RAISE OKL_API.G_EXCEPTION_ERROR;
2537 END IF;
2538
2539 --1.0 Get the release asset customer and account
2540 Open l_hdr_csr(p_rel_chr_id => p_rel_chr_id);
2541 Fetch l_hdr_csr into l_hdr_rec;
2542 If l_hdr_csr%NOTFOUND then
2543 --error : contract header data not found
2544 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2545 p_msg_name => G_CONTRACT_NOT_FOUND,
2546 p_token1 => G_CONTRACT_ID_TOKEN,
2547 p_token1_value => to_char(p_rel_chr_id)
2548 );
2549 RAISE OKL_API.G_EXCEPTION_ERROR;
2550 End If;
2551 Close l_hdr_csr;
2552
2553 --1.1 check if the contract status is 'APPROVED' and that it is a LEASE contract
2554 If upper(l_hdr_rec.sts_code) <> G_APPROVED_STS_CODE Then
2555 --error : contract is not APPROVED
2556 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2557 p_msg_name => G_CONTRACT_NOT_APPROVED
2558 );
2559 RAISE OKL_API.G_EXCEPTION_ERROR;
2560 ElsIf l_hdr_rec.scs_code <> G_LEASE_SCS_CODE and upper(l_hdr_rec.sts_code) = G_APPROVED_STS_CODE Then
2561 --error : Contract is not lease
2562 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2563 p_msg_name => G_CONTRACT_NOT_LEASE
2564 );
2565 RAISE OKL_API.G_EXCEPTION_ERROR;
2566 End If;
2567
2568 --2.0 Get the ib instance id and install at location party site use id
2569 Open l_ib_line_csr(p_rel_chr_id => p_rel_chr_id);
2570 Loop
2571 Fetch l_ib_line_csr into l_ib_line_rec;
2572 Exit when l_ib_line_csr%NOTFOUND;
2573
2574 --3.0 Get original values from install base
2575 l_instance_query_rec.instance_id := to_number(l_ib_line_rec.object1_id1);
2576
2577 csi_item_instance_pub.get_item_instances (
2578 p_api_version => p_api_version,
2579 p_commit => FND_API.G_FALSE,
2580 p_init_msg_list => FND_API.G_FALSE,
2581 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2582 p_instance_query_rec => l_instance_query_rec,
2583 p_party_query_rec => l_party_query_rec,
2584 p_account_query_rec => l_account_query_rec,
2585 p_transaction_id => NULL,
2586 p_resolve_id_columns => FND_API.G_FALSE,
2587 p_active_instance_only => FND_API.G_TRUE,
2588 x_instance_header_tbl => l_instance_header_tbl,
2589 x_return_status => x_return_status,
2590 x_msg_count => x_msg_count,
2591 x_msg_data => x_msg_data);
2592
2593
2594 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2595 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2596 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2597 RAISE OKL_API.G_EXCEPTION_ERROR;
2598 END IF;
2599
2600
2601 If l_instance_header_tbl.COUNT > 0 then
2602 l_instance_header_rec := l_instance_header_tbl(1);
2603
2604
2605 --4.0 Get party_site_id
2606 Open l_party_site_csr (p_site_use_id => to_number(l_ib_line_rec.object_id1_new));
2607 Fetch l_party_site_csr into l_party_site_rec;
2608 If l_party_site_csr%NOTFOUND then
2609 --error : instance location id not found
2610 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2611 p_msg_name => G_INSTALL_LOC_NOT_FOUND,
2612 p_token1 => G_INST_SITE_USE_TOKEN,
2613 p_token1_value => l_ib_line_rec.object_id1_new
2614 );
2615 RAISE OKL_API.G_EXCEPTION_ERROR;
2616 End If;
2617 close l_party_site_csr;
2618
2619 --5.0 Decide if it is required to call instance update API
2620 l_update_required := 'N';
2621
2622 Open l_csi_csr(p_instance_id => l_instance_header_rec.instance_id);
2623 Fetch l_csi_csr into l_csi_rec;
2624 If l_csi_csr%NOTFOUND then
2625 null; --should not happen as we have already queried the instance
2626 End If;
2627 CLose l_csi_csr;
2628
2629 --check for party and account changes
2630 If nvl(l_csi_rec.owner_party_source_table,OKL_API.G_MISS_CHAR) = 'HZ_PARTIES' then
2631 If to_number(l_hdr_rec.object1_id1) <> l_csi_rec.owner_party_id then
2632 l_update_required := 'Y';
2633 l_count := l_party_tbl.COUNT;
2634 Open l_csi_owner_csr(p_instance_id => l_instance_header_rec.instance_id);
2635 Fetch l_csi_owner_csr into l_csi_owner_rec;
2636 If l_csi_owner_csr%NOTFOUND then
2637 null;
2638 Else
2639 l_count := l_count + 1;
2640 l_party_tbl(l_count).instance_party_id := l_csi_owner_rec.instance_party_id;
2641 l_party_tbl(l_count).object_version_number := l_csi_owner_rec.object_version_number;
2642 l_party_tbl(l_count).relationship_type_code := 'OWNER';
2643 l_party_tbl(l_count).party_id := to_number(l_hdr_rec.object1_id1);
2644 End If;
2645 Close l_csi_owner_csr;
2646 End If;
2647 If l_hdr_rec.cust_acct_id <> l_csi_rec.owner_party_account_id then
2648 l_update_required := 'Y';
2649 If l_party_tbl.COUNT <> 0 then
2650 l_party_account_tbl(1).instance_party_id := l_csi_owner_rec.instance_party_id;
2651 l_party_account_tbl(1).party_account_id := l_hdr_rec.cust_acct_id;
2652 l_party_account_tbl(1).relationship_type_code := G_PARTY_RELATIONSHIP;
2653 --l_party_account_tbl(1).parent_tbl_index := l_count;
2654 End If;
2655
2656 End If;
2657 End If;
2658
2659 --check for install location changes
2660 If nvl(l_instance_header_rec.location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_LOCATIONS' then
2661 If l_party_site_rec.location_id <> l_instance_header_rec.location_id then
2662 l_update_required := 'Y';
2663 l_instance_rec.location_id := l_party_site_rec.location_id;
2664 End If;
2665 Elsif nvl(l_instance_header_rec.location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_PARTY_SITES' then
2666 if l_party_site_rec.party_site_id <> l_instance_header_rec.location_id then
2667 l_update_required := 'Y';
2668 l_instance_rec.location_id := l_party_site_rec.party_site_id;
2669 end If;
2670 End If;
2671
2672 If nvl(l_instance_header_rec.install_location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_LOCATIONS' then
2673 If l_party_site_rec.location_id <> l_instance_header_rec.install_location_id then
2674 l_update_required := 'Y';
2675 l_instance_rec.install_location_id := l_party_site_rec.location_id;
2676 End If;
2677 Elsif nvl(l_instance_header_rec.install_location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_PARTY_SITES' then
2678 if l_party_site_rec.party_site_id <> l_instance_header_rec.install_location_id then
2679 l_update_required := 'Y';
2680 l_instance_rec.install_location_id := l_party_site_rec.party_site_id;
2681 end If;
2682 End If;
2683
2684
2685 --6.0 call ib api if required
2686 If l_update_required = 'Y' then
2687
2688 okl_context.set_okc_org_context(p_chr_id => p_rel_chr_id);
2689 --Call get_trx_rec
2690 get_trx_rec(p_api_version => p_api_version,
2691 p_init_msg_list => p_init_msg_list,
2692 x_return_status => x_return_status,
2693 x_msg_count => x_msg_count,
2694 x_msg_data => x_msg_data,
2695 p_cle_id => l_ib_line_rec.cleb_ib_id,
2696 p_transaction_type => G_IB_BKNG_TXN_TYPE,
2697 x_trx_rec => l_transaction_rec);
2698
2699 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2700 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2701 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2702 RAISE OKL_API.G_EXCEPTION_ERROR;
2703 END IF;
2704
2705 l_instance_rec.instance_id := l_instance_header_rec.instance_id;
2706 l_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
2707 csi_item_instance_pub.update_item_instance
2708 (
2709 p_api_version => p_api_version
2710 ,p_commit => fnd_api.g_false
2711 ,p_init_msg_list => p_init_msg_list
2712 ,p_validation_level => fnd_api.g_valid_level_full
2713 ,p_instance_rec => l_instance_rec
2714 ,p_ext_attrib_values_tbl => l_extend_attrib_values_tbl
2715 ,p_party_tbl => l_party_tbl
2716 ,p_account_tbl => l_party_account_tbl
2717 ,p_pricing_attrib_tbl => l_pricing_attribs_tbl
2718 ,p_org_assignments_tbl => l_organization_units_tbl
2719 ,p_asset_assignment_tbl => l_instance_Asset_tbl
2720 ,p_txn_rec => l_transaction_rec
2721 ,x_instance_id_lst => l_id_tbl
2722 ,x_return_status => x_return_status
2723 ,x_msg_count => x_msg_count
2724 ,x_msg_data => x_msg_data
2725 );
2726
2727 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2728 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2729 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2730 RAISE OKL_API.G_EXCEPTION_ERROR;
2731 END IF;
2732
2733 End If;
2734
2735 l_extend_attrib_values_tbl.delete;
2736 l_party_tbl.delete;
2737 l_party_account_tbl.delete;
2738 l_pricing_attribs_tbl.delete;
2739 l_organization_units_tbl.delete;
2740 l_instance_Asset_tbl.delete;
2741 l_instance_header_tbl.delete;
2742 initialize_txn_rec(l_transaction_rec);
2743 Initialize_instance_rec(l_instance_rec);
2744
2745 --7.0 update the transaction status to processed
2746 update_trx_status(p_api_version => p_api_version,
2747 p_init_msg_list => p_init_msg_list,
2748 x_return_status => x_return_status,
2749 x_msg_count => x_msg_count,
2750 x_msg_data => x_msg_data,
2751 p_tas_id => l_ib_line_rec.tas_id,
2752 p_tsu_code => G_TSU_CODE_PROCESSED);
2753
2754 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2755 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2756 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2757 RAISE OKL_API.G_EXCEPTION_ERROR;
2758 END IF;
2759
2760 End If;
2761
2762 End Loop;
2763 close l_ib_line_csr;
2764
2765 --Call end Activity
2766 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2767 EXCEPTION
2768 When OKL_API.G_EXCEPTION_ERROR Then
2769 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2770 (
2771 l_api_name,
2772 G_PKG_NAME,
2773 'OKL_API.G_RET_STS_ERROR',
2774 x_msg_count,
2775 x_msg_data,
2776 '_PVT'
2777 );
2778 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2779 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2780 (
2781 l_api_name,
2782 G_PKG_NAME,
2783 'OKL_API.G_RET_STS_UNEXP_ERROR',
2784 x_msg_count,
2785 x_msg_data,
2786 '_PVT'
2787 );
2788 WHEN OTHERS THEN
2789 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2790 (
2791 l_api_name,
2792 G_PKG_NAME,
2793 'OTHERS',
2794 x_msg_count,
2795 x_msg_data,
2796 '_PVT'
2797 );
2798 End RELEASE_IB_INSTANCE;
2799
2800
2801 --Bug# 5207066
2802 PROCEDURE Is_Inv_Item_Serialized(p_api_version IN NUMBER,
2803 p_init_msg_list IN VARCHAR2,
2804 x_return_status OUT NOCOPY VARCHAR2,
2805 x_msg_count OUT NOCOPY NUMBER,
2806 x_msg_data OUT NOCOPY VARCHAR2,
2807 p_inv_item_id IN NUMBER,
2808 p_chr_id IN NUMBER,
2809 p_cle_id IN NUMBER,
2810 x_serialized OUT NOCOPY VARCHAR2) IS
2811 l_api_version CONSTANT NUMBER := 1.0;
2812 l_api_name VARCHAR2(30) := 'IS_INV_ITEM_SERIALIZED';
2813
2814 l_serialized VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
2815
2816 --cursor to find serialized
2817 CURSOR srl_ctrl_csr (p_inv_item_id IN NUMBER,
2818 p_chr_id IN NUMBER) IS
2819 SELECT mtl.serial_number_control_code
2820 FROM mtl_system_items mtl,
2821 okc_k_headers_b chrb
2822 WHERE mtl.inventory_item_id = p_inv_item_id
2823 AND mtl.organization_id = chrb.inv_organization_id
2824 --BUG# 3489089
2825 AND chrb.id = p_chr_id;
2826
2827 --cursor2 to find serialized
2828 CURSOR srl_ctrl_csr2 (p_inv_item_id IN NUMBER,
2829 p_cle_id IN NUMBER) IS
2830 SELECT mtl.serial_number_control_code
2831 FROM mtl_system_items mtl,
2832 okc_k_headers_b chrb,
2833 okc_k_lines_b cleb
2834 WHERE mtl.inventory_item_id = p_inv_item_id
2835 AND mtl.organization_id = chrb.inv_organization_id
2836 AND chrb.id = cleb.dnz_chr_id
2837 AND cleb.id = p_cle_id;
2838
2839 l_srl_control_code mtl_system_items.serial_number_control_code%TYPE;
2840
2841 l_exception_halt EXCEPTION;
2842
2843 BEGIN
2844 x_serialized := OKL_API.G_FALSE;
2845 x_return_status := OKL_API.START_ACTIVITY (
2846 l_api_name
2847 ,p_init_msg_list
2848 ,'_PVT'
2849 ,x_return_status);
2850 -- Check if activity started successfully
2851 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2852 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2853 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2854 RAISE OKL_API.G_EXCEPTION_ERROR;
2855 END IF;
2856
2857 l_serialized := OKL_API.G_FALSE;
2858 IF p_chr_id IS NOT NULL OR p_chr_id <> OKL_API.G_MISS_NUM THEN
2859 OPEN srl_ctrl_csr (p_inv_item_id => p_inv_item_id,
2860 p_chr_id => p_chr_id);
2861 FETCH srl_ctrl_csr INTO
2862 l_srl_control_code;
2863 CLOSE srl_ctrl_csr;
2864 ELSIF p_cle_id IS NOT NULL OR p_cle_id <> OKL_API.G_MISS_NUM THEN
2865 OPEN srl_ctrl_csr2 (p_inv_item_id => p_inv_item_id,
2866 p_cle_id => p_cle_id);
2867 FETCH srl_ctrl_csr2 INTO
2868 l_srl_control_code;
2869 CLOSE srl_ctrl_csr2;
2870 ELSE
2871 RAISE l_exception_halt;
2872 END IF;
2873
2874 IF NVL(l_srl_control_code,0) IN (2,5,6) THEN
2875 l_serialized := OKL_API.G_TRUE;
2876 END IF;
2877 x_serialized := l_serialized;
2878 OKL_API.END_ACTIVITY (x_msg_count,
2879 x_msg_data );
2880 EXCEPTION
2881 WHEN l_exception_halt THEN
2882 NULL;
2883 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2884 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2885 l_api_name,
2886 G_PKG_NAME,
2887 'OKL_API.G_RET_STS_ERROR',
2888 x_msg_count,
2889 x_msg_data,
2890 '_PVT');
2891 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2892 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2893 l_api_name,
2894 G_PKG_NAME,
2895 'OKL_API.G_RET_STS_UNEXP_ERROR',
2896 x_msg_count,
2897 x_msg_data,
2898 '_PVT');
2899 WHEN OTHERS THEN
2900 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2901 l_api_name,
2902 G_PKG_NAME,
2903 'OTHERS',
2904 x_msg_count,
2905 x_msg_data,
2906 '_PVT');
2907 END Is_Inv_Item_Serialized;
2908
2909 --Bug# 5207066
2910 PROCEDURE RBK_SRL_NUM_IB_INSTANCE
2911 (p_api_version IN NUMBER,
2912 p_init_msg_list IN VARCHAR2,
2913 x_return_status OUT NOCOPY VARCHAR2,
2914 x_msg_count OUT NOCOPY NUMBER,
2915 x_msg_data OUT NOCOPY VARCHAR2,
2916 p_rbk_fin_ast_cle_id IN NUMBER,
2917 p_rbk_chr_id IN NUMBER
2918 ) IS
2919
2920
2921 --cursor to get ib line
2922 Cursor ib_line_csr(p_chrv_id IN Number,
2923 p_fin_ast_cle_id IN Number) is
2924 SELECT ib_cle.id,
2925 ib_cle.cle_id
2926 from okc_k_lines_b ib_cle,
2927 okc_k_lines_b inst_cle,
2928 okc_statuses_b inst_sts
2929 where ib_cle.lse_id = G_IB_LINE_LTY_ID
2930 and ib_cle.dnz_chr_id = p_chrv_id
2931 AND inst_sts.code = ib_cle.sts_code
2932 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
2933 AND ib_cle.cle_id = inst_cle.id
2934 and inst_cle.dnz_chr_id = p_chrv_id
2935 and inst_cle.cle_id = p_fin_ast_cle_id;
2936
2937 -- cursor to get instance_id from okc_k_items
2938 Cursor get_instance_id_csr(p_cle_id IN Number) is
2939 SELECT cim.object1_id1
2940 from okc_k_items cim
2941 where cim.cle_id = p_cle_id;
2942
2943 -- cursor to get original contract id
2944 cursor get_orig_k_id (p_chr_id in number,
2945 p_fin_ast_cle_id in number) is
2946 select chr.ORIG_SYSTEM_ID1 orig_chr_id,
2947 cle.orig_system_id1 orig_fin_ast_cle_id
2948 from okc_k_headers_b chr,
2949 okc_k_lines_b cle
2950 where chr.id = p_chr_id
2951 and cle.id = p_fin_ast_cle_id
2952 and cle.chr_id = p_chr_id
2953 and cle.dnz_chr_id = p_chr_id;
2954
2955 -- gboomina Bug 5362977 - Start
2956 CURSOR srl_num_to_add_csr(p_orig_fin_ast_cle_id IN NUMBER,
2957 p_rbk_fin_ast_cle_id IN NUMBER,
2958 p_orig_chr_id IN NUMBER,
2959 p_rbk_chr_id IN NUMBER ) IS
2960
2961 SELECT orig_ib_cle.id orig_ib_cle_id,
2962 orig_ib_cle.cle_id orig_inst_cle_id,
2963 orig_ib_cle.orig_system_id1 rbk_ib_cle_id
2964 FROM okc_k_lines_b orig_ib_cle,
2965 okc_k_lines_b orig_inst_cle,
2966 okc_k_lines_b rbk_inst_cle,
2967 okc_statuses_b inst_sts
2968 WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
2969 AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
2970 AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
2971 AND orig_ib_cle.cle_id = orig_inst_cle.id
2972 AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
2973 AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
2974 AND rbk_inst_cle.id = orig_inst_cle.orig_system_id1
2975 AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
2976 AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
2977 AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
2978 AND inst_sts.code = orig_ib_cle.sts_code
2979 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
2980
2981
2982 -- Modified query to only fetch IB lines deleted during rebook
2983 CURSOR srl_num_to_exp_csr(p_orig_fin_ast_cle_id IN NUMBER,
2984 p_rbk_fin_ast_cle_id IN NUMBER,
2985 p_orig_chr_id IN NUMBER,
2986 p_rbk_chr_id IN NUMBER) IS
2987 SELECT orig_ib_cle.id ib_cle_id,
2988 orig_ib_cle.cle_id inst_cle_id,
2989 orig_ib_cim.object1_id1 instance_id
2990 FROM okc_k_items orig_ib_cim,
2991 okc_k_lines_b orig_ib_cle,
2992 okc_k_lines_b orig_inst_cle,
2993 okc_statuses_b inst_sts
2994 WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
2995 AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
2996 AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
2997 AND orig_ib_cle.cle_id = orig_inst_cle.id
2998 AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
2999 AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
3000 AND orig_ib_cim.cle_id = orig_ib_cle.id
3001 AND orig_ib_cim.dnz_chr_id = p_orig_chr_id
3002 AND orig_ib_cim.object1_id1 IS NOT NULL
3003 AND inst_sts.code = orig_ib_cle.sts_code
3004 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
3005 AND NOT EXISTS (
3006 SELECT 1
3007 FROM okc_k_lines_b rbk_inst_cle,
3008 okc_statuses_b rbk_inst_sts
3009 WHERE rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
3010 AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
3011 AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
3012 AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
3013 AND rbk_inst_sts.code = rbk_inst_cle.sts_code
3014 AND rbk_inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED'));
3015
3016 CURSOR srl_num_to_update_csr(p_orig_fin_ast_cle_id IN NUMBER,
3017 p_rbk_fin_ast_cle_id IN NUMBER,
3018 p_orig_chr_id IN NUMBER,
3019 p_rbk_chr_id IN NUMBER ) IS
3020
3021 SELECT orig_ib_cle.id orig_ib_cle_id,
3022 orig_ib_cle.cle_id orig_inst_cle_id,
3023 rbk_ib_cle.id rbk_ib_cle_id,
3024 rbk_ib_cle.cle_id rbk_inst_cle_id,
3025 TO_NUMBER(ib_cim.object1_id1) instance_id,
3026 ib_cim.id orig_ib_cim_id
3027 FROM okc_k_lines_b orig_ib_cle,
3028 okc_k_lines_b orig_inst_cle,
3029 okc_k_lines_b rbk_inst_cle,
3030 okc_k_lines_b rbk_ib_cle,
3031 okc_statuses_b inst_sts,
3032 okc_k_items ib_cim
3033 WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
3034 AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
3035 AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
3036 AND orig_ib_cle.cle_id = orig_inst_cle.id
3037 AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
3038 AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
3039 AND rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
3040 AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
3041 AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
3042 AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
3043 AND rbk_ib_cle.cle_id = rbk_inst_cle.id
3044 AND rbk_ib_cle.dnz_chr_id = p_rbk_chr_id
3045 AND rbk_ib_cle.lse_id = G_IB_LINE_LTY_ID
3046 AND inst_sts.code = orig_ib_cle.sts_code
3047 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
3048 AND ib_cim.cle_id = orig_ib_cle.id
3049 AND ib_cim.dnz_chr_id = p_orig_chr_id;
3050
3051 CURSOR srl_num_old_csr(p_instance_id IN NUMBER) IS
3052 SELECT csi_item.serial_number
3053 FROM csi_item_instances csi_item
3054 WHERE csi_item.instance_id = p_instance_id;
3055
3056 l_srl_num_old_rec srl_num_old_csr%ROWTYPE;
3057
3058 CURSOR srl_num_new_csr(p_rbk_fin_ast_cle_id IN NUMBER,
3059 p_rbk_ib_cle_id IN NUMBER) IS
3060 SELECT serial_number
3061 FROM okl_txl_itm_insts iti,
3062 okl_trx_assets tas
3063 WHERE iti.dnz_cle_id = P_rbk_fin_ast_cle_id
3064 AND iti.kle_id = p_rbk_ib_cle_id
3065 AND iti.tal_type = G_TRX_LINE_TYPE_REBOOK
3066 AND tas.tas_type = G_TRX_LINE_TYPE_REBOOK
3067 AND tas.tsu_code = G_TSU_CODE_ENTERED
3068 AND tas.id = iti.tas_id;
3069
3070 l_srl_num_new_rec srl_num_new_csr%ROWTYPE;
3071
3072 -- gboomina Bug 5362977 - End
3073
3074 -- Get Original Inventory Item
3075 CURSOR orig_instance_dtls_csr (p_fin_ast_cle_id IN NUMBER,
3076 p_chr_id IN NUMBER) is
3077 SELECT csi_item.inventory_item_id
3078 FROM csi_item_instances csi_item,
3079 okc_k_items ib_cim,
3080 okc_k_lines_b ib_cle,
3081 okc_k_lines_b inst_cle,
3082 okc_statuses_b inst_sts
3083 WHERE ib_cim.cle_id = ib_cle.id
3084 AND ib_cim.dnz_chr_id = p_chr_id
3085 AND inst_cle.cle_id = p_fin_ast_cle_id
3086 AND inst_cle.lse_id = G_INST_LINE_LTY_ID
3087 AND inst_cle.dnz_chr_id = p_chr_id
3088 AND ib_cle.cle_id = inst_cle.id
3089 AND ib_cle.lse_id = G_IB_LINE_LTY_ID
3090 AND ib_cle.dnz_chr_id = p_chr_id
3091 AND csi_item.instance_id = TO_NUMBER(ib_cim.object1_id1)
3092 AND inst_sts.code = ib_cle.sts_code
3093 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
3094
3095 --- Get quantity cursor
3096 CURSOR get_qty_csr (p_fin_ast_cle_id IN NUMBER) is
3097 SELECT cim.number_of_items,
3098 cim.object1_id1,
3099 cim.object1_id2
3100 FROM OKC_K_ITEMS CIM,
3101 OKC_K_LINES_B MDL,
3102 OKC_LINE_STYLES_B MDL_LSE
3103 WHERE CIM.CLE_ID = MDL.ID
3104 AND MDL.CLE_ID = p_fin_ast_cle_id
3105 AND MDL.LSE_ID = MDL_LSE.ID
3106 AND MDL_LSE.LTY_CODE = G_MODEL_LINE_LTY_CODE;
3107
3108 -- gboomina Bug 5362977 - End
3109
3110 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
3111 l_api_name CONSTANT varchar2(30) := 'RBK_SRL_NUM_IB_INSTANCE';
3112 l_api_version CONSTANT NUMBER := 1.0;
3113
3114 l_orig_inv_org_id okc_k_items.object1_id1%TYPE;
3115 l_orig_inv_item_id okc_k_items.object1_id2%TYPE;
3116
3117 -- gboomina Bug 5362977 - Start
3118 l_orig_serialized VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
3119 l_rbk_serialized VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
3120 -- gboomina Bug 5362977 - End
3121
3122 l_orig_k_id okc_k_headers_b.id%type;
3123 l_orig_fin_ast_cle_id okc_k_lines_b.id%type;
3124 l_instance_id okc_k_items.id%type;
3125 l_instance_cle_id okc_k_items.cle_id%type;
3126
3127 l_instance_rec CSI_DATASTRUCTURES_PUB.instance_rec;
3128 l_upd_instance_rec CSI_DATASTRUCTURES_PUB.instance_rec;
3129
3130 l_upd_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
3131 l_upd_party_tbl CSI_DATASTRUCTURES_PUB.party_tbl;
3132 l_upd_party_tbl_in CSI_DATASTRUCTURES_PUB.party_tbl;
3133 l_upd_account_tbl CSI_DATASTRUCTURES_PUB.party_account_tbl;
3134 l_upd_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
3135 l_upd_org_assignments_tbl CSI_DATASTRUCTURES_PUB.organization_units_tbl;
3136 l_upd_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
3137 l_upd_txn_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
3138 l_upd_instance_id_lst CSI_DATASTRUCTURES_PUB.id_tbl;
3139 l_account_tbl party_account_tbl_type;
3140
3141 -- gboomina Bug 5362977 - Start
3142 l_rbk_model_line_qty okc_k_items.number_of_items%TYPE;
3143 l_rbk_inv_item_id okc_k_items.object1_id1%TYPE;
3144 l_rbk_inv_org_id okc_k_items.object1_id2%TYPE;
3145 l_instance_query_temp_rec CSI_DATASTRUCTURES_PUB.instance_query_rec;
3146 l_instance_header_temp_rec CSI_DATASTRUCTURES_PUB.instance_header_rec;
3147 l_instance_temp_rec CSI_DATASTRUCTURES_PUB.instance_rec;
3148 l_model_line_qty okc_k_items.number_of_items%TYPE;
3149 l_cim_rec okl_okc_migration_pvt.cimv_rec_type;
3150 x_cim_rec okl_okc_migration_pvt.cimv_rec_type;
3151 -- gboomina Bug 5362977 - End
3152
3153 l_trx_type VARCHAR2(30) := G_TRX_LINE_TYPE_REBOOK;
3154
3155 ib_line_id okc_k_lines_b.id%type;
3156 ib_line_cle_id okc_k_lines_b.cle_id%type;
3157
3158 rbk_ib_line_id okc_k_lines_b.id%type;
3159 rbk_ib_line_cle_id okc_k_lines_b.cle_id%type;
3160
3161 l_party_tbl party_tbl_type;
3162 l_party_account NUMBER;
3163 l_inv_mstr_org_id NUMBER;
3164 l_primary_uom_code MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE;
3165 l_cimv_rec cimv_rec_type;
3166 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
3167 l_klev_rec okl_kle_pvt.klev_rec_type;
3168 x_clev_rec okl_okc_migration_pvt.clev_rec_type;
3169 x_klev_rec okl_kle_pvt.klev_rec_type;
3170
3171 l_transaction_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
3172 l_instance_query_rec CSI_DATASTRUCTURES_PUB.instance_query_rec;
3173 l_party_query_rec CSI_DATASTRUCTURES_PUB.party_query_rec;
3174 l_account_query_rec CSI_DATASTRUCTURES_PUB.party_account_query_rec;
3175 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.instance_header_tbl;
3176 l_instance_header_rec CSI_DATASTRUCTURES_PUB.instance_header_rec;
3177
3178 BEGIN
3179
3180 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3181 --call start activity to set savepoint
3182 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
3183 p_init_msg_list,
3184 '_PVT',
3185 x_return_status);
3186 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3187 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3188 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3189 RAISE OKL_API.G_EXCEPTION_ERROR;
3190 END IF;
3191
3192 -- get original contract_id
3193 Open get_orig_k_id(p_rbk_chr_id,p_rbk_fin_ast_cle_id);
3194 Fetch get_orig_k_id into l_orig_k_id,l_orig_fin_ast_cle_id;
3195 If get_orig_k_id%NOTFOUND then
3196 null; --should not happen
3197 End If;
3198 CLose get_orig_k_id;
3199
3200 --okl_debug_pub.logmessage(' original contract id = ' ||l_orig_k_id );
3201 --dbms_output.put_line(' original contract id = ' ||l_orig_k_id);
3202
3203 --okl_debug_pub.logmessage(' original fin asset line id = ' ||l_orig_fin_ast_cle_id );
3204 --dbms_output.put_line(' original fin asset line id = ' ||l_orig_fin_ast_cle_id);
3205
3206 -- get original k inv item details
3207 -- gboomina Bug 5362977 - Start
3208 l_orig_inv_item_id := 0;
3209 OPEN orig_instance_dtls_csr(p_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3210 p_chr_id => l_orig_k_id);
3211 FETCH orig_instance_dtls_csr INTO l_orig_inv_item_id;
3212 CLOSE orig_instance_dtls_csr;
3213
3214 --okl_debug_pub.logmessage(' orig inventory item id = ' ||l_orig_inv_item_id );
3215 --dbms_output.put_line(' orig inventory item id = ' ||l_orig_inv_item_id);
3216
3217 -- get the number of qty
3218 l_rbk_model_line_qty := 0;
3219 OPEN get_qty_csr (p_fin_ast_cle_id => p_rbk_fin_ast_cle_id ); --l_instance_id
3220 FETCH get_qty_csr into
3221 l_rbk_model_line_qty,
3222 l_rbk_inv_item_id,
3223 l_rbk_inv_org_id;
3224 IF get_qty_csr%NOTFOUND Then
3225 --okl_debug_pub.logmessage(' get_qty_csr not found');
3226 --dbms_output.put_line(' get_qty_csr not found');
3227 l_rbk_model_line_qty := 1;
3228 ELSE
3229 Null;
3230 END IF;
3231 CLOSE get_qty_csr;
3232
3233 --okl_debug_pub.logmessage(' inventory org id = ' ||l_orig_inv_org_id );
3234 --okl_debug_pub.logmessage(' inventory item id = ' ||l_orig_inv_item_id );
3235 --dbms_output.put_line(' inventory org id = ' ||l_orig_inv_org_id);
3236 --dbms_output.put_line(' inventory item id = ' ||l_orig_inv_item_id);
3237
3238 l_orig_serialized := OKL_API.G_FALSE;
3239
3240 Is_Inv_Item_Serialized(p_api_version => p_api_version,
3241 p_init_msg_list => p_init_msg_list,
3242 x_return_status => x_return_status,
3243 x_msg_count => x_msg_count,
3244 x_msg_data => x_msg_data,
3245 p_inv_item_id => l_orig_inv_item_id,
3246 p_chr_id => l_orig_k_id,
3247 p_cle_id => l_orig_fin_ast_cle_id,
3248 x_serialized => l_orig_serialized);
3249 -- gboomina Bug 5362977 - End
3250
3251 --okl_debug_pub.logmessage(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3252 --dbms_output.put_line(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3253 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3254 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3255 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3256 RAISE OKL_API.G_EXCEPTION_ERROR;
3257 END IF;
3258
3259 -- gboomina Bug 5362977 - Start
3260 l_rbk_serialized := OKL_API.G_FALSE;
3261 Is_Inv_Item_Serialized(p_api_version => p_api_version,
3262 p_init_msg_list => p_init_msg_list,
3263 x_return_status => x_return_status,
3264 x_msg_count => x_msg_count,
3265 x_msg_data => x_msg_data,
3266 p_inv_item_id => l_rbk_inv_item_id,
3267 p_chr_id => p_rbk_chr_id,
3268 p_cle_id => p_rbk_fin_ast_cle_id,
3269 x_serialized => l_rbk_serialized);
3270
3271 --okl_debug_pub.logmessage(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3272 --dbms_output.put_line(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3273 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3275 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3276 RAISE OKL_API.G_EXCEPTION_ERROR;
3277 END IF;
3278
3279 -- Expire and Re-create IB instance if Inventory item is changed.
3280 IF (l_orig_inv_item_id <> l_rbk_inv_item_id) THEN
3281
3282 FOR srl_num_to_update_rec IN srl_num_to_update_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3283 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3284 p_orig_chr_id => l_orig_k_id,
3285 p_rbk_chr_id => p_rbk_chr_id) LOOP
3286 --
3287 -- Expire IB instance with old inventory item
3288 --
3289
3290 l_instance_query_rec := l_instance_query_temp_rec;
3291 l_instance_header_rec := l_instance_header_temp_rec;
3292 l_instance_rec := l_instance_temp_rec;
3293
3294 l_instance_query_rec.instance_id := srl_num_to_update_rec.instance_id;
3295 -- gboomina Bug 5362977 - End
3296
3297 csi_item_instance_pub.get_item_instances (
3298 p_api_version => p_api_version,
3299 p_commit => FND_API.G_FALSE,
3300 p_init_msg_list => FND_API.G_FALSE,
3301 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3302 p_instance_query_rec => l_instance_query_rec,
3303 p_party_query_rec => l_party_query_rec,
3304 p_account_query_rec => l_account_query_rec,
3305 p_transaction_id => NULL,
3306 p_resolve_id_columns => FND_API.G_FALSE,
3307 p_active_instance_only => FND_API.G_TRUE,
3308 x_instance_header_tbl => l_instance_header_tbl,
3309 x_return_status => x_return_status,
3310 x_msg_count => x_msg_count,
3311 x_msg_data => x_msg_data);
3312
3313
3314 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3315 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3316 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3317 RAISE OKL_API.G_EXCEPTION_ERROR;
3318 END IF;
3319
3320 If l_instance_header_tbl.COUNT > 0 then
3321 l_instance_header_rec := l_instance_header_tbl(1);
3322 end if;
3323
3324 -- gboomina Bug 5362977 - Start
3325 l_instance_rec.instance_id := l_instance_header_rec.instance_id;
3326 l_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3327
3328 --Call get_trx_rec
3329 get_trx_rec(p_api_version => p_api_version,
3330 p_init_msg_list => p_init_msg_list,
3331 x_return_status => x_return_status,
3332 x_msg_count => x_msg_count,
3333 x_msg_data => x_msg_data,
3334 p_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3335 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3336 x_trx_rec => l_transaction_rec);
3337 -- gboomina Bug 5362977 - End
3338
3339 --okl_debug_pub.logmessage(' get_trx_rec x_return_status= ' || x_return_status);
3340 --dbms_output.put_line(' get_trx_rec x_return_status= ' || x_return_status);
3341
3342 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3343 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3344 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3345 RAISE OKL_API.G_EXCEPTION_ERROR;
3346 END IF;
3347
3348 csi_item_instance_pub.expire_item_instance
3349 (
3350 p_api_version => p_api_version
3351 ,p_commit => fnd_api.g_false
3352 ,p_init_msg_list => p_init_msg_list
3353 ,p_validation_level => fnd_api.g_valid_level_full
3354 ,p_instance_rec => l_instance_rec
3355 ,p_expire_children => fnd_api.g_false
3356 ,p_txn_rec => l_transaction_rec
3357 ,x_instance_id_lst => l_upd_instance_id_lst
3358 ,x_return_status => x_return_status
3359 ,x_msg_count => x_msg_count
3360 ,x_msg_data => x_msg_data);
3361
3362 --okl_debug_pub.logmessage(' csi_item_instance_pub.update_item_instance x_return_status = ' || x_return_status);
3363 --dbms_output.put_line(' csi_item_instance_pub.update_item_instance x_return_status = ' || x_return_status);
3364 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3365 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3366 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3367 RAISE OKL_API.G_EXCEPTION_ERROR;
3368 END IF;
3369
3370 l_cim_rec.id := srl_num_to_update_rec.orig_ib_cim_id;
3371 l_cim_rec.object1_id1 := NULL;
3372 l_cim_rec.object1_id2 := NULL;
3373 l_cim_rec.jtot_object1_code := NULL;
3374
3375 okl_okc_migration_pvt.update_contract_item(
3376 p_api_version => 1.0,
3377 p_init_msg_list => okc_api.g_false,
3378 x_return_status =>x_return_status,
3379 x_msg_count =>x_msg_count,
3380 x_msg_data =>x_msg_data,
3381 p_cimv_rec =>l_cim_rec,
3382 x_cimv_rec =>x_cim_rec);
3383
3384 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3386 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3387 RAISE OKL_API.G_EXCEPTION_ERROR;
3388 END IF;
3389
3390 -- Create IB instance with new inventory item
3391 --
3392
3393 Process_IB_Line_1(p_api_version => p_api_version,
3394 p_init_msg_list => p_init_msg_list,
3395 x_return_status => x_return_status,
3396 x_msg_count => x_msg_count,
3397 x_msg_data => x_msg_data,
3398 p_chrv_id => l_orig_k_id,
3399 p_inst_cle_id => srl_num_to_update_rec.orig_inst_cle_id,
3400 p_ib_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3401 x_party_tbl => l_party_tbl,
3402 x_party_account => l_party_account,
3403 x_inv_mstr_org_id => l_inv_mstr_org_id,
3404 x_model_line_qty => l_model_line_qty,
3405 x_primary_uom_code => l_primary_uom_code,
3406 x_inv_org_id => l_orig_inv_org_id);
3407
3408 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3409 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3410 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3411 RAISE OKL_API.G_EXCEPTION_ERROR;
3412 END IF;
3413
3414 Process_IB_Line_2(p_api_version => p_api_version,
3415 p_init_msg_list => p_init_msg_list,
3416 x_return_status => x_return_status,
3417 x_msg_count => x_msg_count,
3418 x_msg_data => x_msg_data,
3419 p_chrv_id => l_orig_k_id,
3420 p_inst_cle_id => srl_num_to_update_rec.orig_inst_cle_id,
3421 p_ib_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3422 p_party_tbl => l_party_tbl,
3423 p_party_account => l_party_account,
3424 p_inv_mstr_org_id => l_inv_mstr_org_id,
3425 p_model_line_qty => l_model_line_qty,
3426 p_uom_code => l_primary_uom_code,
3427 p_trx_type => 'CRB',
3428 p_inv_org_id => l_orig_inv_org_id,
3429 p_rbk_ib_cle_id => srl_num_to_update_rec.rbk_ib_cle_id,
3430 x_cimv_rec => l_cimv_rec);
3431 --dbms_output.put_line('After Process_Line_2 1'||x_return_status);
3432 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3433 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3434 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3435 RAISE OKL_API.G_EXCEPTION_ERROR;
3436 END IF;
3437
3438 END LOOP;
3439
3440 END IF;
3441
3442 IF (l_rbk_serialized = OKL_API.G_TRUE OR l_orig_serialized = OKL_API.G_TRUE) THEN
3443
3444 --okl_debug_pub.logmessage(' IN serialized section ');
3445 --dbms_output.put_line(' IN serialized section ');
3446
3447 --okl_debug_pub.logmessage(' rebook contract fin line id = ' || p_rbk_fin_ast_cle_id);
3448 --okl_debug_pub.logmessage(' original contract fin line id = ' || l_orig_fin_ast_cle_id);
3449
3450 --dbms_output.put_line(' rebook contract fin line id = ' || p_rbk_fin_ast_cle_id);
3451 --dbms_output.put_line(' original contract fin line id = ' || l_orig_fin_ast_cle_id);
3452
3453 -- to expire the instance items
3454 FOR srl_num_to_exp_rec IN srl_num_to_exp_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3455 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3456 p_orig_chr_id => l_orig_k_id,
3457 p_rbk_chr_id => p_rbk_chr_id) LOOP
3458 --okl_debug_pub.logmessage('Expire IB Instance');
3459 --dbms_output.put_line('Expire IB Instance');
3460
3461 --okl_debug_pub.logmessage('srl_num_to_exp_rec.ib_cle_id = ' || srl_num_to_exp_rec.ib_cle_id);
3462 --dbms_output.put_line('srl_num_to_exp_rec.ib_cle_id = ' || srl_num_to_exp_rec.ib_cle_id);
3463
3464 --okl_debug_pub.logmessage('srl_num_to_exp_rec.inst_cle_id = ' || srl_num_to_exp_rec.inst_cle_id);
3465 --dbms_output.put_line('srl_num_to_exp_rec.inst_cle_id = ' || srl_num_to_exp_rec.inst_cle_id);
3466
3467 l_instance_query_rec := l_instance_query_temp_rec;
3468 l_instance_header_rec := l_instance_header_temp_rec;
3469 l_instance_rec := l_instance_temp_rec;
3470
3471 l_instance_query_rec.instance_id := to_number(srl_num_to_exp_rec.instance_id);
3472
3473 csi_item_instance_pub.get_item_instances (
3474 p_api_version => p_api_version,
3475 p_commit => FND_API.G_FALSE,
3476 p_init_msg_list => FND_API.G_FALSE,
3477 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3478 p_instance_query_rec => l_instance_query_rec,
3479 p_party_query_rec => l_party_query_rec,
3480 p_account_query_rec => l_account_query_rec,
3481 p_transaction_id => NULL,
3482 p_resolve_id_columns => FND_API.G_FALSE,
3483 p_active_instance_only => FND_API.G_TRUE,
3484 x_instance_header_tbl => l_instance_header_tbl,
3485 x_return_status => x_return_status,
3486 x_msg_count => x_msg_count,
3487 x_msg_data => x_msg_data);
3488
3489
3490 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3491 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3492 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3493 RAISE OKL_API.G_EXCEPTION_ERROR;
3494 END IF;
3495
3496 If l_instance_header_tbl.COUNT > 0 then
3497 l_instance_header_rec := l_instance_header_tbl(1);
3498 end if;
3499
3500 l_instance_rec.instance_id := l_instance_header_rec.instance_id;
3501 l_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3502
3503 --Call get_trx_rec
3504 get_trx_rec(p_api_version => p_api_version,
3505 p_init_msg_list => p_init_msg_list,
3506 x_return_status => x_return_status,
3507 x_msg_count => x_msg_count,
3508 x_msg_data => x_msg_data,
3509 p_cle_id => srl_num_to_exp_rec.ib_cle_id,
3510 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3511 x_trx_rec => l_transaction_rec);
3512
3513 --okl_debug_pub.logmessage('get_trx_rec x_return_status = ' || x_return_status);
3514 --dbms_output.put_line('get_trx_rec x_return_status = ' || x_return_status);
3515
3516 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3517 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3518 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3519 RAISE OKL_API.G_EXCEPTION_ERROR;
3520 END IF;
3521
3522 csi_item_instance_pub.expire_item_instance
3523 (
3524 p_api_version => p_api_version
3525 ,p_commit => fnd_api.g_false
3526 ,p_init_msg_list => p_init_msg_list
3527 ,p_validation_level => fnd_api.g_valid_level_full
3528 ,p_instance_rec => l_instance_rec
3529 ,p_expire_children => fnd_api.g_false
3530 ,p_txn_rec => l_transaction_rec
3531 ,x_instance_id_lst => l_upd_instance_id_lst
3532 ,x_return_status => x_return_status
3533 ,x_msg_count => x_msg_count
3534 ,x_msg_data => x_msg_data);
3535
3536 --okl_debug_pub.logmessage('csi_item_instance_pub.expire_item_instance x_return_status= ' || x_return_status);
3537 --dbms_output.put_line('csi_item_instance_pub.expire_item_instance x_return_status= ' || x_return_status);
3538 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3539 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3540 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3541 RAISE OKL_API.G_EXCEPTION_ERROR;
3542 END IF;
3543
3544 --okl_debug_pub.logmessage('Abandon Instance line: '||srl_num_to_exp_rec.inst_cle_id);
3545 --dbms_output.put_line('Abandon Instance line: '||srl_num_to_exp_rec.inst_cle_id);
3546
3547 l_clev_rec.id := srl_num_to_exp_rec.inst_cle_id;
3548 l_klev_rec.id := srl_num_to_exp_rec.inst_cle_id;
3549 l_clev_rec.sts_code := 'ABANDONED';
3550
3551 okl_contract_pub.update_contract_line(
3552 p_api_version => 1.0,
3553 p_init_msg_list => OKL_API.G_FALSE,
3554 x_return_status => x_return_status,
3555 x_msg_count => x_msg_count,
3556 x_msg_data => x_msg_data,
3557 p_clev_rec => l_clev_rec,
3558 p_klev_rec => l_klev_rec,
3559 x_clev_rec => x_clev_rec,
3560 x_klev_rec => x_klev_rec
3561 );
3562
3563 --okl_debug_pub.logmessage('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3564 --dbms_output.put_line('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3565
3566 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3567 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3568 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3569 RAISE OKL_API.G_EXCEPTION_ERROR;
3570 END IF;
3571
3572 --okl_debug_pub.logmessage('Abandon IB line: '||srl_num_to_exp_rec.ib_cle_id);
3573 --dbms_output.put_line('Abandon IB line: '||srl_num_to_exp_rec.ib_cle_id);
3574
3575 l_clev_rec.id := srl_num_to_exp_rec.ib_cle_id;
3576 l_klev_rec.id := srl_num_to_exp_rec.ib_cle_id;
3577 l_clev_rec.sts_code := 'ABANDONED';
3578
3579 okl_contract_pub.update_contract_line(
3580 p_api_version => 1.0,
3581 p_init_msg_list => OKL_API.G_FALSE,
3582 x_return_status => x_return_status,
3583 x_msg_count => x_msg_count,
3584 x_msg_data => x_msg_data,
3585 p_clev_rec => l_clev_rec,
3586 p_klev_rec => l_klev_rec,
3587 x_clev_rec => x_clev_rec,
3588 x_klev_rec => x_klev_rec
3589 );
3590
3591 --okl_debug_pub.logmessage('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3592 --dbms_output.put_line('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3593
3594 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3595 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3596 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3597 RAISE OKL_API.G_EXCEPTION_ERROR;
3598 END IF;
3599
3600 END LOOP;
3601
3602 -- Sync New IB instances added
3603
3604 FOR srl_num_to_add_rec IN srl_num_to_add_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3605 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3606 p_orig_chr_id => l_orig_k_id,
3607 p_rbk_chr_id => p_rbk_chr_id) LOOP
3608
3609 --okl_debug_pub.logmessage('Add IB Instance');
3610 --dbms_output.put_line('Add IB Instance');
3611
3612 --okl_debug_pub.logmessage('srl_num_to_add_rec.orig_ib_cle_id = ' || srl_num_to_add_rec.orig_ib_cle_id);
3613 --dbms_output.put_line('srl_num_to_add_rec.orig_ib_cle_id = ' || srl_num_to_add_rec.orig_ib_cle_id);
3614
3615 --okl_debug_pub.logmessage('srl_num_to_add_rec.orig_inst_cle_id = ' || srl_num_to_add_rec.orig_inst_cle_id);
3616 --dbms_output.put_line('srl_num_to_add_rec.orig_inst_cle_id = ' || srl_num_to_add_rec.orig_inst_cle_id);
3617
3618 Process_IB_Line_1(p_api_version => p_api_version,
3619 p_init_msg_list => p_init_msg_list,
3620 x_return_status => x_return_status,
3621 x_msg_count => x_msg_count,
3622 x_msg_data => x_msg_data,
3623 p_chrv_id => l_orig_k_id,
3624 p_inst_cle_id => srl_num_to_add_rec.orig_inst_cle_id,
3625 p_ib_cle_id => srl_num_to_add_rec.orig_ib_cle_id,
3626 x_party_tbl => l_party_tbl,
3627 x_party_account => l_party_account,
3628 x_inv_mstr_org_id => l_inv_mstr_org_id,
3629 x_model_line_qty => l_model_line_qty,
3630 x_primary_uom_code => l_primary_uom_code,
3631 x_inv_org_id => l_orig_inv_org_id);
3632
3633 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
3634 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3635 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3636 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3637 RAISE OKL_API.G_EXCEPTION_ERROR;
3638 END IF;
3639
3640 Process_IB_Line_2(p_api_version => p_api_version,
3641 p_init_msg_list => p_init_msg_list,
3642 x_return_status => x_return_status,
3643 x_msg_count => x_msg_count,
3644 x_msg_data => x_msg_data,
3645 p_chrv_id => l_orig_k_id,
3646 p_inst_cle_id => srl_num_to_add_rec.orig_inst_cle_id,
3647 p_ib_cle_id => srl_num_to_add_rec.orig_ib_cle_id,
3648 p_party_tbl => l_party_tbl,
3649 p_party_account => l_party_account,
3650 p_inv_mstr_org_id => l_inv_mstr_org_id,
3651 p_model_line_qty => l_model_line_qty,
3652 p_uom_code => l_primary_uom_code,
3653 p_trx_type => 'CFA',
3654 p_inv_org_id => l_orig_inv_org_id,
3655 p_rbk_ib_cle_id => srl_num_to_add_rec.rbk_ib_cle_id,
3656 x_cimv_rec => l_cimv_rec);
3657 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
3658 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3659 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3660 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3661 RAISE OKL_API.G_EXCEPTION_ERROR;
3662 END IF;
3663 END LOOP;
3664 END IF;
3665
3666 IF l_rbk_serialized = OKL_API.G_FALSE THEN --- not serialized
3667 --okl_debug_pub.logmessage(' l_serialized is false -> is not serialized' );
3668 --dbms_output.put_line(' l_serialized is false -> is not serialized');
3669
3670 Open ib_line_csr(l_orig_k_id,l_orig_fin_ast_cle_id);
3671 Fetch ib_line_csr into ib_line_id,ib_line_cle_id;
3672 If ib_line_csr%NOTFOUND then
3673 null; --should not happen
3674 End If;
3675 CLose ib_line_csr;
3676
3677 --okl_debug_pub.logmessage(' ib_line_id = ' ||ib_line_id );
3678 --dbms_output.put_line(' ib_line_id = ' ||ib_line_id);
3679 --okl_debug_pub.logmessage(' ib_line_cle_id = ' ||ib_line_cle_id );
3680 --dbms_output.put_line(' ib_line_cle_id = ' ||ib_line_cle_id);
3681
3682 -- get the instance - id
3683 Open get_instance_id_csr(ib_line_id);
3684 Fetch get_instance_id_csr into l_instance_id;
3685 If get_instance_id_csr%NOTFOUND then
3686 null; --should not happen
3687 End If;
3688 Close get_instance_id_csr;
3689
3690 --okl_debug_pub.logmessage(' instance id = ' || l_instance_id );
3691 --dbms_output.put_line(' instance id = ' || l_instance_id);
3692
3693 Open ib_line_csr(p_rbk_chr_id,p_rbk_fin_ast_cle_id);
3694 Fetch ib_line_csr into rbk_ib_line_id,rbk_ib_line_cle_id;
3695 If ib_line_csr%NOTFOUND then
3696 null; --should not happen
3697 End If;
3698 CLose ib_line_csr;
3699
3700 l_instance_query_rec.instance_id := to_number(l_instance_id);
3701
3702 csi_item_instance_pub.get_item_instances (
3703 p_api_version => p_api_version,
3704 p_commit => FND_API.G_FALSE,
3705 p_init_msg_list => FND_API.G_FALSE,
3706 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3707 p_instance_query_rec => l_instance_query_rec,
3708 p_party_query_rec => l_party_query_rec,
3709 p_account_query_rec => l_account_query_rec,
3710 p_transaction_id => NULL,
3711 p_resolve_id_columns => FND_API.G_FALSE,
3712 p_active_instance_only => FND_API.G_TRUE,
3713 x_instance_header_tbl => l_instance_header_tbl,
3714 x_return_status => x_return_status,
3715 x_msg_count => x_msg_count,
3716 x_msg_data => x_msg_data);
3717
3718
3719 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3720 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3721 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3722 RAISE OKL_API.G_EXCEPTION_ERROR;
3723 END IF;
3724
3725 If l_instance_header_tbl.COUNT > 0 then
3726 l_instance_header_rec := l_instance_header_tbl(1);
3727 end if;
3728
3729 l_upd_instance_rec.instance_id := l_instance_header_rec.instance_id;
3730 l_upd_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3731 l_upd_instance_rec.quantity := l_rbk_model_line_qty;
3732
3733 --get trx record
3734 get_trx_rec(p_api_version => p_api_version,
3735 p_init_msg_list => p_init_msg_list,
3736 x_return_status => x_return_status,
3737 x_msg_count => x_msg_count,
3738 x_msg_data => x_msg_data,
3739 p_cle_id => ib_line_id,
3740 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3741 x_trx_rec => l_upd_txn_rec);
3742
3743 --okl_debug_pub.logmessage(' get_trx_rec x_return_status= ' || x_return_status);
3744 --dbms_output.put_line(' get_trx_rec x_return_status= ' || x_return_status);
3745
3746 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3747 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3748 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3749 RAISE OKL_API.G_EXCEPTION_ERROR;
3750 END IF;
3751
3752 csi_item_instance_pub.update_item_instance
3753 (
3754 p_api_version => p_api_version
3755 ,p_commit => fnd_api.g_false
3756 ,p_init_msg_list => p_init_msg_list
3757 ,p_validation_level => fnd_api.g_valid_level_full
3758 ,p_instance_rec => l_upd_instance_rec
3759 ,p_ext_attrib_values_tbl => l_upd_ext_attrib_values_tbl
3760 ,p_party_tbl => l_upd_party_tbl
3761 ,p_account_tbl => l_upd_account_tbl
3762 ,p_pricing_attrib_tbl => l_upd_pricing_attrib_tbl
3763 ,p_org_assignments_tbl => l_upd_org_assignments_tbl
3764 ,p_asset_assignment_tbl => l_upd_asset_assignment_tbl
3765 ,p_txn_rec => l_upd_txn_rec
3766 ,x_instance_id_lst => l_upd_instance_id_lst
3767 ,x_return_status => x_return_status
3768 ,x_msg_count => x_msg_count
3769 ,x_msg_data => x_msg_data
3770 );
3771 --serialized
3772 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3773 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3774 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3775 RAISE OKL_API.G_EXCEPTION_ERROR;
3776 END IF;
3777 END IF;
3778
3779 IF l_rbk_serialized = OKL_API.G_TRUE THEN
3780 -- Sync Serial Numbers
3781 FOR srl_num_to_update_rec IN srl_num_to_update_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3782 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3783 p_orig_chr_id => l_orig_k_id,
3784 p_rbk_chr_id => p_rbk_chr_id) LOOP
3785
3786
3787 l_srl_num_old_rec := NULL;
3788 OPEN srl_num_old_csr(p_instance_id => srl_num_to_update_rec.instance_id);
3789 FETCH srl_num_old_csr INTO l_srl_num_old_rec;
3790 CLOSE srl_num_old_csr;
3791
3792 l_srl_num_new_rec := NULL;
3793 OPEN srl_num_new_csr(p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3794 p_rbk_ib_cle_id => srl_num_to_update_rec.rbk_ib_cle_id);
3795 FETCH srl_num_new_csr INTO l_srl_num_new_rec;
3796 CLOSE srl_num_new_csr;
3797
3798 IF NVL(l_srl_num_old_rec.serial_number,OKL_API.G_MISS_CHAR) <>
3799 NVL(l_srl_num_new_rec.serial_number,OKL_API.G_MISS_CHAR) THEN
3800
3801 --okl_debug_pub.logmessage('Update IB Instance');
3802 --dbms_output.put_line('Update IB Instance');
3803
3804 --okl_debug_pub.logmessage('srl_num_to_update_rec.orig_ib_cle_id = ' || srl_num_to_update_rec.orig_ib_cle_id);
3805 --dbms_output.put_line('srl_num_to_update_rec.orig_ib_cle_id = ' || srl_num_to_update_rec.orig_ib_cle_id);
3806
3807 l_instance_query_rec := l_instance_query_temp_rec;
3808 l_instance_header_rec := l_instance_header_temp_rec;
3809 l_upd_instance_rec := l_instance_temp_rec;
3810
3811 l_instance_query_rec.instance_id := srl_num_to_update_rec.instance_id;
3812
3813 csi_item_instance_pub.get_item_instances (
3814 p_api_version => p_api_version,
3815 p_commit => FND_API.G_FALSE,
3816 p_init_msg_list => FND_API.G_FALSE,
3817 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3818 p_instance_query_rec => l_instance_query_rec,
3819 p_party_query_rec => l_party_query_rec,
3820 p_account_query_rec => l_account_query_rec,
3821 p_transaction_id => NULL,
3822 p_resolve_id_columns => FND_API.G_FALSE,
3823 p_active_instance_only => FND_API.G_TRUE,
3824 x_instance_header_tbl => l_instance_header_tbl,
3825 x_return_status => x_return_status,
3826 x_msg_count => x_msg_count,
3827 x_msg_data => x_msg_data);
3828
3829
3830 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3831 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3832 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3833 RAISE OKL_API.G_EXCEPTION_ERROR;
3834 END IF;
3835
3836 If l_instance_header_tbl.COUNT > 0 then
3837 l_instance_header_rec := l_instance_header_tbl(1);
3838 end if;
3839
3840 l_upd_instance_rec.instance_id := l_instance_header_rec.instance_id;
3841 l_upd_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3842 l_upd_instance_rec.serial_number := l_srl_num_new_rec.serial_number;
3843
3844 --Call get_trx_rec
3845 get_trx_rec(p_api_version => p_api_version,
3846 p_init_msg_list => p_init_msg_list,
3847 x_return_status => x_return_status,
3848 x_msg_count => x_msg_count,
3849 x_msg_data => x_msg_data,
3850 p_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3851 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3852 x_trx_rec => l_upd_txn_rec);
3853
3854 --okl_debug_pub.logmessage('get_trx_rec x_return_status = ' || x_return_status);
3855 --dbms_output.put_line('get_trx_rec x_return_status = ' || x_return_status);
3856
3857 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3858 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3859 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3860 RAISE OKL_API.G_EXCEPTION_ERROR;
3861 END IF;
3862
3863 csi_item_instance_pub.update_item_instance
3864 (
3865 p_api_version => p_api_version
3866 ,p_commit => fnd_api.g_false
3867 ,p_init_msg_list => p_init_msg_list
3868 ,p_validation_level => fnd_api.g_valid_level_full
3869 ,p_instance_rec => l_upd_instance_rec
3870 ,p_ext_attrib_values_tbl => l_upd_ext_attrib_values_tbl
3871 ,p_party_tbl => l_upd_party_tbl
3872 ,p_account_tbl => l_upd_account_tbl
3873 ,p_pricing_attrib_tbl => l_upd_pricing_attrib_tbl
3874 ,p_org_assignments_tbl => l_upd_org_assignments_tbl
3875 ,p_asset_assignment_tbl => l_upd_asset_assignment_tbl
3876 ,p_txn_rec => l_upd_txn_rec
3877 ,x_instance_id_lst => l_upd_instance_id_lst
3878 ,x_return_status => x_return_status
3879 ,x_msg_count => x_msg_count
3880 ,x_msg_data => x_msg_data
3881 );
3882
3883 --okl_debug_pub.logmessage('csi_item_instance_pub.update_item_instance 2 x_return_status= ' || x_return_status);
3884 --dbms_output.put_line('csi_item_instance_pub.update_item_instance 2 x_return_status= ' || x_return_status);
3885 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3886 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3887 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3888 RAISE OKL_API.G_EXCEPTION_ERROR;
3889 END IF;
3890
3891 END IF;
3892 END LOOP;
3893 END IF;
3894
3895 -- gboomina Bug 5362977 - End
3896 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3897 EXCEPTION
3898 When OKL_API.G_EXCEPTION_ERROR Then
3899 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3900 (
3901 l_api_name,
3902 G_PKG_NAME,
3903 'OKL_API.G_RET_STS_ERROR',
3904 x_msg_count,
3905 x_msg_data,
3906 '_PVT'
3907 );
3908 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3909 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3910 (
3911 l_api_name,
3912 G_PKG_NAME,
3913 'OKL_API.G_RET_STS_UNEXP_ERROR',
3914 x_msg_count,
3915 x_msg_data,
3916 '_PVT'
3917 );
3918 WHEN OTHERS THEN
3919 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3920 (
3921 l_api_name,
3922 G_PKG_NAME,
3923 'OTHERS',
3924 x_msg_count,
3925 x_msg_data,
3926 '_PVT'
3927 );
3928
3929 END RBK_SRL_NUM_IB_INSTANCE;
3930
3931 End OKL_ACTIVATE_IB_PVT;