[Home] [Help]
PACKAGE BODY: APPS.OKL_ACTIVATE_IB_PVT
Source
1 package body OKL_ACTIVATE_IB_PVT AS
2 /* $Header: OKLRAIBB.pls 120.13.12020000.2 2013/03/07 05:10:53 racheruv ship $ */
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 -- Bug #15992711 : start
2412 /*CURSOR l_hdr_csr (p_rel_chr_id IN NUMBER) IS
2413 SELECT khr.deal_type,
2414 chr.id,
2415 chr.sts_code,
2416 chr.orig_system_id1,
2417 khr.pdt_id,
2418 chr.start_date,
2419 cplb.object1_id1,
2420 chr.cust_acct_id,
2421 chr.scs_code
2422 FROM OKC_K_PARTY_ROLES_B cplb,
2423 OKC_RULES_B rul,
2424 OKL_K_HEADERS khr,
2425 OKC_K_HEADERS_B chr
2426 WHERE cplb.chr_id = chr.id
2427 AND cplb.dnz_chr_id = chr.id
2428 AND cplb.rle_code = 'LESSEE'
2429 AND rul.dnz_chr_id = chr.id
2430 AND rul.rule_information_category = 'LARLES'
2431 AND rul.dnz_chr_id = khr.id --added as part of performance tuning by dkagrawa
2432 AND khr.id = chr.id
2433 AND chr.id = p_rel_chr_id
2434 AND nvl(rul.Rule_information1,'N') = 'Y';*/
2435 CURSOR l_hdr_csr (p_rel_chr_id IN NUMBER) IS
2436 SELECT khr.deal_type,
2437 chr.id,
2438 chr.sts_code,
2439 chr.orig_system_id1,
2440 khr.pdt_id,
2441 chr.start_date,
2442 cplb.object1_id1,
2443 chr.cust_acct_id,
2444 chr.scs_code
2445 FROM OKC_K_PARTY_ROLES_B cplb,
2446 OKL_K_HEADERS khr,
2447 OKC_K_HEADERS_B chr
2448 WHERE cplb.chr_id = chr.id
2449 AND cplb.dnz_chr_id = chr.id
2450 AND cplb.rle_code = 'LESSEE'
2451 AND EXISTS ( SELECT '1'
2452 FROM okl_k_lines kle
2453 ,okc_k_lines_b cle
2454 WHERE cle.dnz_chr_id = chr.id
2455 AND cle.chr_id = chr.id
2456 AND cle.id = kle.id
2457 AND nvl(kle.re_lease_yn,'N') = 'Y')
2458 AND khr.id = chr.id
2459 AND chr.id = p_rel_chr_id;
2460
2461 l_hdr_rec l_hdr_csr%ROWTYPE;
2462 -- Bug #15992711 : End
2463
2464 ---------------------------------------------------------------------------
2465 --Cursor to find out the instance id and its location on the re-lease asset
2466 --Contract
2467 --------------------------------------------------------------------------
2468 Cursor l_ib_line_csr (p_rel_chr_id in Number) is
2469 Select cim_ib.object1_id1,
2470 iti.object_id1_new,
2471 trx.id tas_id,
2472 cleb_ib.id cleb_ib_id
2473 From okc_k_items cim_ib,
2474 okc_k_lines_b cleb_ib,
2475 okc_line_styles_b lseb_ib,
2476 okc_statuses_b stsb,
2477 okl_txl_itm_insts iti,
2478 okl_trx_assets trx,
2479 okl_trx_types_tl ttyt
2480 where iti.kle_id = cleb_ib.id
2481 and iti.tas_id = trx.id
2482 and trx.tas_type = 'CRL'
2483 and trx.tsu_code = 'ENTERED'
2484 and trx.try_id = ttyt.id
2485 and ttyt.language = userenv('LANG')
2486 and ttyt.name = 'Internal Asset Creation'
2487 and cim_ib.cle_id = cleb_ib.id
2488 and cim_ib.dnz_chr_id = cleb_ib.dnz_chr_id
2489 and cleb_ib.dnz_chr_id = p_rel_chr_id
2490 and lseb_ib.id = cleb_ib.lse_id
2491 and lseb_ib.lty_code = 'INST_ITEM'
2492 and cleb_ib.sts_code = stsb.code
2493 and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
2494
2495 l_ib_line_rec l_ib_line_csr%ROWTYPE;
2496
2497
2498 ----------------------------------------------------------------------------
2499 --Cursor to get owner party and account and location info from install base
2500 ---------------------------------------------------------------------------
2501 Cursor l_csi_csr(p_instance_id in number) is
2502 Select *
2503 from csi_item_instances
2504 where instance_id = p_instance_id;
2505
2506 l_csi_rec l_csi_csr%ROWTYPE;
2507
2508 --------------------------------------------------------------------------
2509 --Cursor to get party site id
2510 -------------------------------------------------------------------------
2511 Cursor l_party_site_csr (p_site_use_id in number) is
2512 Select hps.location_id,
2513 hpsu.party_site_id
2514 from hz_party_sites hps,
2515 hz_party_site_uses hpsu
2516 where hps.party_site_id = hpsu.party_site_id
2517 and hpsu.party_site_use_id = p_site_use_id;
2518
2519 l_party_site_rec l_party_site_csr%ROWTYPE;
2520
2521 --cursor to get owner party rec
2522 cursor l_csi_owner_csr(p_instance_id in number) is
2523 select *
2524 from csi_i_parties
2525 where instance_id = p_instance_id
2526 and relationship_type_code = 'OWNER'
2527 and active_end_date is null;
2528
2529 l_csi_owner_rec l_csi_owner_csr%ROWTYPE;
2530
2531
2532 l_instance_rec csi_datastructures_pub.instance_rec;
2533 l_extend_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2534 l_party_tbl csi_datastructures_pub.party_tbl;
2535 l_party_account_tbl csi_datastructures_pub.party_account_tbl;
2536 l_pricing_attribs_tbl csi_datastructures_pub.pricing_attribs_tbl;
2537 l_organization_units_tbl csi_datastructures_pub.organization_units_tbl;
2538 l_instance_Asset_tbl csi_datastructures_pub.instance_asset_tbl;
2539 l_transaction_rec csi_datastructures_pub.transaction_rec;
2540 l_id_tbl csi_datastructures_pub.id_tbl;
2541
2542 l_update_required varchar2(1) default 'N';
2543 l_count NUMBER default 0;
2544
2545 --instance query recs
2546 l_instance_query_rec CSI_DATASTRUCTURES_PUB.instance_query_rec;
2547 l_party_query_rec CSI_DATASTRUCTURES_PUB.party_query_rec;
2548 l_account_query_rec CSI_DATASTRUCTURES_PUB.party_account_query_rec;
2549 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.instance_header_tbl;
2550 l_instance_header_rec CSI_DATASTRUCTURES_PUB.instance_header_rec;
2551
2552 begin
2553
2554 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2555 --call start activity to set savepoint
2556 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
2557 p_init_msg_list,
2558 '_PVT',
2559 x_return_status);
2560 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2561 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2562 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2563 RAISE OKL_API.G_EXCEPTION_ERROR;
2564 END IF;
2565
2566 --1.0 Get the release asset customer and account
2567 Open l_hdr_csr(p_rel_chr_id => p_rel_chr_id);
2568 Fetch l_hdr_csr into l_hdr_rec;
2569 If l_hdr_csr%NOTFOUND then
2570 --error : contract header data not found
2571 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2572 p_msg_name => G_CONTRACT_NOT_FOUND,
2573 p_token1 => G_CONTRACT_ID_TOKEN,
2574 p_token1_value => to_char(p_rel_chr_id)
2575 );
2576 RAISE OKL_API.G_EXCEPTION_ERROR;
2577 End If;
2578 Close l_hdr_csr;
2579
2580 --1.1 check if the contract status is 'APPROVED' and that it is a LEASE contract
2581 If upper(l_hdr_rec.sts_code) <> G_APPROVED_STS_CODE Then
2582 --error : contract is not APPROVED
2583 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2584 p_msg_name => G_CONTRACT_NOT_APPROVED
2585 );
2586 RAISE OKL_API.G_EXCEPTION_ERROR;
2587 ElsIf l_hdr_rec.scs_code <> G_LEASE_SCS_CODE and upper(l_hdr_rec.sts_code) = G_APPROVED_STS_CODE Then
2588 --error : Contract is not lease
2589 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2590 p_msg_name => G_CONTRACT_NOT_LEASE
2591 );
2592 RAISE OKL_API.G_EXCEPTION_ERROR;
2593 End If;
2594
2595 --2.0 Get the ib instance id and install at location party site use id
2596 Open l_ib_line_csr(p_rel_chr_id => p_rel_chr_id);
2597 Loop
2598 Fetch l_ib_line_csr into l_ib_line_rec;
2599 Exit when l_ib_line_csr%NOTFOUND;
2600
2601 --3.0 Get original values from install base
2602 l_instance_query_rec.instance_id := to_number(l_ib_line_rec.object1_id1);
2603
2604 csi_item_instance_pub.get_item_instances (
2605 p_api_version => p_api_version,
2606 p_commit => FND_API.G_FALSE,
2607 p_init_msg_list => FND_API.G_FALSE,
2608 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2609 p_instance_query_rec => l_instance_query_rec,
2610 p_party_query_rec => l_party_query_rec,
2611 p_account_query_rec => l_account_query_rec,
2612 p_transaction_id => NULL,
2613 p_resolve_id_columns => FND_API.G_FALSE,
2614 p_active_instance_only => FND_API.G_TRUE,
2615 x_instance_header_tbl => l_instance_header_tbl,
2616 x_return_status => x_return_status,
2617 x_msg_count => x_msg_count,
2618 x_msg_data => x_msg_data);
2619
2620
2621 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2622 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2623 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2624 RAISE OKL_API.G_EXCEPTION_ERROR;
2625 END IF;
2626
2627
2628 If l_instance_header_tbl.COUNT > 0 then
2629 l_instance_header_rec := l_instance_header_tbl(1);
2630
2631
2632 --4.0 Get party_site_id
2633 Open l_party_site_csr (p_site_use_id => to_number(l_ib_line_rec.object_id1_new));
2634 Fetch l_party_site_csr into l_party_site_rec;
2635 If l_party_site_csr%NOTFOUND then
2636 --error : instance location id not found
2637 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2638 p_msg_name => G_INSTALL_LOC_NOT_FOUND,
2639 p_token1 => G_INST_SITE_USE_TOKEN,
2640 p_token1_value => l_ib_line_rec.object_id1_new
2641 );
2642 RAISE OKL_API.G_EXCEPTION_ERROR;
2643 End If;
2644 close l_party_site_csr;
2645
2646 --5.0 Decide if it is required to call instance update API
2647 l_update_required := 'N';
2648
2649 Open l_csi_csr(p_instance_id => l_instance_header_rec.instance_id);
2650 Fetch l_csi_csr into l_csi_rec;
2651 If l_csi_csr%NOTFOUND then
2652 null; --should not happen as we have already queried the instance
2653 End If;
2654 CLose l_csi_csr;
2655
2656 --check for party and account changes
2657 If nvl(l_csi_rec.owner_party_source_table,OKL_API.G_MISS_CHAR) = 'HZ_PARTIES' then
2658 If to_number(l_hdr_rec.object1_id1) <> l_csi_rec.owner_party_id then
2659 l_update_required := 'Y';
2660 l_count := l_party_tbl.COUNT;
2661 Open l_csi_owner_csr(p_instance_id => l_instance_header_rec.instance_id);
2662 Fetch l_csi_owner_csr into l_csi_owner_rec;
2663 If l_csi_owner_csr%NOTFOUND then
2664 null;
2665 Else
2666 l_count := l_count + 1;
2667 l_party_tbl(l_count).instance_party_id := l_csi_owner_rec.instance_party_id;
2668 l_party_tbl(l_count).object_version_number := l_csi_owner_rec.object_version_number;
2669 l_party_tbl(l_count).relationship_type_code := 'OWNER';
2670 l_party_tbl(l_count).party_id := to_number(l_hdr_rec.object1_id1);
2671 End If;
2672 Close l_csi_owner_csr;
2673 End If;
2674 If l_hdr_rec.cust_acct_id <> l_csi_rec.owner_party_account_id then
2675 l_update_required := 'Y';
2676 If l_party_tbl.COUNT <> 0 then
2677 l_party_account_tbl(1).instance_party_id := l_csi_owner_rec.instance_party_id;
2678 l_party_account_tbl(1).party_account_id := l_hdr_rec.cust_acct_id;
2679 l_party_account_tbl(1).relationship_type_code := G_PARTY_RELATIONSHIP;
2680 --l_party_account_tbl(1).parent_tbl_index := l_count;
2681 End If;
2682
2683 End If;
2684 End If;
2685
2686 --check for install location changes
2687 If nvl(l_instance_header_rec.location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_LOCATIONS' then
2688 If l_party_site_rec.location_id <> l_instance_header_rec.location_id then
2689 l_update_required := 'Y';
2690 l_instance_rec.location_id := l_party_site_rec.location_id;
2691 End If;
2692 Elsif nvl(l_instance_header_rec.location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_PARTY_SITES' then
2693 if l_party_site_rec.party_site_id <> l_instance_header_rec.location_id then
2694 l_update_required := 'Y';
2695 l_instance_rec.location_id := l_party_site_rec.party_site_id;
2696 end If;
2697 End If;
2698
2699 If nvl(l_instance_header_rec.install_location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_LOCATIONS' then
2700 If l_party_site_rec.location_id <> l_instance_header_rec.install_location_id then
2701 l_update_required := 'Y';
2702 l_instance_rec.install_location_id := l_party_site_rec.location_id;
2703 End If;
2704 Elsif nvl(l_instance_header_rec.install_location_type_code,OKL_API.G_MISS_CHAR) = 'HZ_PARTY_SITES' then
2705 if l_party_site_rec.party_site_id <> l_instance_header_rec.install_location_id then
2706 l_update_required := 'Y';
2707 l_instance_rec.install_location_id := l_party_site_rec.party_site_id;
2708 end If;
2709 End If;
2710
2711
2712 --6.0 call ib api if required
2713 If l_update_required = 'Y' then
2714
2715 okl_context.set_okc_org_context(p_chr_id => p_rel_chr_id);
2716 --Call get_trx_rec
2717 get_trx_rec(p_api_version => p_api_version,
2718 p_init_msg_list => p_init_msg_list,
2719 x_return_status => x_return_status,
2720 x_msg_count => x_msg_count,
2721 x_msg_data => x_msg_data,
2722 p_cle_id => l_ib_line_rec.cleb_ib_id,
2723 p_transaction_type => G_IB_BKNG_TXN_TYPE,
2724 x_trx_rec => l_transaction_rec);
2725
2726 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2727 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2728 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2729 RAISE OKL_API.G_EXCEPTION_ERROR;
2730 END IF;
2731
2732 l_instance_rec.instance_id := l_instance_header_rec.instance_id;
2733 l_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
2734 csi_item_instance_pub.update_item_instance
2735 (
2736 p_api_version => p_api_version
2737 ,p_commit => fnd_api.g_false
2738 ,p_init_msg_list => p_init_msg_list
2739 ,p_validation_level => fnd_api.g_valid_level_full
2740 ,p_instance_rec => l_instance_rec
2741 ,p_ext_attrib_values_tbl => l_extend_attrib_values_tbl
2742 ,p_party_tbl => l_party_tbl
2743 ,p_account_tbl => l_party_account_tbl
2744 ,p_pricing_attrib_tbl => l_pricing_attribs_tbl
2745 ,p_org_assignments_tbl => l_organization_units_tbl
2746 ,p_asset_assignment_tbl => l_instance_Asset_tbl
2747 ,p_txn_rec => l_transaction_rec
2748 ,x_instance_id_lst => l_id_tbl
2749 ,x_return_status => x_return_status
2750 ,x_msg_count => x_msg_count
2751 ,x_msg_data => x_msg_data
2752 );
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 l_extend_attrib_values_tbl.delete;
2763 l_party_tbl.delete;
2764 l_party_account_tbl.delete;
2765 l_pricing_attribs_tbl.delete;
2766 l_organization_units_tbl.delete;
2767 l_instance_Asset_tbl.delete;
2768 l_instance_header_tbl.delete;
2769 initialize_txn_rec(l_transaction_rec);
2770 Initialize_instance_rec(l_instance_rec);
2771
2772 --7.0 update the transaction status to processed
2773 update_trx_status(p_api_version => p_api_version,
2774 p_init_msg_list => p_init_msg_list,
2775 x_return_status => x_return_status,
2776 x_msg_count => x_msg_count,
2777 x_msg_data => x_msg_data,
2778 p_tas_id => l_ib_line_rec.tas_id,
2779 p_tsu_code => G_TSU_CODE_PROCESSED);
2780
2781 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2782 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2783 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2784 RAISE OKL_API.G_EXCEPTION_ERROR;
2785 END IF;
2786
2787 End If;
2788
2789 End Loop;
2790 close l_ib_line_csr;
2791
2792 --Call end Activity
2793 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2794 EXCEPTION
2795 When OKL_API.G_EXCEPTION_ERROR Then
2796 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2797 (
2798 l_api_name,
2799 G_PKG_NAME,
2800 'OKL_API.G_RET_STS_ERROR',
2801 x_msg_count,
2802 x_msg_data,
2803 '_PVT'
2804 );
2805 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2806 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2807 (
2808 l_api_name,
2809 G_PKG_NAME,
2810 'OKL_API.G_RET_STS_UNEXP_ERROR',
2811 x_msg_count,
2812 x_msg_data,
2813 '_PVT'
2814 );
2815 WHEN OTHERS THEN
2816 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2817 (
2818 l_api_name,
2819 G_PKG_NAME,
2820 'OTHERS',
2821 x_msg_count,
2822 x_msg_data,
2823 '_PVT'
2824 );
2825 End RELEASE_IB_INSTANCE;
2826
2827
2828 --Bug# 5207066
2829 PROCEDURE Is_Inv_Item_Serialized(p_api_version IN NUMBER,
2830 p_init_msg_list IN VARCHAR2,
2831 x_return_status OUT NOCOPY VARCHAR2,
2832 x_msg_count OUT NOCOPY NUMBER,
2833 x_msg_data OUT NOCOPY VARCHAR2,
2834 p_inv_item_id IN NUMBER,
2835 p_chr_id IN NUMBER,
2836 p_cle_id IN NUMBER,
2837 x_serialized OUT NOCOPY VARCHAR2) IS
2838 l_api_version CONSTANT NUMBER := 1.0;
2839 l_api_name VARCHAR2(30) := 'IS_INV_ITEM_SERIALIZED';
2840
2841 l_serialized VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
2842
2843 --cursor to find serialized
2844 CURSOR srl_ctrl_csr (p_inv_item_id IN NUMBER,
2845 p_chr_id IN NUMBER) IS
2846 SELECT mtl.serial_number_control_code
2847 FROM mtl_system_items mtl,
2848 okc_k_headers_b chrb
2849 WHERE mtl.inventory_item_id = p_inv_item_id
2850 AND mtl.organization_id = chrb.inv_organization_id
2851 --BUG# 3489089
2852 AND chrb.id = p_chr_id;
2853
2854 --cursor2 to find serialized
2855 CURSOR srl_ctrl_csr2 (p_inv_item_id IN NUMBER,
2856 p_cle_id IN NUMBER) IS
2857 SELECT mtl.serial_number_control_code
2858 FROM mtl_system_items mtl,
2859 okc_k_headers_b chrb,
2860 okc_k_lines_b cleb
2861 WHERE mtl.inventory_item_id = p_inv_item_id
2862 AND mtl.organization_id = chrb.inv_organization_id
2863 AND chrb.id = cleb.dnz_chr_id
2864 AND cleb.id = p_cle_id;
2865
2866 l_srl_control_code mtl_system_items.serial_number_control_code%TYPE;
2867
2868 l_exception_halt EXCEPTION;
2869
2870 BEGIN
2871 x_serialized := OKL_API.G_FALSE;
2872 x_return_status := OKL_API.START_ACTIVITY (
2873 l_api_name
2874 ,p_init_msg_list
2875 ,'_PVT'
2876 ,x_return_status);
2877 -- Check if activity started successfully
2878 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2879 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2880 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2881 RAISE OKL_API.G_EXCEPTION_ERROR;
2882 END IF;
2883
2884 l_serialized := OKL_API.G_FALSE;
2885 IF p_chr_id IS NOT NULL OR p_chr_id <> OKL_API.G_MISS_NUM THEN
2886 OPEN srl_ctrl_csr (p_inv_item_id => p_inv_item_id,
2887 p_chr_id => p_chr_id);
2888 FETCH srl_ctrl_csr INTO
2889 l_srl_control_code;
2890 CLOSE srl_ctrl_csr;
2891 ELSIF p_cle_id IS NOT NULL OR p_cle_id <> OKL_API.G_MISS_NUM THEN
2892 OPEN srl_ctrl_csr2 (p_inv_item_id => p_inv_item_id,
2893 p_cle_id => p_cle_id);
2894 FETCH srl_ctrl_csr2 INTO
2895 l_srl_control_code;
2896 CLOSE srl_ctrl_csr2;
2897 ELSE
2898 RAISE l_exception_halt;
2899 END IF;
2900
2901 IF NVL(l_srl_control_code,0) IN (2,5,6) THEN
2902 l_serialized := OKL_API.G_TRUE;
2903 END IF;
2904 x_serialized := l_serialized;
2905 OKL_API.END_ACTIVITY (x_msg_count,
2906 x_msg_data );
2907 EXCEPTION
2908 WHEN l_exception_halt THEN
2909 NULL;
2910 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2911 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2912 l_api_name,
2913 G_PKG_NAME,
2914 'OKL_API.G_RET_STS_ERROR',
2915 x_msg_count,
2916 x_msg_data,
2917 '_PVT');
2918 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2919 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2920 l_api_name,
2921 G_PKG_NAME,
2922 'OKL_API.G_RET_STS_UNEXP_ERROR',
2923 x_msg_count,
2924 x_msg_data,
2925 '_PVT');
2926 WHEN OTHERS THEN
2927 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
2928 l_api_name,
2929 G_PKG_NAME,
2930 'OTHERS',
2931 x_msg_count,
2932 x_msg_data,
2933 '_PVT');
2934 END Is_Inv_Item_Serialized;
2935
2936 --Bug# 5207066
2937 PROCEDURE RBK_SRL_NUM_IB_INSTANCE
2938 (p_api_version IN NUMBER,
2939 p_init_msg_list IN VARCHAR2,
2940 x_return_status OUT NOCOPY VARCHAR2,
2941 x_msg_count OUT NOCOPY NUMBER,
2942 x_msg_data OUT NOCOPY VARCHAR2,
2943 p_rbk_fin_ast_cle_id IN NUMBER,
2944 p_rbk_chr_id IN NUMBER
2945 ) IS
2946
2947
2948 --cursor to get ib line
2949 Cursor ib_line_csr(p_chrv_id IN Number,
2950 p_fin_ast_cle_id IN Number) is
2951 SELECT ib_cle.id,
2952 ib_cle.cle_id
2953 from okc_k_lines_b ib_cle,
2954 okc_k_lines_b inst_cle,
2955 okc_statuses_b inst_sts
2956 where ib_cle.lse_id = G_IB_LINE_LTY_ID
2957 and ib_cle.dnz_chr_id = p_chrv_id
2958 AND inst_sts.code = ib_cle.sts_code
2959 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
2960 AND ib_cle.cle_id = inst_cle.id
2961 and inst_cle.dnz_chr_id = p_chrv_id
2962 and inst_cle.cle_id = p_fin_ast_cle_id;
2963
2964 -- cursor to get instance_id from okc_k_items
2965 Cursor get_instance_id_csr(p_cle_id IN Number) is
2966 SELECT cim.object1_id1
2967 from okc_k_items cim
2968 where cim.cle_id = p_cle_id;
2969
2970 -- cursor to get original contract id
2971 cursor get_orig_k_id (p_chr_id in number,
2972 p_fin_ast_cle_id in number) is
2973 select chr.ORIG_SYSTEM_ID1 orig_chr_id,
2974 cle.orig_system_id1 orig_fin_ast_cle_id
2975 from okc_k_headers_b chr,
2976 okc_k_lines_b cle
2977 where chr.id = p_chr_id
2978 and cle.id = p_fin_ast_cle_id
2979 and cle.chr_id = p_chr_id
2980 and cle.dnz_chr_id = p_chr_id;
2981
2982 -- gboomina Bug 5362977 - Start
2983 CURSOR srl_num_to_add_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
2988 SELECT orig_ib_cle.id orig_ib_cle_id,
2989 orig_ib_cle.cle_id orig_inst_cle_id,
2990 orig_ib_cle.orig_system_id1 rbk_ib_cle_id
2991 FROM okc_k_lines_b orig_ib_cle,
2992 okc_k_lines_b orig_inst_cle,
2993 okc_k_lines_b rbk_inst_cle,
2994 okc_statuses_b inst_sts,
2995 --Bug# 8766336
2996 okc_statuses_b rbk_inst_sts
2997 WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
2998 AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
2999 AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
3000 AND orig_ib_cle.cle_id = orig_inst_cle.id
3001 AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
3002 AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
3003 AND rbk_inst_cle.id = orig_inst_cle.orig_system_id1
3004 AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
3005 AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
3006 AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
3007 AND inst_sts.code = orig_ib_cle.sts_code
3008 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
3009 --Bug# 8766336
3010 AND rbk_inst_sts.code = rbk_inst_cle.sts_code
3011 AND rbk_inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
3012
3013
3014 -- Modified query to only fetch IB lines deleted during rebook
3015 CURSOR srl_num_to_exp_csr(p_orig_fin_ast_cle_id IN NUMBER,
3016 p_rbk_fin_ast_cle_id IN NUMBER,
3017 p_orig_chr_id IN NUMBER,
3018 p_rbk_chr_id IN NUMBER) IS
3019 SELECT orig_ib_cle.id ib_cle_id,
3020 orig_ib_cle.cle_id inst_cle_id,
3021 orig_ib_cim.object1_id1 instance_id
3022 FROM okc_k_items orig_ib_cim,
3023 okc_k_lines_b orig_ib_cle,
3024 okc_k_lines_b orig_inst_cle,
3025 okc_statuses_b inst_sts
3026 WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
3027 AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
3028 AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
3029 AND orig_ib_cle.cle_id = orig_inst_cle.id
3030 AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
3031 AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
3032 AND orig_ib_cim.cle_id = orig_ib_cle.id
3033 AND orig_ib_cim.dnz_chr_id = p_orig_chr_id
3034 AND orig_ib_cim.object1_id1 IS NOT NULL
3035 AND inst_sts.code = orig_ib_cle.sts_code
3036 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
3037 AND NOT EXISTS (
3038 SELECT 1
3039 FROM okc_k_lines_b rbk_inst_cle,
3040 okc_statuses_b rbk_inst_sts
3041 WHERE rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
3042 AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
3043 AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
3044 AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
3045 AND rbk_inst_sts.code = rbk_inst_cle.sts_code
3046 AND rbk_inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED'));
3047
3048 CURSOR srl_num_to_update_csr(p_orig_fin_ast_cle_id IN NUMBER,
3049 p_rbk_fin_ast_cle_id IN NUMBER,
3050 p_orig_chr_id IN NUMBER,
3051 p_rbk_chr_id IN NUMBER ) IS
3052
3053 SELECT orig_ib_cle.id orig_ib_cle_id,
3054 orig_ib_cle.cle_id orig_inst_cle_id,
3055 rbk_ib_cle.id rbk_ib_cle_id,
3056 rbk_ib_cle.cle_id rbk_inst_cle_id,
3057 TO_NUMBER(ib_cim.object1_id1) instance_id,
3058 ib_cim.id orig_ib_cim_id
3059 FROM okc_k_lines_b orig_ib_cle,
3060 okc_k_lines_b orig_inst_cle,
3061 okc_k_lines_b rbk_inst_cle,
3062 okc_k_lines_b rbk_ib_cle,
3063 okc_statuses_b inst_sts,
3064 okc_k_items ib_cim,
3065 --Bug# 8766336
3066 okc_statuses_b rbk_inst_sts
3067 WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
3068 AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
3069 AND orig_inst_cle.lse_id = G_INST_LINE_LTY_ID
3070 AND orig_ib_cle.cle_id = orig_inst_cle.id
3071 AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
3072 AND orig_ib_cle.lse_id = G_IB_LINE_LTY_ID
3073 AND rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
3074 AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
3075 AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
3076 AND rbk_inst_cle.lse_id = G_INST_LINE_LTY_ID
3077 AND rbk_ib_cle.cle_id = rbk_inst_cle.id
3078 AND rbk_ib_cle.dnz_chr_id = p_rbk_chr_id
3079 AND rbk_ib_cle.lse_id = G_IB_LINE_LTY_ID
3080 AND inst_sts.code = orig_ib_cle.sts_code
3081 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
3082 --Bug# 8766336
3083 AND rbk_inst_sts.code = rbk_ib_cle.sts_code
3084 AND rbk_inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
3085 AND ib_cim.cle_id = orig_ib_cle.id
3086 AND ib_cim.dnz_chr_id = p_orig_chr_id;
3087
3088 CURSOR srl_num_old_csr(p_instance_id IN NUMBER) IS
3089 SELECT csi_item.serial_number
3090 FROM csi_item_instances csi_item
3091 WHERE csi_item.instance_id = p_instance_id;
3092
3093 l_srl_num_old_rec srl_num_old_csr%ROWTYPE;
3094
3095 CURSOR srl_num_new_csr(p_rbk_fin_ast_cle_id IN NUMBER,
3096 p_rbk_ib_cle_id IN NUMBER) IS
3097 SELECT serial_number
3098 FROM okl_txl_itm_insts iti,
3099 okl_trx_assets tas
3100 WHERE iti.dnz_cle_id = P_rbk_fin_ast_cle_id
3101 AND iti.kle_id = p_rbk_ib_cle_id
3102 AND iti.tal_type = G_TRX_LINE_TYPE_REBOOK
3103 AND tas.tas_type = G_TRX_LINE_TYPE_REBOOK
3104 AND tas.tsu_code = G_TSU_CODE_ENTERED
3105 AND tas.id = iti.tas_id;
3106
3107 l_srl_num_new_rec srl_num_new_csr%ROWTYPE;
3108
3109 -- gboomina Bug 5362977 - End
3110
3111 -- Get Original Inventory Item
3112 CURSOR orig_instance_dtls_csr (p_fin_ast_cle_id IN NUMBER,
3113 p_chr_id IN NUMBER) is
3114 SELECT csi_item.inventory_item_id
3115 FROM csi_item_instances csi_item,
3116 okc_k_items ib_cim,
3117 okc_k_lines_b ib_cle,
3118 okc_k_lines_b inst_cle,
3119 okc_statuses_b inst_sts
3120 WHERE ib_cim.cle_id = ib_cle.id
3121 AND ib_cim.dnz_chr_id = p_chr_id
3122 AND inst_cle.cle_id = p_fin_ast_cle_id
3123 AND inst_cle.lse_id = G_INST_LINE_LTY_ID
3124 AND inst_cle.dnz_chr_id = p_chr_id
3125 AND ib_cle.cle_id = inst_cle.id
3126 AND ib_cle.lse_id = G_IB_LINE_LTY_ID
3127 AND ib_cle.dnz_chr_id = p_chr_id
3128 AND csi_item.instance_id = TO_NUMBER(ib_cim.object1_id1)
3129 AND inst_sts.code = ib_cle.sts_code
3130 AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED');
3131
3132 --- Get quantity cursor
3133 CURSOR get_qty_csr (p_fin_ast_cle_id IN NUMBER) is
3134 SELECT cim.number_of_items,
3135 cim.object1_id1,
3136 cim.object1_id2
3137 FROM OKC_K_ITEMS CIM,
3138 OKC_K_LINES_B MDL,
3139 OKC_LINE_STYLES_B MDL_LSE
3140 WHERE CIM.CLE_ID = MDL.ID
3141 AND MDL.CLE_ID = p_fin_ast_cle_id
3142 AND MDL.LSE_ID = MDL_LSE.ID
3143 AND MDL_LSE.LTY_CODE = G_MODEL_LINE_LTY_CODE;
3144
3145 -- gboomina Bug 5362977 - End
3146
3147 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
3148 l_api_name CONSTANT varchar2(30) := 'RBK_SRL_NUM_IB_INSTANCE';
3149 l_api_version CONSTANT NUMBER := 1.0;
3150
3151 l_orig_inv_org_id okc_k_items.object1_id1%TYPE;
3152 l_orig_inv_item_id okc_k_items.object1_id2%TYPE;
3153
3154 -- gboomina Bug 5362977 - Start
3155 l_orig_serialized VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
3156 l_rbk_serialized VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
3157 -- gboomina Bug 5362977 - End
3158
3159 l_orig_k_id okc_k_headers_b.id%type;
3160 l_orig_fin_ast_cle_id okc_k_lines_b.id%type;
3161 l_instance_id okc_k_items.id%type;
3162 l_instance_cle_id okc_k_items.cle_id%type;
3163
3164 l_instance_rec CSI_DATASTRUCTURES_PUB.instance_rec;
3165 l_upd_instance_rec CSI_DATASTRUCTURES_PUB.instance_rec;
3166
3167 l_upd_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
3168 l_upd_party_tbl CSI_DATASTRUCTURES_PUB.party_tbl;
3169 l_upd_party_tbl_in CSI_DATASTRUCTURES_PUB.party_tbl;
3170 l_upd_account_tbl CSI_DATASTRUCTURES_PUB.party_account_tbl;
3171 l_upd_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
3172 l_upd_org_assignments_tbl CSI_DATASTRUCTURES_PUB.organization_units_tbl;
3173 l_upd_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
3174 l_upd_txn_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
3175 l_upd_instance_id_lst CSI_DATASTRUCTURES_PUB.id_tbl;
3176 l_account_tbl party_account_tbl_type;
3177
3178 -- gboomina Bug 5362977 - Start
3179 l_rbk_model_line_qty okc_k_items.number_of_items%TYPE;
3180 l_rbk_inv_item_id okc_k_items.object1_id1%TYPE;
3181 l_rbk_inv_org_id okc_k_items.object1_id2%TYPE;
3182 l_instance_query_temp_rec CSI_DATASTRUCTURES_PUB.instance_query_rec;
3183 l_instance_header_temp_rec CSI_DATASTRUCTURES_PUB.instance_header_rec;
3184 l_instance_temp_rec CSI_DATASTRUCTURES_PUB.instance_rec;
3185 l_model_line_qty okc_k_items.number_of_items%TYPE;
3186 l_cim_rec okl_okc_migration_pvt.cimv_rec_type;
3187 x_cim_rec okl_okc_migration_pvt.cimv_rec_type;
3188 -- gboomina Bug 5362977 - End
3189
3190 l_trx_type VARCHAR2(30) := G_TRX_LINE_TYPE_REBOOK;
3191
3192 ib_line_id okc_k_lines_b.id%type;
3193 ib_line_cle_id okc_k_lines_b.cle_id%type;
3194
3195 rbk_ib_line_id okc_k_lines_b.id%type;
3196 rbk_ib_line_cle_id okc_k_lines_b.cle_id%type;
3197
3198 l_party_tbl party_tbl_type;
3199 l_party_account NUMBER;
3200 l_inv_mstr_org_id NUMBER;
3201 l_primary_uom_code MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE;
3202 l_cimv_rec cimv_rec_type;
3203 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
3204 l_klev_rec okl_kle_pvt.klev_rec_type;
3205 x_clev_rec okl_okc_migration_pvt.clev_rec_type;
3206 x_klev_rec okl_kle_pvt.klev_rec_type;
3207
3208 l_transaction_rec CSI_DATASTRUCTURES_PUB.transaction_rec;
3209 l_instance_query_rec CSI_DATASTRUCTURES_PUB.instance_query_rec;
3210 l_party_query_rec CSI_DATASTRUCTURES_PUB.party_query_rec;
3211 l_account_query_rec CSI_DATASTRUCTURES_PUB.party_account_query_rec;
3212 l_instance_header_tbl CSI_DATASTRUCTURES_PUB.instance_header_tbl;
3213 l_instance_header_rec CSI_DATASTRUCTURES_PUB.instance_header_rec;
3214
3215 BEGIN
3216
3217 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3218 --call start activity to set savepoint
3219 x_return_status := OKL_API.START_ACTIVITY(substr(l_api_name,1,26),
3220 p_init_msg_list,
3221 '_PVT',
3222 x_return_status);
3223 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3224 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3225 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3226 RAISE OKL_API.G_EXCEPTION_ERROR;
3227 END IF;
3228
3229 -- get original contract_id
3230 Open get_orig_k_id(p_rbk_chr_id,p_rbk_fin_ast_cle_id);
3231 Fetch get_orig_k_id into l_orig_k_id,l_orig_fin_ast_cle_id;
3232 If get_orig_k_id%NOTFOUND then
3233 null; --should not happen
3234 End If;
3235 CLose get_orig_k_id;
3236
3237 --okl_debug_pub.logmessage(' original contract id = ' ||l_orig_k_id );
3238 --dbms_output.put_line(' original contract id = ' ||l_orig_k_id);
3239
3240 --okl_debug_pub.logmessage(' original fin asset line id = ' ||l_orig_fin_ast_cle_id );
3241 --dbms_output.put_line(' original fin asset line id = ' ||l_orig_fin_ast_cle_id);
3242
3243 -- get original k inv item details
3244 -- gboomina Bug 5362977 - Start
3245 l_orig_inv_item_id := 0;
3246 OPEN orig_instance_dtls_csr(p_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3247 p_chr_id => l_orig_k_id);
3248 FETCH orig_instance_dtls_csr INTO l_orig_inv_item_id;
3249 CLOSE orig_instance_dtls_csr;
3250
3251 --okl_debug_pub.logmessage(' orig inventory item id = ' ||l_orig_inv_item_id );
3252 --dbms_output.put_line(' orig inventory item id = ' ||l_orig_inv_item_id);
3253
3254 -- get the number of qty
3255 l_rbk_model_line_qty := 0;
3256 OPEN get_qty_csr (p_fin_ast_cle_id => p_rbk_fin_ast_cle_id ); --l_instance_id
3257 FETCH get_qty_csr into
3258 l_rbk_model_line_qty,
3259 l_rbk_inv_item_id,
3260 l_rbk_inv_org_id;
3261 IF get_qty_csr%NOTFOUND Then
3262 --okl_debug_pub.logmessage(' get_qty_csr not found');
3263 --dbms_output.put_line(' get_qty_csr not found');
3264 l_rbk_model_line_qty := 1;
3265 ELSE
3266 Null;
3267 END IF;
3268 CLOSE get_qty_csr;
3269
3270 --okl_debug_pub.logmessage(' inventory org id = ' ||l_orig_inv_org_id );
3271 --okl_debug_pub.logmessage(' inventory item id = ' ||l_orig_inv_item_id );
3272 --dbms_output.put_line(' inventory org id = ' ||l_orig_inv_org_id);
3273 --dbms_output.put_line(' inventory item id = ' ||l_orig_inv_item_id);
3274
3275 l_orig_serialized := OKL_API.G_FALSE;
3276
3277 Is_Inv_Item_Serialized(p_api_version => p_api_version,
3278 p_init_msg_list => p_init_msg_list,
3279 x_return_status => x_return_status,
3280 x_msg_count => x_msg_count,
3281 x_msg_data => x_msg_data,
3282 p_inv_item_id => l_orig_inv_item_id,
3283 p_chr_id => l_orig_k_id,
3284 p_cle_id => l_orig_fin_ast_cle_id,
3285 x_serialized => l_orig_serialized);
3286 -- gboomina Bug 5362977 - End
3287
3288 --okl_debug_pub.logmessage(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3289 --dbms_output.put_line(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3290 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3291 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3292 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3293 RAISE OKL_API.G_EXCEPTION_ERROR;
3294 END IF;
3295
3296 -- gboomina Bug 5362977 - Start
3297 l_rbk_serialized := OKL_API.G_FALSE;
3298 Is_Inv_Item_Serialized(p_api_version => p_api_version,
3299 p_init_msg_list => p_init_msg_list,
3300 x_return_status => x_return_status,
3301 x_msg_count => x_msg_count,
3302 x_msg_data => x_msg_data,
3303 p_inv_item_id => l_rbk_inv_item_id,
3304 p_chr_id => p_rbk_chr_id,
3305 p_cle_id => p_rbk_fin_ast_cle_id,
3306 x_serialized => l_rbk_serialized);
3307
3308 --okl_debug_pub.logmessage(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3309 --dbms_output.put_line(' Is_Inv_Item_Serialized x_return_status = ' ||x_return_status );
3310 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3311 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3312 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3313 RAISE OKL_API.G_EXCEPTION_ERROR;
3314 END IF;
3315
3316 -- Expire and Re-create IB instance if Inventory item is changed.
3317 IF (l_orig_inv_item_id <> l_rbk_inv_item_id) THEN
3318
3319 FOR srl_num_to_update_rec IN srl_num_to_update_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3320 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3321 p_orig_chr_id => l_orig_k_id,
3322 p_rbk_chr_id => p_rbk_chr_id) LOOP
3323 --
3324 -- Expire IB instance with old inventory item
3325 --
3326
3327 l_instance_query_rec := l_instance_query_temp_rec;
3328 l_instance_header_rec := l_instance_header_temp_rec;
3329 l_instance_rec := l_instance_temp_rec;
3330
3331 l_instance_query_rec.instance_id := srl_num_to_update_rec.instance_id;
3332 -- gboomina Bug 5362977 - End
3333
3334 csi_item_instance_pub.get_item_instances (
3335 p_api_version => p_api_version,
3336 p_commit => FND_API.G_FALSE,
3337 p_init_msg_list => FND_API.G_FALSE,
3338 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3339 p_instance_query_rec => l_instance_query_rec,
3340 p_party_query_rec => l_party_query_rec,
3341 p_account_query_rec => l_account_query_rec,
3342 p_transaction_id => NULL,
3343 p_resolve_id_columns => FND_API.G_FALSE,
3344 p_active_instance_only => FND_API.G_TRUE,
3345 x_instance_header_tbl => l_instance_header_tbl,
3346 x_return_status => x_return_status,
3347 x_msg_count => x_msg_count,
3348 x_msg_data => x_msg_data);
3349
3350
3351 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3352 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3353 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3354 RAISE OKL_API.G_EXCEPTION_ERROR;
3355 END IF;
3356
3357 If l_instance_header_tbl.COUNT > 0 then
3358 l_instance_header_rec := l_instance_header_tbl(1);
3359 end if;
3360
3361 -- gboomina Bug 5362977 - Start
3362 l_instance_rec.instance_id := l_instance_header_rec.instance_id;
3363 l_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3364
3365 --Call get_trx_rec
3366 get_trx_rec(p_api_version => p_api_version,
3367 p_init_msg_list => p_init_msg_list,
3368 x_return_status => x_return_status,
3369 x_msg_count => x_msg_count,
3370 x_msg_data => x_msg_data,
3371 p_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3372 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3373 x_trx_rec => l_transaction_rec);
3374 -- gboomina Bug 5362977 - End
3375
3376 --okl_debug_pub.logmessage(' get_trx_rec x_return_status= ' || x_return_status);
3377 --dbms_output.put_line(' get_trx_rec x_return_status= ' || x_return_status);
3378
3379 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3380 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3381 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3382 RAISE OKL_API.G_EXCEPTION_ERROR;
3383 END IF;
3384
3385 csi_item_instance_pub.expire_item_instance
3386 (
3387 p_api_version => p_api_version
3388 ,p_commit => fnd_api.g_false
3389 ,p_init_msg_list => p_init_msg_list
3390 ,p_validation_level => fnd_api.g_valid_level_full
3391 ,p_instance_rec => l_instance_rec
3392 ,p_expire_children => fnd_api.g_false
3393 ,p_txn_rec => l_transaction_rec
3394 ,x_instance_id_lst => l_upd_instance_id_lst
3395 ,x_return_status => x_return_status
3396 ,x_msg_count => x_msg_count
3397 ,x_msg_data => x_msg_data);
3398
3399 --okl_debug_pub.logmessage(' csi_item_instance_pub.update_item_instance x_return_status = ' || x_return_status);
3400 --dbms_output.put_line(' csi_item_instance_pub.update_item_instance x_return_status = ' || x_return_status);
3401 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3402 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3403 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3404 RAISE OKL_API.G_EXCEPTION_ERROR;
3405 END IF;
3406
3407 l_cim_rec.id := srl_num_to_update_rec.orig_ib_cim_id;
3408 l_cim_rec.object1_id1 := NULL;
3409 l_cim_rec.object1_id2 := NULL;
3410 l_cim_rec.jtot_object1_code := NULL;
3411
3412 okl_okc_migration_pvt.update_contract_item(
3413 p_api_version => 1.0,
3414 p_init_msg_list => okc_api.g_false,
3415 x_return_status =>x_return_status,
3416 x_msg_count =>x_msg_count,
3417 x_msg_data =>x_msg_data,
3418 p_cimv_rec =>l_cim_rec,
3419 x_cimv_rec =>x_cim_rec);
3420
3421 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3422 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3423 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3424 RAISE OKL_API.G_EXCEPTION_ERROR;
3425 END IF;
3426
3427 -- Create IB instance with new inventory item
3428 --
3429
3430 Process_IB_Line_1(p_api_version => p_api_version,
3431 p_init_msg_list => p_init_msg_list,
3432 x_return_status => x_return_status,
3433 x_msg_count => x_msg_count,
3434 x_msg_data => x_msg_data,
3435 p_chrv_id => l_orig_k_id,
3436 p_inst_cle_id => srl_num_to_update_rec.orig_inst_cle_id,
3437 p_ib_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3438 x_party_tbl => l_party_tbl,
3439 x_party_account => l_party_account,
3440 x_inv_mstr_org_id => l_inv_mstr_org_id,
3441 x_model_line_qty => l_model_line_qty,
3442 x_primary_uom_code => l_primary_uom_code,
3443 x_inv_org_id => l_orig_inv_org_id);
3444
3445 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3446 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3447 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3448 RAISE OKL_API.G_EXCEPTION_ERROR;
3449 END IF;
3450
3451 Process_IB_Line_2(p_api_version => p_api_version,
3452 p_init_msg_list => p_init_msg_list,
3453 x_return_status => x_return_status,
3454 x_msg_count => x_msg_count,
3455 x_msg_data => x_msg_data,
3456 p_chrv_id => l_orig_k_id,
3457 p_inst_cle_id => srl_num_to_update_rec.orig_inst_cle_id,
3458 p_ib_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3459 p_party_tbl => l_party_tbl,
3460 p_party_account => l_party_account,
3461 p_inv_mstr_org_id => l_inv_mstr_org_id,
3462 p_model_line_qty => l_model_line_qty,
3463 p_uom_code => l_primary_uom_code,
3464 p_trx_type => 'CRB',
3465 p_inv_org_id => l_orig_inv_org_id,
3466 p_rbk_ib_cle_id => srl_num_to_update_rec.rbk_ib_cle_id,
3467 x_cimv_rec => l_cimv_rec);
3468 --dbms_output.put_line('After Process_Line_2 1'||x_return_status);
3469 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3470 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3471 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3472 RAISE OKL_API.G_EXCEPTION_ERROR;
3473 END IF;
3474
3475 END LOOP;
3476
3477 END IF;
3478
3479 IF (l_rbk_serialized = OKL_API.G_TRUE OR l_orig_serialized = OKL_API.G_TRUE) THEN
3480
3481 --okl_debug_pub.logmessage(' IN serialized section ');
3482 --dbms_output.put_line(' IN serialized section ');
3483
3484 --okl_debug_pub.logmessage(' rebook contract fin line id = ' || p_rbk_fin_ast_cle_id);
3485 --okl_debug_pub.logmessage(' original contract fin line id = ' || l_orig_fin_ast_cle_id);
3486
3487 --dbms_output.put_line(' rebook contract fin line id = ' || p_rbk_fin_ast_cle_id);
3488 --dbms_output.put_line(' original contract fin line id = ' || l_orig_fin_ast_cle_id);
3489
3490 -- to expire the instance items
3491 FOR srl_num_to_exp_rec IN srl_num_to_exp_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3492 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3493 p_orig_chr_id => l_orig_k_id,
3494 p_rbk_chr_id => p_rbk_chr_id) LOOP
3495 --okl_debug_pub.logmessage('Expire IB Instance');
3496 --dbms_output.put_line('Expire IB Instance');
3497
3498 --okl_debug_pub.logmessage('srl_num_to_exp_rec.ib_cle_id = ' || srl_num_to_exp_rec.ib_cle_id);
3499 --dbms_output.put_line('srl_num_to_exp_rec.ib_cle_id = ' || srl_num_to_exp_rec.ib_cle_id);
3500
3501 --okl_debug_pub.logmessage('srl_num_to_exp_rec.inst_cle_id = ' || srl_num_to_exp_rec.inst_cle_id);
3502 --dbms_output.put_line('srl_num_to_exp_rec.inst_cle_id = ' || srl_num_to_exp_rec.inst_cle_id);
3503
3504 l_instance_query_rec := l_instance_query_temp_rec;
3505 l_instance_header_rec := l_instance_header_temp_rec;
3506 l_instance_rec := l_instance_temp_rec;
3507
3508 l_instance_query_rec.instance_id := to_number(srl_num_to_exp_rec.instance_id);
3509
3510 csi_item_instance_pub.get_item_instances (
3511 p_api_version => p_api_version,
3512 p_commit => FND_API.G_FALSE,
3513 p_init_msg_list => FND_API.G_FALSE,
3514 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3515 p_instance_query_rec => l_instance_query_rec,
3516 p_party_query_rec => l_party_query_rec,
3517 p_account_query_rec => l_account_query_rec,
3518 p_transaction_id => NULL,
3519 p_resolve_id_columns => FND_API.G_FALSE,
3520 p_active_instance_only => FND_API.G_TRUE,
3521 x_instance_header_tbl => l_instance_header_tbl,
3522 x_return_status => x_return_status,
3523 x_msg_count => x_msg_count,
3524 x_msg_data => x_msg_data);
3525
3526
3527 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3528 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3529 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3530 RAISE OKL_API.G_EXCEPTION_ERROR;
3531 END IF;
3532
3533 If l_instance_header_tbl.COUNT > 0 then
3534 l_instance_header_rec := l_instance_header_tbl(1);
3535 end if;
3536
3537 l_instance_rec.instance_id := l_instance_header_rec.instance_id;
3538 l_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3539
3540 --Call get_trx_rec
3541 get_trx_rec(p_api_version => p_api_version,
3542 p_init_msg_list => p_init_msg_list,
3543 x_return_status => x_return_status,
3544 x_msg_count => x_msg_count,
3545 x_msg_data => x_msg_data,
3546 p_cle_id => srl_num_to_exp_rec.ib_cle_id,
3547 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3548 x_trx_rec => l_transaction_rec);
3549
3550 --okl_debug_pub.logmessage('get_trx_rec x_return_status = ' || x_return_status);
3551 --dbms_output.put_line('get_trx_rec x_return_status = ' || x_return_status);
3552
3553 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3554 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3555 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3556 RAISE OKL_API.G_EXCEPTION_ERROR;
3557 END IF;
3558
3559 csi_item_instance_pub.expire_item_instance
3560 (
3561 p_api_version => p_api_version
3562 ,p_commit => fnd_api.g_false
3563 ,p_init_msg_list => p_init_msg_list
3564 ,p_validation_level => fnd_api.g_valid_level_full
3565 ,p_instance_rec => l_instance_rec
3566 ,p_expire_children => fnd_api.g_false
3567 ,p_txn_rec => l_transaction_rec
3568 ,x_instance_id_lst => l_upd_instance_id_lst
3569 ,x_return_status => x_return_status
3570 ,x_msg_count => x_msg_count
3571 ,x_msg_data => x_msg_data);
3572
3573 --okl_debug_pub.logmessage('csi_item_instance_pub.expire_item_instance x_return_status= ' || x_return_status);
3574 --dbms_output.put_line('csi_item_instance_pub.expire_item_instance x_return_status= ' || x_return_status);
3575 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3576 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3577 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3578 RAISE OKL_API.G_EXCEPTION_ERROR;
3579 END IF;
3580
3581 --okl_debug_pub.logmessage('Abandon Instance line: '||srl_num_to_exp_rec.inst_cle_id);
3582 --dbms_output.put_line('Abandon Instance line: '||srl_num_to_exp_rec.inst_cle_id);
3583
3584 l_clev_rec.id := srl_num_to_exp_rec.inst_cle_id;
3585 l_klev_rec.id := srl_num_to_exp_rec.inst_cle_id;
3586 l_clev_rec.sts_code := 'ABANDONED';
3587
3588 okl_contract_pub.update_contract_line(
3589 p_api_version => 1.0,
3590 p_init_msg_list => OKL_API.G_FALSE,
3591 x_return_status => x_return_status,
3592 x_msg_count => x_msg_count,
3593 x_msg_data => x_msg_data,
3594 p_clev_rec => l_clev_rec,
3595 p_klev_rec => l_klev_rec,
3596 x_clev_rec => x_clev_rec,
3597 x_klev_rec => x_klev_rec
3598 );
3599
3600 --okl_debug_pub.logmessage('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3601 --dbms_output.put_line('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3602
3603 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3604 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3605 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3606 RAISE OKL_API.G_EXCEPTION_ERROR;
3607 END IF;
3608
3609 --okl_debug_pub.logmessage('Abandon IB line: '||srl_num_to_exp_rec.ib_cle_id);
3610 --dbms_output.put_line('Abandon IB line: '||srl_num_to_exp_rec.ib_cle_id);
3611
3612 l_clev_rec.id := srl_num_to_exp_rec.ib_cle_id;
3613 l_klev_rec.id := srl_num_to_exp_rec.ib_cle_id;
3614 l_clev_rec.sts_code := 'ABANDONED';
3615
3616 okl_contract_pub.update_contract_line(
3617 p_api_version => 1.0,
3618 p_init_msg_list => OKL_API.G_FALSE,
3619 x_return_status => x_return_status,
3620 x_msg_count => x_msg_count,
3621 x_msg_data => x_msg_data,
3622 p_clev_rec => l_clev_rec,
3623 p_klev_rec => l_klev_rec,
3624 x_clev_rec => x_clev_rec,
3625 x_klev_rec => x_klev_rec
3626 );
3627
3628 --okl_debug_pub.logmessage('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3629 --dbms_output.put_line('okl_contract_pub.update_contract_line x_return_status= ' || x_return_status);
3630
3631 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3632 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3633 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3634 RAISE OKL_API.G_EXCEPTION_ERROR;
3635 END IF;
3636
3637 END LOOP;
3638
3639 -- Sync New IB instances added
3640
3641 FOR srl_num_to_add_rec IN srl_num_to_add_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3642 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3643 p_orig_chr_id => l_orig_k_id,
3644 p_rbk_chr_id => p_rbk_chr_id) LOOP
3645
3646 --okl_debug_pub.logmessage('Add IB Instance');
3647 --dbms_output.put_line('Add IB Instance');
3648
3649 --okl_debug_pub.logmessage('srl_num_to_add_rec.orig_ib_cle_id = ' || srl_num_to_add_rec.orig_ib_cle_id);
3650 --dbms_output.put_line('srl_num_to_add_rec.orig_ib_cle_id = ' || srl_num_to_add_rec.orig_ib_cle_id);
3651
3652 --okl_debug_pub.logmessage('srl_num_to_add_rec.orig_inst_cle_id = ' || srl_num_to_add_rec.orig_inst_cle_id);
3653 --dbms_output.put_line('srl_num_to_add_rec.orig_inst_cle_id = ' || srl_num_to_add_rec.orig_inst_cle_id);
3654
3655 Process_IB_Line_1(p_api_version => p_api_version,
3656 p_init_msg_list => p_init_msg_list,
3657 x_return_status => x_return_status,
3658 x_msg_count => x_msg_count,
3659 x_msg_data => x_msg_data,
3660 p_chrv_id => l_orig_k_id,
3661 p_inst_cle_id => srl_num_to_add_rec.orig_inst_cle_id,
3662 p_ib_cle_id => srl_num_to_add_rec.orig_ib_cle_id,
3663 x_party_tbl => l_party_tbl,
3664 x_party_account => l_party_account,
3665 x_inv_mstr_org_id => l_inv_mstr_org_id,
3666 x_model_line_qty => l_model_line_qty,
3667 x_primary_uom_code => l_primary_uom_code,
3668 x_inv_org_id => l_orig_inv_org_id);
3669
3670 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
3671 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3672 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3673 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3674 RAISE OKL_API.G_EXCEPTION_ERROR;
3675 END IF;
3676
3677 Process_IB_Line_2(p_api_version => p_api_version,
3678 p_init_msg_list => p_init_msg_list,
3679 x_return_status => x_return_status,
3680 x_msg_count => x_msg_count,
3681 x_msg_data => x_msg_data,
3682 p_chrv_id => l_orig_k_id,
3683 p_inst_cle_id => srl_num_to_add_rec.orig_inst_cle_id,
3684 p_ib_cle_id => srl_num_to_add_rec.orig_ib_cle_id,
3685 p_party_tbl => l_party_tbl,
3686 p_party_account => l_party_account,
3687 p_inv_mstr_org_id => l_inv_mstr_org_id,
3688 p_model_line_qty => l_model_line_qty,
3689 p_uom_code => l_primary_uom_code,
3690 p_trx_type => 'CFA',
3691 p_inv_org_id => l_orig_inv_org_id,
3692 p_rbk_ib_cle_id => srl_num_to_add_rec.rbk_ib_cle_id,
3693 x_cimv_rec => l_cimv_rec);
3694 --dbms_output.put_line('After Process_Line_1 '||x_return_status);
3695 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3696 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3697 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3698 RAISE OKL_API.G_EXCEPTION_ERROR;
3699 END IF;
3700 END LOOP;
3701 END IF;
3702
3703 IF l_rbk_serialized = OKL_API.G_FALSE THEN --- not serialized
3704 --okl_debug_pub.logmessage(' l_serialized is false -> is not serialized' );
3705 --dbms_output.put_line(' l_serialized is false -> is not serialized');
3706
3707 Open ib_line_csr(l_orig_k_id,l_orig_fin_ast_cle_id);
3708 Fetch ib_line_csr into ib_line_id,ib_line_cle_id;
3709 If ib_line_csr%NOTFOUND then
3710 null; --should not happen
3711 End If;
3712 CLose ib_line_csr;
3713
3714 --okl_debug_pub.logmessage(' ib_line_id = ' ||ib_line_id );
3715 --dbms_output.put_line(' ib_line_id = ' ||ib_line_id);
3716 --okl_debug_pub.logmessage(' ib_line_cle_id = ' ||ib_line_cle_id );
3717 --dbms_output.put_line(' ib_line_cle_id = ' ||ib_line_cle_id);
3718
3719 -- get the instance - id
3720 Open get_instance_id_csr(ib_line_id);
3721 Fetch get_instance_id_csr into l_instance_id;
3722 If get_instance_id_csr%NOTFOUND then
3723 null; --should not happen
3724 End If;
3725 Close get_instance_id_csr;
3726
3727 --okl_debug_pub.logmessage(' instance id = ' || l_instance_id );
3728 --dbms_output.put_line(' instance id = ' || l_instance_id);
3729
3730 Open ib_line_csr(p_rbk_chr_id,p_rbk_fin_ast_cle_id);
3731 Fetch ib_line_csr into rbk_ib_line_id,rbk_ib_line_cle_id;
3732 If ib_line_csr%NOTFOUND then
3733 null; --should not happen
3734 End If;
3735 CLose ib_line_csr;
3736
3737 l_instance_query_rec.instance_id := to_number(l_instance_id);
3738
3739 csi_item_instance_pub.get_item_instances (
3740 p_api_version => p_api_version,
3741 p_commit => FND_API.G_FALSE,
3742 p_init_msg_list => FND_API.G_FALSE,
3743 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3744 p_instance_query_rec => l_instance_query_rec,
3745 p_party_query_rec => l_party_query_rec,
3746 p_account_query_rec => l_account_query_rec,
3747 p_transaction_id => NULL,
3748 p_resolve_id_columns => FND_API.G_FALSE,
3749 p_active_instance_only => FND_API.G_TRUE,
3750 x_instance_header_tbl => l_instance_header_tbl,
3751 x_return_status => x_return_status,
3752 x_msg_count => x_msg_count,
3753 x_msg_data => x_msg_data);
3754
3755
3756 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3757 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3758 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3759 RAISE OKL_API.G_EXCEPTION_ERROR;
3760 END IF;
3761
3762 If l_instance_header_tbl.COUNT > 0 then
3763 l_instance_header_rec := l_instance_header_tbl(1);
3764 end if;
3765
3766 l_upd_instance_rec.instance_id := l_instance_header_rec.instance_id;
3767 l_upd_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3768 l_upd_instance_rec.quantity := l_rbk_model_line_qty;
3769
3770 --get trx record
3771 get_trx_rec(p_api_version => p_api_version,
3772 p_init_msg_list => p_init_msg_list,
3773 x_return_status => x_return_status,
3774 x_msg_count => x_msg_count,
3775 x_msg_data => x_msg_data,
3776 p_cle_id => ib_line_id,
3777 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3778 x_trx_rec => l_upd_txn_rec);
3779
3780 --okl_debug_pub.logmessage(' get_trx_rec x_return_status= ' || x_return_status);
3781 --dbms_output.put_line(' get_trx_rec x_return_status= ' || x_return_status);
3782
3783 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3784 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3785 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3786 RAISE OKL_API.G_EXCEPTION_ERROR;
3787 END IF;
3788
3789 csi_item_instance_pub.update_item_instance
3790 (
3791 p_api_version => p_api_version
3792 ,p_commit => fnd_api.g_false
3793 ,p_init_msg_list => p_init_msg_list
3794 ,p_validation_level => fnd_api.g_valid_level_full
3795 ,p_instance_rec => l_upd_instance_rec
3796 ,p_ext_attrib_values_tbl => l_upd_ext_attrib_values_tbl
3797 ,p_party_tbl => l_upd_party_tbl
3798 ,p_account_tbl => l_upd_account_tbl
3799 ,p_pricing_attrib_tbl => l_upd_pricing_attrib_tbl
3800 ,p_org_assignments_tbl => l_upd_org_assignments_tbl
3801 ,p_asset_assignment_tbl => l_upd_asset_assignment_tbl
3802 ,p_txn_rec => l_upd_txn_rec
3803 ,x_instance_id_lst => l_upd_instance_id_lst
3804 ,x_return_status => x_return_status
3805 ,x_msg_count => x_msg_count
3806 ,x_msg_data => x_msg_data
3807 );
3808 --serialized
3809 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3810 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3811 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3812 RAISE OKL_API.G_EXCEPTION_ERROR;
3813 END IF;
3814 END IF;
3815
3816 IF l_rbk_serialized = OKL_API.G_TRUE THEN
3817 -- Sync Serial Numbers
3818 FOR srl_num_to_update_rec IN srl_num_to_update_csr(p_orig_fin_ast_cle_id => l_orig_fin_ast_cle_id,
3819 p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3820 p_orig_chr_id => l_orig_k_id,
3821 p_rbk_chr_id => p_rbk_chr_id) LOOP
3822
3823
3824 l_srl_num_old_rec := NULL;
3825 OPEN srl_num_old_csr(p_instance_id => srl_num_to_update_rec.instance_id);
3826 FETCH srl_num_old_csr INTO l_srl_num_old_rec;
3827 CLOSE srl_num_old_csr;
3828
3829 l_srl_num_new_rec := NULL;
3830 OPEN srl_num_new_csr(p_rbk_fin_ast_cle_id => p_rbk_fin_ast_cle_id,
3831 p_rbk_ib_cle_id => srl_num_to_update_rec.rbk_ib_cle_id);
3832 FETCH srl_num_new_csr INTO l_srl_num_new_rec;
3833 CLOSE srl_num_new_csr;
3834
3835 IF NVL(l_srl_num_old_rec.serial_number,OKL_API.G_MISS_CHAR) <>
3836 NVL(l_srl_num_new_rec.serial_number,OKL_API.G_MISS_CHAR) THEN
3837
3838 --okl_debug_pub.logmessage('Update IB Instance');
3839 --dbms_output.put_line('Update IB Instance');
3840
3841 --okl_debug_pub.logmessage('srl_num_to_update_rec.orig_ib_cle_id = ' || srl_num_to_update_rec.orig_ib_cle_id);
3842 --dbms_output.put_line('srl_num_to_update_rec.orig_ib_cle_id = ' || srl_num_to_update_rec.orig_ib_cle_id);
3843
3844 l_instance_query_rec := l_instance_query_temp_rec;
3845 l_instance_header_rec := l_instance_header_temp_rec;
3846 l_upd_instance_rec := l_instance_temp_rec;
3847
3848 l_instance_query_rec.instance_id := srl_num_to_update_rec.instance_id;
3849
3850 csi_item_instance_pub.get_item_instances (
3851 p_api_version => p_api_version,
3852 p_commit => FND_API.G_FALSE,
3853 p_init_msg_list => FND_API.G_FALSE,
3854 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3855 p_instance_query_rec => l_instance_query_rec,
3856 p_party_query_rec => l_party_query_rec,
3857 p_account_query_rec => l_account_query_rec,
3858 p_transaction_id => NULL,
3859 p_resolve_id_columns => FND_API.G_FALSE,
3860 p_active_instance_only => FND_API.G_TRUE,
3861 x_instance_header_tbl => l_instance_header_tbl,
3862 x_return_status => x_return_status,
3863 x_msg_count => x_msg_count,
3864 x_msg_data => x_msg_data);
3865
3866
3867 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3869 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3870 RAISE OKL_API.G_EXCEPTION_ERROR;
3871 END IF;
3872
3873 If l_instance_header_tbl.COUNT > 0 then
3874 l_instance_header_rec := l_instance_header_tbl(1);
3875 end if;
3876
3877 l_upd_instance_rec.instance_id := l_instance_header_rec.instance_id;
3878 l_upd_instance_rec.object_version_number := l_instance_header_rec.object_version_number;
3879 l_upd_instance_rec.serial_number := l_srl_num_new_rec.serial_number;
3880
3881 --Call get_trx_rec
3882 get_trx_rec(p_api_version => p_api_version,
3883 p_init_msg_list => p_init_msg_list,
3884 x_return_status => x_return_status,
3885 x_msg_count => x_msg_count,
3886 x_msg_data => x_msg_data,
3887 p_cle_id => srl_num_to_update_rec.orig_ib_cle_id,
3888 p_transaction_type => G_IB_BKNG_TXN_TYPE,
3889 x_trx_rec => l_upd_txn_rec);
3890
3891 --okl_debug_pub.logmessage('get_trx_rec x_return_status = ' || x_return_status);
3892 --dbms_output.put_line('get_trx_rec x_return_status = ' || x_return_status);
3893
3894 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3895 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3896 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3897 RAISE OKL_API.G_EXCEPTION_ERROR;
3898 END IF;
3899
3900 csi_item_instance_pub.update_item_instance
3901 (
3902 p_api_version => p_api_version
3903 ,p_commit => fnd_api.g_false
3904 ,p_init_msg_list => p_init_msg_list
3905 ,p_validation_level => fnd_api.g_valid_level_full
3906 ,p_instance_rec => l_upd_instance_rec
3907 ,p_ext_attrib_values_tbl => l_upd_ext_attrib_values_tbl
3908 ,p_party_tbl => l_upd_party_tbl
3909 ,p_account_tbl => l_upd_account_tbl
3910 ,p_pricing_attrib_tbl => l_upd_pricing_attrib_tbl
3911 ,p_org_assignments_tbl => l_upd_org_assignments_tbl
3912 ,p_asset_assignment_tbl => l_upd_asset_assignment_tbl
3913 ,p_txn_rec => l_upd_txn_rec
3914 ,x_instance_id_lst => l_upd_instance_id_lst
3915 ,x_return_status => x_return_status
3916 ,x_msg_count => x_msg_count
3917 ,x_msg_data => x_msg_data
3918 );
3919
3920 --okl_debug_pub.logmessage('csi_item_instance_pub.update_item_instance 2 x_return_status= ' || x_return_status);
3921 --dbms_output.put_line('csi_item_instance_pub.update_item_instance 2 x_return_status= ' || x_return_status);
3922 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3923 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3924 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3925 RAISE OKL_API.G_EXCEPTION_ERROR;
3926 END IF;
3927
3928 END IF;
3929 END LOOP;
3930 END IF;
3931
3932 -- gboomina Bug 5362977 - End
3933 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3934 EXCEPTION
3935 When OKL_API.G_EXCEPTION_ERROR Then
3936 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3937 (
3938 l_api_name,
3939 G_PKG_NAME,
3940 'OKL_API.G_RET_STS_ERROR',
3941 x_msg_count,
3942 x_msg_data,
3943 '_PVT'
3944 );
3945 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3946 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3947 (
3948 l_api_name,
3949 G_PKG_NAME,
3950 'OKL_API.G_RET_STS_UNEXP_ERROR',
3951 x_msg_count,
3952 x_msg_data,
3953 '_PVT'
3954 );
3955 WHEN OTHERS THEN
3956 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3957 (
3958 l_api_name,
3959 G_PKG_NAME,
3960 'OTHERS',
3961 x_msg_count,
3962 x_msg_data,
3963 '_PVT'
3964 );
3965
3966 END RBK_SRL_NUM_IB_INSTANCE;
3967
3968 End OKL_ACTIVATE_IB_PVT;